Problem with PreparedStatement.setTimestamp, ODBC & timezone
I'm having a problem with PreparedStatement.setTimestamp. I'm trying to set a timestamp in a MSSQL table using GMT timezone, so I'm using the version of setTimestamp that lets me specify a calendar (void setTimestamp(int parameterIndex, Timestamp x, Calendar cal)).
The problem I'm having is that when I use the JDBC-ODBC bridge, it's not taking into account the calendar (and timezone) that I specify in setTimestamp(). When I do the same exact thing using a different, non-ODBC driver, it works. The non-odbc driver I'm using is net.sourceforge.jtds.jdbc.Driver. I've tried 2 different JDBC-ODBC bridge drivers (sun.jdbc.odbc.JdbcOdbcDriver and one from Easysoft). They both have this problem.
Here is code that demonstrates my problem. If anyone has any experience with this or knows anything about it, any help would be greatly appreciated.
// Sample code to write the current time to a MSSQL table with a datetime field.
// The table being used is:
// CREATE TABLE test(f1 datetime NULL)
publicclass x
{
publicstaticvoid main(String [] args)throws Exception
{
// THIS DOESN'T WORK--WHEN I READ BACK THE TIME ITS NOT RELATIVE TO GMT:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection dbconn =
java.sql.DriverManager.getConnection("jdbc:odbc:mydsn","myuser","mypassword");
// THIS WORKS--WHEN I READ BACK THE TIME IT IS RELATIVE TO GMT:
// Class.forName("net.sourceforge.jtds.jdbc.Driver");
// java.sql.Connection dbconn =
//java.sql.DriverManager.getConnection(
//"jdbc:jtds:sqlserver://localhost:1433/mydatabase", "myuser", "mypassword");
java.sql.PreparedStatement stmt = dbconn.prepareStatement("INSERT INTO test(f1) VALUES (?)");
java.util.Date dt =new java.util.Date();
java.sql.Timestamp ts =new java.sql.Timestamp(dt.getTime());
stmt.setTimestamp(1, ts, m_gmtCalendar);
stmt.executeUpdate();
stmt.close();
dbconn.close();
}
privatestaticfinal java.util.Calendar m_gmtCalendar =
java.util.Calendar.getInstance(java.util.TimeZone.getTimeZone("GMT"));
}
Message was edited by:
martinog2
