Derby does not support open cursor across commits for XA transactions and hence DatabaseMetaData.supportsOpenCursorsAcrossCommit should return false.

• Key: DERBY-66
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Mamta A. Satoor
• Reporter: Mamta A. Satoor
• Component/s: JDBC
• Affects Version/s: 10.0.2.0
• Fix Version/s: 10.1.1.0

Description

Derby returns false for DatabaseMetaData.supportsOpenCursorsAcrossCommit. But that seems incorrect because Derby does support open cursor across commits and hence DatabaseMetaData.supportsOpenCursorsAcrossCommit return true.

Comments

[Jon Craven]

What's the difference between open cursors across commits and holding cursors across commit? Derby clearly supports the latter, supportsResultSetHoldability() returns true and HOLD_CURSORS_OVER_COMMIT is the default holdability.

Currently the supportsOpenCursorsAcrossCommits returns false, the fix for this is to make it return true? Can more explanation be provided for the previous comment?

[Mamta A. Satoor]

IMO, open cursors across commits and holding cursors across commits mean one and the same thing for Derby. But what is unclear to me is the description of return value of DatabaseMetaData.supportsOpenCursorsAcrossCommit in the JDBC api.

The JDBC api says following
boolean supportsOpenCursorsAcrossCommit() throws SQLExceptionRetrieves whether this database supports keeping cursors open across commits.
Returns:
true if cursors always remain open; false if they might not remain open
Throws:
SQLException - if a database access error occurs

A ResultSet object maintains a cursor pointing to its current row of data. Derby will keep the cursor open across commit if the ResultSet object's holdability is set to HOLD_CURSORS_OVER_COMMIT. If the ResultSet object's holdability is set to CLOSE_CURSORS_AT_COMMIT, the cursor will be closed when the transaction is committed. So, in Derby, whether a cursor stays open or closed across commit depends on the holdability of the ResultSet object. Does it then mean from the above api description that supportsOpenCursorsAcrossCommit() should return false in Derby?
If that is the conclusion, then there is no bug with current implementation of supportsOpenCursorsAcrossCommit().

[Shreyas Kaushik]

I think it's the other way around. As the JDBC api says "Retrieves whether this database supports keeping cursors open across commits" it is the property of the database.

Since Derby can have cursors that are open across commits it should return true.

To be even precise, when creating a ResultSet handle that needs to have cursors open across commits, a check on whether the underlying Database supports such a holdability has to be checked.

[Mamta A. Satoor]

The fix for this went as svn commit: r125571

[Mamta A. Satoor]

I think the patch for this bug is incorrect because Derby doesn't support open cursors across commits for XA transactions. This might have been the reason why DatabaseMetaData.supportsOpenCursorsAcrossCommit was returning false prior to the submitted patch. I will submit a new patch which will have DatabaseMetaData.supportsOpenCursorsAcrossCommit return false.

[Mamta A. Satoor]

The patch for this was committed as revision 152678