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

Nested ResultSets : Invalid operation: result set closed

P: n/a
Hi,
I am using nested resultsets to execute queries on two different
tables.
The code structure is:

stmt1 = conn.createStatement();
rs1 = stmt1.executeQuery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStatement();
rs2 = stmt2.executeQuery(query2);
while (rs2.next()) {
.......
}
}

On running this piece of code, I get an exception
com.ibm.db2.jcc.b.SqlException: Invalid operation: result set closed
at the line......while (rs1.next()) {

On googling, I found that with db2jcc, the default mode for ResultSet
is TYPE_FORWARD_ONLY.
When it first reaches the end of the ResultSet, it returns false and
closes the ResultSet!

So when rs2 is completed, it closes rs2 but why is ResultSet rs1 also
closed when there are more rows in rs1?

The workaround for this is to change the ResultSet type to
TYPE_SCROLL_SENSITIVE / TYPE_SCROLL_INSENSITIVE

But my query is why does DB2 close all open resultsets which are
created using different statements even if one of the resultsets is
closed?

Expecting a response from you gurus..
Thanks
Praveen

Sep 8 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

praveen wrote:
Hi,
I am using nested resultsets to execute queries on two different
tables.
The code structure is:

stmt1 = conn.createStatement();
rs1 = stmt1.executeQuery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStatement();
rs2 = stmt2.executeQuery(query2);
while (rs2.next()) {
.......
}
}
There is nothing wrong with the code as you posted. It should
work, by JDBC spec. There must be a bug in the DB2 driver.
If/when you close stmt1, or re-execute it, by spec it will
automatically close any existing result set from it, but
stmt2 and rs2 should be completely independent.
The only other issue is if you're running this code in
multiple threads, and the conn and stmt objects are
shared. That would be non-threadsafe and cause your
problem. As long as all your JDBC objects are method-level
variables, you will be safe from that issue.

Joe Weinstein at BEA Systems
>
On running this piece of code, I get an exception
com.ibm.db2.jcc.b.SqlException: Invalid operation: result set closed
at the line......while (rs1.next()) {

On googling, I found that with db2jcc, the default mode for ResultSet
is TYPE_FORWARD_ONLY.
When it first reaches the end of the ResultSet, it returns false and
closes the ResultSet!

So when rs2 is completed, it closes rs2 but why is ResultSet rs1 also
closed when there are more rows in rs1?

The workaround for this is to change the ResultSet type to
TYPE_SCROLL_SENSITIVE / TYPE_SCROLL_INSENSITIVE

But my query is why does DB2 close all open resultsets which are
created using different statements even if one of the resultsets is
closed?

Expecting a response from you gurus..
Thanks
Praveen
Sep 8 '06 #2

P: n/a
Hi Joe,
Thanks for your reply.
But as I mentioned, when I create stmt2 using the code
stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_READ_ONLY);
I am able to access rs1 even after rs2 is completed and there is no
exception thrown.

Also, even with the above code line, if I close stmt2 once looping over
rs2 is completed, rs1 gets closed and an exception is thrown at while
(rs1.next())

Also all the variables are local method-level variables and hence not
shared.
Well, as you say, this could be a driver bug. I guess I will have to
carry on with the workaround if there is no other solution.

Thanks
Praveen
jo***********@gmail.com wrote:
praveen wrote:
Hi,
I am using nested resultsets to execute queries on two different
tables.
The code structure is:

stmt1 = conn.createStatement();
rs1 = stmt1.executeQuery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStatement();
rs2 = stmt2.executeQuery(query2);
while (rs2.next()) {
.......
}
}

There is nothing wrong with the code as you posted. It should
work, by JDBC spec. There must be a bug in the DB2 driver.
If/when you close stmt1, or re-execute it, by spec it will
automatically close any existing result set from it, but
stmt2 and rs2 should be completely independent.
The only other issue is if you're running this code in
multiple threads, and the conn and stmt objects are
shared. That would be non-threadsafe and cause your
problem. As long as all your JDBC objects are method-level
variables, you will be safe from that issue.

Joe Weinstein at BEA Systems

On running this piece of code, I get an exception
com.ibm.db2.jcc.b.SqlException: Invalid operation: result set closed
at the line......while (rs1.next()) {

On googling, I found that with db2jcc, the default mode for ResultSet
is TYPE_FORWARD_ONLY.
When it first reaches the end of the ResultSet, it returns false and
closes the ResultSet!

So when rs2 is completed, it closes rs2 but why is ResultSet rs1 also
closed when there are more rows in rs1?

The workaround for this is to change the ResultSet type to
TYPE_SCROLL_SENSITIVE / TYPE_SCROLL_INSENSITIVE

But my query is why does DB2 close all open resultsets which are
created using different statements even if one of the resultsets is
closed?

Expecting a response from you gurus..
Thanks
Praveen
Sep 8 '06 #3

P: n/a
A connection object has an associated autocommit state.

System.out.println("conn connection state is: "+conn.getAutoCommit());
conn.setAutoCommit(false);

will display the connection state and then disable autocommit.

Your description of failure after closing the inner query is indicative
of an autocommit performed at statement close, as stated in the Java
documentation. If the outer cursor is defined including the WITH HOLD
keyworda, it shouldn't be closed when the inner one commits. Without
autocommit, you should invoke the commit method as needed.

I'd suspect, without researching it, that the scrollable result
parameters generate code equivalent to using WITH HOLD.

Phil Sherman
praveen wrote:
Hi,
I am using nested resultsets to execute queries on two different
tables.
The code structure is:

stmt1 = conn.createStatement();
rs1 = stmt1.executeQuery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStatement();
rs2 = stmt2.executeQuery(query2);
while (rs2.next()) {
.......
}
}

On running this piece of code, I get an exception
com.ibm.db2.jcc.b.SqlException: Invalid operation: result set closed
at the line......while (rs1.next()) {

On googling, I found that with db2jcc, the default mode for ResultSet
is TYPE_FORWARD_ONLY.
When it first reaches the end of the ResultSet, it returns false and
closes the ResultSet!

So when rs2 is completed, it closes rs2 but why is ResultSet rs1 also
closed when there are more rows in rs1?

The workaround for this is to change the ResultSet type to
TYPE_SCROLL_SENSITIVE / TYPE_SCROLL_INSENSITIVE

But my query is why does DB2 close all open resultsets which are
created using different statements even if one of the resultsets is
closed?

Expecting a response from you gurus..
Thanks
Praveen
Sep 14 '06 #4

P: n/a
Phil Sherman wrote:
A connection object has an associated autocommit state.

System.out.println("conn connection state is: "+conn.getAutoCommit());
conn.setAutoCommit(false);

will display the connection state and then disable autocommit.

Your description of failure after closing the inner query is indicative
of an autocommit performed at statement close, as stated in the Java
documentation. If the outer cursor is defined including the WITH HOLD
keyworda, it shouldn't be closed when the inner one commits. Without
autocommit, you should invoke the commit method as needed.

I'd suspect, without researching it, that the scrollable result
parameters generate code equivalent to using WITH HOLD.
Java has the (rather stupid) habit of opening all cursors as being WITH
HOLD. This is due to the (also rather stupid) autocommit being default.
Otherwise, the cursor-open and/or any of the fetch operations would trigger
the commit.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 14 '06 #5

P: n/a
Knut Stolze wrote:
Java has the (rather stupid) habit of opening all cursors as being WITH
HOLD
.... as default.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.