ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- create some tables create table t1(c50 char(50), i int); 0 rows inserted/updated/deleted ij> create table t2(c50 char(50), i int); 0 rows inserted/updated/deleted ij> -- populate tables insert into t1 values ('b', 2), ('c', 3), ('d', 4), ('e', 5), ('f', 6), ('g', 7), ('h', 8), ('i', 9), ('j', 10), ('k', 11), ('l', 12), ('m', 13); 12 rows inserted/updated/deleted ij> autocommit off; ij> -- negative ----- position on forward only cursor get cursor c1 as 'select i from t1'; ij> getcurrentrownumber c1; IJ ERROR: GETCURRENTROWNUMBER is not allowed on a forward only cursor. ij> first c1; IJ ERROR: FIRST is not allowed on a forward only cursor. ij> last c1; IJ ERROR: LAST is not allowed on a forward only cursor. ij> previous c1; IJ ERROR: PREVIOUS is not allowed on a forward only cursor. ij> next c1; I ----- 2 ij> before first c1; IJ ERROR: BEFORE FIRST is not allowed on a forward only cursor. ij> after last c1; IJ ERROR: AFTER LAST is not allowed on a forward only cursor. ij> absolute 1 c1; IJ ERROR: ABSOLUTE is not allowed on a forward only cursor. ij> relative 1 c1; IJ ERROR: RELATIVE is not allowed on a forward only cursor. ij> close c1; ij> get scroll insensitive cursor c1 as 'select * from t1'; ij> absolute 0 c1; No current row ij> close c1; ij> get scroll insensitive cursor c1 as 'select * from t1'; ij> relative 0 c1; No current row ij> close c1; ij> get scroll insensitive cursor c1 as 'select * from t1'; ij> relative 2 c1; C50 |I ----- c |3 ij> close c1; ij> -- positive ----- test positioning get scroll insensitive cursor c1 as 'select * from t1'; ij> -- 2 first c1; C50 |I ----- b |2 ij> getcurrentrownumber c1; 1 ij> -- 3 next c1; C50 |I ----- c |3 ij> getcurrentrownumber c1; 2 ij> -- 2 first c1; C50 |I ----- b |2 ij> getcurrentrownumber c1; 1 ij> -- 3 next c1; C50 |I ----- c |3 ij> getcurrentrownumber c1; 2 ij> -- 4 next c1; C50 |I ----- d |4 ij> getcurrentrownumber c1; 3 ij> -- 2 first c1; C50 |I ----- b |2 ij> getcurrentrownumber c1; 1 ij> -- 3 next c1; C50 |I ----- c |3 ij> getcurrentrownumber c1; 2 ij> -- null after last c1; No current row ij> getcurrentrownumber c1; 0 ij> -- beetle 5509 ----- null next c1; No current row ij> -- beetle 5509 getcurrentrownumber c1; 0 ij> -- beetle 5509 ----- 13 previous c1; C50 |I ----- m |13 ij> -- beetle 5509 getcurrentrownumber c1; 12 ij> -- beetle 5509 ----- 12 previous c1; C50 |I ----- l |12 ij> -- beetle 5509 getcurrentrownumber c1; 11 ij> -- 13 last c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 12 ij> -- null before first c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 2 next c1; C50 |I ----- b |2 ij> getcurrentrownumber c1; 1 ij> -- 13 absolute 12 c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 12 ij> -- 3 absolute -11 c1; C50 |I ----- c |3 ij> getcurrentrownumber c1; 2 ij> -- null absolute 13 c1; No current row ij> getcurrentrownumber c1; 0 ij> -- null absolute -13 c1; No current row ij> getcurrentrownumber c1; 0 ij> -- absolute -1 should be last row absolute -1 c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 12 ij> close c1; ij> -- do last first get scroll insensitive cursor c1 as 'select * from t1'; ij> -- 13 last c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 12 ij> -- null next c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 13 last c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 12 ij> -- 12 previous c1; C50 |I ----- l |12 ij> getcurrentrownumber c1; 11 ij> -- 2 first c1; C50 |I ----- b |2 ij> getcurrentrownumber c1; 1 ij> -- null previous c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 2 next c1; C50 |I ----- b |2 ij> getcurrentrownumber c1; 1 ij> close c1; ij> -- do after last first get scroll insensitive cursor c1 as 'select * from t1'; ij> -- null after last c1; No current row ij> -- 13 previous c1; C50 |I ----- m |13 ij> -- 12 previous c1; C50 |I ----- l |12 ij> close c1; ij> -- go to next to last row, then do next get scroll insensitive cursor c1 as 'select * from t1 where i >= 11'; ij> -- 11 next c1; C50 |I ----- k |11 ij> getcurrentrownumber c1; 1 ij> -- 12 next c1; C50 |I ----- l |12 ij> getcurrentrownumber c1; 2 ij> -- 13 last c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 3 ij> -- 12 previous c1; C50 |I ----- l |12 ij> getcurrentrownumber c1; 2 ij> -- null after last c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 13 previous c1; C50 |I ----- m |13 ij> close c1; ij> -- start at after last get scroll insensitive cursor c1 as 'select * from t1 where i >= 11'; ij> -- null after last c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 13 previous c1; C50 |I ----- m |13 ij> getcurrentrownumber c1; 3 ij> close c1; ij> -- use absolute to get rows before ----- scan would get to them get scroll insensitive cursor c1 as 'select i from t1'; ij> -- 6 absolute 5 c1; I ----- 6 ij> getcurrentrownumber c1; 5 ij> -- 9 absolute -5 c1; I ----- 9 ij> getcurrentrownumber c1; 8 ij> -- 6 absolute 5 c1; I ----- 6 ij> getcurrentrownumber c1; 5 ij> close c1; ij> get scroll insensitive cursor c1 as 'select i from t1'; ij> -- null absolute 13 c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 13 previous c1; I ----- 13 ij> getcurrentrownumber c1; 12 ij> close c1; ij> get scroll insensitive cursor c1 as 'select i from t1'; ij> -- null absolute -13 c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 2 next c1; I ----- 2 ij> getcurrentrownumber c1; 1 ij> close c1; ij> -- test relative implementation get scroll insensitive cursor c1 as 'select i from t1'; ij> -- 2 first c1; I ----- 2 ij> getcurrentrownumber c1; 1 ij> -- 13 relative 11 c1; I ----- 13 ij> getcurrentrownumber c1; 12 ij> -- null relative 1 c1; No current row ij> getcurrentrownumber c1; 0 ij> -- 13 last c1; I ----- 13 ij> getcurrentrownumber c1; 12 ij> -- 2 relative -11 c1; I ----- 2 ij> getcurrentrownumber c1; 1 ij> close c1; ij> -- scroll sensitive cursor becomes scroll insensitive commit; ij> get scroll sensitive cursor c1 as 'select i from t1'; ij> first c1; I ----- 2 ij> next c1; I ----- 3 ij> update t1 set i = 666 where i = 2; 1 row inserted/updated/deleted ij> first c1; I ----- 2 ij> rollback; ij> close c1; ij> -- verify that statement cache works ----- correctly with scroll and forward only ----- cursors on same query text get scroll insensitive cursor c1 as 'select i from t1'; ij> get cursor c2 as 'select i from t1'; ij> first c1; I ----- 2 ij> next c2; I ----- 2 ij> first c2; IJ ERROR: FIRST is not allowed on a forward only cursor. ij> close c1; ij> close c2; ij> -- first, last, etc. on empty result set get scroll insensitive cursor c1 as 'select i from t1 where 1=0'; ij> first c1; No current row ij> getcurrentrownumber c1; 0 ij> previous c1; No current row ij> getcurrentrownumber c1; 0 ij> next c1; No current row ij> getcurrentrownumber c1; 0 ij> last c1; No current row ij> getcurrentrownumber c1; 0 ij> next c1; No current row ij> getcurrentrownumber c1; 0 ij> previous c1; No current row ij> getcurrentrownumber c1; 0 ij> absolute 1 c1; No current row ij> getcurrentrownumber c1; 0 ij> absolute -1 c1; No current row ij> getcurrentrownumber c1; 0 ij> close c1; ij> get scroll insensitive cursor c1 as 'select i from t1 where 1=0'; ij> after last c1; No current row ij> getcurrentrownumber c1; 0 ij> previous c1; No current row ij> getcurrentrownumber c1; 0 ij> before first c1; No current row ij> getcurrentrownumber c1; 0 ij> next c1; No current row ij> getcurrentrownumber c1; 0 ij> close c1; ij> get scroll insensitive cursor c1 as 'select i from t1 where 1=0'; ij> absolute 1 c1; No current row ij> absolute -1 c1; No current row ij> close c1; ij> get scroll insensitive cursor c1 as 'select i from t1 where 1=0'; ij> absolute -1 c1; No current row ij> absolute 1 c1; No current row ij> close c1; ij> autocommit on; ij> get scroll insensitive with hold cursor c1 as 'select i from t1 where 1=0'; ij> first c1; No current row ij> first c1; No current row ij> last c1; No current row ij> last c1; No current row ij> absolute 1 c1; No current row ij> absolute -1 c1; No current row ij> before first c1; No current row ij> after last c1; No current row ij> previous c1; No current row ij> next c1; No current row ij> -- beetle 5510 next c1; No current row ij> close c1; ij> -- cursor on a sort get scroll insensitive cursor c1 as 'select * from t1 order by i desc'; ij> -- 2 last c1; C50 |I ----- b |2 ij> -- 13 first c1; C50 |I ----- m |13 ij> -- 2 relative 11 c1; C50 |I ----- b |2 ij> -- 3 previous c1; C50 |I ----- c |3 ij> close c1; ij> -- RTS call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); Statement executed. ij> maximumdisplaywidth 2000; ij> get scroll insensitive cursor c1 as 'select * from t1'; ij> last c1; C50 |I ----- m |13 ij> first c1; C50 |I ----- b |2 ij> next c1; C50 |I ----- c |3 ij> close c1; ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ----- Statement Name: SQL_CURLH000C2 Statement Text: select * from t1 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: Scroll Insensitive ResultSet: Number of opens = 1 Number of writes to hash table = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows filtered = 0 Fetch Size = 16 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 pages visited=1 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> get scroll insensitive cursor c1 as 'select * from t1'; ij> close c1; ij> -- for following set of tests, setting the holdability over commit to false for this connection since that is what we want to test below ----- Using this rather than passing with nohold to cursor statement because this test also runs in jdk13 and lower and there is no way to ----- set the holdability using jdbc api in those jdks (unless trying that through a jdbc program where one can use reflection to set holdability ----- in jdk131) NoholdForConnection; ij> -- beetle 4551 - insensitive cursor uses estimated row count which might be ----- pessimistic and will get out of memory error create table big(a int generated always as identity (start with 1, increment by 1)); 0 rows inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> insert into big values(default); 1 row inserted/updated/deleted ij> get scroll insensitive cursor s1 as 'select * from big b1 left outer join big b2 on b1.a = b2.a left outer join big b3 on b2.a = b3.a left outer join big b4 on b3.a = b4.a left outer join (big b5 left outer join (big b6 left outer join (big b7 left outer join big b8 on b7.a = b8.a) on b6.a=b7.a) on b5.a = b6.a) on b4.a = b5.a'; ij> -- clean up drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table big; 0 rows inserted/updated/deleted ij>