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