Viewing By Entry / Main

Transfer Gotcha when using IN and lists!!

Posted At : September 11, 2007 5:26 PM |

Posted By : Luis Majano

Related Categories: Coldfusion, Transfer

First of all, I am a big advocate of Transfer, it is totally awesome and TQL totally rocks. I came up today to almost pulling my hair out on a very simple TQL query and thought I should share this so people have a grasp on details.

When using a query with the IN modifier and a list, you cannot just map a parameter with a value, you have to specify that its a list. (Reading the docs might help!!). Adventurous Luis, went ahead without fully reading Mr. Mandel's excellent docs and of course, fell in a cavernous pit of despair.

Here is my sample:

You have an incoming argument call it myList which contains a list: '1,2,3'. Then the following TQL


<cfsavecontent variable="tql">
<cfoutput>
SELECT Users.email
FROM mypackage.users as Users
WHERE Users.id IN ( :myList )
</cfoutput>
</cfsavecontent>


<cfset query = transfer.createQuery(tql)>

<cfset query.setParam("myList",arguments.myList,"string")>
<cfset query.setParam("true",true,"boolean")>
<cfset query.setCacheEvaluation(true)>


<cfset qEmails = transfer.listByQuery(query)>

Everything looks well and dandy right?? WRONG!!! This actually produces no results. Why in the world, well read the docs.

Query.setParam(name, [value], [type], [list], [null])
The 'list' argument tells the Query object that the value being set to the mapped parameter is actually a list of values, exactly like the 'list' attribute on . By default this set to false.

Hurray!! You need to set the list argument to TRUE. Man!!

One little TRUE and you are ready to roll!! So if you are working with IN statements, or just plain o'l lists, then remember the list argument.

Thanks Mark for documenting this!!