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
test.jar
Description: application/java-archive
------------------------------------------------- If you wish to unsubscribe from this list, please send an empty message to the following address: [EMAIL PROTECTED] -------------------------------------------------
