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:241147
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
