Invalid operation: result set is closed

Question

When my resultset data is large I get com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.9.78] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null exception but not when I try with less amount of data in resultset

Below is my code snippet

ResultSet rs=null;
String sql_query="select * from exception_main;select * from m_roles"
String query1=sql_query.toUpperCase();
String[] results=query1.split(";");         
CSVWriter writer = new CSVWriter(new FileWriter(csv_file_path + csv_file_name), ',',CSVWriter.NO_QUOTE_CHARACTER);                     
for(int i=0;i<results.length;i++)                         
{                             
  if(results[i].startsWith("SELECT"))                             
  {                                 
    System.out.println("Inside select"+ results[i]);                             

    ps = conn1.prepareStatement(results[i].toString());                             
    rs = ps.executeQuery();             

    ...                        

    //writing to csv file                          
    System.out.println("Count..." + rs.getRow());                        
    writer.writeAll(rs, true);                         

    while(rs.next()){                                  
      rs.deleteRow();                                
    }                          
    System.out.println("Count...3:::::::" + rs1.getRow());     
  }     
}
writer.close(); 
rs.close();

Answer 1

Keep

stmt = conn1.createStatement(); 

before the for loop.

Answer 2

From the Java docs:

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

Hence your error.

Your stmt Statement object is being re-executed everytime in your for loop.

Also, from here:

For non-held cursors, when the driver finishes stepping through the 1st ResultSet, the driver will flow commit if autoCommit is on, and that will close everything else. If you want the other ResultSet remain open, you may want to use the with hold cursor.

---

EDIT:

(to the OP - kindly post one snippet. Don't keep changing snippets all the time) The answer still is the same : you are using two queries on the same ResultSet, in your for loop. That is not going to work. The ResultSet will be closed if you re-execute the same Statement object with different queries. Either use different Statement objects, or use a different approach and drop this for loop idea.