ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Roger Voss" <rog...@qwest.net>
Subject Re: Oracle SQL DATE conversion problem using iBATIS via Java JDBC
Date Sun, 21 Dec 2008 08:53:35 GMT

I rediscovered some of what you have reported here. That was an
extraordinary marshalling of info to explain things in detail!

I also found a happy way to solve this in context of using iBATIS and JDBC.
I posted my fix back on stackoverflow:

using iBATIS custom type handler to address Oracle DATE

I've essentially reverted DATE mapping behavior back to prior Oracle JDBC
driver 9.2 approach. This worked just fine and I'm now a happy camper.


On Sun, Dec 21, 2008 at 12:14 AM, Richard Yee <ryee@cruzio.com> wrote:

>  Roger,
> See:
> http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01
> Specifically:
> Simple Data Types What is going on with DATE and TIMESTAMP?
> This section is on *simple* data types. :-)
> Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to
> java.sql.Timestamp. This made a certain amount of sense because the Oracle
> DATE SQL type contains both date and time information as does
> java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat
> problematic as java.sql.Date does not include time information. It was
> also the case that the RDBMS did not support the TIMESTAMP SQL type, so
> there was no problem with mapping DATE to Timestamp.
> In 9.2 TIMESTAMP support was added to the RDBMS. The difference between
> DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does
> not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped
> to Timestamp. Unfortunately if you were relying on DATE values to contain
> time information, there is a problem.
> There are several ways to address this problem:
>    -
>    Alter your tables to use TIMESTAMP instead of DATE. This is probably
>    rarely possible, but it is the best solution when it is.
>     -
>    Alter your application to use defineColumnType to define the columns as
>    TIMESTAMP rather than DATE. There are problems with this because you
>    really don't want to use defineColumnType unless you have to (see What
>    is defineColumnType and when should I use it?<http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#06_01>).
>     -
>    Alter you application to use getTimestamp rather than getObject. This
>    is a good solution when possible, however many applications contain generic
>    code that relies on getObject, so it isn't always possible.
>     -
>    Set the V8Compatible connection property. This tells the JDBC drivers
>    to use the old mapping rather than the new one. You can set this flag either
>    as a connection property or a system property. You set the connection
>    property by adding it to the java.util.Properties object passed to
>    DriverManager.getConnection or to
>    OracleDataSource.setConnectionProperties. You set the system property
>    by including a -D option in your java command line.
>    java -Doracle.jdbc.V8Compatible="true" MyApp
>  Oracle JDBC 11.1 fixes this problem. Beginning with this release the
> driver maps SQL DATE columns to java.sql.Timestamp by default. There is no
> need to set V8Compatible to get the correct mapping. V8Compatible is
> strongly deprecated. You should not use it at all. If you do set it to true
> it won't hurt anything, but you should stop using it.
> Although it was rarely used that way, V8Compatible existed not to fix the
> DATE to Date issue but to support compatibility with 8i databases. 8i (and
> older) databases did not support the TIMESTAMP type. Setting V8Compatiblenot only caused
SQL DATE to be mapped to
> Timestamp when read from the database, it also caused all Timestamps to be
> converted to SQL DATE when written to the database. Since 8i is desupported,
> the 11.1 JDBC drivers do not support this compatibility mode. For this
> reason V8Compatible is desupported.
> As mentioned above, the 11.1 drivers by default convert SQL DATE to
> Timestamp when reading from the database. This always was the right thing
> to do and the change in 9i was a mistake. The 11.1 drivers have reverted to
> the correct behavior. Even if you didn't set V8Compatible in your
> application you shouldn't see any difference in behavior in most cases. You
> may notice a difference if you use getObject to read a DATE column. The
> result will be a Timestamp rather than a Date. Since Timestamp is a
> subclass of Date this generally isn't a problem. Where you might notice a
> difference is if you relied on the conversion from DATE to Date to
> truncate the time component or if you do toString on the value. Otherwise
> the change should be transparent.
> If for some reason your app is very sensitive to this change and you simply
> must have the 9i-10g behavior, there is a connection property you can set.
> Set mapDateToTimestamp to false and the driver will revert to the default
> 9i-10g behavior and map DATE to Date.
> If possible, you should change your column type to TIMESTAMP instead of
> -Richard
> 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 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.

View raw message