ColdFusion CFQueryParam List Attribute Is Sweeet!
Posted December 4, 2006 at 9:10 AM
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:
Launch code in new window » Download code as text file »
- <!--- 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:
Launch code in new window » Download code as text file »
- 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:
Launch code in new window » Download code as text file »
- 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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Print Page
Newer Post
Making ColdFusion's QueryNew() More Readable
Older Post
Loading Java Classes With coldfusion.runtime.java.JavaProxy
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.
