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
Comments (19) | Post Comment | Ask Ben | Permalink | Print Page
Making ColdFusion's QueryNew() More Readable
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!
Posted by dc on Dec 4, 2006 at 9:57 AM
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.
Posted by Ben Nadel on Dec 4, 2006 at 10:03 AM
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.
Posted by Javier Julio on Dec 4, 2006 at 11:16 AM
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!
Posted by Kevin Sargent on Dec 4, 2006 at 4:51 PM
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 ) )#"/>
Posted by Stanley Steamer on Dec 12, 2006 at 3:03 PM
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.
Posted by Stanley Steamer on Dec 12, 2006 at 3:07 PM
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.
Posted by Ben Nadel on Dec 12, 2006 at 3:24 PM
Oh MAN! Thank you! I was banging my head on this problem. Glad I found your post on the subject.
Posted by Cris on Dec 28, 2006 at 6:26 PM
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.
Posted by William Haun on Feb 22, 2007 at 5:02 PM
Will,
Good point! I made that mistake when I was first messing around with this stuff.
Posted by Ben Nadel on Feb 22, 2007 at 5:08 PM
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".
Posted by Andy Matthews on Jun 25, 2007 at 5:08 PM
Always glad to help :)
Posted by Ben Nadel on Jun 25, 2007 at 5:11 PM
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 ;)
Posted by Kerr on Jul 27, 2007 at 2:00 PM
@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 :)
Posted by Ben Nadel on Jul 28, 2007 at 5:19 PM
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
Posted by Mark Flewellen on Oct 30, 2007 at 7:03 PM
@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.
Posted by Ben Nadel on Oct 31, 2007 at 7:23 AM
Another great tip Ben! I ended up here after a google search too. Those darn brackets were missing; knew it was something simple :)
Posted by Sam Daams on Jan 29, 2008 at 7:59 PM
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
Posted by Jose Galdamez on Jun 24, 2008 at 1:48 PM
@Jose,
Glad you are finding value in this stuff. Yeah, CFUNITED was a blast.
Posted by Ben Nadel on Jun 24, 2008 at 2:02 PM
