I ran into a really frustrating "bug" last night that took me over an hour to debug! I was trying to run this massive ColdFusion query and kept getting an error about a data type not casting property in a CFQueryParam tag. The thing that was frustrating me so much, and making the error so hard to debug, was that the CFQueryParam tag was not even in a chunk of SQL that was supposed to be executing!!
Now, this query was massively huge (over 1,000 lines) and could certainly have been refactored into several small queries; but, it was one of those queries that started out small and grew very quickly as the client asked for more and more functionality to be added to the same page. But don't get bogged down with that aspect - instead, notice that what I am demonstrating in this dumbed down version of the query is a complete misunderstanding between ColdFusion CFQueryParam binding and SQL execution:
<!--- Create a query to hold our insert or update data. ---> <cfset qContact = QueryNew( "id, first_name, last_name, age", "integer, varchar, varchar, integer" ) /> <!--- Add a row to the update data. ---> <cfset QueryAddRow( qContact ) /> <!--- Update new row cells. NOTICE that I am only setting values for ID, first name, and last name; I am leaving the AGE attribute blank. That is because this is an UPDATE data set, not an INSERT data in which the data would all be required. ---> <cfset qContact[ "id" ][ 1 ] = JavaCast( "int", 4 ) /> <cfset qContact[ "first_name" ][ 1 ] = JavaCast( "string", "Anna" ) /> <cfset qContact[ "last_name" ][ 1 ] = JavaCast( "string", "Banana" ) /> <!--- !! LEAVING AGE FIELD NULL !! ---> <!--- Update the database. ---> <cfquery name="qUpdate" datasource="#REQUEST.DSN.Source#"> DECLARE @contact TABLE ( id INT, first_name VARCHAR( 30 ), last_name VARCHAR( 30 ), age INT ); DECLARE @id INT ; <!--- Put one conact in our table. NOTE: This table would not have to be populated ordinarilly. I am only doing this so that the example will work. Normally, this contact table (or whatever table I was targetting), would be a concrete table. ---> INSERT INTO @contact ( id, first_name )( SELECT 4, 'Anna' ); <!--- Set the internal which we are going to use to see if we are updating or inserting a record. ---> SET @id = ISNULL( ( SELECT id FROM @contact WHERE id = <cfqueryparam value="#qContact.id#" cfsqltype="cf_sql_integer" /> ), 0 ); <!--- Check to see if the ID was found or not. ---> IF (@id = 0) BEGIN <!--- The ID we have in our incoming data did NOT correspond to an ID existing in our database; therefore, we need to insert a new contact. ---> INSERT INTO @contact ( id, first_name, last_name, age ) VALUES ( <cfqueryparam value="#qContact.id#" cfsqltype="cf_sql_integer" />, <cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#qContact.age#" cfsqltype="cf_sql_integer" /> ); <!--- Store the new ID. ---> SET @id = @@Identity; END ELSE BEGIN <!--- The ID we have in our incoming data DID correspond to an ID in the existing dataase; therefore, we are just going to update the row based on the available data in the query. ---> UPDATE @contact SET <!--- Check for first name data. ---> <cfif Len( qContact.first_name )> first_name = <cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />, </cfif> <!--- Check for last name data. ---> <cfif Len( qContact.last_name )> last_name = <cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />, </cfif> <!--- Check for age data. ---> <cfif IsNumeric( qContact.age )> age = <cfqueryparam value="#qContact.age#" cfsqltype="cf_sql_integer" />, </cfif> id = @id WHERE id = @id ; END ; </cfquery>
There's a lot going on here, but basically, we want to either insert or update a row in the Contact datatable. To determine whether we are updating or inserting, we take the given ID and try to match it up to a record in the database. If the ID exists, we are updating the row with ONLY the data that is available. If the ID does not exist, we are inserting the new row with all the required data fields.
Now, this should be broken down into different queries, probably, but again, not the point of the post. When I run the code above, we should expect to update the only row in the table, but instead, we get the following ColdFusion error:
Invalid data for CFSQLTYPE CF_SQL_INTEGER. coldfusion.sql.Parameter$DataTypeMismatchException: Invalid data for CFSQLTYPE CF_SQL_INTEGER.
Ok, so there's a problem with the integer value. But which CFQueryParam tag? Well, since I know that we have an ID that lines up with an existing ID, I know that we are running the UPDATE portion of the SQL, not the INSERT. And, if you look in the update portion, you will see that the only CF_SQL_INTEGER value is the age field. However, you will also notice that I am only updating that field if the age value is numeric (which it is not since we left it NULL in our incoming query).
So, WTF?!? The only other CF_SQL_INTEGER is in the INSERT portion of the SQL statement, but that code is not even executing! Furthermore, when I comment out the INSERT portion, the code runs the UPDATE portion without error. At this point in debugging, I was seriously losing my mind; I had code that was erroring out, but when I commented out a piece of code that wasn't even being executed, the rest of the code ran perfectly well.
I was just about to start rewriting this massive query when I had my eureka moment! It suddenly become so obvious: ColdFusion CFQueryParam data binding does NOT happen at the same time as the execution of the SQL code. Furthermore, CFQueryParam data binding doesn't even depend on the path of SQL execution.
Think about the ColdFusion CFQuery tag as a function call and the CFQueryParam tags are your function arguments. Arguments need to be defined and set no matter what the internals of the function are doing; the same is true for the CFQueryParam tags. My error in thinking in the above query was that the CFQueryParam tags within the INSERT portion were not being evaluated since that part of the SQL was not being executed. This was dead wrong. All of the CFQueryParam tag data bindings were being evaluated, but only some of those bindings were then put into use.
And, since all of the CFQueryParam tags were being evaluated, the CFQueryParam tag for AGE in the INSERT portion was also being evaluated. However, since the age column in the incoming data set was NULL, ColdFusion could not properly convert it into an INTEGER value (hence the data conversion error). As a quick fix for this, I put a Val() around the value:
<cfqueryparam value="#Val( qContact.age )#" cfsqltype="cf_sql_integer" />
Now, even though that part of the SQL was not being executed, the CFQueryParam tag was evaluated properly.
It is rare that I have a query with such complicated logic that combines both ColdFusion and SQL conditional statements; but, when it does come up, it is very important to understand when ColdFusion CFQueryParam tag data binding occurs and to know that this happens regardless of SQL statement execution. In fact, you should probably think of all of your CFQueryParam tag bindings as taking place as the first step in the ColdFusion CFQuery tag; then, once all data bindings have been implemented, your full SQL statement will executed.
Want to use code from this post? Check out the license.