Question about autocommit on and JDBC updatable resultset apis

[Mamta Satoor <ma...@Remulak.Net>]

Hi,

Just a liitle background : For positioned update/deletes using SQL,
Derby
requires that the application should have autocommit off. This is so
that the
cursor is not closed before the positioned statements against it.

My initial thinking for JDBC 2.0 updatable resultsets was that the
applications
do not need autocommit off since the update/delete issued by
updateRow/deleteRow
under the covers will go directly to the parser and hence there will be
no commit issued
by the JDBC layer and the updatable resultset object will continue to
stay open.

All this is well and good as long as there are no SQL exceptions.
Consider following
example where there is an updatable resultset open on a table, and
another statement
tries to drop that table.
   con.setAutoCommit(true);
   s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
   rs = s.executeQuery("SELECT c11 FROM t1 FOR UPDATE");
   rs.next();
   System.out.println("Opened an updatable resultset against t1. Now try
to drop table t1 through another Statement");
   stmt1 = con.createStatement();
   try {
    stmt1.executeUpdate("drop table t1");
    System.out.println("FAIL!!! drop table should have failed because
the updatable resultset is still open");
   }
   catch (SQLException e) {
    if (e.getSQLState().equals("X0X95"))
     System.out.println("expected exception " + e.getMessage());
    else
     System.out.println("Unexpected exception " + e.getMessage());
   }
   System.out.println("Continue with the deleteRow on the updatable
ResultSet object");
   rs.deleteRow();

deleteRow finds that rs is in closed state, The rs object got closed by
the earlier
drop table failure. The severity of the drop table exception was
STATEMENT_SEVERITY only but since autocommit is on,
impl.jdbc.TransactionResourceImpl.java, in its handleException code
raises
the severity to TRANSACTION_SEVERITY. Because of the raised severity,
the updatable resultset is closed as part of the clean up work. And that
is why
deleteRow() finds resultset as closed and it throws an exception that
cursor is closed.

I think this might be confusing for the Derby user. I think they would
not expect
the drop table failure to close the updatable resultset. So, should we
require that
autocommit be off for JDBC updatable resultset apis? Or should we just
make
sure that this behavior is documented well.

BTW, If autocommit was set to off for the test case above, the drop
table exception
severity will not get upgraded to TRANSACTION_SEVERITY and hence
updatable resultset object will not be touched by the exception cleanup
code.

thanks,
Mamta