SQLServerException The result set is closed

Question

I am getting 'SQLServerException : 0 The result set is closed. The result set is closed.@java.lang.Thread:run:722' in the following code.

I can see I am not closing the statement or resultset then why I am getting this exception.

Anyone can help?? Thanks in advance

private boolean isConnectionValid(Connection connection){
    //SQL statement to execute the query.
    Statement statement = null;     
    //resultSet receives the result of statement execution.
    ResultSet resultSet = null;         
    //detect the connectivity.
    try{
        //create a statement.
        statement = connection.createStatement();                       
        //define the specific query after the statement is created.
        String query = databaseType == DatabaseType.ORACLE? "select 1 from dual" : "select 1";          
        //apply the statement to execute the query.
        resultSet = statement.executeQuery(query);                      
        // if the resultSet.next() returns true, the connection is fine. Otherwise the connection is invalid.
        return  resultSet.next();   
    }catch(SQLException e){
        //If any SQL Exception is caught, the connection is invalid as well.
        Common.logException2(getLogger(), e, null);
        return false;
    }finally{           
        //finally close statement and resultSet to prevent cursor leak if any of them is not null.
        Common.closeStatementAndResultSet(statement, resultSet, getLogger());
    }

One example where I am using isConnectionValid method is following:

public boolean execute(Logger logger) throws SQLException {
    try {
        if( !query.toUpperCase().startsWith("SELECT") ) {
            queryLoggerInfo(database.getDbName() + " " + this);
        }
        return statement.execute();
    } catch (SQLException e) {
       if (database.isConnectionValid(connectionId)){
            //log it
        } else {
            // other log methods
        }
        throw e;
    }
}

it doesnt look like you are opening the connection anywhere. is connection.Open being called? — gh9, Feb 17 '15 at 13:13
@gh9 yeah, this is part of check i am doing. So I am opening connection and doing some stuff. When I get some error/exception I run this method to check if the connection is valid. — Charychap, Feb 17 '15 at 13:18
where is the code which you're calling `.isConnectionValid(connection);` — The Coder, Feb 17 '15 at 13:23
Provide the full stack trace so we know at which line and which component this exception is thrown. Your post is confusing at the moment. On a side note you're better of letting a connection pool handle validation. Commons DBCP let's you do this using the `validationQuery` parameter `validationQuery="SELECT version();"`. — Deepak Bala, Feb 17 '15 at 14:09
@DeepakBala Unfortunately, there is no stack trace - since the exception is caught. The logging of the process shows this error... — Charychap, Feb 17 '15 at 14:13

score 2 · Answer 1 · edited May 23 '17 at 12:28

The finally block is being called before the return is evaluated, and when you return your value, the resultset is already closed.

Try changing your code this way:

boolean output = resultSet.next();
return output;

Please refer to this question

Edit:

It is always true. I have created a test to prove my point:

public class Testing {

    public static void main(String[] args) {
        Testing t = new Testing();

        System.out.println("Test1: " + t.getStringListValue());
        System.out.println("Test2: " + t.getStringList().size());
    }

    public String getStringListValue() {
        List<String> stringList = new ArrayList<String>();

        try {
            stringList.add("a");
            stringList.add("b");
            stringList.add("c");

            return stringList.get(2);
        } catch (Exception e) {

        } finally {
            stringList.clear();
        }

        return null;
    }

    public List<String> getStringList() {
        List<String> stringList = new ArrayList<String>();

        try {
            stringList.add("a");
            stringList.add("b");
            stringList.add("c");

            return stringList;
        } catch (Exception e) {

        } finally {
            stringList.clear();
        }

        return null;
    }
}

In the getStringList() method, I call clear() in the finally block, and when I try to get the size, I get 0 as expected.

In the getStringListValue() on the other hand, I also call clear, but I return the value of the second element in the List as you suggest, and I can print it's value.

What happens here is that a pointer to an String object has been created in the return line and received the value of the second element in the array. So, the clear() in the finally block clears the List but the return String maintains the copied value.

This is not alwasy true I guess. I did simple experiment I made an ArrayList (AL)and add 3 values in it. In the return statement of the try block I try to get value at nth index i.e. 'AL.get(2) and in the finally block I clear the list i.e. AL.clear(). I can see that I get the retrun value. While debugging I see that pointer goes to return in try block then finally block and then back to return and returns value what i was expecting. — Charychap, Feb 17 '15 at 15:42
I agree with your example and I would expect the same. But then you are contradicting your original answer about the resultset is close because finally block is executed. In the original question then the resutset.next() value should be stored as return value is stored in your example. Correct me if i am wrong. — Charychap, Feb 19 '15 at 09:27

SQLServerException The result set is closed

1 Answers1