Thanks, Roy. I've added your sleuthing to the bug.
Cheers,
-Rick
Roy Lyseng wrote:
Hi,
part 3 - "Call level interface" chapter 4.50 Prepare, says that the
statement text can (among other things) be a <dynamic select
statement> which is defined in part 5 - "Host language bindings"
chapter 15.6 Prepare statement as a <cursor specification>, which is
defined in part 2 "Foundation", chapter 14.1 Declare cursor as:
<cursor specification> ::=
<query expression> [ <order by clause> ] [ <updatability clause> ]
and
<updatability clause> ::=
FOR { READ ONLY | UPDATE [OF <column name list> ] }
I saved you the trouble, Bernt :-)
Roy
Rick Hillegas wrote:
Hi Bernt,
I'm afraid I still can't reconstruct how this syntax would be
standard. Perhaps someday over a beer you guys can lead me through
the ansi codicils. As I read volume 5 of the spec, it seems to me
that <query specification> (section 7.2) adds host parameters to the
definition of <query specification> in volume 2--but I can't find any
mention of an <updatability clause>.
In any event, Dan's argument about backward compatibility is
compelling. I like your suggestion that this odd syntax should be
optional.
Cheers,
-Rick
Bernt M. Johnsen wrote:
Rick Hillegas (JIRA) wrote (2005-08-23 00:12:08):
[
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.
You have to consult "Part 5: Host Language Bindings (SQL/Bindings)"
too to actually comprehend what the standard actually says. Since the
JDBC API kind of does what Part 5 is descibing, requiring FOR UPDATE
actually makes sense (but it took 3 of us in Trondheim to figure it
out.... the SQL standard has become way too large ;-( )
I recommend removing this non-standard syntax from naked SELECT
statements which are not embedded in cursors.
I think it should not be removed (see above), but it could be made
optional (for compatability with other db's and since its redundant in
JDBC where one have to specify CONCUR_UPDATABLE too)
