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.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.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
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:
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.
Want to use code from this post? Check out the license.