QueryAddRow(), Date/Times, WDDX, And ORDER BY, OH MY!
Posted February 28, 2007 at 8:15 AM by Ben Nadel
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 ] =
- CreateObject(
- "java",
- "java.util.Date"
- ).Init(
- 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.
Reader Comments
Ben - another option when dealing with these queries made from scratch is to use something else that returns a query and manipulate it - for example you could do "SELECT 0 as id, some_date_column as mydate FROM some table or tables WHERE 1=2" and then just add rows to it - the point being that rather than use queryNew() just get yourself a query object with no rows from a database and stuff things in there - but your initial query object that you create is properly constructed with the underlying types. of course this is a pain if you dont even have a DB in the site, but then again, you can do a cfdirectory whith a wacky filter that you know will return no rows, and then just make do with the column names you have (or use a QofQ to rename them) and then start stuffing data in there with queryAddRow.
of course all of this kinda pre-dates the cf7 ability to create types with the query new, but i have seen issues with both wddx and more specifically serializeJSON around queries in general, and so its always good to think of some end-arounds. cheers!
@Jon,
Hmm, definitely an interesting idea. Thanks for the tip.
