Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
cfqueryparam BIND list limit
Author: Bryan Stevenson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241282
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
Author: Cutter (CFRelated)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241231
Dave, thanks for the clarification.
Cutter
Dave Watts wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241229
> 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!
Author: Cutter (CFRelated)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241217
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 -----
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241194
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
Author: Everett, Al \(NIH/NIGMS\) [C]
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241148
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
Author: Billy Jamme
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:46049#241147
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
