CFQueryParam Data Conversion Caveat (Thanks Tony Petruzzi)
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:
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 knew I saw this question before. ColdFusion Muse was talking about this at the beginning of the year.
http://www.coldfusionmuse.com/index.cfm/2007/2/24/implicit.conversion
Seems he pinpointed it to the JDBC driver.
Tony its a great work. This code is very difficult, try to understand :) Thank you.