|
I've been working on modifying some Java code that accesses a SQLAnywhere 12 database. I've just updated the JDBC drivers to use the sajdbc4.jar file, and so far it's working fine. One of the columns in the database is a timestamp, but it's been causing problems because it holds data from multiple timezones. I would like to convert the column to a "timestamp with time zone". But my test harness is now throwing the following error: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] at java.sql.Timestamp.valueOf(Timestamp.java:246) at sybase.jdbc4.sqlanywhere.IConnection.parseTimestamp(IConnection.java:751) at sybase.jdbc4.sqlanywhere.IIResultSet.getTimestamp(Native Method) at sybase.jdbc4.sqlanywhere.IResultSet.getTimestamp(IResultSet.java:541) Does the latest iAnywhere JDBC driver support timezone with time stamp? Or is there a specific method that must be called on the driver to get this data? |
|
After an internal discussion with iAnywhere engineering, it turns out this is a bug in the driver software. A case has been opened to fix it, so a patch should be out sometime soon. In the meantime, this is what happened: The driver is handing a string to The following code will perform a conversion for you. This code is server time zone independent. The time is translated in to a date object on the client, and can be converted using standard java public static Timestamp attemptConversionOfTimeZoneColumn( String rawData ) throws ParseException, SQLException { // SQLAnywhere sends us the time zone in +XX:XX format. // DateFormat wants this in +XXXX format. // Pull the last colon character out of the string, then send the converted string to parse() String parsedString; int colonPosition = rawData.trim().indexOf( ':', rawData.length() - 5 ); if ( colonPosition > 0 ) parsedString = rawData.substring( 0, colonPosition ) + rawData.substring( colonPosition + 1 ); else parsedString = rawData; final DateFormat df = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss.SSSSSS Z" ); Date testDate = df.parse( parsedString ); if ( testDate != null ) return new Timestamp( testDate.getTime() ); else throw new SQLException("Unable to convert '" + rawData + "' in to a Timestamp."); } |
|
As with the SQL Anywhere ODBC Driver, the SA JDBC driver also supports timestamp with time zone but only for display purposes (i.e. it treats these values as strings not timestamps). Calling getTimeStamp() will therefore cause the above error. Instead, your best bet is to prepare a statement that casts the timestamp with time zone value as a timestamp. That will allow the server to "do the right thing" with the timestamp with time zone value. You can then use getTimestamp() on the result of the cast. |
Just another very wild guess: May you use the Resultset.getTimestamp(String columnLabel, Calendar cal) method, i.e. additionally specifying a Calender object to hold the time zone information? Or use a generic getObject() method?
Cf. the following doc for IBM's JDBC driver - I do not know at all if this applies to the SQL Anywhere driver or not. - Just wild guesses...
Is your field a normal timestamp field, or a new one with timezone information ?
http://dcx.sybase.com/1200/en/dbreference/timestamp-with-timezone.html
When you use the simple timestamp database type, then there is no way to store/retrieve the timezone....
It's a timestamp with time zone column.
The call getTimeStamp(columnLabel, calendar) is used to translate a "time zone missing" data in to a specific time zone. And that's one possible way to solve the problem (decide on a time zone for the database, and have the software convert everything in to that time zone).
The problem, however, is that reporting software doesn't have any idea about that "we picked it" time zone. So I would like to store the time zone in the field, to prevent ambiguity.
IBM, FYI, handles this cleanly. Their driver translates the time while creating a Timezone object. It appears (from the stack trace above) that the iAnywhere driver doesn't do this. It just hands the data off, and assumes it's correct.
I'm not sure the IBM way is better. If the drivers are running on the end users time zone, then OK
But when accessing via web services or web pages the driver is the "located" on the server time zone. And it's then still up to you to do the conversions..
Sorry, but we haven't used them currently. Still too many installations with asa 9-11 out there
I think you are missing the bigger picture. The iAnywhere driver won't even LOAD a time with time zone field. It just throws an exception.