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

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

Billy Jamme said: ----- Excess quoted text cut - see Original Post for more ----- The limit it probably somewhere in a .properties file where you can up it and recompile the driver. Jochem

I realize that the list is a list of integers, but, being a list, wouldn't the datatype be something akin to a string? How is that handled within cfqueryparam? Cutter Jochem van Dieten wrote: ----- Excess quoted text cut - see Original Post for more -----

All I can tell ya is Oracle is limited to 1000 elements in your IN/NOT IN list.....so 2100+ is most liley not allowed ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com

> I realize that the list is a list of integers, but, being a > list, wouldn't the datatype be something akin to a string? > How is that handled within cfqueryparam? The CFQUERYPARAM tag tells the database that it's a series of integers, and that's all that matters. Each integer value is replaced within the SQL statement using a placeholder, and the database is told that the placeholder corresponds to an integer value. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information!

Dave, thanks for the clarification. Cutter Dave Watts wrote: ----- Excess quoted text cut - see Original Post for more -----