ColdFusion CFQueryParam Varchar-List Puts In Single Quotes For You
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:
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:
<!--- 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.
Want to use code from this post? Check out the license.
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.
@Dan,
I believe you are correct. I know that it will definitely convert numbers to date/time stamps.
cfqueryparam list maybe the greatest thing ever....well ok that maybe a bit much but still very cool and very useful.
@Scott,
Agreed - it is pretty badass.
Hm,
When I do this in my query:
Flag = <cfqueryparam value="#arguments.bean.getFlag()#" <cfsqltype="cf_sql_bit">
and then dump the query, It shows that FLAG = "TRUE".
So it does not appear that it is casting the boolean to a bit.
@CFUser,
As long as the datatype in the database is a "bit", it should work fine. Is your query not running properly?
YOU ROCK!!! You're WAY better than documentation!
@Cheryl,
Ha ha, thanks!
Well that just saved me a ton of time and a headache. Thanks!
Ben,
Thanks for this CFQueryParam List post and your previous one, it has really helped me out.
But I don't know if I've found another issue that is related to this. But here's what I'm trying to do.
I have two tables. Members and Companies. A Member can have multiple companies. So I have a field in the members table called company_id its a varchar and can contain one or a list of ids separated by commas that matches up to the company_id (id, integer, auto number) in the Companies table.
I want to select all the companies a member has listed. I'm doing some thing like this.
<cfquery name="GrabInfoForEmail">
Select members.firstname, members.lastname, companies.company_name
FROM members, companies
WHERE member_id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
AND companies.company_id IN (<cfqueryparam value="members.company_id" cfsqltype="cf_sql_varchar" list="true" separator=",">)
</cfquery>
It returns nothing. But if I change members.company_id in the cfqueryparam tag to an actual list like 1,3,7. It works.
If I change cfsqltype to cf_sql_integer it throws an error.
Any thoughts?
@Jeff,
The CFQueryParam tag is used to bind ColdFusion data to the query. You cannot use it to bind SQL data to the query because, for one reason, the SQL data is not available at the time the CFQueryParam data executes.
Remving the CFQueryParam tag is the way to do what you want.
@Jeff
Two things:
<cfqueryparam value="members.company_id" cfsqltype="cf_sql_varchar" list="true" separator=",">
the value would need to be wrapped with ## like this: <cfqueryparam value="#company_id#" cfsqltype="cf_sql_varchar" list="true">
Also you might rewrite that query using joins:
<cfquery name="GrabInfoForEmail">
Select members.firstname, members.lastname, companies.company_name
FROM members
inner join companies on members.company_id = companies.company_id
WHERE member_id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
</cfquery>
forgive the typos, hopefully you get the point.
@Ben,
Thanks for the quick reply. Didn't get a chance to mess with this code till today.
What you said works. Kind of? Maybe I can't do this with Natural joins?
I now have this code...
<cfquery name="GrabInfoForEmail">
Select members.firstname, members.lastname, companies.company_name
FROM members, companies
WHERE companies.company_id IN (members.company_id)
</cfquery>
members.company_id is a field (within the members table) with a list of IDs (1,3,4).
It only returns the info for the first in the list. So what ever company_id 1 is.
But if I change the code to "companies.company_id IN (1,3,4)" It works and returns all company info for 1, 3, and 4.
Am I missing some thing? Can you not do this with Natural joins? I've been looking around trying to find info on this, haven't found much.
@Scott
Thanks for the reply. What you suggested works too. But it does the same thing as the natural join from above.
@Jeff,
To be honest, I have never worked with a list of IDs stored in a field AND used it in a JOIN logic. It's possible that the database engine doesn't work that way.
I think the problem is that the database field is a VARCHAR and, in a way, you are trying to do an implicit conversion of data types to a list of integers.
I'll try to do some thinking on this.
This was the perfect solution to an issue I have been having trouble with in regards to single quotes in a query variable. Thanks very much.
@SuperAlly,
Glad this helped out.
@SuperAlly,
Could you please let me know what you did, and I have same issue.
Thanks,
Leon
@Leon,
Sure, but I basically just did what Ben outlined above, I certainly didn't reinvent the wheel :) I was dealing with a form field with a multi-select list of cities. I did:
WHERE municipality IN
(
<cfqueryparam value="#FORM.city#" cfsqltype="cf_sql_varchar" list="true" />
)
Cheers
@SuperAlly,
Thanks, it's helpful.
+1 for the separator attribute :)
@Existdissolve,
I know this sounds crazy, but I don't think I even noticed that other people mentioned the separator attribute. I don't remember knowing that that was even in the possible tag attributes. I'll have to play around with this.
@Ben--
The docs are a little weird on this attribute, IMO. They say that "separator" is required if you "specify a list in value attribute." Does that mean it's required if you specify a list in the value attribute WITHOUT using the list attribute? And does "required" mean if you don't use it, the value attribute will be treated as a regular string, and not as a list (e.g., single quotes around list items)?
I tried passing my pipe-delimited list to an IN() without the list attribute and, as expected, it passed a regular string to SQL (e.g., 'item one,item two,item three'), apparently ignoring the separator attribute.
<cfqueryparam value="#mylist#" separator="|" />
When I added the list attribute back in, CF passed a nice set of quoted list items to SQL (e.g., 'item one','item two','item three').
<cfqueryparam value="#mylist#" separator="|" list="true" />
So I guess I take a bit of exception to the language of "required." As your example shows, cfqueryparam will work just fine with a combination of value and list, so long as the list is comma-delimited. Separator is only really "required" if your delimiter is not a comma.
Am I missing something here?
And since I'm already complaining :), the term "separator" is strange. Since it's only used in conjunction with lists, I think "delimiter" makes more sense.
Ok, enough complaining. Even though the documentation is a bit wonky, I'm just happy that this functionality exists :). It's a prime example of ColdFusion doing what ColdFusion does best--making web application development super fast and easy.
@Existdissolve,
Yeah, the documentation is a bit funky. It's a bit odd to say something is required AND give it a default value. Although I am not sure about the rest of the docs - maybe they use the same approach.
I want to play around, see if I can get this to do anything interesting.
Hello, I am not sure why this not working. I am using mysql db and my query didn't run when I supply two values
<cfquery name="dff" datasource="#request.dsn#">
select * from courseregistration where COURSEID <> <cfqueryparam value="139,132" cfsqltype="cf_sql_numeric"
list="true"
/>
</cfquery>
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '132' at line 1
@Jameson,
Since you are using a list you have to use the IN operator. In your case NOT IN.
Try
I'm assuming COURSEID is an integer. But I think (correct me if I'm wrong) you still need to do cfsqltype="cf_sql_varchar" because you are working with a list. If not, try cfsqltype="cf_sql_integer"
As always, your blog is better CF documentation than that provided by Adobe.
very helpful blog... I agree with @Jim Kochert
Thanks again for this one useful tip!