Re: Storing time values via jdbc thin driver

On Nov 29, 11:30 pm, hpuxrac <johnbhur...@xxxxxxxxxxxxx> wrote:
On Nov 28, 11:02 am, "Chris Seidel" <csei...@xxxxxxxx> 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
.

Relevant Pages

  • Re: JDBC URL for Oracle Database With Failover (2 db servers)
    ... After you've loaded the driver, you can establish a connection using ... each form requires a database URL. ... For Oracle, the database URL has ...
    (comp.lang.java.databases)
  • Re: Access97 Pass-Thru Query giving Numeric Value out of range(#0)
    ... this is mapping error occurring due to the Oracle data type for the specified ... The reason of this problem may be the ODBC driver issue or the data type of the columnin the ... values go out of range for these data columns, the Oracle driver throws the error message. ...
    (microsoft.public.access.queries)
  • Re: Data source not found no default driver specified.
    ... VB6,TCP/IP,ADO FOR SERVER SIDE ... ORACLE 9I FOR DATABASE. ... >application.Have checked the oracle driver entry in the odbc data ...
    (comp.databases.oracle.server)
  • Data source not found no default driver specified.
    ... VB6,TCP/IP,ADO FOR SERVER SIDE ... ORACLE 9I FOR DATABASE. ... application.Have checked the oracle driver entry in the odbc data ...
    (comp.databases.oracle.server)
  • RE: ORACLE DRIVER
    ... DBI - is the perl module to handle all the request of the database, connecting, disconnecting, routing the database request to proper driver. ... DBD::Oracle - is the Oracle driver that DBI can use to handle oracle request. ...
    (perl.dbi.users)