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 a table
create table t1(c11 int, c12 int);
0 rows inserted/updated/deleted
ij> -- insert data into tables
insert into t1 values(1,1);
1 row inserted/updated/deleted
ij> insert into t1 values(2,2);
1 row inserted/updated/deleted
ij> -- set autocommit off
autocommit off;
ij> -- first test - make sure that only cursors created with holdability true
-- have open resultsets after commit

-- declare 3 different kind of cursors one for each jdbc release so far
get with nohold cursor jdk1 as 'SELECT * FROM t1';
ij> get scroll insensitive with nohold cursor jdk2 as 'SELECT * FROM t1';
ij> get with hold cursor jdk4 as 'SELECT * FROM t1';
ij> -- do fetches from these cursors
next jdk1;
C11        |C12        
-----------------------
1          |1          
ij> next jdk2;
C11        |C12        
-----------------------
1          |1          
ij> next jdk4;
C11        |C12        
-----------------------
1          |1          
ij> --commit
commit;
ij> -- now try the fetch on cursors again after commit
-- cursors jdk1 and jdk2 will give errors
next jdk1;
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.
ij> next jdk2;
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.
ij> next jdk4;
C11        |C12        
-----------------------
2          |2          
ij> -- end of resultset for jdk4, but try next again
next jdk4;
No current row
ij> close jdk4;
ij> next jdk4;
IJ ERROR: Unable to establish cursor JDK4@CONNECTION0
ij> -- clean up.
close jdk1;
ij> close jdk2;
ij> -- second test - make sure that all the cursors (including holdability true)
-- have their resultsets closed after rollback.

-- declare the cursors again, this time, try with rollback
get with nohold cursor jdk1 as 'SELECT * FROM t1';
ij> get scroll insensitive with nohold cursor jdk2 as 'SELECT * FROM t1';
ij> get with hold cursor jdk4 as 'SELECT * FROM t1';
ij> -- do fetches from these cursors
next jdk1;
C11        |C12        
-----------------------
1          |1          
ij> next jdk2;
C11        |C12        
-----------------------
1          |1          
ij> next jdk4;
C11        |C12        
-----------------------
1          |1          
ij> --rollback
rollback;
ij> -- now try the fetch on cursors again after rollback
-- all the cursors will give errors
next jdk1;
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.
ij> next jdk2;
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.
ij> next jdk4;
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.
ij> -- clean up.
close jdk1;
ij> close jdk2;
ij> close jdk4;
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- recreate and populate the table for next test
create table t1(c11 int, c12 int);
0 rows inserted/updated/deleted
ij> insert into t1 values(1,1);
1 row inserted/updated/deleted
ij> insert into t1 values(2,2);
1 row inserted/updated/deleted
ij> -- fourth test - try to change the isolation level while there are
-- held cursors
get with nohold cursor jdk1 as 'SELECT * FROM t1';
ij> get with hold cursor jdk4 as 'SELECT * FROM t1';
ij> next jdk1;
C11        |C12        
-----------------------
1          |1          
ij> next jdk4;
C11        |C12        
-----------------------
1          |1          
ij> -- changing isolation while cursor is open would fail; 
-- but for client/server, with small data set, the server would already be
-- closed. See discussion re DERBY-3801.
-- close jdk4 and then should be able to change isolation
close jdk4;
ij> set isolation to serializable;
0 rows inserted/updated/deleted
ij> -- clean up.
close jdk1;
ij> -- fifth test - try isolation level change alongwith changing the isolation
-- level of just one statement
get with hold cursor jdk4 as 'SELECT * FROM t1';
ij> get with nohold cursor jdk1 as 'SELECT * FROM t1 WITH CS';
ij> next jdk4;
C11        |C12        
-----------------------
1          |1          
ij> next jdk1;
C11        |C12        
-----------------------
1          |1          
ij> close jdk4;
ij> -- should be able to change the isolation now
set isolation READ UNCOMMITTED;
0 rows inserted/updated/deleted
ij> set isolation RS;
0 rows inserted/updated/deleted
ij> -- clean up.
close jdk1;
ij> -- sixth test - try positioned update with hold cursor
get with hold cursor jdk4 as 'SELECT * FROM t1 FOR UPDATE';
ij> -- following should give error because cursor is not positioned on any row
update t1 set c12=12 where current of jdk4;
ERROR 24000: Invalid cursor state - no current row.
ij> select * from t1;
C11        |C12        
-----------------------
1          |1          
2          |2          
ij> next jdk4;
C11        |C12        
-----------------------
1          |1          
ij> update t1 set c12=12 where current of jdk4;
1 row inserted/updated/deleted
ij> select * from t1;
C11        |C12        
-----------------------
1          |12         
2          |2          
ij> commit;
ij> -- after commit, the next transaction should do a fetch again before doing
-- any positioned update
update t1 set c12=123 where current of jdk4;
ERROR 24000: Invalid cursor state - no current row.
ij> select * from t1;
C11        |C12        
-----------------------
1          |12         
2          |2          
ij> next jdk4;
C11        |C12        
-----------------------
2          |2          
ij> update t1 set c12=23 where current of jdk4;
1 row inserted/updated/deleted
ij> select * from t1;
C11        |C12        
-----------------------
1          |12         
2          |23         
ij> close jdk4;
ij> update t1 set c12=234 where current of jdk4;
ERROR 42X30: Cursor 'JDK4' not found. Verify that autocommit is off.
ij> select * from t1;
C11        |C12        
-----------------------
1          |12         
2          |23         
ij> -- seventh test - try positioned delete with hold cursor
get with hold cursor jdk4 as 'SELECT * FROM t1 FOR UPDATE';
ij> -- following should give error because cursor is not positioned on any row
delete from t1 where current of jdk4;
ERROR 24000: Invalid cursor state - no current row.
ij> select * from t1;
C11        |C12        
-----------------------
1          |12         
2          |23         
ij> next jdk4;
C11        |C12        
-----------------------
1          |12         
ij> delete from  t1 where current of jdk4;
1 row inserted/updated/deleted
ij> select * from t1;
C11        |C12        
-----------------------
2          |23         
ij> commit;
ij> -- after commit, the next transaction should do a fetch again before doing
-- any positioned delete
delete from t1 where current of jdk4;
ERROR 24000: Invalid cursor state - no current row.
ij> select * from t1;
C11        |C12        
-----------------------
2          |23         
ij> next jdk4;
C11        |C12        
-----------------------
2          |23         
ij> delete from t1 where current of jdk4;
1 row inserted/updated/deleted
ij> select * from t1;
C11        |C12        
-----------------------
ij> close jdk4;
ij> delete from t1 where current of jdk4;
ERROR 42X30: Cursor 'JDK4' not found. Verify that autocommit is off.
ij> select * from t1;
C11        |C12        
-----------------------
ij> -- populate the table for next test
insert into t1 values(1,1);
1 row inserted/updated/deleted
ij> insert into t1 values(2,2);
1 row inserted/updated/deleted
ij> -- eighth test - scrollable cursors
get scroll insensitive with hold cursor jdk4 as 'SELECT * FROM t1';
ij> commit;
ij> previous jdk4;
No current row
ij> after last jdk4;
No current row
ij> before first jdk4;
No current row
ij> first jdk4;
C11        |C12        
-----------------------
1          |1          
ij> last jdk4;
C11        |C12        
-----------------------
2          |2          
ij> next jdk4;
No current row
ij> previous jdk4;
C11        |C12        
-----------------------
2          |2          
ij> next jdk4;
No current row
ij> close jdk4;
ij> first jdk4;
IJ ERROR: Unable to establish cursor JDK4@CONNECTION0
ij> -- ninth test - close the updateable holdable cursor after commit
-- we get npe
get with hold cursor jdk4 as 'SELECT * FROM T1 FOR UPDATE';
ij> next jdk4;
C11        |C12        
-----------------------
1          |1          
ij> commit;
ij> close jdk4;
ij> -- tenth test - bug 4515 - have a more useful message
-- update where current of fails in autocommit=true, held open cursor

autocommit on;
ij> get with hold cursor scrollCursor as 'select * from t1 for update of c12';
ij> next scrollCursor;
C11        |C12        
-----------------------
1          |1          
ij> -- commented out for DERBY-4778
-- update t1 set c12=c12+1 where current of scrollCursor;

-- clean up.
close scrollCursor;
ij> drop table t1;
0 rows inserted/updated/deleted
ij>