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.

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.

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.

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.

Pass a new 5 element list to cfqueryparamOracle 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 ;)

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.

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.

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

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!

@RonIf 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.

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.

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.

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!

I am the co-founder and lead engineer at InVision App, Inc — the world's leading prototyping,
collaboration & workflow platform. I also rock out in JavaScript and ColdFusion 24x7 and I dream about
promise resolving asynchronously.