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:

>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/ReadMsg?listName=derby-dev@db.apache.org&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





Mime
View raw message