forupdate.out

derby database source code.good for you.

ij> execute T3PK using 'values (7)';
I |B         
-----
7 |iiii      
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-----
Statement Name: 
	SQL_CURSH200C1
Statement Text: 
	select i, b from t3  where i = ? FOR UPDATE
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Index Row to Base Row ResultSet for T3:
Number of opens = 1
Rows seen = 1
Columns accessed from heap = {0, 1}
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	Index Scan ResultSet for T3 using constraint T3PK at read committed isolation level using exclusive row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 1
	Rows filtered = 0
	Fetch Size = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		next time in milliseconds/row = 0
	scan information: 
		Bit set of columns fetched=All
		Number of columns fetched=2
		Number of deleted rows visited=0
		Number of pages visited=1
		Number of rows qualified=1
		Number of rows visited=1
		Scan type=btree
		Tree height=1
		start position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 
		stop position: 
	> on first 1 column(s).
	Ordered null semantics on the following columns: 
		qualifiers:
None
ij> remove T3PK;
ij> commit;
ij> prepare T3PKFORCE as 'select i, b from t3 where i = ? FOR UPDATE';
ij> prepare T3PK as 'select i, b from t3 where i < ? FOR UPDATE';
ij> execute T3PK using 'values (7)';
I |B         
-----
1 |hhhh      
2 |uuuu      
3 |yyyy      
4 |aaaa      
5 |jjjj      
6 |rrrr      
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-----
Statement Name: 
	SQL_CURSH200C3
Statement Text: 
	select i, b from t3 where i < ? FOR UPDATE
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Index Row to Base Row ResultSet for T3:
Number of opens = 1
Rows seen = 6
Columns accessed from heap = {0, 1}
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	Index Scan ResultSet for T3 using constraint T3PK at read committed isolation level using exclusive row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 6
	Rows filtered = 0
	Fetch Size = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		next time in milliseconds/row = 0
	scan information: 
		Bit set of columns fetched=All
		Number of columns fetched=2
		Number of deleted rows visited=0
		Number of pages visited=1
		Number of rows qualified=6
		Number of rows visited=7
		Scan type=btree
		Tree height=1
		start position: 
	None
		stop position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 
		qualifiers:
None
ij> remove T3PK;
ij> commit;
ij> -- non-unique index
prepare T3BI as 'select i, b from t3  where b = ? FOR UPDATE';
ij> execute T3BI using 'values (''cccc'')';
I |B         
-----
10 |cccc      
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-----
Statement Name: 
	SQL_CURSH200C3
Statement Text: 
	select i, b from t3  where b = ? FOR UPDATE
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Index Row to Base Row ResultSet for T3:
Number of opens = 1
Rows seen = 1
Columns accessed from heap = {0, 1}
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	Index Scan ResultSet for T3 using index T3BI at read committed isolation level using exclusive row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 1
	Rows filtered = 0
	Fetch Size = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		next time in milliseconds/row = 0
	scan information: 
		Bit set of columns fetched=All
		Number of columns fetched=2
		Number of deleted rows visited=0
		Number of pages visited=1
		Number of rows qualified=1
		Number of rows visited=2
		Scan type=btree
		Tree height=1
		start position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 
		stop position: 
	> on first 1 column(s).
	Ordered null semantics on the following columns: 
		qualifiers:
None
ij> remove T3BI;
ij> commit;
ij> prepare T3BIFORCE as 'select i, b from t3 where b = ? FOR UPDATE';
ij> commit;
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
Statement executed.
ij> -- see what happens to a cursor that updates the primary key.
----- first case - no update;
get cursor T3C1 as 'select i,b from t3 where i = 4 for update';
ij> next T3C1;
I |B         
-----
4 |aaaa      
ij> next T3C1;
No current row
ij> close T3C1;
ij> commit;
ij> -- second case - simple update;
get cursor T3C1 as 'select i,b from t3 where i = 4 for update';
ij> next T3C1;
I |B         
-----
4 |aaaa      
ij> update t3 set i = 13 where current of T3C1;
1 row inserted/updated/deleted
ij> next T3C1;
No current row
ij> close T3C1;
ij> commit;
ij> -- third (evil) case - update to change key value and insert a new value;
get cursor T3C1 as 'select i,b from t3 where i = 6 for update';
ij> next T3C1;
I |B         
-----
6 |rrrr      
ij> update t3 set i = 14 where current of T3C1;
1 row inserted/updated/deleted
ij> insert into t3 values (6, 'new!');
1 row inserted/updated/deleted
ij> -- We will not see the newly inserted row because we are now using index scan on the
----- updateable cursor and we already get a row with that key from the unique index.
----- We would get the new row if the index were not unique.  Beetle 3865.
next T3C1;
No current row
ij> close T3C1;
ij> commit;
ij> -- reset autocomiit
autocommit on;
ij> -- drop the tables
drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> -- bug 5643
----- JCC throws NPE when trying to execute a cursor after the resultset is closed
autocommit off;
ij> create table t1 (c1 int);
0 rows inserted/updated/deleted
ij> insert into t1 (c1) values (1),(2),(3);
3 rows inserted/updated/deleted
ij> get cursor curs1 as 'select * from t1 for update of c1';
ij> prepare curs1 as 'update t1 set c1=c1 where current of curs1';
ij> next curs1;
C1         
-----
1          
ij> close curs1;
ij> execute curs1;
ERROR (no SQLState): ResultSet for cursor CURS1 is closed.
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij>