|
Home > Archive > SQL Server JDBC > March 2006 > getting Multiple resultsets in java using stored procedure in MS SQL Server 2000
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
getting Multiple resultsets in java using stored procedure in MS SQL Server 2000
|
|
|
| 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);
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
| |
| subhash.daga@gmail.com 2006-03-05, 8:24 pm |
| You need to use executeQuery() method not execute(). So try something
like this:
ResultSet result = cStmt.executeQuery();
if(result!=null) {
... do your stuff ...
}
| |
|
| 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
Joe Weinstein wrote:
> anil wrote:
>
>
> 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.
| |
|
| 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
| |
|
|
|
| Thank you Joe.
I implemented it using simple statement (multiple statements instead of
proc).
| |
|
| Thank you Green.
Its working perfectly fine, the way I wanted.
|
|
|
|
|