[Reactor For CF] MySQL 5 default date w/Connector/J

[Ken Dunnington]

I just came across a quirk with using default dates (0000-00-00) in MySQL 5 if you're using a JDBC connection. I was getting this error, "Value '0000-00-00' can not be represented as java.sql.Timestamp", which I tried to fix by editing my base TO object to initialize my date fields to Now(). But that didn't work, so I did some (very brief) investigating, and it turns out this is the expected behavior for Connector/J 3.1. From the MySQL site: http://dev.mysql.com/doc/refman/5.0/en/cj-upgrading.html#cj-upgrading-3-0-to-3-1

Datetimes with all-zero components ('0000-00-00 ...') — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards.

So, if you've installed Connector/J 3.1 in order to more reliably use MySQL 5 with CF, be sure to set your date values to default to something other than '0000-00-00' - I used '1930-11-01' because that's the default date I get when I format my dates wrong when saving to the db. :) Not that I would do such a thing... Anyone know of a better solution?
-- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/