ColdFusion CFQueryParam Varchar-List Puts In Single Quotes For You

Posted June 17, 2008 at 9:30 AM by Ben Nadel

Tags: ColdFusion

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

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment