holdcursorjdbc30.out

derby database source code.good for you.

-----------------------
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 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> next  test1;
No current row
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> --  test negative case of trying non next operations after commit
get with hold cursor test1 as 
    'select * from foo for update of data';
ij> next  test1;
A          |DATA       
-----------------------
1          |10         
ij> commit;
ij> delete from foo where current of test1;
ERROR 24000: Invalid cursor state - no current row.
ij> next  test1;
A          |DATA       
-----------------------
1          |20         
ij> commit;
ij> update foo set data=-3000 where current of test1;
ERROR 24000: Invalid cursor state - no current row.
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> --  test positive case of trying delete/update after commit and next.
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> delete from foo where current of test1;
1 row inserted/updated/deleted
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |30         
ij> next  test1;
A          |DATA       
-----------------------
1          |40         
ij> update foo set data=-3000 where current of test1;
1 row inserted/updated/deleted
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> --  make sure above deletes/updates worked.
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          |30         
ij> commit;
ij> next  test1;
A          |DATA       
-----------------------
1          |-3000      
ij> next  test1;
A          |DATA       
-----------------------
1          |50         
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 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;
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 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 ';
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 ';
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 ';
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 ';
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 ';
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 ';
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 ';
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 ';
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 5: basic btree scan tests (multiple 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.
------------------------------------------------------------------------------
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 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 * from foo ';
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 ';
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 ';
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 ';
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 ';
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 ';
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 ';
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 ';
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 6: basic tests for cursors with order by
--     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 cursors which include an order by
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.  This select should only use the
-- index with no interaction with the base table.
get with hold cursor test1 as
    'select a,data from foo order by data desc';
ij> close test1;
ij> commit;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select a,data from foo order by data desc';
ij> commit;
ij> close test1;
ij> -- should fail
next test1;
IJ ERROR: Unable to establish cursor
ij> get with hold cursor test1 as
    'select a,data from foo order by data desc';
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 a,data from foo order by data desc';
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 a,data from foo order by data desc';
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 a,data from foo order by data desc';
ij> next  test1;
No current row
ij> commit;
ij> next  test1;
No current row