Re: [Erserver-general] Why does Statement.close() close result

Jan Wieck wrote:
> How is the automatic cursor code controlled by setFetchSize() affected
> by a Statement.close() before the result set is consumed? Does that need
> to use holdable cursors here because in turn the transaction around the
> cursor could end sooner than expected?
>
>
> Jan
>

This is an interesting question.  The current logic for cursor backed
result sets will error if you try to access the result set after a
commit/rollback (actually you will get an error when you exhaust the
already fetched values that are in memory and try to fetch more from the
database).

This behavior is allowed by the jdbc spec.  In fact there are methods in
Connection, DatabaseMetaData and Statement dealing with "Result Set
Holdability" as of jdbc3/jdk1.4.

Most of these methods are currently not implemented by the jdbc driver
however.  And further more the nature of the methods are such that they
envision the 'holdability' setting to be one set at the Connection
level, as opposed to something that might vary from ResultSet to
ResultSet as it would need to in our current implementation.

We could support ResultSets being usable across transaction boundaries
for both the regular and cursor backed cases if WITH HOLD was used for
7.4 servers.  I would be inclined to think that we would do this only if
the client insisted on requiring cursors be held over transaction
boundaries.  Given what I remember of the implementation discussions on
WITH HOLD, I wouldn't want that to be the default (or am I remembering
it wrong and WITH HOLD doesn't have any adverse side effects that might
be nasty).

thanks,
--Barry