Consider not closing result set after fetching last row in auto-commit mode

[mrotteveel]

NOTE: This ticket describes and discusses a potential change, and is not a statement of intent or promise to actually make this change.

In JDBC 3.0 and earlier, section 10.1 "Transaction Boundaries and Autocommit" specified the following:

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes the JDBC driver to do a transaction commit after each individual SQL statement as soon as it is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:

• For Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing.
• For Select statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following occurs:
  • all of the rows have been retrieved
  • the associated Statement object is re-executed
  • another Statement object is executed on the same connection
• For CallableStatement objects, the statement is complete when all of the associated result sets have been closed.

To comply with this, Jaybird will close the result set in auto-commit mode when all rows have been fetched (see FBStatement.RSListener#allRowsFetched(ResultSet)). However, JDBC 4.0 changed the second list item to only:

For Select statements, the statement is complete when the associated result set is closed.

And the conditions of when a result set is closed was moved to section 15.2.5 (JDBC 4.3) to:

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

---

Note – Some JDBC driver implementations may also implicitly close the ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next method of ResultSet returns false.

---

In other words, our current behaviour still complies (see the note), but we are allowed to relax this, which would be in line with the behaviour of some of the other driver implementations. A potential downside is that implementations that rely on this implicit close might keep their result set open for longer, and thus delay the auto-commit.

[mrotteveel]

Decided not to do this for Jaybird 5. Keeping it open to reconsider this for a later version.

[mrotteveel]

jdp-2024-03: Do not close result set after last row in auto-commit