java.sql.sqlRecoverableException: Closed statement: next

Question

My code throws

java.sql.sqlRecoverableException: Sentencia cerrada: next

which, in English, I guess it would be:

java.sql.sqlRecoverableException: Closed statement: next

This is my code:

public TransactionArray() throws SQLException {

  /* Obtenemos la tabla de transacciones. */
  Connection connection;
  connection = ConnectionManager.getConnection(STATISTIC_DATA_BASE);
  Statement stmt = null;
  String query =
          "select * " +
          "from " + "dCellStatistic" + ".F_Transaction";
  ResultSet rs = null;
  try {
     stmt = connection.createStatement();
     rs = stmt.executeQuery(query);
  } catch (SQLException e ) {
     e.printStackTrace();
  } finally {
     if (stmt != null) { stmt.close(); }
  }

  /* Construimos las transacciones a partir de los registros. */
  List<Transaction> transactionList = new ArrayList<Transaction>();
  while (rs.next()) { //THE PROBLEM ARISES IN THIS LINE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
     transactionList.add(new Transaction(rs));
  }
  array = transactionList.toArray(new Transaction[transactionList.size()]);

}

Any clues of what I may be doing wrong? I have seen two threads on Code Ranch about similar issues, but none of them seemed to provide a solution for my case.

Your English is pretty good but why is the code in Spanish? :) — adarshr, Feb 19 '13 at 13:03
See the answer from @EricGalluzzo below. For future reference, this behavior is documented in the [Javadoc](http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html) - `A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.`. — Perception, Feb 19 '13 at 13:05
@adarshr Thanks for the compliment. I'm Spanish and I work in Spain. We usually write the comments in the language of our country. — Josep, Feb 19 '13 at 13:10
@adarshr, Oracle has different languages versions that throws errors in the chosen language. — Chexpir, Jan 23 '14 at 12:04

score 21 · Accepted Answer · answered Feb 19 '13 at 13:02

21

You are closing the statement prior to retrieving the information from the result set. Move the stmt.close() call after the rs.next() loop (but keep the try/finally).

answered Feb 19 '13 at 13:02

Eric Galluzzo

3,191
1
20
20

It works! But why should I keep the "finally" block if it no longer contains any statement? – Josep Feb 19 '13 at 13:06
2

You should put the `rs.next()` inside the main try block, and then in the finally block, keep the `stmt.close()`. – Eric Galluzzo Feb 19 '13 at 13:07

score 4 · Answer 2 · answered Feb 19 '13 at 13:23

Since the fixed code doesn't fit in a comment, I publish it here for future reference.

public TransactionArray() throws SQLException {
  List<Transaction> transactionList = new ArrayList<Transaction>();

  /* Obtenemos la tabla de transacciones. */
  Connection connection;
  connection = ConnectionManager.getConnection(STATISTIC_DATA_BASE);
  Statement stmt = null;
  String query =
          "select * " +
          "from " + "dCellStatistic" + ".F_Transaction";

  /* Construimos las transacciones a partir de los registros. */
  ResultSet rs;
  try {
     stmt = connection.createStatement();
     rs = stmt.executeQuery(query);
     while (rs.next()) {
        transactionList.add(new Transaction(rs));
     }
  } catch (SQLException e ) {
     e.printStackTrace();
  } finally {
     if (stmt != null) {
        stmt.close();
     }
  }
  array = transactionList.toArray(new Transaction[transactionList.size()]);
}

java.sql.sqlRecoverableException: Closed statement: next

2 Answers2