473,854 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Nested ResultSets : Invalid operation: result set closed

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

stmt1 = conn.createStat ement();
rs1 = stmt1.executeQu ery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStat ement();
rs2 = stmt2.executeQu ery(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_ON LY.
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_SEN SITIVE / TYPE_SCROLL_INS ENSITIVE

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
5 25492

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

stmt1 = conn.createStat ement();
rs1 = stmt1.executeQu ery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStat ement();
rs2 = stmt2.executeQu ery(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_ON LY.
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_SEN SITIVE / TYPE_SCROLL_INS ENSITIVE

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
Hi Joe,
Thanks for your reply.
But as I mentioned, when I create stmt2 using the code
stmt2 = conn.createStat ement(ResultSet .TYPE_SCROLL_IN SENSITIVE,
ResultSet.CONCU R_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***********@g mail.com wrote:
praveen wrote:
Hi,
I am using nested resultsets to execute queries on two different
tables.
The code structure is:

stmt1 = conn.createStat ement();
rs1 = stmt1.executeQu ery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStat ement();
rs2 = stmt2.executeQu ery(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_ON LY.
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_SEN SITIVE / TYPE_SCROLL_INS ENSITIVE

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
A connection object has an associated autocommit state.

System.out.prin tln("conn connection state is: "+conn.getAutoC ommit());
conn.setAutoCom mit(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.createStat ement();
rs1 = stmt1.executeQu ery(query1);
while (rs1.next()) {
.....
stmt2 = conn.createStat ement();
rs2 = stmt2.executeQu ery(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_ON LY.
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_SEN SITIVE / TYPE_SCROLL_INS ENSITIVE

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
Phil Sherman wrote:
A connection object has an associated autocommit state.

System.out.prin tln("conn connection state is: "+conn.getAutoC ommit());
conn.setAutoCom mit(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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
11291
by: j vickroy | last post by:
Hello, I'm trying to understand the behavior of the Python 2.3 logging module (MS Windows 2k) with regard to RotatingFileHandler. The following script illustrates a puzzling problem. What is wrong with this script? Thanks, -- jv
1
3279
by: solar | last post by:
CaN somebody help me find the error in my code ? I get the error "invalid operation" with the following line : tdf.Fields("CustomerID") = 121 Public Function Test() Dim dbs As DAO.Database Dim rst As DAO.Recordset
0
1772
by: colt | last post by:
I have a query that is pretty straightforward and I am trying to generate 5 reports from that query. The reports produce totals by Project Manager, Category, Client, Account Manager and Account Executive. The first three reports work fine. However, when I try to change the sorting and grouping to either Account Manger or Account Executive, I get an "Invalid Operation" error and the report does not open. Has anybody seen this before?
0
2947
by: cclwebmaster | last post by:
When running the following code from our Web site at http://www.corporateconnectionlines.com we get this error message: Error Type: ADODB.Connection.1 (0x800A0E78) Invalid operation on closed object libs/classDb.asp, line 13 Page: GET /eservices/ratesDaily.asp
0
2085
by: adougall | last post by:
I would like to know what the outcome was on the topic for Nested Stored Procedure returning result sets in DB2 on AS/400 was. I am getting the same problem RESULT_SET_LOCATOR in *LIBL type *SQLUDT not found.
0
1924
by: Systemino | last post by:
Hallo, I have a site developed on ASP.NET 2.0. It stay on my notebook and it works with a ORACLE database (ver. 9.0). It's all right!! Then I reached my definite site version on IIS 6.0 in other server (with S.O. Windows 2003). The server has the framework 2.0 .NET and the oracle client. Now when I try to connect on the same database I get error: "Invalid operation. Connection Closed".
7
11639
by: jaegertw2 | last post by:
Hi guys, I've seen a few other similar posts, but i still cant figure out exactly what I'm doing wrong or what i need to do to fix it. I'm trying to find the ID number of the last record in a stored query. This method worked perfectly for a stored table, but with this query reulsts in an invalid operation error. If I remove the DB_OPEN_TABLE parameter it says "Too few parameters. Expected 34." The criteria for this query has some...
7
1476
by: MoRpHeOo | last post by:
Hi everybody, ¿can help me?, I'm novice in C#, can tell me what type of variable gives this operation. Dim a As UInteger = 63689
5
3214
lotus18
by: lotus18 | last post by:
WaAh! My form was working fine earlier but later it displays Invalid Operation Exception was unhandled. I don't know what's wrong with my form every time I load it.I checked its codes but I find what's wrong with it. If I'm going to remove Transaction.Courses.Show(False, txtSearch.Text) at line #2 of frmCourses it works fine. Of course I cannot filter the records : ( This is my sample codes clsCourses
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11041
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10692
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7921
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7083
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5753
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5946
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4565
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4167
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.