Bug #12026 getString on Date-Field in Database creates exception

• Submitted: 19 Jul 2005 8:49
• Modified: 19 Jul 2005 12:56
• Reporter: Florian Rissner
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.1.10
• OS: Windows (Windows)
• CPU Architecture: Any

[19 Jul 2005 8:49] Florian Rissner

Description:
Executing a getString() on a Date- or DateTime-Field containing "0000-00-00" produces follwoing exception:
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Timestamp
        at com.mysql.jdbc.ResultSet.getNativeTimestamp(ResultSet.java:4108)
        at com.mysql.jdbc.ResultSet.getNativeConvertToString(ResultSet.java:3367)
        at com.mysql.jdbc.ResultSet.getNativeString(ResultSet.java:3975)
        at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5080)
        at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4926)

same error occurs if You use getTimestamp()

How to repeat:
# Database
CREATE TABLE `getStringcheck` (
  `ID` int(11) NOT NULL default '0',
  `DatTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `DummyText` varchar(100) collate latin1_german1_ci NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci; 

insert into getStringcheck (ID,DatTime,DummyText) values (1,now(),"foo1");
insert into getStringcheck (ID,DummyText) values (2,"foo2");
insert into getStringcheck (ID,DatTime,DummyText) values (3,now(),"foo3");

# JAVA-Prog

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://anaeclu1:3306/test", "test", "test");
DatabaseMetaData dbmd = con.getMetaData();
System.out.println(dbmd.getDatabaseProductName()+" "+dbmd.getDatabaseMajorVersion()+"."+dbmd.getDatabaseMinorVersion()+"\n"+dbmd.getDriverName()+" "+dbmd.getDriverVersion());
Statement stmnt = con.createStatement();
ResultSet res = stmnt.executeQuery("select * from getStringcheck");
res.last();
System.out.println("returned Rows:"+res.getRow());
res.beforeFirst();
System.out.println("firstRun");
try{
	int i = 0;
while (res.next()){
	System.out.print("Row: "+i++ +": ");
	System.out.print(res.getString(1)+",");
	System.out.print(res.getString(2)+",");
	System.out.print(res.getString(3)+";");
	System.out.println();
}
}catch(SQLException e){e.printStackTrace();}
res.beforeFirst();
System.out.println("secondRun");
try{
	int i = 0;
while (res.next()){
	System.out.print("Row: "+i++ +": ");
	System.out.print(res.getInt(1)+",");
	System.out.print(res.getTimestamp(2)+",");
	System.out.print(res.getString(3)+";");
	System.out.println();
}
}catch(SQLException e){e.printStackTrace();}

# Comment
runnig this with Driver version 3.0.9 and up works as expected. 
(getString() returns "0000-00-00", getTimestamp() returns null)
runnig this with driver version 3.1.10 fails
both methods throw exceptions

Suggested fix:
implement same behaviour than in driver 3.0.9

e.g. 
if using getString() 
return stringrepresentation without mentioning the column-type

if using getTimestamp()
return null and a warning

[19 Jul 2005 12:56] Mark Matthews

This is not a bug, and the reasons why (and how to work around it) are documented here in the upgrade notes (always a good idea to check these out when moving to a different major version of the driver):

http://dev.mysql.com/doc/connector/j/en/cj-upgrading-3-0-to-3-1.html

Quoting:

"#

Datetimes with all-zero components ('0000-00-00 ...') - These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the 'zeroDateTimeBehavior' configuration property. The allowable values are: 'exception' (the default), which throws a SQLException with a SQLState of 'S1009', 'convertToNull', which returns NULL instead of the date, and 'round', which rounds the date to the nearest closest value which is '0001-01-01'.

Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via 'noDatetimeStringSync=true' (the default value is 'false') so that you can get retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any timezone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time."