Bug #3103 setObject() with java.util.Date creates 0000-Entries
Submitted: 8 Mar 2004 4:14 Modified: 9 Mar 2004 9:08
Reporter: Klaus Halfmann Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:mysql-connector-java-3.0.11-stable-bin.j OS:Windows (Windows XP)
Assigned to: Mark Matthews CPU Architecture:Any

[8 Mar 2004 4:14] Klaus Halfmann
Description:
a MySQL Prepared Statement will accept a java.util.Date for
setObject() without any Warning or Error but will not set
anything, but insert 0-Values. This is the same for 
DATE, TIME and TIMESTAMP columns.

How to repeat:
/*
 * @(#) TestMySQL.java
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;

public class TestMySQL extends TestCase {

    /** The date we use for testing */
    static final java.util.Date TEST_DATE = new java.util.Date(1250978400000L);

    /** The time we use for testing */
    static final java.util.Date TEST_TIME = new java.util.Date(17896000L);

    /** The timestamp we use for testing */
    static final java.util.Date TEST_STAMP = new java.util.Date(1250999896000L);

    /** Connection used for Testing */
    static Connection con;

    public TestMySQL (String name) {
        super (name);
    }

    /** Create a MySQL Connection
     */
    public void doCreate() throws Exception {

        // This is the Future "com.mysql.jdbc.Driver"
        String driver = "com.mysql.jdbc.Driver";
        String sqlurl = "jdbc:mysql://localhost/test";
   
        Class.forName(driver);
        con = DriverManager.getConnection(sqlurl);
        
        Statement stm = con.createStatement();
        
        stm.executeUpdate("CREATE TABLE date_test ("
                        + " name    char(32) NOT NULL, "
                        + " adate   DATE , "
                        + " atime   TIME , "
                        + " astamp  TIMESTAMP , "
                        + " PRIMARY KEY (name))");
                        
        stm.close();
    }

    /**
     * Chek the dates inserted / updated.
     */
    private void checkDate(String name) throws Exception {
        
        Statement stm = con.createStatement();
        ResultSet res = stm.executeQuery(
            "SELECT adate,atime,astamp FROM date_test "
          + "WHERE name='" + name + "'");
        assertTrue(res.next());
        
        assertEquals(TEST_DATE,  res.getObject(1));            
        assertEquals(TEST_TIME,  res.getObject(2));            
        assertEquals(TEST_STAMP, res.getObject(3));    

        res.close();
    }

    /** Test using an INSERT Statement and SQL Types .
     */
    public void testInsertSQL() throws Exception {

        PreparedStatement pstm = con.prepareStatement(
            "INSERT INTO date_test VALUES(?,?,?,?)");
        
        pstm.setString(1,"sql");
        pstm.setObject(2, new java.sql.Date     (TEST_DATE.getTime()));
        pstm.setObject(3, new java.sql.Time     (TEST_TIME.getTime()));
        pstm.setObject(4, new java.sql.Timestamp(TEST_STAMP.getTime()));
        
        assertEquals(1, pstm.executeUpdate());
        
        pstm.close();
        
        checkDate("sql");
        
    }

    /** Test using an INSERT Statement and util Dates.
     */
    public void testInsertUtil() throws Exception {

        PreparedStatement pstm = con.prepareStatement(
            "INSERT INTO date_test VALUES(?,?,?,?)");
        
        pstm.setString(1,"util");
        pstm.setObject(2,TEST_DATE);
        pstm.setObject(3,TEST_TIME);
        pstm.setObject(4,TEST_STAMP);
        
        assertEquals(1, pstm.executeUpdate());
        
        pstm.close();
        
        checkDate("util");
        
    }

    /** Close the Connection
     */
    public void doClose() throws Exception {
        Statement stm = con.createStatement();
        stm.executeUpdate("DROP TABLE date_test");
        stm.close();
        con.close();
        con = null; // Release to gc
    }

    // Static methodes

    /**
     * @return the suite of tests to execute.
     */
    public static Test suite () {
        TestSuite suite = new TestSuite ();

        // Order is important so ...
        suite.addTest(new TestMySQL("doCreate"));
        suite.addTest(new TestMySQL("testInsertSQL"));
        suite.addTest(new TestMySQL("testInsertUtil"));
        suite.addTest(new TestMySQL("doClose"));
        return suite;
    }

    /**
     * main function for testing.
     */
    public static void main (String[] args) {
        junit.textui.TestRunner.run (suite ());
    }
}

1) testInsertUtil(TestMySQL) junit.framework.AssertionFailedError: 
       expected:<Sun Aug 23 00:00:00 CEST 2009> but was:<null>
   at TestMySQL.checkDate(TestMySQL.java)
   at TestMySQL.testInsertUtil(TestMySQL.java:129)

 
mysql> SELECT * FROM date_test;
+------+------------+----------+----------------+
| name | adate      | atime    | astamp         |
+------+------------+----------+----------------+
| sql  | 2009-08-23 | 05:58:16 | 20090823055816 |
| util | 0000-00-00 | 00:00:00 | 00000000000000 |
+------+------------+----------+----------------+
2 rows in set (0.00 sec)

mysql  Ver 12.21 Distrib 4.0.14, for Win95/Win98 (i32)

Suggested fix:
Either:
 
a) (Prefered) silently Map the java.util.Date to the correct type
    (eventually add a Warning: "XXXX was mapped to YYYYY loosing
     Precision" ...)

b) throw some SQLException telling the user whats wrong
[9 Mar 2004 9:08] Mark Matthews
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This fix should show up in the nightly snapshot builds after 00:00 GMT, March 09.

The driver will now silently convert the java.util.Date instance into a java.sql.Timestamp, which is the only 'JDBC standard' type that can hold the value with full precision.