ResultSet Shelf Life

A ResultSet behaves differently depending on whether the autocommit property is set on or off. If autocommit is on, you are limited to one active, valid ResultSet. Associated ResultSetMetaData objects, and ResultSet objects returned from Statement#getGeneratedKeys() can also be open at the same time but have the same "shelf life" - They are valid until closed, committed, or another statement is executed by the Statement object. A Statement that returns a ResultSet defers committing the transaction, so iteration over the ResultSet is consistent with the data and the transaction isolation level. Each execution of a Statement will commit any previously deferred transaction and so closes any associated ResultSet.

If autocommit is off, you can have as many ResultSet objects as you need, provided each ResultSet comes from a unique Statement instance. You can not have multiple open ResultSet objects from the same Statement. When a Statement is executed, any currently associated ResultSet is closed and cannot be reopened or reused in any way.

Although we do not support more than one ResultSet from the same Statement, regardless of the autocommit setting, some applications may have success accessing multiple ResultSet objects from the same Statement if the size of the ResultSet was small enough (less than 100KB) for the entire contents of the result to be returned to the client in one response message. In this case, the application can iterate over the ResultSet safely. It is not until the client has to go back to the server for more data that an error occurs. Since the entire ResultSet is already on the client, there is no error. It is not recommended, nor supported, that clients rely on this functionality.