Error with queryNew and query of queries

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments