Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

jdbc Operation not allowed after ResultSet closed

Posted on 2004-08-17
18
Medium Priority
?
5,919 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
16 Comments
 
LVL 9

Accepted Solution

by:
mmuruganandam earned 240 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 260 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
The viewer will learn how to implement Singleton Design Pattern in Java.
This video teaches viewers about errors in exception handling.
Suggested Courses

876 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