Return-Path: 
 <derby-dev-return-44582-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 6699 invoked from network); 13 Jun 2007 08:33:22 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 13 Jun 2007 08:33:22 -0000
Received: (qmail 16585 invoked by uid 500); 13 Jun 2007 08:33:26 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 16372 invoked by uid 500); 13 Jun 2007 08:33:25 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 16359 invoked by uid 99); 13 Jun 2007 08:33:25 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 13 Jun 2007 01:33:25 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: local policy)
Received: from [192.18.1.36] (HELO gmp-ea-fw-1.sun.com) (192.18.1.36)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 13 Jun 2007 01:33:21 -0700
Received: from d1-emea-10.sun.com (d1-emea-10.sun.com [192.18.2.120])
	by gmp-ea-fw-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l5D8Wtqo027986
	for <derby-dev@db.apache.org>; Wed, 13 Jun 2007 08:32:59 GMT
Received: from conversion-daemon.d1-emea-10.sun.com by d1-emea-10.sun.com
 (Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
 id <0JJK00201FKNWZ00@d1-emea-10.sun.com>
 (original mail from Jorgen.Loland@Sun.COM) for derby-dev@db.apache.org; Wed,
 13 Jun 2007 09:32:55 +0100 (BST)
Received: from [129.159.112.237] by d1-emea-10.sun.com
 (Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
 with ESMTPSA id <0JJK0072OFQRQ9KA@d1-emea-10.sun.com> for
 derby-dev@db.apache.org; Wed, 13 Jun 2007 09:32:53 +0100 (BST)
Date: Wed, 13 Jun 2007 10:32:51 +0200
From: =?ISO-8859-1?Q?J=F8rgen_L=F8land?= <Jorgen.Loland@Sun.COM>
Subject: Re: Updating rows with an open cursor...what is the expected
 behavior?
In-reply-to: <466EEF36.5080801@gmail.com>
Sender: Jorgen.Loland@Sun.COM
To: derby-dev@db.apache.org
Message-id: <466FABB3.6050608@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 8BIT
References: <466ED7F4.407@gmail.com> <466EE859.4080400@sbcglobal.net>
 <466EEF36.5080801@gmail.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070419)
X-Virus-Checked: Checked by ClamAV on apache.org

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