Re: cfqueryparam BIND list limit
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: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241282 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
Re: cfqueryparam BIND list limit
Dave, thanks for the clarification. Cutter Dave Watts wrote: >>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! > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241231 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: cfqueryparam BIND list limit
> 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! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241229 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
Re: cfqueryparam BIND list limit
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: > Billy Jamme said: > >>The problem occurs when I attempt to use cfqueryparam to fetch a >>2100+ item list of records. >> >> >> SELECT id >> FROM tempTable >> WHERE id IN (>cfsqltype="cf_sql_integer">) >> > > >>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." > > >>Now the question. Is there a way to get around this limit? > > > The limit it probably somewhere in a .properties file where you can up > it and recompile the driver. > > Jochem > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241217 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
Re: cfqueryparam BIND list limit
Billy Jamme said: > > The problem occurs when I attempt to use cfqueryparam to fetch a > 2100+ item list of records. > > > SELECT id > FROM tempTable > WHERE id IN ( cfsqltype="cf_sql_integer">) > > 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." > Now the question. Is there a way to get around this limit? The limit it probably somewhere in a .properties file where you can up it and recompile the driver. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241194 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
RE: cfqueryparam BIND list limit
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: SELECT id FROM tempTable WHERE id IN () And I receive the error: "The DBMS returned an unspecified error." However if I select the recordset using: Query 2: SELECT id FROM tempTable WHERE id IN (#list#) I receive no error. And receive the recordset. To add to the pie, if I run this query: Query 3: SELECT id, FROM tempTable WHERE id IN 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
