ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Richard Yee <r...@cruzio.com>
Subject Re: Oracle SQL DATE conversion problem using iBATIS via Java JDBC
Date Sun, 21 Dec 2008 08:02:15 GMT
Perhaps you should use TIMESTAMP instead of DATE.

-R

Roger Voss wrote:
> I posted following question/problem on stackoverflow, so if anyone 
> knows a resolution, would be good to see it answered there:
>
> Oracle SQL DATE conversion problem using iBATIS via Java JDBC 
> <http://stackoverflow.com/questions/383783/oracle-sql-date-conversion-problem-using-ibatis-via-java-jdbc>
>
> Here's the problem description:
>
> I'm currently wrestling with an Oracle sql DATE conversion problem 
> using iBATIS from Java.
>
> Am using the Oracle JDBC thin driver ojdbc14 version 10.2.0.4.0. 
> iBATIS version 2.3.2. Java 1.6.0_10-rc2-b32.
>
> The problem revolves around a column of DATE type that is being 
> returned by this snippet of SQL:
>
>   SELECT *
>   FROM   TABLE(pk_invoice_qry.get_contract_rate(?,?,?,?,?,?,?,?,?,?)) 
> order by from_date
>
> The package procedure call returns a ref cursor that is being wrapped 
> in a TABLE to where is then easy to read the result set as though were 
> a select query against a table.
>
> In PL/SQL Developer, one of the columns returned, FROM_DATE, of SQL 
> DATE type, has precision to time of day:
>
>     Tue Dec 16 23:59:00 PST 2008
>
> But when I access this via iBATIS and JDBC, the value only retains 
> precision to day:
>
>     Tue Dec 16 12:00:00 AM PST 2008
>
> This is clearer when displayed like so:
>
> Should have been:
> 1229500740000 milliseconds since epoch
> Tuesday, December 16, 2008 11:59:00 PM PST
>
> But getting this instead:
> 1229414400000 milliseconds since epoch
> Tuesday, December 16, 2008 12:00:00 AM PST
> (as instance of class java.sql.Date)
>
> No matter what I try, I am unable to expose the full precision of this 
> DATE column to be returned via Java JDBC and iBATIS.
>
> What iBATIS is mapping from is this:
>
> FROM_DATE : 2008-12-03 : class java.sql.Date
>
> The current iBATIS mapping is this:
>
> <result property="from_date" jdbcType="DATE" javaType="java.sql.Date"/>
>
> I've also tried:
>
> <result property="from_date" jdbcType="DATETIME" 
> javaType="java.sql.Date"/>
>
> or
>
> <result property="from_date" jdbcType="TIMESTAMP" 
> javaType="java.sql.Timestamp"/>
>
> But all attempted mappings yield the same truncated Date value. It's 
> as though JDBC has already done the damage of loosing data precision 
> before iBATIS even touches it.
>
> Clearly I'm loosing some of my data precision by going through JDBC 
> and iBATIS that is not happening when I stay in PL/SQL Developer 
> running the same SQL snippet as a test script. Not acceptable at all, 
> very frustrating, and ultimately very scary.
>


Mime
View raw message