Solved

jdbc Operation not allowed after ResultSet closed

Posted on 2004-08-17
18
5,755 Views
Last Modified: 2012-05-05
jdbc , jsp and mysql


Lets say we have 2 tables
      1. category
      2. subcategory

eg.

      String sel_cat = "select * from category"'

      ResultSet rs = connector.executeQuery(sel_cat);
      
      while(rs.next())
      {
            
            String cat_id = rs.getString("catid");

            String sub = "select * from subcategory where cat_id="+cat_id;

            ResultSet rs1 = connector.executeQuery(sub);
            
      }


Error:

      -Operation not allowed after ResultSet closed
0
Comment
Question by:christopher sagayam
  • 6
  • 4
  • 3
  • +2
18 Comments
 
LVL 9

Accepted Solution

by:
mmuruganandam earned 60 total points
ID: 11819488
Don't use the same statement to get another resultset.
That is causing the problem
0
 
LVL 9

Expert Comment

by:mmuruganandam
ID: 11819497
Instead,

     String sel_cat = "select * from category"'

     ResultSet rs = connector.executeQuery(sel_cat);
     
     while(rs.next())
     {
         
          String cat_id = rs.getString("catid");

          String sub = "select * from subcategory where cat_id="+cat_id;

          ResultSet rs1 = connector1.executeQuery(sub); // use a different statement
       
          // process rs1 here and close it here...
     }
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11819691
You don't need to do that. Simply use a join:

ResultSet rs1 = connector.executeQuery("select c.id, s.something, s.somethingelse from category c, subcategory s, where c.id = s.id
");
         
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 11821821
So still no solution in sight suppose if I want to open multiple recordsets usnig jdbc.
0
 
LVL 92

Expert Comment

by:objects
ID: 11826468
> So still no solution in sight suppose if I want to open multiple recordsets usnig jdbc.

mmuruganandam's comment above show how to achieve that.
0
 
LVL 4

Expert Comment

by:bhpr
ID: 11850420
>So still no solution in sight suppose if I want to open multiple recordsets usnig jdbc.

you can make a method... something like this...

synchronized public Object execute(String _sqlQuery) throws Exception {
            Object _obj = null;
            Statement statement = null;
            ResultSet _rs = null;
            
            if (!isConnected()) {
                  close();
                  init();
            }
            
            if (_sqlQuery.trim().toUpperCase().startsWith("SELECT")) {
                  //System.out.println("executeQuery.SELECT\n"+_sqlQuery);
                  statement = connection.createStatement();
                  _obj = (Object)statement.executeQuery(_sqlQuery);
            } else {
                  //System.out.println("executeQuery.UPDATE\n"+_sqlQuery);
                  statement = connection.createStatement();
                  _obj =  ""+statement.executeUpdate(_sqlQuery);
            }

            
            //if (statement != null)       { try{statement.close();      }catch (Exception _e){      }      }

            return _obj;
      }

bhpr
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 11850436
so anyhow new connections need to be made to the mysql database for creating a new recordset right ?

using the same connection different recordsets cannot be opened I guess ?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11850447
>>So still no solution in sight suppose if I want to open multiple recordsets usnig jdbc.

Why would you want multiple recordsets out of interest?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Author Comment

by:christopher sagayam
ID: 11850473
I meant resultsets

Is it possible to have

          ResultSet rs1 = connector.executeQuery(sub);
          ResultSet rs2 = connector.executeQuery(sub1);
          ResultSet rs3 = connector.executeQuery(sub);

?



0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11850549
>>I meant resultsets

Yes, i know, but why would you want them?

It is possible incidentally
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 11850565
to query  different tables without using a join



0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11850574
Why would you not want to do a join? Doing the following:

>>String sel_cat = "select * from category"'

followed by

>>String sub = "select * from subcategory where cat_id="+cat_id;

is simply inefficient


0
 
LVL 92

Expert Comment

by:objects
ID: 11856770
You can use the same connection, you just need to create a seperate statement.
Is that a problem for you?
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 11857617
so still no solution in sight ! :(

for opening multiple recordsets with a single connection!

objects

Yes there seems to be a problem  I am getting the error

"Operation not allowed after ResultSet closed
"
0
 
LVL 92

Assisted Solution

by:objects
objects earned 65 total points
ID: 11858159
Just use a different Statement using the same connection:

Statement statement1 = connection.createStatement();
Statement statement2 = connection.createStatement();
ResultSet rs1 = statement1.executeQuery(sub);
ResultSet rs2 = statement2.executeQuery(sub1);
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 11951371
I would like to split the points between mmuruganandam  and objects with GRADE A each.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now