Army wrote:
The remaining question is whether or not this is correct. I'm hoping
Knut Anders is right when he wrote:
knut> there's no guarantee (I think) as to when the rows are actually read.
knut> But I'll leave it to the scholars to find the chapter and verse in
the
knut> appropriate spec... ;)
That's what I'm looking for :)
SQL-2003 states the following for cursors and updates (Chp 4.32.2.
Operations on and using cursors):
------8<------
If a cursor is open, and the SQL-transaction in which the cursor was
opened makes a significant [1] change to SQL-data, then whether that
change is visible through that cursor before it is closed is determined
as follows:
- If the cursor is insensitive, then significant changes are not visible.
- If the cursor is sensitive, then significant changes are visible.
- If the cursor is asensitive, then the visibility of significant
changes is implementation-dependent.
------>8-------
Comment:
[1] significant meaning update performed by other commands than <update
statement: posisioned> that would have had affect if commited before the
cursor was opened.
Hence, the behavior described by Army is correct iff the cursor is
defined as asensitive. Further more:
Chp 14.1 (declare cursor):
------8<-----
5) If <cursor sensitivity> is not specified, then ASENSITIVE is implicit.
------>8-----
My understanding is therefore that the differences between index and
scan is allowed by SQL 2003. An interesting question is what Derby does
if the cursor is specified to be either sensitive or insensitive...
Note that I was able to reproduce this
behavior with a JDBC program, inlined at the end of this email. That
program uses the Connection.createStatement() method, which means that
result sets from that statement should default to TYPE_FORWARD_ONLY (as
opposed to SCROLL_INSENSITIVE or SCROLL_SENSITIVE).
So it seems like the question is: should a "forward only" result set be
sensitive to updates to rows that it hasn't read yet? Or is this
documented somewhere in JDBC as "not guaranteed" (or whatever the
correct term is)? Unless there is doc saying that such a thing leads to
undefined behavior (per Knut Anders' suggestion), it seems odd (to me)
that the "sensitivity" of a forward-only result set apparently depends
on the underlying Derby scan type...
As defined in SQL 2003, ASENSITIVE is the default sensitivity.
--
Jørgen Løland
