db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <ma...@Remulak.Net>
Subject Re: [VOTE][PATCH]delete from the resultset using JDBC 2.0 UpdatableResultset APIs
Date Fri, 07 Jan 2005 21:20:04 GMT


Suresh Thalamati wrote:

> 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

Hi Suresh,

I am working on writing some more tests based on your feedback. Once I have them ready,
I will post it here and let me know if that is what you had in mind.

thanks,
Mamta

Mime
View raw message