zeroDateTimeBehavior won't work
Posted by: Gustav Oesgaard
Date: September 16, 2005 03:52AM

Hi,

I´ve been using Connector/J 3.0 for a while but have now decided to use Connector/J 3.1 (using 3.1.10) instead.

I noticed that the behavior for reading DATETIME fields containing "0000-00-00 00:00:00" (which was read without problem before) has changed so that an Exception is thrown when trying to read it:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column X to TIMESTAMP

However, I also read (in the upgrading section of the Connector/J documentation: that there was a way of modifying this behavior:

From http://dev.mysql.com/doc/connector/j/en/cj-upgrading.html)
"...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'. ...

This property should be passed with the URL when using the setURL() on the MysqlDataSource object according to configuration properties section of the documentation (http://dev.mysql.com/doc/connector/j/en/cj-configuration-properties.html).

My code looks something like this now:

...
MysqlDataSource dataSource = new MysqlDataSource();

dataSource.setURL("jdbc:mysql://localhost:3306/databaseName?zeroDateTimeBehavior=convertToNull");

connection = dataSource.getConnection(username, password);

Statement stmt = connection.createStatement();

// reg_date is of type DATETIME
ResultSet rs = stmt.executeQuery("SELECT reg_date FROM order");

while(rs.next()) {
System.out.println(rs.getString(1)); // This is where the Exception is thrown when finding a "0000-00-00 00:00:00".
}
...

What am I doing wrong? I´ve also tried combining with the noDatetimeStringSync without results...


Any advice much appreciated!

Best regards
Gustav Oesgaard

Options: ReplyQuote


Subject
Written By
Posted
zeroDateTimeBehavior won't work
September 16, 2005 03:52AM


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.