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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Thursday, April 01, 2004 8:59 AM To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] ----------------------------------------------------------------- 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
