ColdFusion QueryNew(), JavaCast(), And Date/Time Values
Posted November 13, 2006 at 9:23 AM by Ben Nadel
I was working on some updates to Skin Spider this morning when I came across an error that didn't throw any error message. The error was occuring on a page performing a ColdFusion query of queries. And, while there was no message or detail in the Application's exception object, the stack trace did help me out.
Here is the query of queries that I was running:
- <cfquery name="qVideo" dbtype="query">
- SELECT
- id,
- name,
- ext,
- description,
- rating,
- video_gallery_id,
- date_created
- FROM
- qVideoTable
- WHERE
- is_favorite = 1
- ORDER BY
- date_updated DESC
- </cfquery>
And this is the stack trace that I got:
java.lang.ClassCastException at
java.lang.String.compareTo(Unknown Source) at
coldfusion.sql.imq.GenericComparator.compare(Comparator.java:67) at coldfusion.sql.imq.TableSorter.compareTo(TableSorter.java:156) at coldfusion.sql.imq.TableSorter.compareTo(TableSorter.java:145) at coldfusion.sql.imq.TableSorter.merge(TableSorter.java:184) at coldfusion.sql.imq.TableSorter.mergeSort(TableSorter.java:210) at coldfusion.sql.imq.TableSorter.mergeSort(TableSorter.java:205) at coldfusion.sql.imq.TableSorter.mergeSort(TableSorter.java:220) at
coldfusion.sql.imq.imqTable.sort(imqTable.java:485) at
coldfusion.sql.imq.imqTable.sort(imqTable.java:501) at
coldfusion.sql.imq.rttSelectStmt.evaluate(rttSelectStmt.java:67) at coldfusion.sql.imq.jdbcStatement.fetchResult(jdbcStatement.java:539) at coldfusion.sql.imq.jdbcStatement.execute(jdbcStatement.java:131) at coldfusion.sql.imq.jdbcPreparedStatement.execute(jdbcPreparedStatement.java:95) at coldfusion.sql.Executive.executeQuery(Executive.java:722) at
coldfusion.sql.SqlImpl.execute(SqlImpl.java:240) at
While this can be pretty scary when you first look at it, it actually contained all the information that I needed to lock down the error code. The line that gave it away was:
coldfusion.sql.imq.TableSorter.compareTo()
Immediately this threw red flag: Ok, the error is happening when ColdFusion is trying to sort a table by comparing values (yeah, how else can you sort anything). The only choice at this point was the ORDER BY clause of the ColdFusion query of queries.
Now, the error wasn't happening all the time; it only happened after I updated the database using the Application's API. This update to the database was updating the column "date_updated" in the XML database file. Ah ha! That makes sense. Well, at least the error lines up with the chain of events.
But, why was the error being thrown? When I looked in the DatabaseService.cfc that ran updates on the XML data files, I saw that I had set the data conversion for DATETIME to be:
- VARIABLES.DataTypeMap[ "DATETIME" ] = StructNew();
- VARIABLES.DataTypeMap[ "DATETIME" ].JavaCast = "string";
- VARIABLES.DataTypeMap[ "DATETIME" ].QueryNew = "VARCHAR";
This made sense to me since JavaCast() doesn't have a "Date" conversion. However, I was pretty sure this was causing the error, so it clearly wasn't the correct conversion. Now, while JavaCast() doesn't have a date data type, QueryNew() certainly does: "DATE". But, putting in just that change, the errors will still getting thrown. As a last attempt, I decided that since date/time objects have FLOAT equivalents, I would try that for the JavaCast():
- VARIABLES.DataTypeMap[ "DATETIME" ] = StructNew();
- VARIABLES.DataTypeMap[ "DATETIME" ].JavaCast = "float";
- VARIABLES.DataTypeMap[ "DATETIME" ].QueryNew = "DATE";
This worked like a charm. No more errors. Now, date/time columns in QueryNew() are of type "DATE" and, when I need to set those values manually using JavaCast(), I cast them to Java "FLOAT" values before passing them into the query object.
Reader Comments
This helped me tonight-- I am building a search interface on BlueDragon JX/Lucene that uses custom fields.
One of the fields is a PRICE.
The index is created by looping through all our products and combining all the criteria needed within the cfoutput using QueryNew().
Everything went well until I tried to run a Query of a Query on the cfsearch results to filter by price range.
It would not give me the result I wanted, searching for a range of 10.00 - 20.00 would return 1 - 20, I could not get the QofQ to work.
Searching for JavaCast led me back here.
So I added:
JavaCast("float", mydata.PRICE )
on the QueryNew() field -- still no go :(
So I also added:
JavaCast('int', arguments.filter) to the cfqueryParam, still no go--
Then I just added:
intLtPrice = Int(arguments.filter)
and
JavaCast('int', intLtPrice )
to the cfqueryparam.
Then BAM it worked. It seems like cfqueryparam also depends on the type casting to happen before the tag is called.
Without cfqueryparam the number 10 worked fine, but if it was set as a variable, or showed up as a primitive yet to be cast it would fail even if I used JavaCast in the param.
@Kevin,
Funky stuff, right?! I've said it before - ColdFusion query of queries are, at the same time, amazing *and* downright frustrating. One thing that I have found tends to work as a sort of last resort is casting both the query value and the compared value. Example:
WHERE
CAST( x AS INT ) =
CAST( <cfqueryparam cfsqltype="cf_sql_integer" /> AS INT )
To see a better exploration of this, take a look here:
http://www.bennadel.com/blog/379-ColdFusion-Query-of-Queries-Unexpected-Data-Type-Conversion.htm
Seems crazy, but sometimes it just works.
