Bug #2671 PreparedStatement datetime silent bug
Submitted: 7 Feb 2004 5:28 Modified: 10 Feb 2004 7:12
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.1 OS:Windows (Win98)
Assigned to: Mark Matthews CPU Architecture:Any

[7 Feb 2004 5:28] [ name withheld ]
Description:
First of all I'm not sure whether this is a JDBC bug or not, but I can't test it other way.

Environment:
- MySQL server 4.1.1-alpha on Linux. (Mandrake 8.1)
- mysql-connector-java-3.1-nightly-20040203-bin.jar (on Win98) 
- JSDK 1.4.2 b-28 (on Win98)
- Apache Tomcat 4.1.29 (on Win98)

So if you run the code above, the retval integer value will be set to 0, showing that no rows affected by the update. (It should be.) No exception generated either.
The most interesting thing is, by removing ANY of the affected columns (leaving only 3) or even changing the date value from null to anything else (see the commented part below) the update will succeed.

Please explain this to me, because this is weird.

How to repeat:
The install script:
drop table test3;
CREATE TABLE test3 (
  `id` int(8) NOT NULL auto_increment,
  `sap_id` int(8) unsigned zerofill default NULL,
  `login_name` varchar(30) binary NOT NULL default '',
  `full_name` varchar(100) default NULL,
  `created_time` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unq_id` (`id`),
  UNIQUE KEY `unq_login_nev` (`login_name`),
  UNIQUE KEY `unq_sap_id` (`sap_id`),
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test3 (id,login_name,full_name) values ('1','blewis','Bob Lewis');

The code:
  String query="              " +
    "UPDATE                   " +
    "  test3                  " +    
    "SET                      " +    
    "  sap_id=?               " +
    "  ,login_name=?          " +
    "  ,full_name=?           " +
    "  ,created_time=?        " +
    "WHERE                    " +
    "  id = ?                 ";
   
  java.sql.Date mydate=null;
  //java.sql.Date mydate= new java.sql.Date(0);
  try
  {
    java.sql.PreparedStatement pstmt=conn.prepareStatement(query);

    pstmt.setInt   (1 ,13);
    pstmt.setString(2 ,"llewis");
    pstmt.setString(3 ,"Lob Lewis");
    pstmt.setDate  (4 ,mydate);
    pstmt.setInt   (5 ,1);

    int retval=pstmt.executeUpdate();
    out.println(retval);
  }
  catch (java.sql.SQLException sqlEx)
  {
    // handle any errors 
    out.println("Error."); 
    out.println(" SQLException: " + sqlEx.getMessage()); 
    out.println(" SQLState:     " + sqlEx.getSQLState()); 
    out.println(" VendorError:  " + sqlEx.getErrorCode());
  }
[10 Feb 2004 2:29] [ name withheld ]
See bug 2692, it might be related.
[10 Feb 2004 7:12] Mark Matthews
Thanks for your bug report. It turned out to be a bug in the encoding of NULL parameters in the JDBC driver.

Please see a nightly snapshot build of Connector/J 3.1 after 00:00 GMT, Febuary 11th to test the fix.