Maximum list values for cfqueryparam

October 22, 2008

I've just managed to get ColdFusion 8 to blow up MS SQL Server 2000! This is the error message I was getting:

 

Type Database
Detail [Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 0.
Message Error Executing Database Query.

 

It turns out that this is due to passing a list with too many values to a cfqueryparam tag with the list attribute set to true. There are three simple solutions:

  1. As I know that the values are safe I'm not too worried about the security implications, so I could replace
    IN ( <cfqueryparam value="#my_long_list#" cfsqltype="cf_sql_integer" list="true" /> )
    with
    IN ( #my_long_list# ). 
  2. Limit the number of values in the list I'm passing in.
  3. Use a subquery in my where clause.

In the interest of science I discovered that the maximum number of values that you can pass is 2096, which is a bit extreme and way more than is sensible!

 

7 comments

  1. oddly enough I had this exact limitation pop up in one of our legacy products yesterday, it was part of some java code where a query was being ran for each item on a list and with each iteration it included the sql for each previous item as a negated where clause so by the time it reach the end of the list (28 items) it had the modified contents of the previous 27 queries (each of those including the cumulative queries of the queries before them also) there were well over 2000 statement placeholders and sql server exploded :)

    if i could find who wrote the code i think i would slap them in their face haha.

    The error I got was :-
    org.apache.ojb.broker.PersistenceBrokerSQLException:
    * Can't prepare statement: < lots and lots of sql here >
    * Exception message is [Prepared or callable statement has more than 2000 parameter markers.]
    * Vendor error code [0]
    * SQL state code [22025]

    I'm posting it in case it helps other people find this page when searching on google trying to figure out why their giant queries are killing sql server :)

    Comment by Ciqala Burt – October 22, 2008
  2. @Ciqala, thanks for posting that comment. I ran into the issue on a legacy site as well, sadly it was me that wrote it about 5 years ago - I just hadn't anticipated the site having over 2096 products on offer!

    Comment by John Whish – October 22, 2008
  3. Well I hope you gave yourself a good slap :)

    Comment by Ciqala Burt – October 22, 2008
  4. I ran into that limit on bind parameters several years ago myself. And blogged about it at the time although the blog where I posted it isn't up anymore. I don't remember where I found it, but I know it reminded me of a school management application I had worked on where there were frequently long lists of students with checkboxes and I wondered if they'd run into the issue.

    Comment by ike – October 22, 2008
  5. Assuming you're using this in a SQL IN clause, you probably should look at using WHERE EXISTS instead. All DBMS's have limits on the number of elements in an IN clause.

    Comment by Brian Kotek – October 22, 2008
  6. @Ciqala, I took myself to one side and gave myself a good talking to :)

    @Ike, yeah that would do it, I guess you'd just have to not bind parameters in that instance. Although ticking 2096+ tick boxes would be a bit excessive :)

    @Brian, I was using an 'IN' clause. I didn't know that about using EXISTS. Thanks for the tip.

    Comment by John Whish – October 23, 2008
  7. There was usually a "select all" at the top. ;)

    Comment by ike – October 23, 2008

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.