db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bob Davison <Bob.Davi...@reuters.com>
Subject RE: Oracle 9i TIMESTAMP in primary key problems
Date Thu, 01 Apr 2004 16:21:13 GMT
Maury,

Many people had problems with Oracle 9i JDBC drivers and Oracle DATE
columns, the hours/minutes/seconds were getting set to zero.  This was
due to a change Oracle made to their drivers when they introduced the
TIMESTAMP type in Oracle 9i.  What they did was map their DATE datatype
to java.sql.Date (rather than java.sql.Timestamp in 8i), not a good idea
really as Oracle DATE stores day/time to second granularity and
java.sql.Date only stores day information, all times are set to zero.
Maybe the introduction of their own TIMESTAMP type forced the issue,
anyway, whatever the reason this is what they did and they didn't
consider it a bug.

To fix this in Torque some helpful soul issued a special release of the
Village software which Torque uses.  This fix remaps the sql.Date back
to a sql.Timestamp, thus restoring everyone's missing time info.

So it seems that if you use Oracle 9i you can ...
 - use Oracle DATE and run with Oracle 8i drivers
 - use Oracle DATE, run with Oracle 9i drivers and the Village patch
 - use Oracle TIMESTAMP, run with Oracle 9i drivers but don't use dates
in primary keys

.../Bob

-----Original Message-----
From: Jarrell, Maury [mailto:Maury.Jarrell@fhr.com] 
Sent: 01 April 2004 16:30
To: 'Apache Torque Users List'
Subject: RE: Oracle 9i TIMESTAMP in primary key problems


Bob,

What were the original problems you encountered?  I did a couple of
searches on the list archive and couldn't find them.

Thanks,
Maury

-----Original Message-----
From: Bob Davison [mailto:Bob.Davison@reuters.com] 
Sent: Thursday, April 01, 2004 8:59 AM
To: torque-user@db.apache.org
Subject: Oracle 9i TIMESTAMP in primary key problems

Hi folks,

More problems with the Oracle 9i DATE vs TIMESTAMP issue.

Due to the problems with the Oracle 9i JDBC drivers and DATE columns we
moved to TIMESTAMP columns instead.  This seemed fine until we used one
of these columns in a primary key.

When Torque does an update or delete it builds a WHERE clause based on
the primary key fields, if any of these are dates then it calls the
getDateString() on the DB adapter class.  For DBOracle this results in a
call to the Oracle TO_DATE(...) function.  This function only handles
second granularity, sub-second information is lost, so if your record
had sub-second information in the TIMESTAMP field then the WHERE clause
will fail to identify your record and the UPDATE or DELETE statement
will silently fail to update or delete your record.

It would be possible to modify DBOracle.getDateString to call the Oracle
9i TO_TIMESTAMP() function but that would upset Oracle 8i and earlier
and may not work with DATE columns in Oracle 9i.

I can't see a way to work around these Oracle DATE/TIMESTAMP issues.  We
have put an auto-increment primary key on this table now just so updates
and deletes work.

.../Bob




--------------------------------------------------------------- -
        Visit our Internet site at http://www.reuters.com

Get closer to the financial markets with Reuters Messaging - for more
information and to register, visit http://www.reuters.com/messaging

Any views expressed in this message are those of  the  individual
sender,  except  where  the sender specifically states them to be the
views of Reuters Ltd.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org



-----------------------------------------------------------------
        Visit our Internet site at http://www.reuters.com

Get closer to the financial markets with Reuters Messaging - for more
information and to register, visit http://www.reuters.com/messaging

Any views expressed in this message are those of  the  individual
sender,  except  where  the sender specifically states them to be
the views of Reuters Ltd.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message