db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suresh Thalamati <tsur...@Source-Zone.org>
Subject Re: [VOTE][PATCH]delete from the resultset using JDBC 2.0 Updatable Resultset APIs
Date Fri, 07 Jan 2005 19:45:14 GMT
Mamta Satoor wrote:

>I would like to resubmit the patch for Updatable ResultSet JDBC 2.0
>- delete functionality only. This patch has changes as discussed in thread
>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
>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,
>If createStatement tries to get an updatable ResultSet for
>be a warning accumulated on the Connection object and the driver will
>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.
>stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
>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
>SQL standards
>Derby Documentation for Updatable Cursors
>ResultSet, DatabaseMetaData apis in JDBC 2.0
>Please send in your vote/comments/concerns.
Hi Mamta,

  You may want to test following two scenarions  if you have not done 

  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


View raw message