ColdFusion QueryNew(), JavaCast(), And Date/Time Values
Posted November 13, 2006 at 9:23 AM
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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- 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():
Launch code in new window » Download code as text file »
- 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.
Download Code Snippet ZIP File
Comments (0) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
There are no comments posted for this web log entry.
