Re: '0000-00-00' can not be represented... - Navneet Karnani - com.mysql.lists.java

6 messages in com.mysql.lists.javaRe: '0000-00-00' can not be represent...

From	Sent On	Attachments
Morten Norby Larsen	05 Feb 2005 16:31
PA	05 Feb 2005 16:54
Mark Matthews	05 Feb 2005 17:09
Morten Norby Larsen	07 Feb 2005 06:57
Navneet Karnani	25 Feb 2005 01:03
Mark Matthews	25 Feb 2005 07:04

Subject:	Re: '0000-00-00' can not be represented...
From:	Navneet Karnani (navn...@myrealbox.com)
Date:	02/25/2005 01:03:20 AM
List:	com.mysql.lists.java

Hi,

Is there a solution for this ? I have seen the MySQL Connector/NET also crash on a similar value.

Another similar problem is a 0 in the timestamp field.

I did open a bug for the same on the mysql site. But havent got any updates there only that this is a known issue.

- Navneet

Morten Norby Larsen wrote:

Thanks Mark,

I should of course have checked the docs.

Now that I have read them, I have small question:

Given that the value "0000-00-00" is a documented behaviour of MySQL, I would like our client to handle it reliably. Would the best way be to get a null value, and then check with wasNull() if the null I got really was a NULL (in the database sense) or if it was something else. If it was something else, it must have been a '0000-00-00'. It's not an ideal solution, but if it is deterministic, I guess it will work.

Other ideas?

Thanks,

On 6 Feb 2005, at 02:09, Mark Matthews wrote:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Morten Norby Larsen wrote:

A user of the YourSQL client (MacOSX 10.3 only) has drawn our attention to a problem where the date '0000-00-00' cannot be represented by a java.sql.Date object.

The error happens when getString is called on the ResultSet. Here is a stack trace:

java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:5620) at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:2149) at com.mysql.jdbc.ResultSet.getString(ResultSet.java:2076) at YSDBTableDataSource.updateData(YSDBTableDataSource.java:1614) at YSDBTableDataSource.reloadFromDatabase(YSDBTableDataSource.java:1535) at YSDBTableDataSource.setTableName(YSDBTableDataSource.java:296) at YSSchemaDoc.tableSelected(YSSchemaDoc.java:827) at YSSchemaDoc.browserClicked(YSSchemaDoc.java:664)

The Connector/J version is 3.1.6.

It has been working at some stage, so it must somehow have something to do with the driver.

Any ideas how to get this special MySQL feature to work?

Thanks,

Morten,

- From the docs about configuration properties (it's also in the CHANGES I believe), you need to use the 'zeroDatetimeBehavior' configuration property:

zeroDateTimeBehavior

What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are 'exception', 'round' and 'convertToNull'. Exception is the default value.

-Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity www.mysql.com

MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: http://www.mysqluc.com/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCBW5mtvXNTca6JD8RAiAgAJ9nL7/IYsC9u/tCGccB56+0BgoPqgCgje3D s3OIEFu6fjK1zchtLO7Aaco= =fsjg -----END PGP SIGNATURE-----