holdcursorjdbc30.sql

derby database source code.good for you.

------------------------------------------------------------------------------
-- TEST CASES SPECIFIC TO STORE IMPLEMENTATION OF HOLD CURSOR:
-- overview:
--    TEST  0: basic heap  scan tests (0 rows).
--    TEST  1: basic heap  scan tests (multiple rows)
--    TEST  2: basic btree scan tests (zero rows/update nonkey field)
--    TEST  3: basic btree scan tests (multiple rows/update nonkey field)
--    TEST  4: basic btree scan tests (zero rows/read only/no group fetch)
--    TEST  5: basic btree scan tests (multiple rows/read only/no group fetch)
--    TEST  6: basic tests for cursors with order by
--    TEST  7: test of hold cursor code in DistinctScalarAggregateResultSet.java
--    TEST  8: test of hold cursor code in GroupedAggregateResultSet.java
--    TEST  9: test scan positioned on a row which has been purged.
--    TEST 10: test scan positioned on a page which has been purged
--
------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- TEST 0: basic heap scan tests (0 rows).
--     The following tests that no matter where commit comes in the state of
--     the scan that the scan will continue after the commit.  Tests various
--     states of scan like: before first next, after first next, before close,
--     after close.
------------------------------------------------------------------------------

run resource 'createTestProcedures.subsql';
autocommit off;
create table foo (a int, data int);

-- the following for update cursors will all use group fetch = 1, thus each
-- next passes straight through to store.
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '1');
get with hold cursor test1 as 'select * from foo for update';
close test1;
commit;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;


commit;

------------------------------------------------------------------------------
-- TEST 1: basic heap scan tests (multiple rows)
--     The following tests that no matter where commit comes in the state of
--     the scan that the scan will continue after the commit.  Tests various
--     states of scan like: before first next, after first next, before close,
--     after close.
------------------------------------------------------------------------------

insert into foo values (1, 10);
insert into foo values (1, 20);
insert into foo values (1, 30);
insert into foo values (1, 40);
insert into foo values (1, 50);

-- the following for update cursors will all use group fetch = 1, thus each
-- next passes straight through to store.
get with hold cursor test1 as 'select * from foo for update';
close test1;
commit;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 'select * from foo for update';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;


commit;

------------------------------------------------------------------------------
-- TEST 2: basic btree scan tests (zero rows/update nonkey field)
--     The following tests that no matter where commit comes in the state of
--     the scan that the scan will continue after the commit.  Tests various
--     states of scan like: before first next, after first next, before close,
--     after close.
------------------------------------------------------------------------------

autocommit off;
drop table foo;
create table foo (a int, data int);
create index foox on foo (a);

-- the following for update cursors will all use group fetch = 1, thus each
-- next passes straight through to store.
get with hold cursor test1 as
    'select * from foo for update of data';
close test1;
commit;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;


commit;

------------------------------------------------------------------------------
-- TEST 3: basic btree scan tests (multiple rows/update nonkey field)
--     The following tests that no matter where commit comes in the state of
--     the scan that the scan will continue after the commit.  Tests various
--     states of scan like: before first next, after first next, before close,
--     after close.
------------------------------------------------------------------------------

insert into foo values (1, 10);
insert into foo values (1, 20);
insert into foo values (1, 30);
insert into foo values (1, 40);
insert into foo values (1, 50);

-- the following for update of data cursors will all use group fetch = 1, thus each
-- next passes straight through to store.
get with hold cursor test1 as
    'select * from foo for update of data';
close test1;
commit;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as 
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;

--  test negative case of trying non next operations after commit
get with hold cursor test1 as 
    'select * from foo for update of data';
next  test1;
commit;
delete from foo where current of test1;
next  test1;
commit;
update foo set data=-3000 where current of test1;
next  test1;
next  test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;

--  test positive case of trying delete/update after commit and next.
get with hold cursor test1 as 
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
delete from foo where current of test1;
commit;
next  test1;
next  test1;
update foo set data=-3000 where current of test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;

--  make sure above deletes/updates worked.
get with hold cursor test1 as 
    'select * from foo for update of data';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
close test1;
commit;
-- should fail
next test1;


commit;

------------------------------------------------------------------------------
-- TEST 4: basic btree scan tests (zero rows/read only/no group fetch)
--     The following tests that no matter where commit comes in the state of
--     the scan that the scan will continue after the commit.  Tests various
--     states of scan like: before first next, after first next, before close,
--     after close.
------------------------------------------------------------------------------

-- basic tests for btree
autocommit off;
drop table foo;
create table foo (a int, data int);
create index foox on foo (a);

-- the following for read cursors will all use group fetch = 1, thus each
-- next passes straight through to store.  This select should only use the
-- index with no interaction with the base table.
get with hold cursor test1 as
    'select a from foo ';

close test1;
commit;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo ';
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo ';
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo ';
next  test1;
commit;
next  test1;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo ';
next  test1;
commit;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo ';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail
next test1;

get with hold cursor test1 as
    'select * from foo ';
next  test1;
commit;
next  test1;
commit;
next  test1;
next  test1;
next  test1;
next  test1;
commit;
close test1;
-- should fail