Mamta Satoor wrote:
Hi,
I would like to resubmit the patch for Updatable ResultSet JDBC 2.0 - delete functionality only. This patch has changes as discussed in thread http://nagoya.apache.org/eyebrowse/[EMAIL PROTECTED]&msgNo=1356
My vote : +1. Please send in your votes.
Following is a brief description of what the patch provides
The JDBC 2.0 API introduced the ability to update/delete/insert rows from a ResultSet using methods in the Java programming language rather than having to send an SQL command. In order to be able to update a ResultSet using these new JDBC 2.0 apis, the ResultSet should be updatable. The JDBC programmer gets an updatable ResultSet by supplying ResultSet.CONCUR_UPDATABLE to the cresteStatement method.
Derby currently supports FORWARD_ONLY and SCROLL_INSENSITIVE resultsets in read only mode. There is no support for SCROLL_SENSITIVE resultsets. In addition, SQL standards do not support updatable SCROLL_INSENSITIVE cursors.
Based on these facts, this patch will support updatable ResultSet for only ResultSet.TYPE_FORWARD_ONLY. You get such a ResultSet with following createStatement call stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
If createStatement tries to get an updatable ResultSet for SCROLL_INSENSITIVE or SCROLL_SENSITIVE ResultSet, there will be a warning accumulated on the Connection object and the driver will return SCROLL_INSENSITIVE READ_ONLY ResultSet.
Implementation of this forward only updatable ResultSet is coded based on the existing positioned update/delete cursor code. Because of that, the select sql sent on the Statement object will have the same syntax and restrictions as for FOR UPDATE sql for positioned updatable cursors.
eg stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("SELECT c32 FROM t3 FOR UPDATE");
Please refer to "SELECT statement" in Derby Reference manual index. In that same section, there is "Requirements for Updatable Cursors" sub-section which goes over what is allowed in the SELECT sql for an updatable cursor.
One difference to notice between updatable cursors and updatable ResultSets is that the JDBC program is not required to have autocommit off when using updatable ResultSets JDBC apis..
Now, moving on to what happens when a deleteRow method is called after the updatable ResultSet is positioned on a row. deleteRow will delete the row from the database and the ResultSet object will be positioned before the next row. The program then need to reposition the ResultSet with next() before issuing any methods other than close on the ResultSet object.
Also, because the Resultset is positioned before the next row, the deleted row is not detected and is not visible. Hence, the DatabaseMetaData methods, ownDeletesAreVisible and deletesAreDetected will return false for FORWARD_ONLY updatable ResultSets. And as per the JDBC specs, ResultSet.rowDeleted should not be relied upon, since the driver can't detect deletes.
I think that covers everything. Following is a good list of reference material related to this patch JDBC Tutotial http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/makingupdates.html
SQL standards Derby Documentation for Updatable Cursors ResultSet, DatabaseMetaData apis in JDBC 2.0
Please send in your vote/comments/concerns. Mamta
Hi Mamta,
You may want to test following two scenarions if you have not done already:
1) delete from result set that involves table with referential actions (on delete cascade ..etc). Concerned about
How the result set will look after a delete on a table that has self refential delete cascade, especially if the delete happens to affect the
rows that are yet to be positioned.
2) rollback of a transaction that has deletes using update resultset. This will help in making sure all contexts are set
properly.
-suresht
