java.sql.SQLException: The result set is closed on Firebird when executing two statements

Question

I try to make 2 queries from a Firebird 2.5 DB. I use two separate Statement objects, but when in second query program try to use data from first query, it gets an error:

java.sql.SQLException: The result set is closed.

conn = DriverManager.getConnection(
                    strURL,
                    strUser, strPassword);

if (conn == null) {
    System.err.println("Could not connect to database");
}

Statement stmt = conn.createStatement();
Statement statement = conn.createStatement();

ResultSet rs = stmt.executeQuery(strSQL);
ResultSet rs2 = null;

try {
    while (rs.next()) {
        String strSQL2 = "SELECT PATIENT_NAME_R, PATIENT_DOB, PATIENT_ID, PATIENT_SEX, PATIENT_ADDRESS_CITY, PATIENT_ADDRESS_SHF FROM PATIENTS WHERE PATIENT_UID = " + rs.getObject(1);
        rs2 = statement.executeQuery(strSQL2);
        try {
            while (rs2.next()) {
                System.out.println("СПРАВКА");
                System.out.println("Ф.И.О.: " + rs2.getObject(1).toString().trim() + " Дата рождения: " + rs2.getObject(2));
                System.out.println("СНИЛС: " + rs2.getObject(3));
                System.out.println("Адрес: " + rs2.getObject(5) + " " + rs2.getObject(6));
                System.out.println("Врач: " + rs.getString("STUDY_MD"));
                System.out.println("----------------------------------------------------------------");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

In while (rs2.next()) in line System.out.println("Врач: " + rs.getString("STUDY_MD")), I get error

java.sql.SQLException: The result set is closed
    at org.firebirdsql.jdbc.AbstractResultSet.checkOpen(AbstractResultSet.java:297)
    at org.firebirdsql.jdbc.AbstractResultSet.getField(AbstractResultSet.java:788)
    at org.firebirdsql.jdbc.AbstractResultSet.getString(AbstractResultSet.java:844)
    at sample.Main$MedicalCert.run(Main.java:156)
    at java.lang.Thread.run(Thread.java:748)

You can't use the same connection. It's like a toilet, if you go in, the previous occupant has to come out. — Kayaman, Jan 23 '20 at 08:22
Yep, problem has gone. It mean I must use new connection every time I want to use new statement object? — Алекс, Jan 23 '20 at 08:24
No, but you can't use 2 statements **at the same time** on a single connection. In this case you've noticed that the previous resultset has been closed when you've created a new one. If you do them one after another, then there's no problem. Depending on what you're querying, you might be able to rewrite the whole thing to a single query though. — Kayaman, Jan 23 '20 at 08:24
@Kayaman You can use 2 statements at the same time on a single connection, if you disable auto-commit. The only reason that the result set is closed from the first statement, is that in auto-commit mode, the execution of a statement ends any existing transactions and starts a new transaction, thus closing the result set. — Mark Rotteveel, Jan 23 '20 at 10:50
@MarkRotteveel ah I see. I suppose that makes sense, just not a scenario (doing "partial" queries like that) I run into too often. — Kayaman, Jan 23 '20 at 10:58
@Kayaman you can even have several transactions in one connection in Firebird, dunno on JDBC provides for it — Arioch 'The, Jan 24 '20 at 09:14

Mark Rotteveel · Accepted Answer · 2020-01-24T08:05:48.843

The problem is that you are executing two statements on a connection that is in auto-commit. In auto-commit mode, when you execute a statement, result sets created by other statements are closed.

As documented in the JDBC 4.3 specification, in section 15.2.5 Closing a ResultSet Object:

A ResultSet object is implicitly closed when

The associated Statement object is re-executed

The ResultSet is created with a Holdability of CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs

With Jaybird (the Firebird JDBC driver), you have three options:

Disable auto-commit before executing the statements:
```
conn.setAutoCommit(false);
```
Make the first statement holdable over commit, by executing as
```
Statement stmt = conn.createStatement(
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
        ResultSet.HOLD_CURSORS_OVER_COMMIT);
```
This will cause the produced result set to remain open after the (automatic) commit. Be aware though, that in this mode, Jaybird will fetch the entire result set in memory, instead of fetching it in batches.
Similar to the previous option, you can configure Jaybird to default to using holdable result sets, by specifying the connection property defaultResultSetHoldable set. See also Default holdable result sets. Setting this property will affect all statements and result sets.

The first option is preferable.

As an aside, I highly recommend you start using try-with-resources: your current code is vulnerable to resource leaks.

As indicated in the comments by Kayaman, you should consider rewriting your queries so it is just a single query. You are currently creating what is known as the N+1 query problem (executing a statement, and then for each row of that statement, executing another statement). Using a join in your query would allow you to execute it as a single query. And as a warning, your query is vulnerable to stored SQL injection because you concatenate a value into the query string.

java.sql.SQLException: The result set is closed on Firebird when executing two statements

1 Answers1