Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Seth Johnson and Bob Chesley
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Seth Johnson Bob Chesley

CFQueryParam Data Conversion Caveat (Thanks Tony Petruzzi)

By
Published in Comments (2)

Yesterday, I was talking about the difference between the time when ColdFusion CFQueryParam tags bind data and the time when SQL code is actually executed. In the comments to the tag, Tony Petruzzi mentioned that it is important to realize that simply putting Val() around a ColdFusion variable does not always convert it to the proper data type; rather, that is just returns a numeric value that might be an integer or a decimal.

To his comment, I stated that it really didn't matter since the CFQueryParam tag does the proper data conversion for you. This can be seen in this example where the CFSqlType attribute is casting the passed-in value to the appropriate SQL data type:

<!---
	Query for data. Here, we are going to let
	CFQueryParam take care of the data type
	conversions for us.
--->
<cfquery name="qData" datasource="#REQUEST.DSN.Source#">
	SELECT
		<cfqueryparam
			value="3.5"
			cfsqltype="cf_sql_integer"
			/> AS d_int,

		<cfqueryparam
			value="39435"
			cfsqltype="cf_sql_timestamp"
			/> AS d_time
	;
</cfquery>


<!--- Dump out data types. --->
<cfdump
	var="#qData#"
	label="CFQueryParam Data Conversions."
	/>

Running this, you can see that the CFQueryParam tag converted the data:

CFQueryParam Data Type Conversion Using CFSqlType Attribute

Here, the decimal 3.5 was converted to the proper int, 3, and the integer 39435 was converted to the proper date-time stamp, 2007-12-19.

I just assumed that ColdFusion was using the CFSqlType attribute value to make the data conversion; but, as Tony Petruzzi points out, it is actually the database driver that is making this conversion, not ColdFusion. And, not only is it the database driver, but this automagical data conversion does not work the same across different databases:

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.

This is a very important point to understand. By knowing how this feature works at a core level, it will more readily help me to help others. Luckily, I use Microsoft SQL Server which rocks the cat's pajamas; but, I can see that if I were to switch databases, a lot of my code that relies on this automagical data transformation would have to be changed. I guess this comes down to a real portability question - do you want to leverage the features of a database driver that couple you tightly to that driver?

Want to use code from this post? Check out the license.

Reader Comments

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