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

[Peter J. Farrell]

Ken Dunnington said the following on 2/27/2006 4:00 PM: 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/ Yeah, this is a long standing issue with the JDBC driver for MySQL.  View the bug here: http://bugs.mysql.com/bug.php?id=3331 You can see my LiveDocs comment here: http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/functio6.htm Maybe use null instead of the default MySQL value?  I usually use "" to represent null - then my DAO checks something like: <cfqueryparam value="#arguments.addressBean.getDate_updated()#" cfsqltype="cf_sql_timestamp" null="#NOT Len(arguments.addressBean.getDate_updated())#" /> .Peter -- Peter J. Farrell :: Maestro Publishing Member Team Mach-II :: Member Team Fusion http://blog.maestropublishing.com Create boilerplate beans and transfer objects for ColdFusion! Fire up a cup of Rooibos! http://rooibos.maestropublishing.com/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/