 DB date precision missing |
Joined: 10-April-2008 Posts: 5 | Posted:
10-April-2008 02:02 Hi, I'm trying to get a timestamp using the mod-db with oracle. when I say SELECT session_start FROM ....it only gives me the date part of it, in the form 2008-04-09. Whereas if I do the exact same thing from JDBC, I get the full precision. Nothing seems to persuade Netkernel to do otherwise. I saw it with a 'date' type object, and a co-worker reports that the 'timestamp' object behaves the same way. To make matters worse, when I try to implement a write-around by using the to_char function to format the date, I start getting a SAX error: org.xml.sax.SAXParseException Element type "TO_CHAR" must be
followed by either attribute specifications, ">" or "/>".
executing subrequest: [SOURCE active:dwQuery+operand@var%3Aoperand
in urn:org:ten60:netkernel:ext:sys as
com.ten60.netkernel.urii.IURAspect] |
If I say 'to_blah' instead of 'to_char,' it gives me a (reasonable) oracle error. So it looks to me like several bugs at work. Are these known? Is there a writearound? Thanks, -= miles =-More code ... ...
var query = <sql>
select to_char(session_start, 'YYYY-MM-DD HH24:MI:SS') from
pc_session
</sql>
reply = queryDW( query ); // queryDW sets up & issues the subrequest
|
|
| Re: DB date precision missing |
Joined: 7-February-2005 Posts: 397 Location: UK | Posted:
10-April-2008 08:16 Hi miles, The lack of precision is implementation specific to the oracle driver. When the XMLized resultset is created the toString() method of field value is called. With other JDBC drivers this returns full precision but with oracle it returns just the date part. You're close to having the workaround. Your select statement needs to give the selected table a column name otherwise the XMLization will put an empty string in the element name and that results in the SAX parse exception. (We should add some checking to catch this!). Please try this...
var query = <sql>
select to_char(session_start, 'YYYY-MM-DD HH24:MI:SS') as dateWorkAround from
pc_session
</sql>
reply = queryDW( query ); // queryDW sets up & issues the subrequest
|
You should now get an XML doc... <results>
<row>
<dateWorkAround>YYYY-MM-DD HH24:MI:SSM</dateWorkAround>
</row>
</results> |
FYI you are not limited to returning XML from the DB queries - this is a convenient default. If necessary you can always ask for the response as an IAspectResultSet - which provides the JDBC ResultSet object. http://docs.1060.org/docs/3.3.0/book/developerreference/doc_aspect_IAspectResultSet.htmlLet us know how you get on. P. |
| more clues? |
Joined: 10-April-2008 Posts: 5 | Posted:
14-April-2008 21:11 So as it turns out, one of the other developers here at findlaw knew about this solution of using 'as'
As pertains to IAspectResultSet, the documentation is a bit short of details, particularly regarding how I would use it from javascript.
In what sense does it "hold" a result set? Could I maybe see a brief example?
Thanks,
-= miles =-
|
| Java JDBC ResultSet |
Joined: 7-February-2005 Posts: 397 Location: UK | Posted:
15-April-2008 14:53 Hi Miles, The IAspectResultSet holds a JDBC ResultSet object which can be obtained like this... import org.ten60.rdbms.representation.IAspectResultSet
import java.sql.*;
req=context.createSubRequest("active:sql);
req.addArgument("operand","my-sql-query.xml");
req.setAspectClass(IAspectResultSet.clasS);
resp=context.issueSubRequestForAspect(req);
resultset=resp.getResultSet(); |
Take care when using the raw ResultSet - this is down at raw Java and is not managed by the NK infrastructure. Depending on your JDBC driver/DB it will be mutable (so use of it is not cacheable in NK) and it is bound to a live connection so can cause contention of your connection pool. Cheers, Peter |
| Javascript example? |
Joined: 10-April-2008 Posts: 5 | Posted:
17-April-2008 23:22 Most of what we do is just basically get the data and stuff it into XML to return it. So contention with other threads shouldn't generally be an issue. It seems like just getting the resultset and looping through using next() would be more straightforward than looping through an XML structure, which is what a lot of our existing code does.
However, much our framework is based in javascript. Is there a way to implement the example you show in javascript?
If not, then I'll do it in java, but I have not had a need to use Java yet inside of netkernel.
Thanks,
-= miles =- |
| Stick with XML |
Joined: 15-February-2005 Posts: 127 Location: Fort Collins, CO | Posted:
18-April-2008 00:04 Miles,
I was reading this thread and I'm not sure if you have given up on using active:sqlQuery because of the date issue or not. I suggest that if you can solve the Oracle specific issue and use active:sqlQuery and then use the returned XML you will be best served in the long run.
If you think you must use a ResultSet directly then yes, program it in Java but I still would suggest that you return an XML document.
Randy |
| java/javascript |
Joined: 10-April-2008 Posts: 5 | Posted:
18-April-2008 02:51 Mostly I was curious from a standpoint of understanding how to apply the documentation to our applications, which are mostly written in javascript.
thanks,
-= miles =- |
| Javascript Example |
Joined: 7-February-2005 Posts: 397 Location: UK | Posted:
18-April-2008 08:07 The language you use on NK doesn't really matter - ultimately they all get compiled to bytecode and they all have access to the kernel/address space through the NKF context object. Here's the same script in javascript... importPackage(Packages.org.ten60.rdbms.representation)
importPackage(Packages.java.sql)
req=context.createSubRequest("active:sql);
req.addArgument("operand","my-sql-query.xml");
req.setAspectClass(IAspectResultSet);
resp=context.issueSubRequestForAspect(req);
resultset=resp.getResultSet(); |
Cheers, P. |
