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);
get with hold cursor scrollCursor as 'select * from t1 for update of c12';
update t1 set c12=c12+1 where current of scrollCursor;
– clean up.
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
'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!