Hi Daniel and Dag,
If we change the code uploaded by Dag and set autoCommit = true and
removed the explicit commit before the positioned update, the same
exception will be thrown because an implicit commit will happen right
before the positioned update. Is that the correct behaviour? Is it so
that it is not possible to modify a resultSet with positioned updates if
autoCommit = true even when using holdability = HOLD_CURSORS_OVER_COMMIT?
Fernanda
Dag H. Wanvik wrote:
Hi Dan,
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?
Thanks, you may be right...I'll go hunt for it...
The problem in the present case, of course, is that a new next() would
take you past the row for which an update was intended. Since forward
only forbids relative(0), I can't see a way to reposition the row
correctly in this case. With scrollable, updatable result sets it
could possibly work, though (using relative(0)). Hmm. It seems less than
orthogonal, in case what you suggest it accurate.
Just tried to access the row using ResultSet#getInt(1) immediately
after the commit(). That gave no error. Are you suggesting it should
have done so?
I also wonder what the cursor position would be "in the meantime",
just after the commit? Prior to the initial next(), the cursor is defined
to be immediately prior to the first row: so where is it just after a
commit, if it does not stay "in place", in some limbo state?
I'll go dig some more into it..
Thanks,
Dag
Daniel>
Daniel> The bug 4515 (Cloudscape bug tracking system) was fixed, I think to return the correct error as you are seeing.
Daniel>
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: 10.2.0.0
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> --
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
Daniel>
