Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with:

Setting ColdFusion Query Cell Values With JavaCast() Fixes Errors

Posted by Ben Nadel
Tags: ColdFusion

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.




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.

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.