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
Hi,

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

The supported delete functionlaity is as follows.

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 currently. In addition, SQL standards do not support
updatable
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,
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 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
FOR UPDATE sql for 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