SQL Server JDBC - Re: getting Multiple resultsets in java using stored procedure in

Author

Re: getting Multiple resultsets in java using stored procedure in

Joe Weinstein

2006-03-05, 8:24 pm

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.

> boolean result = cStmt.execute();
> System.out.println("result:" + result);
>
> if(result)
> {
> ResultSet rs1 = cStmt.getResultSet();
> System.out.println("rs1" + rs1);
> while(rs1.next())
> {
> <process resultset>
> }
> rs1.close();
> boolean result2 = cStmt.getMoreResults();
> System.out.println("result2:" + result2);
> if(result2)
> {
> ResultSet rs2 = cStmt.getResultSet();
> System.out.println("rs2" + rs2);
> while(rs2.next())
> {
> <process resultset>
>
> }
> rs2.close();
> }
> }
>
> I'm getting the resultset object in java but there are no records in
> the resultset, but if i run the same query seperately in the query
> analyser, i get the results.
> Am I doing anything wrong?
>
> Any help in this regard would be really great. Thank you.
>
> -Anil
>

Joe Weinstein

2006-03-05, 8:24 pm

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:
>
>
>

Joe Weinstein

2006-03-06, 7:16 pm

anil wrote:
> Thanks Joe.
> The csv parameter list is unknown, so I cannot dynamically generate the
> callable statement. (ofcourse, I can parse and tokenize them, and
> dynamically assign it to the Cstmt, but that looks cumbersome )
> This works well in Oracle I guess. Any workaround for this in MS SQL
> 2000?
>
> -Anil

No it doesn't work with Oracle. It has been a longstanding temptation
for JDBC progrmmers to want to send various pieces of SQL as 'parameters'.
A string which the DBMS would parse as a list if it got it as SQL, will
be taken as a single simple string if send as a parameter. You're actually
lucky it works this way. Otherwise you would have a devil of a time if
you actually had strings in your database that had commas in them.
For safety and sanity, a parameter is *a* parameter. One value.
In your case of an unknown number of parameters, you have to do
the necessary parsing into the actual parameter values. This can be
done at the client in Java, or you can write a procedure which takes
your oen string, and parses it at the DBMS...
Good luck,
Joe Weinstein at BEA Systems

Sponsored Links