Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6052
  • Last Modified:

jdbc Operation not allowed after ResultSet closed

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
christopher sagayam
Asked:
christopher sagayam
  • 6
  • 4
  • 3
  • +2
2 Solutions
 
mmuruganandamCommented:
Don't use the same statement to get another resultset.
That is causing the problem
0
 
mmuruganandamCommented:
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
 
CEHJCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
christopher sagayamAuthor Commented:
So still no solution in sight suppose if I want to open multiple recordsets usnig jdbc.
0
 
objectsCommented:
> 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
 
bhprCommented:
>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
 
christopher sagayamAuthor Commented:
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
 
CEHJCommented:
>>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
 
christopher sagayamAuthor Commented:
I meant resultsets

Is it possible to have

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

?



0
 
CEHJCommented:
>>I meant resultsets

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

It is possible incidentally
0
 
christopher sagayamAuthor Commented:
to query  different tables without using a join



0
 
CEHJCommented:
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
 
objectsCommented:
You can use the same connection, you just need to create a seperate statement.
Is that a problem for you?
0
 
christopher sagayamAuthor Commented:
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
 
objectsCommented:
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
 
christopher sagayamAuthor Commented:
I would like to split the points between mmuruganandam  and objects with GRADE A each.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now