By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Resultset close after commit?

P: n/a
Hi all,

I am connecting to DB2 8.1 on Solaris through JDBC type 4 drivers
(driver: com.ibm.db2.jcc.DB2Driver). Problem is that when a cursored
query is done, the resultset gets closed after commit
(resultset.next() throws SqlException "Invalid operation: result set
closed").

Is there a way to tell DB2 not to close the resultset/cursor?

I am on JDK 1.3. I guess Resultset.HOLD_CURSORS_OVER_COMMIT got
introduced in JDK 1.4.

Is there any other way out?

Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Gandalf wrote:
Hi all,

I am connecting to DB2 8.1 on Solaris through JDBC type 4 drivers
(driver: com.ibm.db2.jcc.DB2Driver). Problem is that when a cursored
query is done, the resultset gets closed after commit
(resultset.next() throws SqlException "Invalid operation: result set
closed").

Is there a way to tell DB2 not to close the resultset/cursor?

I am on JDK 1.3. I guess Resultset.HOLD_CURSORS_OVER_COMMIT got
introduced in JDK 1.4.

Is there any other way out?


Just a shot in the dark... you did open the cursor using the WITH HOLD
option, didn't you?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2

P: n/a

"Gandalf" <ga*****@techie.com> wrote in message
news:be**************************@posting.google.c om...
Hi all,

I am connecting to DB2 8.1 on Solaris through JDBC type 4 drivers
(driver: com.ibm.db2.jcc.DB2Driver). Problem is that when a cursored
query is done, the resultset gets closed after commit
(resultset.next() throws SqlException "Invalid operation: result set
closed"). ===
You might need to apply the latest fixpak (at least fixpak4). DB2 will hold
the cursor by default.
Is there a way to tell DB2 not to close the resultset/cursor?

I am on JDK 1.3. I guess Resultset.HOLD_CURSORS_OVER_COMMIT got
introduced in JDK 1.4.
====
Yes, it is introduced in JDK1.4.

Is there any other way out?

Thanks.

Nov 12 '05 #3

P: n/a
> Just a shot in the dark... you did open the cursor using the WITH HOLD
option, didn't you?


Thanks for the response.
Thats kind of exactly what I wanted to know - how do you open the
cursor using WITH HOLD option in JDK1.3 framework.

I am using PreparedStatement to execute the query. The method

Connection.prepareStatement(String sql, int resultSetType, int
resultSetConcurrency, int resultSetHoldability)

seems to have been added in JDK1.4 only (note the last parameter -
resultSetHoldability).

I am not sure how sure how to do it in earlier versions.
I am using DB2 8.1 fp3 Type 4 drivers (driver:
com.ibm.db2.jcc.DB2Driver). The driver return true for
supportsOpenCursorsAcrossCommit(), but I am not able to turn on the
feature!!

I am desperately in need of help :(

Thanks.
Nov 12 '05 #4

P: n/a
"Gandalf" <ga*****@techie.com> wrote in message
news:be**************************@posting.google.c om...
Just a shot in the dark... you did open the cursor using the WITH HOLD
option, didn't you?


Thanks for the response.
Thats kind of exactly what I wanted to know - how do you open the
cursor using WITH HOLD option in JDK1.3 framework.

I am using PreparedStatement to execute the query. The method

Connection.prepareStatement(String sql, int resultSetType, int
resultSetConcurrency, int resultSetHoldability)

seems to have been added in JDK1.4 only (note the last parameter -
resultSetHoldability).

I am not sure how sure how to do it in earlier versions.
I am using DB2 8.1 fp3 Type 4 drivers (driver:
com.ibm.db2.jcc.DB2Driver). The driver return true for
supportsOpenCursorsAcrossCommit(), but I am not able to turn on the
feature!!

I am desperately in need of help :(

Thanks.


I don't know anything about Java, but did you try just adding WITH HOLD on
the end of DECLARE CURSOR Select statement? Check the SQL Reference for
details.
Nov 12 '05 #5

P: n/a
Thanks guys, for all the input.

I figured out that the problem was not of DB2 closing the
resultset/cursors on commit but not being able to call
Resultset.next() after all the results have been consumed.

Apparantely, DB2 Universal Database driver throw an error (Invalid
operation: result set closed) when Resultset.next() is called *after
all the results have been consumed*. Oracle doesn't throw this error,
it keeps on returning false. Our application which was developed on
Oracle made multiple calls to Resultset.next() through different
threads. Writing a wrapper over Resultset solved the problem.

Also, on DB2 8.1 FP3 (that we are using, possibly on other versions
too) and while using JDBC 2.0 (JDK1.3) compliant drivers, which
doesn't support configuring the holdability of Resultset, it seems
like the results are opened WITH HOLD option by default.

Thanks once again.
Nov 12 '05 #6

P: n/a
Check your jdbc driver for searching statement or preparedStatement
alternative method. Maybe you can do the statement with the property WITH
HOLD. I had the same problem, and i solved it with Informix driver, which
has preparedStatement method with a parameter for set with hold property.
Salu2.

Nov 12 '05 #7

P: n/a
Check your jdbc driver for searching statement or preparedStatement
alternative method. Maybe you can do the statement with the property WITH
HOLD. I had the same problem, and i solved it with Informix driver, which
has preparedStatement method with a parameter for set with hold property.
Salu2.

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.