I'm working alongside my client today (well, "alongside" as in virtually on the net) when he pings me with an odd error. His template allows you to sort a set of data by selecting a value from a drop down. Whenever he picks Published Year as the sort, he would get a very odd error:
Error casting an object of type to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was designed.
The SQL in question was extremely simple:
<cfquery name="foo" dbtype="query">
select *
from tempquery
order by #foter.sorter#
</cfquery>
Now ignore for a moment the select * and possibly unsafe order by. Instead note that this is a query of query. One of the first things I check for in cases like this is ColdFusion getting confused by the column type. When you create a query by hand (temp query was made with query new), and you then later perform a query of query on it, ColdFusion has to guess at what your column types are. It looks at the first few rows and makes assumptions based on what it sees.
However - you can stop ColdFusion from guessing. If you read the docs on QueryNew(), you will see that it takes a second argument. This argument is a list of column types that correspond to the initial list of columns in the first argument.
I had him add this list and the error went away.
Archived Comments
Rey,
It should be noted that the datatype list is only available in MX7 and up.
In MX6.1 I use the following technique. I have a table called "QueryDataTypes". That table contains a column for each datatype. So I've got a column called "character" one called "numeric" one called "Date", etc.. There's nothing in the table other than these columns, and of course each column is of the type that it's named after.
Then when I need a query with specific data types I run code like this:
<CFSet DataTypeSelectList = "">
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Character AS UNCPath")>
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Character AS ClientNumber")>
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Character AS Requestor")>
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Character AS AnsweringRep")>
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Character AS StartTime")>
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Character AS EndTime")>
<CFSet DataTypeSelectList = ListAppend(DataTypeSelectList,"Date AS ScheduledDate")>
<CFQuery Name="myQuery" DataSource="#myDataSource#">
SELECT #DataTypeSelectList#
FROM QueryDataTypes
</CFQuery>
and that leaves me with an empty query object that I can then use as I need.
Thats an interesting take for sure. Sorry if it wasn't clear folks - this is a CF7 fix. (This being my blog post, not Chris' comment.)
I bumped into this doing an insert of data from a query of query. I was staring at the data saying it is in X format but queryparam kept saying no it isn't.
What really bothers me is that when you create a query using the queryNew function and specify the data type of a column as date you end up with an odbcdate e.g. {ts '2001-07-21 14:22:32'}
Actually when you perform query of queries it also converts the dates to an odbcdate format if I recall correctly. It doesn't matter if you are working with the data locally but sending odbcdates in json could make parsing the dates on the other side with javascript a bit of a challenge.