Bug #2437 PreparedStatement.setNull does not set null

• Submitted: 18 Jan 2004 14:10
• Modified: 18 Jan 2004 15:10
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 3.0.10
• OS: Linux (Linux 2.6.1-gentoo)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[18 Jan 2004 14:10] [ name withheld ]

Description:
This code does not throw exception (vendor_employee cannot be null, see table  
structure):  
  
PreparedStatement   prepStmt;  
String              sqlcmd;  
  
sqlcmd = "update contract " +  
             "set vendor_employee = ? " +  
             "where id = ?";  
prepStmt = conn.prepareStatement(sqlcmd);  
prepStmt.setNull(1, java.sql.Types.BIGINT);  
prepStmt.setString(2, "1");  
prepStmt.executeUpdate();  
  
This does not works too:  
prepStmt.setString(1, vendorEmployee);  
  
  
  
  
update command in mysql returns:  
  
mysql> update contract set vendor_employee=null where id=462;  
--------------  
update contract set vendor_employee=null where id=462  
--------------  
  
Query OK, 0 rows affected (0.01 sec)  
Rows matched: 1  Changed: 0  Warnings: 1  
  
  
  
Additional informations:  
------------------------  
  
table structure:  
  
CREATE TABLE `contract` (  
  `id` bigint(20) unsigned NOT NULL default '0',  
  `vendor_employee` bigint(20) unsigned NOT NULL default '0',  
  PRIMARY KEY  (`id`),  
) TYPE=InnoDB;  
  
  
java version "1.4.2_03"  
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_03-b02)  
Java HotSpot(TM) Client VM (build 1.4.2_03-b02, mixed mode)  
  
MySQL 4.0.16  
  

How to repeat:
PreparedStatement   prepStmt;  
String              sqlcmd;  
  
sqlcmd = "update contract " +  
             "set vendor_employee = ? " +  
             "where id = ?";  
prepStmt = conn.prepareStatement(sqlcmd);  
prepStmt.setNull(1, java.sql.Types.BIGINT);  
prepStmt.setString(2, "1");  
prepStmt.executeUpdate();

[18 Jan 2004 15:10] Mark Matthews

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please see the following manual section that explains why MySQL will accept a NULL in a NOT NULL column (it silently converts it to the DEFAULT value):

http://www.mysql.com/doc/en/constraint_NOT_NULL.html