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
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