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());
}
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()); }