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 Mon, 22 Nov 2004 09:35:30 GMT

I would like to submit a patch for JDBC 2.0 Updatable Resultset apis -
functionality only. Please send in your vote based on the following
and the attahced patch.

The supported delete functionlaity is as follows.

The JDBC 2.0 API introduced the ability to update/delete/insert rows
a resultset using methods in the Java programming language rather than
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 currently. In addition, SQL standards do not support
SCROLL_INSENSITIVE cursors. Based on these facts, updatable resultsets
will be supported only for ResultSet.TYPE_FORWARD_ONLY resultsets.
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
or SCROLL_SENSITIVE resultsets, there will be a warning accumulated on
the connection object and a SCROLL_INSENSITIVE READ_ONLY resultset
would be returned. (can find tests for this in
lang/updatableResultSet.java test)

Implementation of this forward only updatable resultset is coded based
on the
existing positioned update cursors code. And because of that, the select
sql sent
on the Statement object will have the same syntax and restrictions as
FOR UPDATE sql for updatable cursors.
 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
 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"
which goes over what is allowed in the SELECT sql for an updatable

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
resultset which means that the deleted row is not removed from the
and stays visible in the resultset. The corresponding
DatabaseMetaData.ownDeletesAreVisible method will return true for

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

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.

View raw message