Solved

jdbc Operation not allowed after ResultSet closed

Posted on 2004-08-17
18
5,809 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

734 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