db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: [jira] Updated: (DERBY-231) "FOR UPDATE" required for updatable result set to work
Date Fri, 04 Nov 2005 03:17:58 GMT
Bernt M. Johnsen wrote:

>>>>>>>>>>>>>Daniel John Debrunner wrote (2005-11-03
10:44:06):
>>
>>According to the SQL standard a statement like SELECT * FROM T is
>>updateable, Derby does not make that statement updatable in all cases.
>>And by updateable, I mean through positioned updated/delete.
> 
> 
> If updatability is not specified, a SELECT * FROM T will be
> implicitely read only in the context of a cursor which is insensitive,
> scrollable or have an order by clause. Otherwise it is implicitely
> updatable. See e.g. ISO/IEC 9075-2:1999, ch 14.1, Syntax Rules ยง10,
> p. 652.
> 
> 
>>The SQL standard does not require that the client's JDBC result set is
>>updateable to make the statement updateable, obviouly because the SQL
>>standard is self contained and independent of the JDBC spec.
> 
> 
> I would argue that you can not view JDBC and SQL independently and
> that a call to executeQuery() would establish such cursor context,
> although it will not use only the SQL syntax but also the JDBC API.

Thanks for the useful infomation, but how are you inferring those cursor
context attributes from the JDBC ResultSet being read-only?

A Derby forward only ResultSet, is read only but:

   - is not scrollable
   - does not have an order by clause

So it fails on two of the context items that make the SQL statement
implicitly read only. It then comes down to is it insensitive? I always
need to go to the JDBC tutorial book to clarify what insensitive means
in this context and I don't have it with me until tomorrow. Though I did
discover a post by Mamta that states Derby's forward only ResultSets are
sensitive:

http://mail-archives.apache.org/mod_mbox/db-derby-dev/200501.mbox/%3C41DEF292.D44626CE@Remulak.Net%3E

Another way of looking at it is, what you are saying that with a
statement like SELECT * FROM T, I can only perform a positioned update
on it if:

   - I make the JBDC ResultSet updateable

   - or I add a FOR UPDATE clause to the SQL statement.

Now the FOR UPDATE clause is obvious, but I can't see the link to the
JDBC ResultSet being updateable. I can't see other databases having this
requirement, especially as they supported positioned updates with
read-only ResultSets before JDBC supported updateable ResultSets. I can
see other databases being as restrictive as Derby, hence requiring the
FOR UPDATE, but the old comment did indicate that was against the SQL
standard.


The patch can probably proceed without this being resolved, but it would
be good to come to clear agreement on if SELECT * FROM T can be updated
with a positioned update/delete and a read-only ResultSet. (Even if
Derby doesn't support it today, it would be nice to know or not if it is
meant to be supported).

Dan.


Mime
View raw message