Uploaded image for project: 'JDK'
  1. JDK
  2. JDK-6596714

PreparedStatement.setTimestamp(int,Timestamp,Calendar) produces incorrect values

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: P5
    • Resolution: Future Project
    • Affects Version/s: 6
    • Fix Version/s: None
    • Component/s: core-libs
    • Labels:

      Description

      FULL PRODUCT VERSION :
      java version "1.6.0_02"
      Java(TM) SE Runtime Environment (build 1.6.0_02-b06)
      Java HotSpot(TM) Client VM (build 1.6.0_02-b06, mixed mode, sharing)

      ADDITIONAL OS VERSION INFORMATION :
      Microsoft Windows XP [Version 5.1.2600]

      EXTRA RELEVANT SYSTEM CONFIGURATION :
      Microsoft Office Access 2003 (11.6566.8132) SP2

      A DESCRIPTION OF THE PROBLEM :
      The setTimestamp(int, Timestamp, Calendar) produces incorrect values in an Access database.

      The method appears use the default Calendar and then adjust by subtracting the GMT offset of the Calendar provided in its parameter. The result is that the method places an incorrect value in the database in almost all circumstances.
      This is just plain wrong; the behaviour of the method should not be affected by the default timezone. That's the whole point of the method!

      I suspect this bug is closely related to bug #4881343. My guess is that the implementation of PreparedStatement.setTimestamp(int,Timestamp,Calendar) was made by the same person who tried to fix ResultSet.getTimestamp(int,Calendar), and they made the same mistake in both places.

      STEPS TO FOLLOW TO REPRODUCE THE PROBLEM :
      1. Create an Access database.
      2. Create a table in the database with a DATETIME column.
      3. Obtain a connection to the database.
      4. Create a prepared statement for inserting values into aforementioned table. Make the value to be inserted into the column a parameter of the prepared statement.
      5. Use the PreparedStatement.setTimestamp(int,Timestamp,Calendar) method to set the parameter that corresponds to the DATETIME column.

      EXPECTED VERSUS ACTUAL BEHAVIOR :
      EXPECTED -
      I execute the sample code provided below. It is inserting the timestamp value 2007-08-09 01:35:11 (GMT) into the database in a variety of ways. I expect to see the following values inserted into the database:

      << Following 5 values inserted while default timezone is "Australia/Adelaide" GMT offset +0930 >>
      2007-08-09 11:05:11 <- From setTimestamp(int,Timestamp) when default timezone is +9300
      2007-08-09 11:05:11 <- From setTimestamp(int,Timestamp,Calendar) with default calendar given
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0000 calendar given
      2007-08-09 11:05:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0930 calendar given
      2007-08-08 20:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT-0500 calendar given

      << Following 5 values inserted while default timezone is GMT >>
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp) when default timezone is +0000
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp,Calendar) with default calendar given
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0000 calendar given
      2007-08-09 11:05:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0930 calendar given
      2007-08-08 20:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT-0500 calendar given


      ACTUAL -
      The values that actually appear in my database are as follows:
      << Following 5 values inserted while default timezone is "Australia/Adelaide" GMT offset +0930 >>
      2007-08-09 11:05:11 <- From setTimestamp(int,Timestamp) when default timezone is +9300
      2007-08-09 01:05:11 <- From setTimestamp(int,Timestamp,Calendar) with default calendar given
      2007-08-09 11:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0000 calendar given
      2007-08-09 01:05:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0930 calendar given
      2007-08-09 16:05:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT-0500 calendar given
      << Following 5 values inserted while default timezone is GMT >>
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp) when default timezone is +0000
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp,Calendar) with default calendar given
      2007-08-09 01:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0000 calendar given
      2007-08-08 16:05:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT+0930 calendar given
      2007-08-08 06:35:11 <- From setTimestamp(int,Timestamp,Calendar) with GMT-0500 calendar given

      In almost all cases, the setTimestamp(int,Timestamp,Calendar) method has placed an incorrect value into the database. The only time it place the correct value was when the default timezone was GMT and given Calendar also had a timezone of GMT.
      Clearly the value the method places in the database is influenced by the default timezone; the whole point of the method is to NOT be influenced by the default timezone.

      REPRODUCIBILITY :
      This bug can be reproduced always.

      ---------- BEGIN SOURCE ----------
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.SQLException;
      import java.sql.Statement;
      import java.sql.Timestamp;

      import java.util.Calendar;
      import java.util.TimeZone;


      public class ODBCPreparedStatementTest {

      public static void main(String[] args) throws Exception {

      String conn_str = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\\Temp\\timezone_test.mdb";
      Connection db_connection = DriverManager.getConnection(conn_str, "", "");
      Statement db_statement = db_connection.createStatement();

      try {
      db_statement.execute("DROP TABLE [foo1]");
      } catch (SQLException sqle) { ;}

      db_statement.execute("CREATE TABLE [foo1] ( [BAR] DATETIME )");

      PreparedStatement db_prepstatement;
      Timestamp ts_val = new Timestamp(System.currentTimeMillis());
      Calendar cal1 = Calendar.getInstance();
      Calendar cal2 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
      Calendar cal3 = Calendar.getInstance(TimeZone.getTimeZone("GMT+0930"));
      Calendar cal4 = Calendar.getInstance(TimeZone.getTimeZone("GMT-0500"));

      db_prepstatement = db_connection.prepareStatement("INSERT INTO [foo1] VALUES (?)");

      db_prepstatement.setTimestamp(1, ts_val);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal1);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal2);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal3);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal4);
      db_prepstatement.execute();

      TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
      cal1 = Calendar.getInstance();

      db_prepstatement.setTimestamp(1, ts_val);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal1);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal2);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal3);
      db_prepstatement.execute();
      db_prepstatement.setTimestamp(1, ts_val, cal4);
      db_prepstatement.execute();

      db_connection.commit();
      db_connection.close();
      }

      }


      ---------- END SOURCE ----------

      CUSTOMER SUBMITTED WORKAROUND :
      Don't use the method. Insert values by manually constructing an INSERT INTO sentence that explicitly gives a timestamp literal value in the timezone I want and execute the sentence via Statement.execute().

      As noted in bug #4380653, the workaround of setting the default timezone to the one I want and using the setTimestamp(int,Timestamp) method is not a good idea; it has side-effects and would create a race condition if there are connections to more than one database with values being written in different timezones to the different databases.

        Attachments

          Activity

            People

            Assignee:
            lancea Lance Andersen
            Reporter:
            ryeung Roger Yeung (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Imported:
              Indexed: