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