CFQueryParam Data Conversion Caveat (Thanks Tony Petruzzi)
Posted December 19, 2007 at 9:37 AM by Ben Nadel
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?
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.
