Matt Wegrzyn wrote:
> Looks like a java exception error is thrown when
> using J connectors and selecting a date/time field
> that has a value of 0000-00-00 00:00:00. No error
> occured with ODBC, although getting this Java
> error with JDBC:
>
> 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
>
> Stack trace:
>
> java.sql.SQLException: Value '0000-00-00' can not
> be represented as java.sql.Timestamp
> at
> com.mysql.jdbc.ResultSet.getNativeTimestamp(Result
> Set.java:4108)
> at
> com.mysql.jdbc.ResultSet.getTimestampInternal(Resu
> ltSet.java:5662)
> at
> com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.ja
> va: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.ja
> va:708)
> at
> coldfusion.sql.Executive.executeQuery(Executive.ja
> va:638)
> at
> coldfusion.sql.Executive.executeQuery(Executive.ja
> va:599)
> at
> coldfusion.sql.SqlImpl.execute(SqlImpl.java:236)
> at
> coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.j
> ava:498)
>
>
> Any ideas?
Matt,
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'. "
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html