Setting ColdFusion Query Cell Values With JavaCast() Fixes Errors
I LOVE ColdFusion query of queries. They rock. They do, however, present some problems from time to time. One problem that I have encountered is that errors are thrown when running a query of queries on a query that has been manually updated. Take for example, this snippet of code:
<!--- Loop over users. --->
<cfloop query="qUser">
<!--- Check for participation. --->
<cfif qUser.pickup_count>
<!--- The user did participate in the time span. --->
<cfset qUser[ "did_participate" ][ qUser.CurrentRow ] = 1 />
</cfif>
</cfloop>
Here, I am merely looping over a query and setting a field value to 1 (defaulted to zero in original query) if the given user has at least one pickup (in a recycling program). Then, afterwards, if I run this query of queries (modified for example):
<cfquery name="qUserOverview" dbtype="query">
SELECT
(
COUNT( * )
) AS participating_user_count
FROM
qUser
WHERE
did_participate = 1
</cfquery>
... This MIGHT throw the 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 problem stems from the fact that the query of queries is having trouble figuring out the data type of the "1" I was setting into the query cell. It wants to see an integer value (of the original default value) and is having trouble converting the "1" into an "int" value. This is extremely frustrating as it makes no sense from a user standpoint. The problem, as I just stumbled upon, can be easily fixed with a JavaCast():
<!--- The user did participate in the time span. --->
<cfset qUser[ "did_participate" ][ qUser.CurrentRow ] =
JavaCast( "int", 1 )
/>
If I re-run the query of queries now, no problem what so ever. I think the thing to take away here is to remember that underneath the surface, the Query object is really a special form of Java result set that contains Java values. If we need to set value of a cell, we are setting Java values and must be sending values via JavaCast() to ensure logical data type conversion. Of course, the same thing could be said about anthing in ColdFusion, but for some reason, this rings most true with query objects.
Want to use code from this post? Check out the license.
Reader Comments
Thanks for this Ben. Solved a tricky issue for me. Also note that the same applies to QueryAddColumn(query, columnname, array) code, so you need to make sure all the elements in the array are also Javacast-ed. Also, I think this has been fixed in CF8.
JavaCast worked for my problem. Thanks Ben!
@Anthony,
Awesome man, glad to help.