Re: Storing time values via jdbc thin driver

On Nov 29, 11:30 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Nov 28, 11:02 am, "Chris Seidel" <csei..._at_arcor.de> wrote:
>
> > Hi,
>
> > I have an Oracle 9.2 database.
> > If I use the 9.2 driver java.sql.Time is stored with 1900-01-01 HH:MM.
> > If I use the 10.2 driver java.sql.Time is stored with 1970-01-01 HH:MM.
>
> > Is this a know bug/feature?
> > Do I have to update all my records to 1970 when using oracle 10 driver?
>
> > Thank you
>
> I don't understand exactly what you are trying to do.
>
> Are you trying to store a time value from an application into a column
> in oracle which is in reality a date datatype in oracle?
>
> If that's the case ... oracle stores both a date and a time in a date
> datatype.
>
> If so ... you need to be careful about a database design where you are
> only using "part" of an oracle datatype.
>
> It is possible to create a char definition of length 8 for a column
> and from the application store in a field that looks like HH:MM:SS
> ( Hour Hour Minute Minute Second Second ).
>
> Of course it is also possible to use only the part of the date
> datatype that has time information and ignore/mask out the date
> portion if you know how to do it.
>
> There is also a datatype known as systimestamp that gives you better
> precision below the second level for a time value ... yes it also has
> a date part that also you can "ditch and/or ignore".
>
> Best place to start might be looking at the Oracle Concepts
> documentation and make sure that you understand the datatypes your
> application is trying to process against.

The trouble the OP has is that Oracle JDBC driver implementations of the same Java type persistence are different between driver versions. That this type is initially inappropriate because it is not natively supported by the database is irrelevant to the case IMO. A lot of people commented in this thread and all of them seem to agree that choice of the data type was inappropriate because Oracle does not support it, but that was not the question asked.

The issue here is that JDBC is supposed to be platform-independent, it should work the same with any database, at least with *standard* types (and TIME is a standard *core* SQL data type. By the way, Oracle documentation claims *full* conformance to the Core SQL:2003 feature F051 "Basic date and time", which mandates support of the TIME data type.) Let's face it: Java programmers are not supposed to know specifics of particular database platform they stuff their data into, standard APIs should work the same against any database. For them, the database is just some data storage, they don't really care how it works and if it's any different from some other database. The database vendor is expected to provide the driver that takes care of specific implementation details, and Oracle claims to have provided such driver. However, different versions of this driver give different results for the same basic calls using only standard types. Then you guys jump in and say that TIME is not supported in Oracle - so what? The JDBC driver should take care of this, and it should do so in consistent manner not changing between versions (I should stress we are talking about core standard stuff here.) However, it does not - its behavior changed since 10g.

Answering OP's question: this is a known behavior change between 8i/9i and 10g JDBC drivers, documented in the Metalink note 269517.1 and bug #3038799. If you are sure you will stick to 10g driver, just update all rows so that the date component is Jan 1, 1970, I think this should do the trick. If you are going to use different driver versions, either stop using literals and start using binds with properly initialized date component (the note recommends to always initialize a Calendar object to Jan 1st, 1970 and then instantiate java.sql.Time from it) or choose a different data type (INTERVAL DAY TO SECOND, for example.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sun Nov 30 2008 - 13:12:13 CST