jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Accessing Return Code Closes RecordSet? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Accessing Return Code Closes RecordSet?" Watch "Accessing Return Code Closes RecordSet?" New topic
Author

Accessing Return Code Closes RecordSet?

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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:



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


SCJP Tipline, etc.
Napa Sreedhar
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
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
Ranch Hand

Joined: Jan 29, 2002
Posts: 62
I am really unsure why the result set is getting closed.
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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.
 
Consider Paul's rocket mass heater.
 
subject: Accessing Return Code Closes RecordSet?
 
Similar Threads
stored procedure: Parameter index out of range
Oracle Store Procedures
myresultset.first
Error in getting data from database
Calling Oracle stored procedures in JRun container