db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable result set to work
Date Mon, 22 Aug 2005 22:12:08 GMT
    [ http://issues.apache.org/jira/browse/DERBY-231?page=comments#action_12319649 ] 

Rick Hillegas commented on DERBY-231:
-------------------------------------

I'm a little confused about why Derby allows a FOR UPDATE clause on naked SELECT statements.
I would appreciate a pointer to where the SQL 2003 spec supports this syntax. This is my reading
of the SQL 2003 spec:

The SELECT statement is defined in Volume 2, section 7.12 of the SQL 2003 spec in the <query
specification> production. This production does not allow a FOR UPDATE clause.

The FOR UPDATE clause (the <updatability clause> production) is defined inside section
14.1 of the same spec, the <declare cursor> production. Looking through the index, it
appears that an <updatability clause> is only legal in the context of a cursor.

I recommend removing this non-standard syntax from naked SELECT statements which are not embedded
in cursors.

Perhaps what gave rise to this confusion is the jargon in the comments of Derby's language
layer. In that jargon, a ResultSet is sometimes called a cursor. This is a useful way to look
at a ResultSet but if you press the term too far, you blur the distinction between <query
specification> and <declare cursor>.

> "FOR UPDATE" required for updatable result set to work
> ------------------------------------------------------
>
>          Key: DERBY-231
>          URL: http://issues.apache.org/jira/browse/DERBY-231
>      Project: Derby
>         Type: Improvement
>   Components: SQL
>     Versions: 10.0.2.1
>     Reporter: Dag H. Wanvik
>     Priority: Minor
>  Attachments: fff
>
> To get an updatable result set, the JDBC 3.0 spec, section 14.2.4 
> "Modifying ResultSet Objects" states: 
>     "ResultSet objects with concurrency CONCUR_UPDATABLE can be updated
>      using ResultSet objects".
> In addition, Derby requires the SQL SELECT statement to have a "FOR
> UPDATE" clause for updates to be allowed. This may be a usability issue, as
> many examples, e.g. in "JDBC API tutorial and reference and reference"
> book and the JDBC 3.0 Specification (14.2.4.1) do not include a "FOR
> UPDATE" clause in the SQL SELECT.
> Mamta Satoor says:
> "Derby implements the JDBC updatable resultset by using the existing
>  updatable cursor implementation. And in order to do that, it requires
>  that the SELECT statement should include the FOR UPDATE clause. One
>  can change the Derby implementation so that it does not require FOR
>  UPDATE clause to piggyback on updatable cursor implementation."
> Dan DeBrunner says:
> "Technically I wonder if this is covered by the JDBC standard, I see
>  nothing in the JDBC 3.0 that states any requirements for the SQL
>  statement for an updateable result set. I know the JDBC tutorial book
>  has some guidelines as to what will typically work, but isn't it up to
>  the database engine to define what works here?
>  Having said that I think that not requiring the FOR UPDATE would be a
>  useful improvement."

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message