ColdFusion CFQueryParam List Attribute Is Sweeet!
I just came across the a post over on CF-Talk about using the List attribute of ColdFusion's CFQueryParam tag. I have know about this attribute but have never used it or tested it. No better time than the present right? I thought I would give it a shot:
<!--- Set the ID list. --->
<cfset lstIDs = "1,2,3,4,5,6,7,8,9,10" />
<!--- Query the database. --->
<cfquery name="qTest" datasource="...">
SELECT
id,
name
FROM
blog_entry
WHERE
id IN (
<cfqueryparam
value="#lstIDs#"
cfsqltype="CF_SQL_INTEGER"
list="yes"
/>
)
</cfquery>
This works quite nicely! If you get the sql result of the query and dump that out, this is what the SQL code ends up looking like:
SELECT
id, name
FROM
blog_entry
WHERE
id IN ( ?,?,?,?,?,?,?,?,?,?,? )
As you can see, it treats each element of the list as its own bound variable (each list item gets its own CFQueryParam of sorts).
Now, one thing that I end up doing with many IN () clauses is appending a zero "0" to the list so as not to ever allow the list to be zero length:
SELECT
id
FROM
[table]
WHERE
id IN ( #ListAppend( lstIDs, 0 )# )
This way, since zero is never a valid id in my database schemas, this list will never be zero length and the functionality of the IN clause will never be hurt by the added zero. I thought maybe I could replace this idea with NULL attribute of the CFQueryParam as I do with dates:
<cfqueryparam
value="#lstIDs#"
cfsqltype="CF_SQL_INTEGER"
list="yes"
null="#YesNoFormat( NOT Len( lstIDs ) )#"
/>
Since the SQL "IN ( null )" works, I would figure this above CFQueryParam would work, but in fact, it does not. I think the problem is that it gets confused, or is conflicted between the list and null. Since it treats each list item as its own CFQueryParam, then it cannot apply NULL to a list that has no elements.
However, I found out that, just as with a regular ColdFusion list, you can have empty list items (multiple commas in a row) in a CFQueryParam list:
<cfqueryparam
value="1,2,3,,,,,4,5,,,,,6,7,8,,,"
cfsqltype="CF_SQL_INTEGER"
list="yes"
/>
This works just fine. Remember that ColdFusion ignores empty list values MOST of the time. Taking this into account, I can replace my ListAppend() method call by simply adding zero to the CFQueryParam value:
<cfqueryparam
value="#lstIDs#,0"
cfsqltype="CF_SQL_INTEGER"
list="yes"
/>
Notice "#lstIDs#,0". This works the same way by adding a zero to the list string. Now, even if lstIDs is zero length and the resultant value comes out to be ",0", ColdFusion will treat it as a list with only one item. How cool is that? This gives me the ability to use this feature of the CFQueryParam without having to know if the list has a length or not. Not to mention, the syntax is MUCH shorter than the whole ListAppend() method call and is, in my opinion, easier to read.
Now, you might think to yourself that you probably SHOULD know if a list has a length before you use it in a SQL statement. Perhaps you are right. But, honestly, for the kinds of things I am doing, there is nothing very major going on. But, I am always open to suggestions.
Want to use code from this post? Check out the license.
Reader Comments
BTW Oracle baulks at IN lists of greater than 1000 (>= 9.0.2.x anyway)
One to watch out for, a handy attribute of cfqueryparam!
Silly Oracle :)
Thanks for the heads up dc. I don't know much about Oracle, but if I switch over I will keep that in mind.
I can't speak from experience as I'm grateful not to work with Oracle but from what I've heard from fellow employees and good friends in the CF community Oracle can be just the thing to make your life end early. :) I'm sure its not all that bad but from I what I hear its different and it seems to hard to do some of the easy tasks we already know how in databases like SQL Server or MySQL.
Great tip, I use IN() and cfqueryparam all the time, but have always done more long winded checking of the list, this will save time for sure!
why not add your evaluation comment to the list (yes/no) attribute. It requires extra processing not needed 99 percent of the time, but it solves the problem of keeping your logic in one queryparam.
<cfqueryparam
value="#lstIDs#" cfsqltype="CFSQLINTEGER" list="#YesNoFormat( Listlen( lstIDs ) )#" null="#NOT YesNoFormat( Listlen( lstIDs ) )#"/>
jav..
Oracle rocks, sure some things are different, but some things make your life much much easier. Oracle Analytics allow you to do some awesome coding in your queries fast as lightning. Windowing and whatnot make for reports which rollup a ton of data faster than you can say Yippie Kay Yea. If you ever get the chance, spend some time learning Analytics, it will save the day many times over. BTW - SQL Server should include more robust analytics in the future. I use both, but have found that feature wise Oracle breaks the curve, even if it fails in the simplicity category.
Stanley,
That is a good suggestion. That would take care of the list of nulls. However, I feel that that duplicates the logic between the LIST and the NULL attributes. I see what you are saying in terms of the logic placement, but I am not sure if that outweighs the added effort.
Oh MAN! Thank you! I was banging my head on this problem. Glad I found your post on the subject.
Here's a reminder you still have you put the parentheses around the cfqueryparam: IN (<cfqueryparam ... list="yes" />)
I just beat my head against the wall for half an hour over that stupid oversight.
Will,
Good point! I made that mistake when I was first messing around with this stuff.
Dude...
This saved my butt just today. Thanks for the blog post. It's the first thing that came up when I googled for "cfqueryparam, list".
Always glad to help :)
Note that, at least in Oracle, a separate query plan will be saved in the shared pool correlating with the number of items you have in your list.
Ex:
Assuming a query is identical other than the cfqueryparam using list="true" in question.
Pass a 5 element list to cfqueryparam
Oracle generates and stores query plan A
Pass a 7 element list to cfqueryparm
Oracle generates and stores query plan B (crap!)
Pass a new 5 element list to cfqueryparam
Oracle looks up and uses query plan A (yay!)
This occurs because Oracle looks up queries in the shared pool based on the exact length and content of the query SQL. Dynamic list lengths create more or less "?" bind variable placeholders correlating to the length of the list. The good news is that, regardless of list content, same length lists will reuse a stored query plan.
Having worked at length with SQL Server, Oracle and MySQL, Oracle is definitely the hardest to get your hands around and can be the most unforgiving form a performance standpoint. At the same time, it has scaled for me better than any other platform. You just have to be really really nice to it ;) Also, someone mentioned query windowing, which is the best feature in Oracle. Looking forward and backward within a query is a feature that I sorely miss, and has the effect of getting one out of scenarios where they would otherwise use a cursor (gasp).
btw Ben, you need a "Preview Comment" button in a bad way... err, rather I do ;)
@Kerr,
That's an interesting aspect of Oracle.
As far as the "preview" button, I can certainly put in on the list of things to do :)
Hi Ben,
Just wondering if there is anyway to order the query when you pass the list into it. If I pass in a list of say 5,8,2,7,9
I find that the query outputs it as
2,5,7,8,9
Any thoughts
@Mark,
I never even thought to look at the ordering as I usually only use this within an IN ( ... ) clause (where order does not come into play). If order is important to you, you probably need to do some sort of CFLoop over list and treat them each individually.
Another great tip Ben! I ended up here after a google search too. Those darn brackets were missing; knew it was something simple :)
Ben,
Your site is great man. I'm glad you document all these neat little tidbits cause they save my life just almost every time I look for tips and tricks.
I was forever trying to figure out what the list attribute was good for. I read the documentation on Adobe's web site, but without any code samples using it the docs weren't of much help.
For me the understanding was more of a fundamental issue. I have to dynamically generate a query with multiple LIKE '%#foo#%' conditions, and I was trying to see if the list attribute would automate the task of writing multiple LIKE statements. It turns out that it won't, which is fine cause I was planning on using a list loop anyway. But now I see how great the list attribute goes with the IN clause.
Keep up the great work.
Hope you had fun at CF United.
Jose
@Jose,
Glad you are finding value in this stuff. Yeah, CFUNITED was a blast.
Thanks Ben!!!
I found it very helpful to....as always :)
Cheers!
This is a great tip and saved me lots of time. Thanks for posting this.
Ben,
In our code (CF 7, Oracle), we've got a problem. The previous coder put:
<cfquery>
Select blah
From blah
Where blah
<cfif ListLen(variable) lt 150>
AND that IN (<cfqueryparam value="variable" cfsqltype="CF_SQL_NUMERIC" list="yes")
</cfif>
</cfquery>
Problem is that the "variable" list is greater than 150 thus it's not filtering out all that needs to be filtered out. Usually "variable" is -1 (another way to avoid nulls, I suppose) which is set near the top of the page.
Based on another comment (near top), I see that Oracle chokes at 1000. I would assume based on the "150" that it's characters (our list mostly consists of four-digit numbers).
In SQL*Plus, I tried to copy/paste the entire script in and it choked -- probably at 1,000 characters. Had to paste in one line at a time.
I think my solution may need to be a temp table, but wondered if you had any other ideas.
-R
@Randall,
It's been a while since I experimented with this, so I cannot remember if the IN () approach broke due to the large number of values, or if it broke from the large number of query parameters.
If it's a parameter issue, you can always try using the IN without the CFQueryParam tag - just be sure to check the list of any possibly dangerous values.
If it breaks simply because of the number of values in the IN () clause, then that's another issue. Yeah, a temp table might be the best approach, then use a JOIN.
I have attempted to generate sql statements that contain the <cfqueryparam > tags in text variables. The query looks great when dumped, but will not execute correctly in the Coldfusion <cfquery > tags. Is this an interpretation issue or am I barking up the wrong tree? Thank you for your time!
@Ron
If I'm understanding your question correctly, it sounds like you are literally storing the code "<cfqueryparam..." inside a string and then outputting that string inside <cfquery>? If so, the tag doesn't work that way. CFQUERYPARAM is executed at runtime, so in order for it to work it has to be inside a CFQUERY tag and not a string. Hope that helps.
@Jose,
You are correct. That is what I was doing and your explanation answers my question.
Thank you.
@Ron
Glad I could be of assistance. CFQUERYPARAM really is a great one to have, even if it does get a bit tedious to write out multiple times. :)
@Jose,
Thanks for jumping in and helping out @Ron.
@Ron,
While I wouldn't really suggest storing ColdFusion code in a database (or however it is being stored), you *could* get this to work if you wrote the TEXT value to a file first and then CFInclude'd it into the CFQuery tag. Again, not my style, but that would execute it appropriately.
I must be little off on this but does cfqueryparam has any effect on sql view tables?
because I'm getting more getTickCount() (5 seconds more) on cfqueryparam
@Damiththa,
Not that I know of - but, I'm not a huge SQL person. I have heard from time to time that *some* queries perform worse with query param binding; however, I believe the vast majority of queries perform better with such variable bindings.
Might have done this the long way if I hadn't stumbled on this post - *so* useful; thanks, Ben!
you rock, Ben! I just came across this after running into this problem and searching, and it saved me a whole heck of a lot of headache and work! I know it's an old post and all, but THANKS! This was a big help today!
We are trying to keep conditional statements out of our queries. This piece fit nicely into that puzzle. Thanks, Ben!