Andreas Korneliussen wrote:
Currently Derby supports a limited combination of ResultSet types and
Resultset concurrency modes. Common for all the current combinations, is
that Derby can support HOLD_CURSORS_OVER_COMMIT, however for future
combinations this may be a problem.
We have a problem in that Derby may not be architected to correctly
implement "holdable" scrollable updatable resultsets (SUR), and as a
fallback, we may consider not to support the holdabilitiy for SUR.
I also saw some JIRAs with synopsis:
Derby-1005: "Holdability for a connection should automatically become
CLOSE_CURSORS_AT_COMMIT for a global transaction."
and
Derby-1006:"Client allows setHoldability to HOLD_CURSORS_OVER_COMMIT on
both connection and statement in a global transaction "
I am not sure these are relevant for the discussion, however it seems to
me that there are other places in the system where Derby cannot support
HOLD_CURSORS_OVER_COMMIT.
I think before any SUR changes the only place where holdability is not
supported is within global (XA) transactions.
If the initial implementation of SUR doesn't support holdable result
sets then I think that's a great first step.
I think Derby is architected to support the holdability mode
CLOSE_CURSOR_AT_COMMIT by all combinations of ResultSets.
I therefore find it reasonable to consider changing the *default*
holdability mode from HOLD_CURSORS_OVER_COMMIT to CLOSE_CURSORS_AT_COMMIT.
This is of course a incompatible change for existing applications, and
has the potential to break them.
Clients which depend on HOLD_CURSOR_OVER_COMMIT, should as a consequence
explictly set the holdability. I think that a client should not depend
on holdability mode without specifying it from the application, or at
least check the DatabaseMetadata.getDefaultHoldability() and then call
setHoldability() on the Connection it if it depends on something else.
If Derby cannot support the specified holdability for a specific
resultset type, Derby could downgrade the holdabiltiy, and give a warning.
The JDBC 3.0 spec is silent on this, sections 14.1.1 and 14.1.2 describe
what can be done when a ResultSet type or concurrency is not supported.
However in 14.1.3 there is no comment about what can be done when a
requested holdability is not supported. Also ResultSet does not have a
getHoldability method to allow the application to determine if its
request was honoured or not.
Connection.getHoldability() is intended to be used here as a guideline
as with JDBC 4 ResultSet.getHoldability has been added.