When ColdFusion connects to MySQL 4/5 databases, the default choice is to use JDBC. This is all well and fine, but a difference-of-opinion between JDBC Dates and MySQL dates can cause your application fits. The issue is created when you use an empty string (not a NULL, which is a different bird all together) to update a date field. MySQL interprets this as '0000-00-00 00:00:00' and stores that value. JDBC by design does not recognize '0000-00-00 00:00:00' as a valid date and returns an error.
CLARIFICATION UPDATE: The error won't actually occur until you attempt to retrieve the record with the 'empty string' date. In other words, the above INSERT would work, but the SELECT would fail because MySQL is returning '0000-00-00 00:00:00' via JDBC. This detachment of 'issue creation' from 'issue result' can greatly compound the frustration/confusion.
The most common solutions suggested for dealing this are either a work-around or not easily applied. A little experimentation revealed an ideal solution to this problem.
First, lets look at the old (wrong) ways of doing things. The work-around I've seen posted is to set the date to {ts '1970-01-01 00:00:00'}, which is the Java "zero epoch" value as defined in the JDBC docs (http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Time.html). Wild coincidences aside, chances are you will be inserting an inaccurate value in your database record and you will be forced to include workaround code every time you validate your date against 'null-er-sorta'.
The other, more common solution is to put:
...in the connection string of your ColdFusion datasource (via the ColdFusion administrator). This is all well and good if you a) have access to the administrator and b) aren't planning to distribute your application. It is also easy to forget during migration (he says from experience). It works, but it is hardly ideal.
The solution I stumbled upon is so straight-forward it seems obvious in hindsight. If you are allowing a column in your MySQL database to accept NULL values, then you should first set the column to accept them. If you are using the MySQL Query Browser tool, simply uncheck the "NOT NULL" column and ensure the "Default Value" is set to NULL (see image).
The next step is to ensure you are inserting a true NULL value into your database. There is a big difference between and empty string and a true NULL value, so use the following:
This basically says "Insert the date in the variable DateThatMightBeNull unless it isn't a date, in which case insert a true NULL". Both the JDBC driver and MySQL know how to deal with NULL dates so there won't be any errors!
Aug 18, 2009 at 1:37 AM Good post, I've not had this specific issue myself as I've not worked with MySQL/CF as I'm a MSSQL guy but the same approach is preferable regardless of your DBMS, using the cfqueryparam null attribute is always a good suggestion.
This one gripe I have with CF, that it doesn't support NULL values properly, in my mind, as you say yourself, NULL and an Empty String are to very different things, it's a shame that CF treats them all the same, all part of it's 'typeless' style I suppose which in turn has it's own benefits, would just be nice to know if a returned value it actually a NULL or just an empty string.
Rob
Aug 18, 2009 at 8:57 AM @Robert: true enough. Another is ColdFusion beginning iterations from 1 instead of 0. While the former seems more logical to people who have only ever used or initially started in ColdFusion, iterating from 0 is an old-school standard across pretty much every other language (with reasonable enough justifications for why). Having to 'switch gears' when writing JavaScript/AS3/etc. can get tedious.