Is there a way to find out a database cursor is open or not in Java?

Question

Is there a way in JDBC to find out the cursor opened or not in JAVA. We are getting an exception when tried to access an un opened cursor. java.sql.SQLException: Cursor is closed.

Here is what is happening.

The Stored proc is designed this way. the proc returns the cursor and a couple of other columns. For some conditions, the DB guy is not opening the cursor and returns only the other remaining column outside of the cursor. So when the Java code tries to get cursor, it throws this exception. I know, I can catch the exception and proceed further with the remaining columns. But I was wondering is there a different (may be better) way to handle this? Or just need to open the cursor in the stored proc, even if there is nothing to return as part of the cursor?

Answer 1

This means you're not handling your ResultSet properly. (ResultSet is the object representation of the cursor.)

Don't worry about figuring out how to check if a cursor is open or not; figure out why your ResultSet has gone out of scope and you'll answer your own question.

Post some code or a more detailed explanation of what's going on.

My guess is that you're passing a ResultSet out of the persistence tier when you'd be a lot better off mapping it into an object or collection and closing it within method scope. Return that object or collection instead of a ResultSet.

Database cursors are scarce resources. You should keep them open for the minimum time required to get your data and then close them so someone else can use them. It'll help with scalability - and solve this exception as a side benefit.

Answer 2

You will get this exception whenever you attempt to access a ResultSet while it, its statement or its connection has been closed. Note that reusing a statement to open other ResultSets will also cause all previously opened resultsets being closed.

Review the flow in your JDBC code. Ensure that it follows the standard JDBC idioms as outlined in the Sun tutorial. To learn more how to use basic JDBC properly, you may find this article useful as well.

Answer 3

As of 1.6, ResultSet has an isClosed method. I'm assuming your exception is happening on a ResultSet. That being said, I agree with the other answers that if you are seeing your ResultSet close out from under you, you likely have a flaw in your usage and just testing for isClosed isn't going to help you get out of that problem - you will just find that it is closed when you need it to be open.

Answer 4

I encountered this issue in Oracle before. It has nothing to do with the ResultSet, it's open. The error is caused by the STP which may return an empty cursor, or close the cursor under certain conditions.

Please check all the STPs you use and make sure it always returns an open cursor.

Answer 5

Perhaps the stored proc could be changed to return a value indicating if the cursor is open. Something like the following might be useful:

TYPE tCursor IS REF CURSOR;

PROCEDURE A_PROC(returned_cursor    OUT tCursor,
                 bCursor_is_open    OUT BOOLEAN,
                 aNother_value      OUT NUMBER,
                 yet_another_value  OUT NUMBER);

The procedure should set bCursor_is_open to TRUE if it opens the cursor, or FALSE if the cursor is not opened.

Answer 6

Couldn't find anything to check that. Did a work aroung in Java code after catching this exception