MySQL Forums
Forum List  »  Connector/JDBC and Java

Re: zeroDateTimeBehavior won't work
Posted by: Mark Matthews
Date: September 16, 2005 07:57AM

Gustav Oesgaard wrote:
> 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-upgradi
> ng.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-config
> uration-properties.html).
>
> My code looks something like this now:
>
> ...
> MysqlDataSource dataSource = new
> MysqlDataSource();
>
> dataSource.setURL("jdbc:mysql://localhost:3306/dat
> abaseName?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

Gustav,

I tested this again (as I reported in your bug report), and it appears that you're going to need to set connection properties via the accessor methods on the datasource, if you're using a datasource. This isn't necessary if using the driver interface:

msds.setZeroDateTimeBehavior("convertToNull");

I'll make sure this gets into the documentation for 3.1.11.

-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: zeroDateTimeBehavior won't work
September 16, 2005 07:57AM


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.