QueryNew() Type Casting Issues With Manually Altered Queries
Posted April 19, 2006 at 8:12 AM by Ben Nadel
I am having this really strange problem involving a query of queries that I have never encountered before. Basically I am building a query with the QueryNew() method:
<cfset REQUEST.SearchQuery = QueryNew(
"title, date, preview, link, score",
"VARCHAR, DATE, VARCHAR, VARCHAR, INTEGER"
) />
Then I build the query based on other queries. If I display this one, at this point, it's totally fine. After building the query I do a QofQ like so:
<cfquery name="REQUEST.SearchQuery" dbtype="query">
SELECT title
FROM REQUEST.SearchQuery
ORDER BY
score DESC,
[date] DESC
</cfquery>
This sometimes throws the following 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."
I have narrowed it down to the line of code that causes the "casting" issue. It happens when I update the SCORE value. It really doesn't matter how it is updated as long as it is set by a method call.
For example, the following cause NO ERROR:
<cfset REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] = 100 />
<cfset REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] = 0 />
<cfset REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] =
REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] />
However, the following all cause error:
<cfset REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] = ArrayLen( arrCriteria ) />
<cfset REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] =
Max( 5, REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] ) />
Heck, even this causes errors:
REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] =
Max( REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ],
REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ]
) />
Its like any function call that returns a number corrupts the SCORE value for the QofQ.
NOW, EVEN MORE CRAZY, if I take out the data types in the QueryNew() method, so that it is just:
<cfset REQUEST.SearchQuery = QueryNew(
"title, date, preview, link, score",
) />
, the whole thing works like a charm. CRAZY! I have posted this to the CFTalk list... can't wait to see what people's solutions are.
Reader Comments
Ben,
I had the same issue in a different context. I was attempting to add boolean columns to 2 queries using the QueryAddColumn function and specifying the column type as BIT. From this I was UNIONing the QUERY results together using QofQ. The error disappeared after I removed the BIT casting parameter from the QueryAddColumn functions with your recommendation.
Thanks for posting this. You saved me a ton of time with that coding gem.
Thans,
Mike
@Mike,
Glad to help.
Just had this after some seemingly minor changes to a page... it turns out, moving a query object from the unnamed scope to the session makes then entire structure more type-sensitive.
I got a "Error casting..." message for:
SELECT * FROM session.st_invoice.query WHERE division_code > 0;
...despite that...
SELECT * FROM st_invoice.query WHERE division_code > 0;
...worked fine, having changed NOTHING else in the code except moving the QueryNew and all its relevant commands to write directly to the session variable.
It took me about 5 minutes to figure out What, but I spent the better part of the day on "why" before coming to the realization that if I set division code with JavaCast, it worked just fine...
@Christopher,
That is odd. I wonder why the scope change would have any change at all in the sensitivity. I didn't see from your code, though, where the JavaCast() made a difference.
You are the man Ben! I was absolutely stumped by this crappy error message and my experience with QoQ is seriously lacking. Still got inspiration when I read about JavaCast() on numbers. Solved the error.
I wish the Coldfusion had proper debugging tools like Java.
any way thank you very much and to everyone else who uses this site.
Just for the error was caused by a variable I was using that had a number stored. However the Cold fusion debugger only complained when I tried to sort my newly created query. Oh well, I won't forget this any time soon.
thanks
Eish
@Eish,
Glad to help out.
