MySQL Bugs: #13030: JDBC 3.2 alpha returns sql timestamp error when working with datetime values.

Bug #13030	JDBC 3.2 alpha returns sql timestamp error when working with datetime values.
Submitted:	7 Sep 2005 4:21	Modified:	7 Sep 2005 12:05
Reporter:	Matt Wegrzyn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.2 Alpha	OS:	Windows (Windows XP Home)
Assigned to:		CPU Architecture:	Any

Description:
I don't know if you guys are accepting 3.2 alpha bugs, but there is a bug when selecting a date/time value of "0000-00-00 00:00:00" from a table using the J 3.2 alpha connector. Returns error message somewhere along the lines "Cannot convert SQL timestamp of 0000-00-00". I uninstalled it, installed 3.1 and wasn't getting the error anymore.

How to repeat:
Create a table with a date/time column and insert a value of 0000-00-00 00:00:00 and select the data.

Looks like the problem appears on 3.1 jdbc as well, now that I test it further. Exact error returned from Coldfusion application:

 Error Executing Database Query.
Value '0000-00-00' can not be represented as java.sql.Timestamp
 
ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem.
SQL 	   SELECT user_id, floodtime, skin_id, lang_id FROM cfb_sessions WHERE bbtoken = (param 1) ;
DATASOURCE 	  datasource1
SQLSTATE 	  S1009

Although, using ODBC, worked like a charm, don't understand why 0000:00:00 would raise an exception.

Here's a java stack trace, if anybody needs it:

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.getTimestampInternal(ResultSet.java:5662)
	at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:5320)
	at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:4433)
	at coldfusion.sql.QueryTable.populate(QueryTable.java:329)
	at coldfusion.sql.QueryTable.populate(QueryTable.java:215)
	at coldfusion.sql.Executive.getRowSet(Executive.java:296)
	at coldfusion.sql.Executive.executeQuery(Executive.java:708)
	at coldfusion.sql.Executive.executeQuery(Executive.java:638)
	at coldfusion.sql.Executive.executeQuery(Executive.java:599)
	at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236)
	at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:498)

This behavior is as-intended. See http://dev.mysql.com/doc/mysql/en/cj-upgrading.html#cj-upgrading-3-0-to-3-1

" 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'. "

That explains it, thanks. I'll set the default value for the column to use the lowest possible date/time value that won't throw an exception.

PS

Nice name.