Bug #2671 PreparedStatement datetime silent bug
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());
}
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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()); }