SQL Parameters limit?

HCL Domino Domino Forum
1

We have a website running on a domino server on iSeries. Typically to get or give backend data we create stored procedures on the iSeries and call them from a domino webpage.

Below I’ve included an example of a call where I pass in 10 parameters. It’s been suggested to me that we might want a new webpage where we would need to pass 200+(Two HUNDRED) parameters to a stored procedure.

Is this feasable? The help database says that there’s no practical limit to the length of the SQL statement but passing 200 parameters to a stored procedure makes me nervous about performance and reliability.

While we have Notrix, they want to try and do it this way so the data is upload “live”…

Any insight would be appreciated.

Dim con As New ODBCConnection

Dim qry As New ODBCquery

Set result = New ODBCResultset

Dim code As String

Dim p1 As Double

code = doc.remote_user(0)

p1 = Val(doc.pro1(0))

p2 = Val(doc.pro2(0))

p3 = Val(doc.pro3(0))

p4 = Val(doc.pro4(0))

p5 = Val(doc.pro5(0))

p6 = Val(doc.pro6(0))

p7 = Val(doc.pro7(0))

p8 = Val(doc.pro8(0))

p9 = Val(doc.pro9(0))

p10 = Val(doc.pro10(0))



Set Qry.Connection = con





Set result.query = qry

qry.sql = "CALL WEB.WEBCHARGES('" & code & "'," & p1 & "," & p2 & "," & p3 & "," & p4 & "," & p5 & "," & p6 & "," & p7 & "," & p8 & "," & p9 & "," & p10 &")"

’ Print qry.sql

result.execute
2

Subject: SQL Parameters limit?

David,

There are default limits on the Domino side that can be adjusted.

First off, there is the limit on how long the URL can be regardless of parameter count. You can change this in the Domino server doc Internet Protocols ==> HTTP ==> HTTP Protocol Limits section.

Second, ODBC has a limitation. I don’t recall what it is but when my monster query, where I feed a ton of parameters to a stored procedure in SQL Server, bombed, I solved the problem by rewriting my agent in Java using JDBC as the interface to SQL Server.

Third, SQL Server 2000 has a limitation of 8,060 bytes per row, so if your resultset requires building a table with rows in excess of this, you will have to get creative, as we did.

Ken

3

Subject: RE: SQL Parameters limit?

Ken,

Thanks for the response on this. I’m not actually passing the parameters through a URL, but in a backend agent that reached out to the iSeries (as/400) backend server via odbc. I knew about the URL length problem but didn’t know you could adjust it. Thanks for that little tidbit! :slight_smile:

Regarding ODBC it’s Interesting that it seems you did hit some limitations doing this… doesn’t sound too promising to me…