ColdFusion CFQueryParam Binding vs. SQL Execution : Kinky Solutions : A Student's Perspective by Ben Nadel

ColdFusion CFQueryParam Binding vs. SQL Execution

Posted December 18, 2007 at 8:29 AM

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:

Launch code in new window » Download code as text file »

<cfset qContact = QueryNew(
"id, first_name, last_name, age",
"integer, varchar, varchar, integer"
) />
<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"
) />
<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
);
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" />
);
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
<cfif Len( qContact.first_name )>
first_name = <cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />,
</cfif>
<cfif Len( qContact.last_name )>
last_name = <cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />,
</cfif>
<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:

Launch code in new window » Download code as text file »

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

Download Code Snippet ZIP File

Related Blog Entries

CFQueryParam Data Conversion Caveat (Thanks Tony Petruzzi)

Getting One Record Per Group From A One-to-Many Join

DNA Based Dating Service

ColdFusion Jobs - Find or Post A ColdFusion Job Through DeveloperCircuit.com

Reader Comments

Can I suggest something to try?

I found that overall unless very few of the statements will actually be an update on the table that performing the update statement then checking @@rowcount (SQL Server specific of course) to check the number of rows affected then doing an insert if neccisary at that point is quite a bit faster than doing a select then doing the update or insert depending on the select.

Or if 99% of the statements are going to be inserts (it really has to be nearly all of them) than trying the insert and catching the duplicate key errors (of course that almost never happens) and updating at that point can also be faster if you are performing a significant number of inserts.

Posted by Mark B on Dec 18, 2007 at 9:19 AM

Word to the wise and heed my warning:

It's a very common misconception in the CF world that using val() around a value will ensure the value is an integer.

THIS IS WRONG!

Val() returns the numeric value of a string.

val("3.2456") => 3.2456
val("1234") => 1234

If you're application calls strictly for integer values (such as primary keys in a database), you need to use the Int() in conjunction with val() to ensure an integer value is returned.

int(val("3.2456")) => 3
int(val("1234")) => 1234

Posted by tony petruzzi on Dec 18, 2007 at 10:00 AM

Damn browser. Finishing my previous comment.

As such your cfqueryparam:

Should be:

Posted by Tony Petruzzi on Dec 18, 2007 at 10:02 AM

@Mark,

I like that. I never thought about checking the @@RowCount after an update and then performing an optional insert. Very clever.

@Tony,

You make a good point about the Val() method in general. However, as far as it goes when dealing with the CFQueryParam tag, ColdFusion (I think) should convert the data type for you based on the cfsqltype attribute (I think!?!).

To test, I ran this:

<cfquery name="qTest" datasource="#REQUEST.DSN.Source#">
SELECT
( <cfqueryparam value="3.5" cfsqltype="cf_sql_integer" /> ) AS test
</cfquery>

In the final query, the "test" column is "3", not "3.5". I don't know if ColdFusion is doing the conversion, or if SQL is going the converstion, but somehow, the CF_SQL_INTEGER type is making the conversion happen.

Posted by Ben Nadel on Dec 18, 2007 at 10:27 AM

@Ben,

Actually it's the driver that is doing the conversion. The only reason I stress the point about doing the conversion within CF with int() is because the Oracle driver I'm using doesn't and throws an error.

Posted by Tony Petruzzi on Dec 18, 2007 at 11:06 AM

@Tony,

Very interesting! I wish the documentation was a bit more obvious on this matter (maybe I just missed it). Thanks for pointing that out.

Posted by Ben Nadel on Dec 18, 2007 at 11:09 AM

Jesus Christ on a stick.

Posted by Frank Tudor on Dec 18, 2007 at 1:11 PM

He also likes being on a bicycle :)

Posted by Ben Nadel on Dec 18, 2007 at 1:27 PM

Seems like a prime example of code that belongs in a stored procedure to me.

Posted by Eric Knipp on Dec 19, 2007 at 4:47 PM

@Eric,

You might be right. I know very little about stored procedures; but, this is certainly a place where ALL speed boosts would be helpful. And, since parameters need to be passed into a stored procedure, certainly, we wouldn't run into the problem of not understanding what data is where, when.

Posted by Ben Nadel on Dec 21, 2007 at 11:14 AM

Post Comment | Ask Ben

ColdFusion CFQueryParam Binding vs. SQL Execution : Kinky Solutions : A Student's Perspective by Ben Nadel