holdcursorjdbc30.out

derby database source code.good for you.

ij> ------------------------------------------------------------------------------
-- 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';
ij> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> autocommit off;
ij> create table foo (a int, data int);
0 rows inserted/updated/deleted
ij> -- 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');
0 rows inserted/updated/deleted
ij> get with hold cursor test1 as 'select * from foo for update';
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> commit;
ij> next  test1;
No current row
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> commit;
ij> ------------------------------------------------------------------------------
-- 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);
1 row inserted/updated/deleted
ij> insert into foo values (1, 20);
1 row inserted/updated/deleted
ij> insert into foo values (1, 30);
1 row inserted/updated/deleted
ij> insert into foo values (1, 40);
1 row inserted/updated/deleted
ij> insert into foo values (1, 50);
1 row inserted/updated/deleted
ij> -- 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';
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |30         
ij> next  test1;
A          |DATA       
-----------------------
1          |40         
ij> next  test1;
A          |DATA       
-----------------------
1          |50         
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |30         
ij> next  test1;
A          |DATA       
-----------------------
1          |40         
ij> next  test1;
A          |DATA       
-----------------------
1          |50         
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as 'select * from foo for update';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |30         
ij> next  test1;
A          |DATA       
-----------------------
1          |40         
ij> next  test1;
A          |DATA       
-----------------------
1          |50         
ij> next  test1;
No current row
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> commit;
ij> ------------------------------------------------------------------------------
-- 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;
ij> drop table foo;
0 rows inserted/updated/deleted
ij> create table foo (a int, data int);
0 rows inserted/updated/deleted
ij> create index foox on foo (a);
0 rows inserted/updated/deleted
ij> -- 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';
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> commit;
ij> next  test1;
No current row
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> next  test1;
No current row
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> commit;
ij> ------------------------------------------------------------------------------
-- 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);
1 row inserted/updated/deleted
ij> insert into foo values (1, 20);
1 row inserted/updated/deleted
ij> insert into foo values (1, 30);
1 row inserted/updated/deleted
ij> insert into foo values (1, 40);
1 row inserted/updated/deleted
ij> insert into foo values (1, 50);
1 row inserted/updated/deleted
ij> -- 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';
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |30         
ij> next  test1;
A          |DATA       
-----------------------
1          |40         
ij> next  test1;
A          |DATA       
-----------------------
1          |50         
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select * from foo for update of data';
ij> next  test1;
A          |DATA