db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <Dag.Wan...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-610) After commit, holdable result set cursor used in positioned update can't access row
Date Mon, 10 Oct 2005 17:02:32 GMT

>>>>> "Daniel" == Daniel John Debrunner (JIRA) <derby-dev@db.apache.org>

Daniel> I think the current behaviour is correct. After a commit the
Daniel> result set is open but is not positioned on a row. The result
Daniel> set must be re-positioned with a next() or other method before
Daniel> acessing a row or modifying it with a positioned update or
Daniel> delete. I think this may be from the SQL standard, or is it
Daniel> the JDBC standard?

I found it in the SQL standard, 2003, vol II, page 94:

"However, before either an <update statement:positioned> or a <delete
statement:positioned> is permitted to reference that cursor in the new
SQL-transaction,a <fetch statement> shall be issued against the

When I try updateRow() after a commit, that, too, fails in Derby with
the same error. I wonder if that is correct, though, I can't see JDBC
addressing this at all. I guess if we allow for the fact that JDBC
builds on SQL semantics, it is not too surprising, but still I'd love
to see the JDBC spec come clean on this ;-) Lance? :)

I should think this behavior would come as a surprise to JDBC users,
though; JDBC just states that holdable cursors remain open after a

Also, since we don't support refreshRow yet, once the transaction is
committed, we have effectively lost the current row for good
(forward-only, can only do next() ).

I'll close the issue if the consensus is that the updateRow should
fail as well.


Daniel> The bug 4515 (Cloudscape bug tracking system) was fixed, I think to return the
correct error as you are seeing.
Daniel> > After commit, holdable result set cursor used in positioned update can't 
access row
Daniel> > ------------------------------------------------------------------------------------
Daniel> >
Daniel> >          Key: DERBY-610
Daniel> >          URL: http://issues.apache.org/jira/browse/DERBY-610
Daniel> >      Project: Derby
Daniel> >         Type: Bug
Daniel> >   Components: SQL
Daniel> >     Versions:
Daniel> >     Reporter: Dag H. Wanvik
Daniel> >     Priority: Minor
Daniel> >  Attachments: Main.java
Daniel> >
Daniel> > Using the embedded driver, with autocommit off, and holdability for a
Daniel> > updatable result set  (HOLD_CURSORS_OVER_COMMIT), I first
Daniel> > position on a row in the result set, and then commit the
Daniel> > transaction. The result set should still be open since holdability is
Daniel> > active.  However, when accessing the result set using a named cursor
Daniel> > in a subsequent "Positioned update" statement, Derby returns an error
Daniel> > message: "Invalid cursor state - no current row" (SQLState 24000).
Daniel> > Please see the self-contained repro case in the attachment for
Daniel> > details.
Daniel> > It appears the problem is related to reopening of holdable result sets
Daniel> > after a commit. During normal use of result sets, the ResultSet#next()
Daniel> > operation will make sure the result set is opened (after locks were
Daniel> > released at commit time). Apparently no such reopening is performed in
Daniel> > the "positioned update" case. (small detail: Note that in the
Daniel> > "positioned update" case, the reopening is for accessing the current
Daniel> > row (again), not the next - I am not sure if that matters, though).
Daniel> -- 
Daniel> This message is automatically generated by JIRA.
Daniel> -
Daniel> If you think it was sent incorrectly contact one of the administrators:
Daniel>    http://issues.apache.org/jira/secure/Administrators.jspa
Daniel> -
Daniel> For more information on JIRA, see:
Daniel>    http://www.atlassian.com/software/jira
Dag H. Wanvik
Sun Microsystems, Web Services, Database Technology Group
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax:  +47 73842101

NOTICE: This email message is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or distribution
is prohibited. If you are not the intended recipient, please contact
the sender by reply email and destroy all copies of the original

View raw message