db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <ma...@Remulak.Net>
Subject [VOTE][PATCH]delete from the resultset using JDBC 2.0 Updatable Resultset APIs
Date Fri, 17 Dec 2004 18:46:31 GMT

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.

Output of svn stat
M      java\engine\org\apache\derby\impl\jdbc\EmbedDatabaseMetaData.java
M      java\engine\org\apache\derby\impl\jdbc\EmbedResultSet20.java
M      java\engine\org\apache\derby\impl\jdbc\EmbedConnection.java
M      java\engine\org\apache\derby\iapi\sql\ResultSet.java
M      java\engine\org\apache\derby\iapi\reference\SQLState.java
M      java\engine\org\apache\derby\loc\messages_en.properties
A      java\testing\org\apache\derbyTesting\functionTests\tests\lang\updatableResultSet.java
A      java\testing\org\apache\derbyTesting\functionTests\master\updatableResultSet.out
M      java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall

View raw message