I used to get around Oracle's 1000-item list limit by breaking up the list into an array of lists then looping over the array and putting OR between each iteration.
I expect that there is a much more elegant way to do it with a temporary table. -----Original Message----- From: Billy Jamme [mailto:[EMAIL PROTECTED] Sent: Monday, May 22, 2006 1:40 PM To: CF-Talk Subject: cfqueryparam BIND list limit Hello All, I'm having some DB issues and I'm wondering if I could get some help with it. I'm running CFMX7, and MSSQL2k. On this specific task, I'm using the IN or NOT IN clauses in sql to grab a set of records. The problem occurs when I attempt to use cfqueryparam to fetch a 2100+ item list of records. Query 1: <cfquery name="selectList" datasource="database"> SELECT id FROM tempTable WHERE id IN (<cfqueryparam value="#list#" list="true" cfsqltype="cf_sql_integer">) </cfquery> And I receive the error: "The DBMS returned an unspecified error." However if I select the recordset using: Query 2: <cfquery name="selectList" datasource="database"> SELECT id FROM tempTable WHERE id IN (#list#) </cfquery> I receive no error. And receive the recordset. To add to the pie, if I run this query: Query 3: <cfquery name="selectList" datasource="database"> SELECT id, FROM tempTable WHERE id IN <cfqueryparam value="#list#" list="true" cfsqltype="cf_sql_integer"> </cfquery> I still get the same error message: "The DBMS returned an unspecified error." Even though syntaxically the query is invalid. To further troubleshoot I installed the jTBS 1.2 drivers and I ran all 3 queries again. The result this time around was: Query 1: "Prepared or callable statement has more than 2000 parameter markers." Query 2: No errors. Query 3: "Prepared or callable statement has more than 2000 parameter markers." What I'm guessing is that there is some form up of upper limit on the number of BIND variables that each driver can handle. What leads me to this conclusion is a couple of things. 1. The query will crash before the DB "sees" it. 2. Non-BINDed variables execute fine. Also, if I use the default DataDirect SQL drivers I can increase the number of BIND variables to 2097, where if I run the jTDS drivers I'm limited to 2000. Now the question. Is there a way to get around this limit? And if so, should I try to? Many thanks, Jim ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241148 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
