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 Wed, 08 Dec 2004 08:25:15 GMT
Hi,

Now that we have branched the codeline for a stable release, it looks
like this might be a good time to come back to the patch for
JDBC 2.0 Updatable Resultset apis - delete functionality only.
Since this is a new feature, it was decided on the list to wait until
a stable release to review and checkin the patch.

I will start out with my +1 vote :-). Please send in your votes.

The changes are pretty much the same as what I had submitted couple of
weeks back with 2 exceptions. I have added property svn:eol-style
native to the test file and the master output file for the test. In
addition, I have changed the header of the test file to use the
standard Apache Copyright header.

Following is a brief description of the proposed functionality.

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 resulset 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 resultsets 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 resultsets, 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 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 Derby documentation under "SELECT statement" section.
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.

Now, moving on to what happens when a deleteRow method is called on
an updatable resultset. A ResultSet.deleteRow api will leave a "hole"
in the resultset which means that the deleted row is not removed from
the resulset and stays visible in the resultset. The corresponding
DatabaseMetaData.ownDeletesAreVisible method will return true for
ResultSet.TYPE_FORWARD_ONLY.

A blank row ("hole") is used as a placeholder where the deleted row
used to be. All the columns in the hole would return 0/NULL on getXXX
method calls (depending on the data type). There are metadata calls in
JDBC 2.0 apis which help the JDBC user to detect such holes.

DatabaseMetaData.deletesAreDetected will return true for
ResultSet.TYPE_FORWARD_ONLY.
ResultSet.rowDeleted will return true if the resultset is positioned on
a row that has been deleted using deleteRow.

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



Mime
View raw message