db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas Korneliussen <Andreas.Kornelius...@Sun.COM>
Subject Re: [jira] Updated: (DERBY-231) "FOR UPDATE" required for updatable result set to work
Date Thu, 03 Nov 2005 14:01:09 GMT
Daniel John Debrunner wrote:
> Bernt M. Johnsen wrote:
>>The patch looks sound. I'll commit when I have run derbyall and
>>experimented a bit on my own.
> So the patch removes this warning
> - **
> - ** According to ANSI, cursors are updatable by default, unless
> - ** they can't be (e.g. they contain joins).  But this would mean
> - ** that we couldn't use an index on any single-table select,
> - ** unless it was declared FOR READ ONLY.  This would be pretty
> - ** terrible, so we are breaking the ANSI rules and making all
> - ** cursors (i.e. select statements) read-only by default.
> - ** Users will have to say FOR UPDATE if they want a cursor to
> - ** be updatable.  Later, we may have an ANSI compatibility
> - ** mode so we can pass the NIST tests.
> but I can't see why it is removed. It seems that Derby will stil not
> support a positioned update/delete on
> whereas the comment implies according to the SQL standard it should.

Thanks for reviewing the changes.

The fix changes the default behavior for cursors, and cursors are now 
updatable, unless they can't be (e.g they contain joins), and unless the 
concurrency mode is READ_ONLY. The default behaviour is used if and only 
if no update clause is specified (FOR UPDATE / FOR READ ONLY). On a 
single table SELECT, we do not have to use "FOR READ ONLY" to use an 
index, since the update mode will be READONLY if the concurrency mode is 
READ_ONLY. Users do not have to say "FOR UPDATE" if they want a cursor 
to be updatable, they can use concurrency mode UPDATABLE.

As you can see, the comment is therefore no longer valid.

> I'm not saying that this change needs to improve Derby to support
> positioned updates on such statements, but the code should continue to
> document such limitations or variations to the standard.
> Though maybe the patch does allow positioned updates on 'SELECT * FROM
> T' if the JDBC result set is updateable? If that's the case, then some
> additional tests should be added.

The patch does allow positioned updates on 'SELECT * FROM T' if the 
concurrency mode is set to CONCUR_UPDATABLE

This is tested implicitly by the fact that the JDBC driver uses 
positioned updates when doing updateRow(). It produces statements like: 
"update table T set ... where current of SQLCUR0".  Positioned updates 
is therefore tested in the jdbc/updateableResultSet.java test.

We do also plan to provide more tests as part of providing updatable 
scrollable resultsets.

> Then the change itself would seem to possibly make any future change to
> supported positioned update/delete on such statements harder. This is
> because the statement is made read-only by the use of a read-only JDBC
> result set. As I've said before, the JDBC result set can be read-only,
> and positioned updates must still work. I'm not sure if such a concern
> should block the patch or not, maybe it's up to the next person who
> addresses this issue to modify the code. The only concern I then have,
> is there any user visible impact of this setting to read only, that
> would change later?

There is no "setting to read only" as part of this patch, it is rather 
the oposite.  Today, the updatemode always defaults to read only if the 
updateclause is unspecified. The change is simply to allow the cursor to 
be UPDATABLE if the concurrency mode for the statement is 

-- Andreas

View raw message