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
LVL 6
christopher sagayamAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mmuruganandamConnect With a Mentor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
objectsConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.