I am working on this mini ColdFusion application that uses a query object and caches it as a WDDX XML file. Usually, I think that WDDX is sooo freakin' cool, I am shocked I didn't start using it earlier. But, when it comes to Date/Time objects, WDDX seems to not be playing well with manually built ColdFusion queries.
Now, I am not sure if this is a WDDX problem or a query building problem. As I have talked about before, building ColdFusion queries manually can be problematic because the underlying Java ResultSet object is type-sensitive where as ColdFusion is NOT type sensitive. When I add rows to the cached query using QueryAddRow(), I have to JavaCast() the values that I set into the new record cells otherwise performing further ColdFusion query of queries will fail on ORDER BY clauses (potentially) as the values are not of the correct type and the generic CompareTo() fails.
Most values are easy to deal with, but the one that keeps throwing me through a loop is the Date/Time object (think CF_SQL_TIMESTAMP). I can't just throw a regular time stamp into the query (Now()), as that gets stored as a Java String object and will certainly crash if used in conjunction with ORDER BY clauses. To overcome this, I usually cast the Date/Time object a FLOAT value when storing it. This works great and the ORDER BY clauses work great. Even the WDDX seems to convert everything nicely ... we sort of; the WDDX conversion is still storing the Date/Time stamp as a float in the XML file (ex. 39141.3359375). Still, it's cool, going from the WDDX back into a query works fine with the float.
The problem with this approach in this application is that I am actually using some AJAX and need to convert the query values to JSON. This causes a serious problem as the value coming out of the cached query, while still technically a date, looks nothing like a date to the algorithm that uses IsDate() when converting the ColdFusion value to JSON. I could use IsNumericDate(), but then values that were ACTUALLY numbers and not Date/Time stamps would also get converted to JSON date objects.
To get around this, I tried storing a Java Date object directly into the query:
<cfset APPLICATION.NoteTable[ "date_posted" ][ intRow ] =
Year( Now() ),
Month( Now() ),
Day( Now() ),
Hour( Now() ),
Minute( Now() ),
Second( Now() )
This stores fine and works swimmingly with further ORDER BY query of query clauses. It seems to crap out when WDDX converts this value to XML and then later back into a ColdFusion query object. The numbers get changed... or rather, the IN-dates are different than the OUT-dates. Such a pain in the butt!
I love ColdFusion query of queries and the ColdFusion query object in general. I really think it is an amazing feature of the language. But, sometimes, they can be such a pain when you are really trying to push the limits on how they get used.
For now, I will just go with storing a float value. Not a great solution, but ok for the Beta.
Want to use code from this post? Check out the license.