ColdFusion CFQueryParam Varchar-List Puts In Single Quotes For You
Posted June 17, 2008 at 9:30 AM
I love ColdFusion's CFQueryParam tag. I use it in almost all of my queries. One of the CFQueryParam features that I love is the List attribute; the List attribute, which I have blogged about before, allows you to use one CFQueryParam tag to bind a list of values to your query. Most of the time, I use this with a list of IDs:
Launch code in new window » Download code as text file »
- WHERE
- id IN (
- <cfqueryparam
- value="1,2,34"
- cfsqltype="cf_sql_integer"
- list="true"
- />
- )
This has always worked great. The other day, however, it did trip me up a bit. I started using it with a list of Varchar values rather than Integer value. In a particular query, I wanted to get all contacts from a list of given states:
Launch code in new window » Download code as text file »
- <!--- Select contacts from NY and MA. --->
- <cfquery name="qContact" datasource="#DSN.Source#">
- SELECT
- c.id,
- c.first_name,
- c.last_name
- FROM
- contact c
- INNER JOIN
- contact_information ci
- ON
- (
- c.contact_information_id = ci.id
- AND
- ci.state IN (
- <cfqueryparam
- value="'NY','MA'"
- cfsqltype="cf_sql_varchar"
- list="true"
- />
- )
- )
- </cfquery>
Notice that I am passing the state abbreviation list, 'NY','MA' into the CFQueryParam tag. This looked good to me, but nothing came back in my query. I knew there were contacts in the database that were in NY and MA, but yet, no results. This actually took me a good 15 minutes to debug (especially because it was behind an AJAX call which isn't the easiest thing to debug - I had to use CFThrow tags rather than CFDump tags to get any data echoed). As it turns out, ColdFusion's CFQueryParam puts in the single quotes for you; so, instead of passing in:
'NY','MA'
... all I needed to do was pass in:
NY,MA
... and the combination of cf_sql_varchar and list="true" would know to create a varchar list (using single quotes).
I wonder if I have made this mistake before on other applications and just never noticed since it didn't error out? Hmmmmm.
Download Code Snippet ZIP File
Comments (4) | Post Comment | Ask Ben | Permalink | Print Page
Reader Comments
I'm just going from memory on this so someone correct me if I'm wrong ... I believe that CF will convert a boolean value to 0 or 1 if the cfsqltype is set to "bit". This eliminates the need to do any condition statements to get the correct value to the database. It also allows for boolean expressions to be put directly into the queryparam value field.
Posted by Dan Roberts on Jun 17, 2008 at 10:03 AM
@Dan,
I believe you are correct. I know that it will definitely convert numbers to date/time stamps.
Posted by Ben Nadel on Jun 17, 2008 at 11:13 AM
cfqueryparam list maybe the greatest thing ever....well ok that maybe a bit much but still very cool and very useful.
Posted by Scott P on Jun 17, 2008 at 1:10 PM
@Scott,
Agreed - it is pretty badass.
Posted by Ben Nadel on Jun 17, 2008 at 1:12 PM
