Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with:

CFQueryParam Data Conversion Caveat (Thanks Tony Petruzzi)

By Ben Nadel on
Tags: ColdFusion

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?




Reader Comments

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.