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)

Answer 1

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:

1. Disable auto-commit before executing the statements:

   conn.setAutoCommit(false);
   

2. 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.

3. 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.