Getting error SQLServerException: The result set is closed

Question

I have tried many ways but always i'm getting this error.
Actually i'm trying to access result set values from other class and for Database Query i have created a separate class.
Please do not mark this as previously asked because i got the solution only of single class.
This is my DBVerification class

    public class DBVerification {
        private static String DB_URL = PropertyManager.getInstance().getDB_URL();
        private static String DB_USER= PropertyManager.getInstance().getDB_USER();
        private static String DB_PASSWORD= PropertyManager.getInstance().getDB_PASSWORD();
        private static String DBClass= PropertyManager.getInstance().getDBClass();
        private static Connection connection;
        public static ArrayList<ResultSet> executeStoredProcedure(String query) throws ClassNotFoundException, SQLException
            {   
                   ArrayList<ResultSet> resultset = new ArrayList<ResultSet>();
                   Class.forName(DBClass);
                   connection= DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
                   CallableStatement cstmt = connection.prepareCall( "{ call " + query+" }" );
                   //System.out.println("{ call " + query+" }");
                  try {
                   boolean results = cstmt.execute();
                   int rsCount = 0;
                   do {
                        if(results) {
                           ResultSet rs = cstmt.getResultSet();
                           resultset.add(rs);
                           rsCount++;
                           System.out.println("RESULT SET #" + rsCount);
                          // rs.close();
                        }
                        System.out.println();
                        results = cstmt.getMoreResults();
                        } while(results);
                   //cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);
                   //cstmt.close();
                  }
                   catch (Exception e) {
                          e.printStackTrace();
                       }
                  return resultset;
            }
public static void closeDB() throws SQLException
    {        
               connection.close();        
    }
    }

This is my second class Reimbursement class

public class Reimbursement
{
ArrayList<ResultSet> result = DBVerification.executeStoredProcedure("getreimbursements");
      for (ResultSet curInstance: result) {
          if(result.indexOf(curInstance) == 0)
                  {
              while(curInstance.next())
              {
                     String branchName=curInstance.getString("BranchName");
                     String department=curInstance.getString("DepartmentName");
                     String employee=curInstance.getString("EmployeeName");
                     String title=curInstance.getString("Title");
                     String claimdate=ValueConverter.DateFormat(curInstance.getString("Date"));  


              }
              curInstance.close();

                  }
          if(result.indexOf(curInstance) == 1)
          {
              while(curInstance.next())
              {
                  String category=curInstance.getString("Category");
                     String expensedate=ValueConverter.DateFormat(curInstance.getString("ExpenseDate"));
                     String description=curInstance.getString("Description");
                     String approvedby=curInstance.getString("ApprovedBy");


              }
              curInstance.close();

          }
        }
         DBVerification.closeDB();
}

Please do not look for main method because this is for testing class so i'm already using this class in my xml file.
Please give me suggestion that what i'm doing wrong it give me error message that 'The result set is closed'.

Image of exception actually it is my test class so it will display error only in this form i have edited the line which was indicated
exception message

Can you add the stacktrace to your question so we can see which line is causing the problem? — DaveH, Jan 28 '19 at 12:26

score 1 · Answer 1 · answered Jan 28 '19 at 12:46

1

I think your problem might be as follows

you loop around the response to the call to your stored procedure, adding each result set from the call in to an arraylist
you return the arraylist back to your calling method and iterate over it
you try to process each resultset in turn.

Unfortunately, I think that the action of cstmt.getMoreResults() closes any open result sets before moving to the next one. What you are ending up with is an arraylist of closed ResultSet objects. When you try to read from them, you get the error saying "result set is already closed"

from the java docs

boolean getMoreResults() throws SQLException

Moves to this Statement object's next result, returns true if it is a ResultSet object, and implicitly closes any current ResultSet object(s) obtained with the method getResultSet.

answered Jan 28 '19 at 12:46

DaveH

7,187
5
32
53

Yes @DaveH the problem is same as you mention above array list return correct result but when i tried to read the column name from using .next() then it fails, So from your end any solution how should i change my code to get resultset values – Upkar Singh Jan 28 '19 at 12:54
I think your best option is to move the logic that you have in your reimbursement class in to the DB access class and, rather than returning an arraylist of ResultSets, you'll need to return an object that contains the results from your Callable Statement – DaveH Jan 28 '19 at 13:09
I have multiple classes like reimbursement class, then i will need to write same code for all the classes then i think their is no option to create DB class, but @DaveH i just need is that my logic of DB should run in one class and i could access all result form one class because i have multiple classes same like reimbursement – Upkar Singh Jan 28 '19 at 13:24
To have one class like DBVerification that handles all the database interaction is possible, but would be quite a significant redesign. It's also hard to think of an implementation of it that wouldn;t be quite brittle – DaveH Jan 28 '19 at 13:36

Getting error SQLServerException: The result set is closed

1 Answers1