Accessing Return Code Closes RecordSet?

[Corey McGlone]

I'm invoking a stored procedure (in SQL 2000) from a Java application. That stored procedure returns a return code and a result set. My code looks something like this:

// Create a CallableStatement to invoke the stored procedure
CallableStatement cs = conn.prepareCall("{? = call Name_Of_Stored_Procedure(?, ?)}");
 
// Set the parameters, including the return code
cs.registerOutParameter(1, Types.INTEGER); //return code
cs.setString(2, lastName);
cs.setString(3, firstName);
 
// Execute the query
ResultSet rs = cs.executeQuery();
 
// Check the return code. If it's 0 (indicating success, process the ResultSet).
if ( cs.getInt(1) == 0 )
{
    // This line gives me an error
    while ( rs.next() )
    {
        // Do something with the result set
    }
}

If I execute this code as is, I get an error at the line "while ( rs.next() )". My stack dump reads that the ResultSet is closed.

To test, I removed the if check so that I no longer check the return code and my code executes just fine. So, it would appear that checking the return code closes the ResultSet. Why? Is there any way to check it without closing the ResultSet?

Thanks,
Corey

[Napa Sreedhar]

I remember reading that we have to maintain the order in a specific way

like
rs = cstmt.executeQuery()
process the result set while(rs.next()) {

}
// out parameter
cstmt.getInt(1)

[Napa Sreedhar]

I am really unsure why the result set is getting closed.

[Corey McGlone]

Originally posted by Napa Sreedhar:
I remember reading that we have to maintain the order in a specific way

like
rs = cstmt.executeQuery()
process the result set while(rs.next()) {

}
// out parameter
cstmt.getInt(1)

I think something like that would function, but it really doesn't work for what I want to do. I want to first check the return code to ensure that there is valid data in the result set. If the return code shows an error condition, I don't want to do the processing.

[Jeanne Boyarsky]

Corey,
Can you just copy the data into a data structure and process it later (after knowing the error code? I think this would work unless you are dealing with very large data sets, like we do. Even copying thousands of records is doable.

[Sonny Gill]

Corey,

This is from the Javadocs for Callable Statement -

For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters.

This is probabely the cause of your problem.

Cheers

[Corey McGlone]

Originally posted by Jeanne Boyarsky:
Corey,
Can you just copy the data into a data structure and process it later (after knowing the error code? I think this would work unless you are dealing with very large data sets, like we do. Even copying thousands of records is doable.

Jeanne,

That ended up being my workaround. I put all the information into a data structure and, when I was done, I checked the return code. If it was an error code, I simply set my data structure to null, destroying anything I had put in there previously. Apparently, that may be the way to go.

Thanks for the update, Sonny.