[DERBY-4778] In Client mode, holdable resultset cursor used in positioned update shouldn't access row with autocommit=true

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.3.3.0
• Component/s: JDBC
• Labels: derby_triage10_8
• Urgency: Low
• Bug behavior facts: Embedded/Client difference
• Assignee: Unassigned
• Reporter: Yun Lee
• Created: 16/Aug/10 13:34
• Updated: 16/Feb/11 14:39

Description

Consider sql snippet below, which is from the tenth test in holdCursorIJ.sql:

create table t1(c11 int, c12 int);
insert into t1 values(1,1);
insert into t1 values(2,2);
autocommit on;
get with hold cursor scrollCursor as 'select * from t1 for update of c12';
next scrollCursor;
update t1 set c12=c12+1 where current of scrollCursor;
– clean up.
close scrollCursor;

look at 'update' line, in Embed mode, 'ERROR 24000: Invalid cursor state - no current row.' will be thrown, it's correct, just like SQL standard, and this behaviour is discussed in DERBY-610.

'The SQL standard 2003, Part 2: Foundation
(SQL/Foundation) p 827:

> 6) If CR is a holdable cursor and a <fetch statement>has not been
> issued against CR within the current SQL- transaction,then an
> exception condition is raised: "invalid cursor state".

That is, a positioning operation is required before the row can be
updated or deleted. Derby implements rowUpdate and rowDelete using
positioned cursor operations and has the same limitation, and will
give the same exception for forward only, updatable result sets. For
Scrollable, updatable insensitive result sets being implemented as
DERBY-690 and DERBY-775, the behavior should be the same. JDBC
doesn't address this requirement, as far as I can tell.'

However, in Client mode, it will succeed. It's not correct!

Issue Links

• is part of: DERBY-310 Document and/or change Derby client code to match behavior with Embedded driver where possible.
• is related to: DERBY-3801 Convert "org.apache.derbyTesting.functionTests.tests.lang.holdCursorIJ.sql" to junit.