August, If the patch is Oracle-specific, SQLTypes is definitely not the right place to apply a patch. Having said that, let's first have a look at the patch. For this, can you please create a Jira issue at http://jira.codehaus.org/browse/CASTOR, and attach all relevant information (incl. A possible patch) ?
Thanks Werner > -----Original Message----- > From: August Detlefsen [mailto:[EMAIL PROTECTED] > Sent: Samstag, 15. April 2006 03:09 > To: dev@castor.codehaus.org > Subject: [castor-dev] More on the Oracle bad dates issue > > I think I have tracked the bad dates issue ( > http://jira.codehaus.org/browse/CASTOR-1341 ) down to a flaw > in the Oracle JDBC driver. > > In database version 10.2, with the latest JDBC driver (v > 10.2.0.1) oracle.sql.TIMESTAMP.timestampValue() does not > return correct values for BC dates. The date is always off by > one year from the expected/actual value when the actual date > is in the BC era. > > Create the test table (see below) and then run the attached > test file with: > > java -classpath /path/to/ojdbc14.jar:test.jar > test.OracleTimestampTest \ username password > jdbc:oracle:thin:username/[EMAIL PROTECTED]:1521:SID > oracle.jdbc.driver.OracleDriver > > The class uses pure JDBC (I wanted to eliminate Castor as a > potential point of failure) to select a timestamp as a > Timestamp, String and Byte Array and will produce output like > the following. Notice that for the BC dates, the 'Java > Timestamp (via timestampValue())' line is always one year > less than the expected value returned by to_char (The > 'Testing Date:' line): > > Testing Date: 0748-04-21 12:18:58 BC with bytes: Typ=180 Len=7: > 93,52,4,21,13,19,59 > Timestamp via ResultSet.getObject: > [EMAIL PROTECTED] > Timestamp via ResultSet.getTimestamp(): > 0748-04-21 12:18:58 BC > Oracle Timestamp: > Timestamp Bytes (via oracle.sql.TIMESTAMP.toBytes()): > 93, 52, 4, 21, 13, 19, 59 > Java Timestamp (via > oracle.sql.TIMESTAMP.timestampValue()): 0749-04-21 12:18:58 BC > > Testing Date: 0044-03-15 11:23:45 BC with bytes: Typ=180 Len=7: > 100,56,3,15,12,24,46 > Timestamp via ResultSet.getObject: > [EMAIL PROTECTED] > Timestamp via ResultSet.getTimestamp: > 0044-03-15 11:23:45 BC > Oracle Timestamp: > Timestamp Bytes (via oracle.sql.TIMESTAMP.toBytes()): > 100, 56, 3, 15, 12, 24, 46 > Java Timestamp (via > oracle.sql.TIMESTAMP.timestampValue()): 0045-03-15 11:23:45 BC > > > The bytes returned by TIMESTAMP.toBytes() are always the same > as those from dump(the_timestamp, 10) so the error must lie > in the toTimestamp() method. > > How does this apply to Castor you ask? > > I dug in to the SQLTypes class to find out where Castor gets > Timestamps from (in the getObject method). I modified the > Types.TIMESTAMP case to add some logging and found the same > issue with BC dates was happening: > > case Types.TIMESTAMP: > log.debug("getObject() TIMESTAMP called"); > > Object tempTs = rs.getTimestamp( index, getCalendar() ); > > if (tempTs != null) { > log.debug(" Returning Timestamp: " + > LOG_FORMAT.format( (Timestamp)tempTs )); > } else { > log.debug(" Returning Timestamp: null"); > } > > return tempTs; > > Here is some logging output from an application that uses > this modifed SQLTypes. Again I used direct JDBC and to_char > to get the expected dates: > > 04-14 17:50:05 [DEBUG] [LoginController] - USER DATES > SELECTED VIA JDBC: > 04-14 17:50:05 [DEBUG] [LoginController] - CREATED: > 12-25-0003 02:21:12 AD > 04-14 17:50:05 [DEBUG] [LoginController] - MODIFIED: > 04-14-1817 02:38:47 BC > // Corresponds to CREATED date: > 04-14 17:50:06 [DEBUG] [Class] - getObject() TIMESTAMP called > 04-14 17:50:06 [DEBUG] [Class] - Timestamp Class: class > java.sql.Timestamp > 04-14 17:50:06 [DEBUG] [Class] - Value: > 12-25-0003 02:21:12 AD > // Corresponds to MODIFIED date: > 04-14 17:50:06 [DEBUG] [Class] - getObject() TIMESTAMP called > 04-14 17:50:06 [DEBUG] [Class] - Timestamp Class: class > java.sql.Timestamp > 04-14 17:50:06 [DEBUG] [Class] - Value: > 04-14-1818 02:38:47 BC > > Note that the MODIFIED date is off by 1 year. This leads me > to believe that ResultSet.getTimestamp() in the Oracle JDBC > drivers is using the flawed toTimestamp() method in their > internal oracle.sql.TIMESTAMP class to produce a > java.sql.Timestamp(). (I'd love to know for sure if anyone > could point me to the Oracle JDBC source code) > > I have a fix for Castor that correctly converts the bytes in > the oracle.sql.TIMESTAMP, but is SQLTypes the correct place > for the fix? > > Is there a place in Castor for Oracle-specific tweaks like this? > > Thanks, > August > > > To create the test entries: > > CREATE TABLE test_table ( > the_timestamp TIMESTAMP > ); > > INSERT INTO test_table (the_timestamp) VALUES ( > TO_DATE('0044-03-15 11:23:45 BC', 'YYYY-MM-DD HH24:MI:ss AD') > ); INSERT INTO test_table (the_timestamp) VALUES ( > TO_DATE('0748-04-21 12:18:58 BC', 'YYYY-MM-DD HH24:MI:ss AD') ); > > > The query run by the testing class: > > SELECT TO_CHAR(the_timestamp, 'YYYY-MM-DD HH24:mi:ss AD') AS > the_chars, the_timestamp, DUMP(the_timestamp, 10) AS > the_bytes FROM test_table ORDER BY the_timestamp ASC > > > > ------------------------------------------------- If you wish to unsubscribe from this list, please send an empty message to the following address: [EMAIL PROTECTED] -------------------------------------------------
