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 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.
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.
An issue with changing the default holdability, is that in JDBC 2 (JDK
1.3), there is no way to specify holdability, and all ResultSets get the
default.
--Andreas
