Bug #29075 java.sql.SQLException: Operation not allowed after ResultSet closed

• Submitted: 13 Jun 2007 14:29
• Modified: 13 Jun 2007 15:31
• Reporter: sheng hui
• Status: Not a Bug
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[13 Jun 2007 14:29] sheng hui

Description:
I get an error java.sql.SQLException: Operation not allowed after ResultSet closed , if i try to use another resultset rs2, the error points out to rs.next(). 
I think in java it closes the resultset if it uses another resultset. but in my code i need to use both resultset, How can I solve this problem? This is part of my code where error occurs. thanks in advance for your help.

How to repeat:
 try{
            st = cn.createStatement();
            String query1 = "Select Code from Classifications_RADLEXsys_2007";
            rs = st.executeQuery(query1);
            while(rs.next()){
                int lv = 0;
                String query2 = "Select RelatedCode from Relations_RADLEXsys_2007 where Code = " +"'"+ rs.getString("Code")+"'";
                rs2 = st.executeQuery(query2);
                while( rs2.next()){
                    lv++;
                    String query3 = "Select RelatedCode from Relations_RADLEXsys_2007 where Code = " + "'"+rs2.getString("RelatedCode")+"'";
                    rs2 = st.executeQuery(query3);
                }
                writeSys_LV(lv, rs.getString("Code"));
            }
        }
        catch(java.sql.SQLException sqle) {
            System.out.println(sqle);
            System.exit(1);
        }

[13 Jun 2007 15:07] Mark Matthews

Statement instances can only have one active result set at a time. I'd tell you to use two different statements so you can have two different active result sets, but that's usually a sign that you should be doing a JOIN in the database (which seems to be true in your case as well), see:

http://dev.mysql.com/doc/refman/5.0/en/join.html

and

http://www.w3schools.com/sql/sql_join.asp

[13 Jun 2007 15:31] sheng hui

hi Mark,
thank you very much and i have already a idea with your hints.