MariaDB Client Library for Java Applications
  1. MariaDB Client Library for Java Applications
  2. CONJ-57

When calling setObject(new Date(), Types.TimeStamp) from a Preparedstatement the field is filled with a zero date String.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: jdbc-1.1.3
    • Fix Version/s: None
    • Labels:
      None
    • Global Rank:
      3346

      Description

      When calling setObject(new Date(), Types.TimeStamp) from a Preparedstatement the field is filled with a zero date String.
      When you wrap the Date in a Timestamp Object it's working as expected.
      Steps to reproduce:

      • Execute the follwing testcase:
      import org.junit.After;
      import org.junit.Before;
      import org.junit.Test;
      
      import java.sql.*;
      import java.util.Calendar;
      import java.util.Date;
      import java.util.TimeZone;
      
      import static org.junit.Assert.assertEquals;
      
      public class MariaDBDateTest {
      
          private static final String url =
                  "jdbc:mysql://" +
                          // "jdbc:mariadb://" +
                          System.getProperty("hostname", "localhost") + ":" +
                          System.getProperty("port", "3306") + "/" +
                          System.getProperty("dbname", "test");
          private static final String user = "root";
          private static final String passwd = "";
      
          private Connection con;
          private Date date;
      
          @Before
          public void setUp() throws Exception {
              // Load in the DriverManager Class
              Class.forName("org.mariadb.jdbc.Driver");
      //        Class.forName("com.mysql.jdbc.Driver");
      
              con = DriverManager.getConnection(url, user, passwd);
      
              date = Calendar.getInstance(TimeZone.getTimeZone()).getTime();
      //                new Date();
      //                Calendar.getInstance().getTime();
      
              createDB();
              System.out.println("Setup Complete.");
              System.out.println("**********");
          }
      
          @After
          public void tearDown() throws Exception {
              con.close();
              System.out.println("Teardown Complete.");
              System.out.println("**********");
          }
      
          private void createDB() throws Exception {
              con.setAutoCommit(true);
      
              PreparedStatement pstmt_drop = con.prepareStatement("DROP TABLE IF EXISTS test_table");
              try {
                  pstmt_drop.executeUpdate();
              } catch (Throwable t) {
              }
      
              PreparedStatement pstmt_create = con.prepareStatement("CREATE TABLE test_table " +
                      "(" +
                      "id INT PRIMARY KEY, " +
                      "someDate1 DATETIME(3)," +
                      "someDate2 DATETIME(3)" +
                      ")");
              pstmt_create.executeUpdate();
      
      
          }
      
          @Test
          public void testDateTime() throws Exception {
      
              PreparedStatement pstmt_add = con.prepareStatement("INSERT INTO test_table (id, someDate1, someDate2) VALUES (?, ?, ?)");
      
      
              pstmt_add.setInt(1, 1);
              pstmt_add.setObject(2, new Timestamp(date.getTime()), Types.TIMESTAMP);
              pstmt_add.setObject(3, date, Types.TIMESTAMP);
              pstmt_add.executeUpdate();
      
      
              String sql = "SELECT ID, someDate1, someDate2 FROM test_table t0 WHERE ID = ?";
              PreparedStatement pstmt = con.prepareStatement(sql);
              pstmt.setInt(1, 1);
      
              ResultSet rs = pstmt.executeQuery();
              while (rs.next()) {
                  assertEquals(1, rs.getInt(1));
                  assertEquals(date, rs.getObject(2));
      //            assertEquals(date,  rs.getObject(3));
              }
          }
      
      }
      
      • Then execute mariadb client
        MariaDB [test]> select * from test_table;
        +----+-------------------------+-------------------------+
        | id | someDate1               | someDate2               |
        +----+-------------------------+-------------------------+
        |  1 | 2013-07-19 07:00:17.452 | 0000-00-00 00:00:00.000 |
        +----+-------------------------+-------------------------+
        1 row in set (0.00 sec)
        
        MariaDB [test]> 
        

        Activity

        Hide
        Vladislav Vaintroub added a comment -

        Is there any good reason to use java.util.Date() instead of appropriate datatype (java.util.Timestamp?)

        Show
        Vladislav Vaintroub added a comment - Is there any good reason to use java.util.Date() instead of appropriate datatype (java.util.Timestamp?)
        Hide
        Frank S added a comment -

        I don't know if this is a good reason to use Date. I'm testing the maria db connector against existing code which has used the mysql connector before. And with the mysql connector it was working. You could not exclude that others running against the same problem when switching from mysql to the maria conenctor.

        Show
        Frank S added a comment - I don't know if this is a good reason to use Date. I'm testing the maria db connector against existing code which has used the mysql connector before. And with the mysql connector it was working. You could not exclude that others running against the same problem when switching from mysql to the maria conenctor.
        Hide
        Vladislav Vaintroub added a comment -

        I would not expect the bug to have a high impact, since support for java.util.Date is not documented ( I use http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-type-conversions.html as reference). so it is a matter of luck that it happens to work

        Show
        Vladislav Vaintroub added a comment - I would not expect the bug to have a high impact, since support for java.util.Date is not documented ( I use http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-type-conversions.html as reference). so it is a matter of luck that it happens to work
        Hide
        Vladislav Vaintroub added a comment -

        The test case here still has some problems.
        1 - it does not compile
        2 - it expects java.sql.Date (returned by getObject()) to be the same as java.util.Object (the same as in assertEquals)
        3 - it does not take fraction of seconds into account.

        Show
        Vladislav Vaintroub added a comment - The test case here still has some problems. 1 - it does not compile 2 - it expects java.sql.Date (returned by getObject()) to be the same as java.util.Object (the same as in assertEquals) 3 - it does not take fraction of seconds into account.

          People

          • Assignee:
            Vladislav Vaintroub
            Reporter:
            Frank S
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - Not Specified
              Not Specified
              Remaining:
              Remaining Estimate - 0 minutes
              0m
              Logged:
              Time Spent - 3 hours
              3h