Re: Rrror when using 0000-00-00 value in datetime?
Posted by: Mark Matthews
Date: September 07, 2005 06:14AM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: Rrror when using 0000-00-00 value in datetime?
September 07, 2005 06:14AM


Sorry, you can't reply to this topic. It has been closed.

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.