Tuesday, September 5, 2006

CFQUERYPARAM and IN/NOT IN clauses

I discovered today, to my unpleasant surprise, that there is apparently a limit to the number of elements there can be in an SQL IN/NOT IN clause using cfqueryparam. I'm not sure of the limit, but I had an IN clause with some 5,898 primary keys in it, and I consistently got this exception from CF:
The DBMS returned an unspecified error.

This suggests to me that perhaps the JDBC driver or MS SQL Server 2005 itself has a limit on the number of items in an IN clause. This post at HouseOfFusion suggests that Oracle has a limit of 1,000 items, and that MS SQL Server’s limit is something around 2,100 items.

My workaround was to loop over the list of 5,898 items individually, like this:
WHERE ID IN (
<cfloop list="#Arguments.IDs#" index="id">
#id#<cfif id neq ListLast(Arguments.ID)>,</cfif>
</cfloop>
)

It’s not the greatest thing ever, I know. Hopefully I can use <cfqueryparam/> instead of the raw value in it. That’ll be a test for later.

tags: , , , , ,

1 comment:

Jon said...

This is a fundamental limit for databases.
SQL is not designed for large in-line datasets.
You are abusing IN. When you run into this problem it is a 'bad smell' coming from your DB schema, time for a refactor.
If you really need more items than that then you can use a temporary table, insert all the values into it, and then use a select inside of the IN. Selects are not constrained by the in-line data limit.

Post a Comment