Skip to main content
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with: Dmitry Kolesnikov
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with: Dmitry Kolesnikov

Setting ColdFusion Query Cell Values With JavaCast() Fixes Errors

By
Published in Comments (3)

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

19 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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel