Re: getting Multiple resultsets in java using stored procedure in MS SQL Server 2000

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



anil wrote:

Hi Joe and Subhash, thank you for your replies.

I am using a comma seperated value string as the parameter viz
'aaa','bbb','ccc'
variable defined in sp is varchar(4000).
the query used in the procedure is a select statement with an IN clause
eg. select field_name from table where field_name IN (<csv string>)

just executing the query with a csv string as parameter works fine, but
the procedure fails.

I found out that if the parameter is a single string 'aaa', i'm getting
the resultset with records, but if its a csv string, its returning an
empty resultset.
I tried printing out the input variable in the sp and it looks
fine...it prints viz 'aaa','bbb','ccc'

did any of you came across this problem, any pointers would help.
Thank you.

-Anil

Ok, well that's your problem. You have one variable marker in the SQL,
but you want the DBMS to accept it as a list of parameter values.
That won't fly. Parameter substitution is really only single data
values. If the procedure is defined to take three parameters,
you will have to put a '?' for every value, eg:

cStmt = conn.prepareCall("{ call spXXX (?,?,?)}");
cStmt.setString(1, "aaa");
cStmt.setString(1, "bbb");
cStmt.setString(1, "ccc");
etc.
HTH,
Joe Weinstein at BEA Systems


Joe Weinstein wrote:

anil wrote:


I have a stored procedure with multiple (two) queries in MS-SQL 2000
Server,

I'm trying to get the resultset in java
like


cStmt = conn.prepareCall("{ call spXXX (?)}");
cStmt.setString(1, val);


What is the variable defined as in the procedure?
What is the column type that the variable is
used to compare to? I suspect you're trying to rely
on the DBMS doing implicit conversion, which it will
do for fresh SQL, but not parameters.
Joe Weinstein at BEA Systems.



.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Error using " in .open with ADODB.Recordset
    ... recordset but I'm getting hung up on the SQL statement. ... query that has a string as a condition. ... Dim mrk As String ...
    (microsoft.public.data.ado)