Calling a stored procedure with many arguments

From: Tommy Karlsson (tommy.karlsson.350_at_student.lu.se)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 13:34:49 +0100

Hi!
First Iīd like to apologize for double-posting this question. I first posted
it in comp.lang.java.help, but then realized this is probably a better place
to post it.

Now for my problem;

First some system info: Iīm using MS SQL-Server 2000 and Microsoft SQLServer
2000 Driver for JDBC (which is java 1.3 compatible, and uses JDBC spec 2.0).

What Iīm trying to do is to call a stored procedure in the database with
quite alot of arguments (41 in this example).

The call is set up dynamically, using a few steps;

1. put the name of the procedure in a string
String procedure="SaveOpRegWeb"; //just as an example, this is done
dynamicaly of course, but that isnt very interesting here.

2. create a string with a number of space-separated questionmarks, that
represents the arguments
//attributes is a LinkedList with a number of names of arguments in it (a
bit confusing name). The actual literals, with names corresponding to the
names of the arguments, are all stored in a LiteralTable as explained later.
String arguments="";
for(int i=0;i<attributes.size();i++) {
   arguments+="? ";
}
arguments.trim(); // just to remove the ending white-space

Someone in comp.lang.java.help told me that the arguments should be
comma-separated, but that just gives me this error:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect
syntax near ','.
So, from what I understand, thatīs not the issue.

3. create a CallableStatement object
//sqlconn is an Object of my own class SQLServerConnection, where
getConnection() returns a java.sql.Connection object.
CallableStatement statement=sqlconn.getConnection().prepareCall("{call
"+procedure+" "+arguments+"}");

4. map the questionmarks in the arguments-string to the actual arguments
//this goes something like this (whith some more checks here and there,
which arent very interesting)
//literals is an object of my own type LiteralTable, that hold a number of
Literals
//a Literal is also my own type and hold a literalīs value, type (of my own
defined set of types) and SQLType, which is the type as defined by
java.sql.Types

    ListIterator li = attributes.listIterator();
    Literal l;

    for (int index = 1; li.hasNext(); index++) {
      String next = (String)li.next();
      l = literals.getLiteral(next);
      ps.setObject(index, l.getValue(), l.getSQLType());
   }

5. execute the CallableStatement
//ok, everything works just fine this far, but when i try to execute the
statement (doing statement.execute();) I get this error:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The identifier that
starts with '
SaveOpRegWeb@P1@P2@P3@P4@P5@P6@P7@P8@P9@P10@P11@P12@P13@P14@P15@P16@P17@P18@
P19@P20@P21@P22@P23@P24@P25@P26@P27@P28@P29@P30@P31@' is too long. Maximum
length is 128.

As indicated by that string, the maximum number of parameters allowed seems
to be 31 in this case.
So my question is obviously: How do I call a stored procedure with more
arguments than that?
I should also mention that changing the number of arguments of the stored
procedure in the database is _not_ an option.

To solve this issue is quite crucial to my current project, so I would
really appreciate some help on this one!
Thanks in advance
/Tommy Karlsson

Relevant Pages

  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • RE: SQL stored procedure executing twice
    ... caused the stored procedure to execute twice. ... from one parent record to another. ... Dim stDocName As String ... The table tempCount does not change until I execute the VBA code line: ...
    (microsoft.public.access.modulesdaovba)
  • Re: EXECUTE permission denied on object... uh?
    ... Right click on the stored procedure ... Make sure the user in your conn string is allowed to EXECUTE ... with this user account as the group that has EXECUTE ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: EXECUTE tsqlstring with parameters
    ... I've tried using exec by itself, exec sp_executesql with an embedded exec ... string and sp_executesql with parameter substitution. ... until I need to execute a stored procedure that uses an OUTPUT parameter. ...
    (microsoft.public.sqlserver)
  • RE: SQL stored procedure executing twice
    ... from one parent record to another. ... Dim stDocName As String ... My pass-thru query syntax going to stored procedure ...
    (microsoft.public.access.modulesdaovba)