Oracle 9i TIMESTAMP in primary key problems

[Bob Davison <Bo...@reuters.com>]

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