Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJDBC30.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJDBC30.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJDBC30.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJDBC30.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,1590 @@ +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'); +Statement executed. +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 +----- +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 +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 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> 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 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> 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> 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> -- insert into foo (select a + 5, data + 50 from foo); +----- 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 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; +A |DATA +----- +1 |50 +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; +A |DATA +----- +1 |50 +ij> commit; +ij> next test1; +A |DATA +----- +1 |40 +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; +A |DATA +----- +1 |50 +ij> commit; +ij> next test1; +A |DATA +----- +1 |40 +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; +A |DATA +----- +1 |50 +ij> commit; +ij> next test1; +A |DATA +----- +1 |40 +ij> commit; +ij> next test1; +A |DATA +----- +1 |30 +ij> next test1; +A |DATA +----- +1 |20 +ij> next test1; +A |DATA +----- +1 |10 +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; +A |DATA +----- +1 |50 +ij> commit; +ij> next test1; +A |DATA +----- +1 |40 +ij> commit; +ij> next test1; +A |DATA +----- +1 |30 +ij> next test1; +A |DATA +----- +1 |20 +ij> next test1; +A |DATA +----- +1 |10 +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; +A |DATA +----- +1 |50 +ij> commit; +ij> next test1; +A |DATA +----- +1 |40 +ij> commit; +ij> next test1; +A |DATA +----- +1 |30 +ij> next test1; +A |DATA +----- +1 |20 +ij> next test1; +A |DATA +----- +1 |10 +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 7: test of hold cursor code in DistinctScalarAggregateResultSet.java +----- Directed test of hold cursor as applies to sort scans opened by +----- DistinctScalarAggregateResultSet.java. +----- +drop table t1; +ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist. : +ij> create table t1 (c1 int, c2 int); +0 rows inserted/updated/deleted +ij> insert into t1 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10); +6 rows inserted/updated/deleted +ij> select * from t1; +C1 |C2 +----- +NULL |NULL +1 |1 +NULL |NULL +2 |1 +3 |1 +10 |10 +ij> select sum(distinct c1) from t1; +1 +----- +16 +WARNING 01003: Null values were eliminated from the argument of a column function. : +ij> get with hold cursor test1 as 'select sum(distinct c1) from t1'; +ij> commit; +ij> next test1; +1 +----- +16 +WARNING 01003: Null values were eliminated from the argument of a column function. : +ij> close test1; +ij> get with hold cursor test1 as 'select sum(distinct c1) from t1'; +ij> next test1; +1 +----- +16 +WARNING 01003: Null values were eliminated from the argument of a column function. : +ij> commit; +ij> next test1; +No current row +ij> commit; +ij> close test1; +ij> commit; +ij> ------------------------------------------------------------------------------ +----- TEST 8: test of hold cursor code in GroupedAggregateResultSet.java +----- Directed test of hold cursor as applies to sort scans opened by +----- GroupedAggregateResultSet.java. +----- +drop table t1; +0 rows inserted/updated/deleted +ij> create table t1 (c1 int, c2 int); +0 rows inserted/updated/deleted +ij> insert into t1 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10); +6 rows inserted/updated/deleted +ij> select * from t1; +C1 |C2 +----- +NULL |NULL +1 |1 +NULL |NULL +2 |1 +3 |1 +10 |10 +ij> select sum(distinct c1) from t1 group by c2; +1 +----- +6 +10 +NULL +WARNING 01003: Null values were eliminated from the argument of a column function. : +ij> commit; +ij> get with hold cursor test1 as 'select sum(distinct c1) from t1 group by c2'; +ij> commit; +ij> next test1; +1 +----- +6 +ij> next test1; +1 +----- +10 +ij> commit; +ij> next test1; +1 +----- +NULL +WARNING 01003: Null values were eliminated from the argument of a column function. : +ij> close test1; +ij> get with hold cursor test1 as 'select sum(distinct c1) from t1 group by c2'; +ij> next test1; +1 +----- +6 +ij> commit; +ij> next test1; +1 +----- +10 +ij> commit; +ij> next test1; +1 +----- +NULL +WARNING 01003: Null values were eliminated from the argument of a column function. : +ij> close test1; +ij> ------------------------------------------------------------------------------ +----- TEST 9: test scan positioned on a row which has been purged. +----- +drop table t1; +0 rows inserted/updated/deleted +ij> create table t1 (c1 int, c2 int); +0 rows inserted/updated/deleted +ij> create index tx on t1 (c1); +0 rows inserted/updated/deleted +ij> insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6); +6 rows inserted/updated/deleted +ij> get with hold cursor test1 as + 'select c1 from t1'; +ij> next test1; +C1 +----- +1 +ij> commit; +ij> -- at this point the btree scan is positioned by "key" on (1,1). Make sure +----- deleting this key doesn't cause any problems. +delete from t1 where c1 = 1 or c1 = 2; +2 rows inserted/updated/deleted +ij> next test1; +C1 +----- +2 +ij> -- at this point the btree scan is positioned on (3, 3), let's see what happens +----- if we delete (3,3) and look at current scan. +delete from t1 where c1 = 3; +1 row inserted/updated/deleted +ij> -- position on (4,4) +next test1; +C1 +----- +3 +ij> commit; +ij> -- delete all the rows and hopefully get all rows to be purged by the time +----- the scan does the next. +delete from t1; +3 rows inserted/updated/deleted +ij> commit; +ij> next test1; +C1 +----- +4 +ij> close test1; +ij> ------------------------------------------------------------------------------ +----- TEST 10: test scan positioned on a page which has been purged (should really +----- not be any different than a row being purged). +----- +drop table t1; +0 rows inserted/updated/deleted +ij> create table t1 (c1 varchar(1000), c2 int); +0 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); +Statement executed. +ij> create index tx on t1 (c1); +0 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL); +Statement executed. +ij> insert into t1 values (PADSTRING('1',1000), 1), (PADSTRING('2',1000), 2), (PADSTRING('3',1000), 3), (PADSTRING('4',1000), 4), (PADSTRING('5',1000), 5), (PADSTRING('6',1000), 6), (PADSTRING('7',1000), 7); +7 rows inserted/updated/deleted +ij> get with hold cursor test1 as + 'select c1 from t1'; +ij> next test1; +C1 +----- +1 & +ij> commit; +ij> -- at this point the btree scan is positioned by "key" on (1,1). Make sure +----- deleting this key doesn't cause any problems. +delete from t1 where c1 = PADSTRING('1',1000) or c1 = PADSTRING('2',1000); +2 rows inserted/updated/deleted +ij> next test1; +C1 +----- +2 & +ij> -- at this point the btree scan is positioned on (3, 3), let's see what happens +----- if we delete (3,3) and look at current scan. +delete from t1 where c1 = PADSTRING('3',1000); +1 row inserted/updated/deleted +ij> -- position on (4,4) +next test1; +C1 +----- +3 & +ij> commit; +ij> -- delete all the rows and hopefully get all rows to be purged by the time +----- the scan does the next. +delete from t1; +4 rows inserted/updated/deleted +ij> commit; +ij> next test1; +C1 +----- +4 & +ij> close test1; +ij> ------------------------------------------------------------------------------ +----- TEST 11: beetle 4902: test query plans which use reopenScan() on a btree to +----- do the inner table processing of a join. Prior to the fix a null +----- pointer exception would be thrown after the commit, as the code +----- did not handle keeping the resultset used for the inner table +----- open across commits in this case. +----- +drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist. : +ij> create table t1 (i1 int, i2 int); +0 rows inserted/updated/deleted +ij> create table t2 (i1 int, i2 int); +0 rows inserted/updated/deleted +ij> create index t1_idx on t1 (i1); +0 rows inserted/updated/deleted +ij> create index t2_idx on t2 (i1); +0 rows inserted/updated/deleted +ij> insert into t1 values (1, 10), (2, 20), (3, 30), (4, 40), (5, 50); +5 rows inserted/updated/deleted +ij> insert into t2 values (1, 10), (2, 20), (4, 40), (5, 50); +4 rows inserted/updated/deleted +ij> commit; +ij> -- force nestedLoop to make sure reopenScan() is used on inner table. +get with hold cursor test11 as +'select * from + t1, t2 + where t1.i1 = t2.i1'; +ij> commit; +ij> next test11; +I1 |I2 |I1 |I2 +----- +1 |10 |1 |10 +ij> commit; +ij> next test11; +I1 |I2 |I1 |I2 +----- +2 |20 |2 |20 +ij> commit; +ij> next test11; +I1 |I2 |I1 |I2 +----- +4 |40 |4 |40 +ij> next test11; +I1 |I2 |I1 |I2 +----- +5 |50 |5 |50 +ij> commit; +ij> commit; +ij> next test11; +No current row +ij> commit; +ij> close test11; +ij> ------------------------------------------------------------------------------ +----- TEST 12: beetle 4902: test query plans which use reopenScan() on a base table +----- to do the inner table processing of a join. Prior to the fix a null +----- pointer exception would be thrown after the commit, as the code +----- did not handle keeping the resultset used for the inner table +----- open across commits in this case. +----- +drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> create table t1 (i1 int, i2 int); +0 rows inserted/updated/deleted +ij> create table t2 (i1 int, i2 int); +0 rows inserted/updated/deleted +ij> insert into t1 values (1, 10), (2, 20), (3, 30), (4, 40), (5, 50); +5 rows inserted/updated/deleted +ij> insert into t2 values (1, 10), (2, 20), (4, 40), (5, 50); +4 rows inserted/updated/deleted +ij> commit; +ij> -- force nestedLoop to make sure reopenScan() is used on inner table. +get with hold cursor test12 as +'select * from + t1, t2 + where t1.i1 = t2.i1'; +ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); +Statement executed. +ij> commit; +ij> next test12; +I1 |I2 |I1 |I2 +----- +1 |10 |1 |10 +ij> commit; +ij> next test12; +I1 |I2 |I1 |I2 +----- +2 |20 |2 |20 +ij> commit; +ij> next test12; +I1 |I2 |I1 |I2 +----- +4 |40 |4 |40 +ij> next test12; +I1 |I2 |I1 |I2 +----- +5 |50 |5 |50 +ij> commit; +ij> commit; +ij> next test12; +No current row +ij> commit; +ij> close test12; +ij> drop table foo; +0 rows inserted/updated/deleted +ij> drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> drop function padstring; +0 rows inserted/updated/deleted +ij> drop procedure wait_for_post_commit; +0 rows inserted/updated/deleted +ij> commit; +ij> exit; Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJava.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJava.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJava.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJava.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,51 @@ +Creating table... +done creating table and inserting data. +testHoldability with HOLD_CURSORS_OVER_COMMIT +testStatements() +checkResultSet before commit +11, testtable1-one +checkResultSet after commit +12, testtable1-two +checkResultSet before commit +21, testtable2-one +checkResultSet after commit +EXPECTED EXCEPTION:Invalid operation: result set closed +testStatementsInProcedure() +checkResultSet before commit +11, testtable1-one +checkResultSet before commit +21, testtable2-one +checkResultSet after commit +11, testtable1-one +checkResultSet after commit +EXPECTED:ResultSet is null +testHoldability with CLOSE_CURSORS_AT_COMMIT +testStatements() +checkResultSet before commit +11, testtable1-one +checkResultSet after commit +12, testtable1-two +checkResultSet before commit +21, testtable2-one +checkResultSet after commit +EXPECTED EXCEPTION:Invalid operation: result set closed +testStatementsInProcedure() +checkResultSet before commit +11, testtable1-one +checkResultSet before commit +21, testtable2-one +checkResultSet after commit +11, testtable1-one +checkResultSet after commit +EXPECTED:ResultSet is null +Start multi table query with holdability true test +value of t2.c22 is 1 +value of t2.c22 is 2 +Multi table query with holdability true test over +Start isolation level change test +Switch isolation while there are open cursors +Should see exceptions +ERROR X0X03: Invalid transaction state - held cursor requires same isolation level : +PASS: Can't change isolation if they are open cursor +Isolation level change test over +testCloseCursor() Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJavaReflection.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJavaReflection.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJavaReflection.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/holdCursorJavaReflection.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,24 @@ +Creating table... +done creating table and inserting data. +Start holdable cursor after commit test +do next() before commit +look at first column's value: 1 +After commit, look at first column's value: 1 +do next() after commit. Should be at the end of resultset +one more next() here will give no more rows +Holdable cursor after commit test over +Start hold cursor for prepared statements test +do next() before commit +look at first column's value: 1 +After commit, look at first column's value: 1 +do next() after commit. Should be at the end of resultset +one more next() here will give no more rows +Holdable cursor after commit for prepared statements test over +Expected Exception:Table 'T1NOTTHERE' does not exist. : +Start hold cursor for callable statements test +do next() before commit +look at first column's value: 1 +After commit, look at first column's value: 1 +do next() after commit. Should be at the end of resultset +one more next() here will give no more rows +Holdable cursor after commit for callable statements test over Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,904 @@ +ij> --table used for export +create table ex_emp(id int , name char(7) , skills varchar(200), salary decimal(10,2)) ; +0 rows inserted/updated/deleted +ij> --table used for import +create table imp_emp(id int , name char(7), skills varchar(200), salary decimal(10,2)) ; +0 rows inserted/updated/deleted +ij> --After an export from ex_emp and import to imp_emp both tables should have +-----same data. +-----double delimter cases with default character delimter " +-----field seperator character inside a double delimited string as first line +insert into ex_emp values(99, 'smith' , 'tennis"p,l,ayer"', 190.55) ; +1 row inserted/updated/deleted +ij> -- Perform Export: +call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , + null, null, null) ; +Statement executed. +ij> -- Perform Import +call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , + null, null, null, 0) ; +Statement executed. +ij> insert into ex_emp values(100, 'smith' , 'tennis"player"', 190.55) ; +1 row inserted/updated/deleted +ij> insert into ex_emp values(101, 'smith' , 'tennis"player', 190.55) ; +1 row inserted/updated/deleted +ij> insert into ex_emp values(102, 'smith' , '"tennis"player', 190.55) ; +1 row inserted/updated/deleted +ij> insert into ex_emp values(103, 'smith' , '"tennis"player"', 190.55) ; +1 row inserted/updated/deleted +ij> insert into ex_emp values(104, 'smith' , '"tennis"""""""""""""""""""""""""""""""""""""player"', null) ; +1 row inserted/updated/deleted +ij> --empty string +insert into ex_emp values(105, 'smith' , '""', 190.55) ; +1 row inserted/updated/deleted +ij> --just delimeter inside +insert into ex_emp values(106, 'smith' , '"""""""""""""""""""', 190.55); +1 row inserted/updated/deleted +ij> --null value +insert into ex_emp values(107, 'smith"' , null, 190.55) ; +1 row inserted/updated/deleted +ij> --all values are nulls +insert into ex_emp values(108, null , null, null) ; +1 row inserted/updated/deleted +ij> -- Perform Export: +call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , + null, null, null) ; +Statement executed. +ij> -- Perform Import +call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , + null, null, null, 0) ; +Statement executed. +ij> select * from ex_emp; +ID |NAME |SKILLS |SALARY +----- +99 |smith |tennis"p,l,ayer" |190.55 +100 |smith |tennis"player" |190.55 +101 |smith |tennis"player |190.55 +102 |smith |"tennis"player |190.55 +103 |smith |"tennis"player" |190.55 +104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL +105 |smith |"" |190.55 +106 |smith |""""""""""""""""""" |190.55 +107 |smith" |NULL |190.55 +108 |NULL |NULL |NULL +ij> select * from imp_emp; +ID |NAME |SKILLS |SALARY +----- +99 |smith |tennis"p,l,ayer" |190.55 +99 |smith |tennis"p,l,ayer" |190.55 +100 |smith |tennis"player" |190.55 +101 |smith |tennis"player |190.55 +102 |smith |"tennis"player |190.55 +103 |smith |"tennis"player" |190.55 +104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL +105 |smith |"" |190.55 +106 |smith |""""""""""""""""""" |190.55 +107 |smith" |NULL |190.55 +108 |NULL |NULL |NULL +ij> --checking query +select count(*) from imp_emp, ex_emp + where ex_emp.id = imp_emp.id and + (ex_emp.skills=imp_emp.skills or + (ex_emp.skills is NULL and imp_emp.skills is NULL)); +1 +----- +11 +ij> delete from imp_emp where id < 105; +7 rows inserted/updated/deleted +ij> --export from ex_emp using the a query only rows that got deleted in imp_emp +call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from ex_emp where id < 105', + 'extinout/emp.dat' , null, null, null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , + null, null, null, 0) ; +Statement executed. +ij> --checking query +select count(*) from imp_emp, ex_emp + where ex_emp.id = imp_emp.id and + (ex_emp.skills=imp_emp.skills or + (ex_emp.skills is NULL and imp_emp.skills is NULL)); +1 +----- +10 +ij> --export the columns in different column order than in the table. +call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select name , salary , skills, id from ex_emp where id < 105', + 'extinout/emp.dat' , null, null, null) ; +Statement executed. +ij> -- import them in to a with order different than in the table; +call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'NAME, SALARY, SKILLS, ID', null, + 'extinout/emp.dat', null, null, null, 1) ; +Statement executed. +ij> --check query +select count(*) from imp_emp, ex_emp + where ex_emp.id = imp_emp.id and + (ex_emp.skills=imp_emp.skills or + (ex_emp.skills is NULL and imp_emp.skills is NULL)); +1 +----- +6 +ij> -- do import replace into the table with table order but using column indexes +call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,null, '4, 1, 3, 2', + 'extinout/emp.dat', null, null, null, 1) ; +Statement executed. +ij> --check query +select count(*) from imp_emp, ex_emp + where ex_emp.id = imp_emp.id and + (ex_emp.skills=imp_emp.skills or + (ex_emp.skills is NULL and imp_emp.skills is NULL)); +1 +----- +6 +ij> --replace using insert column names and column indexes +call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'SALARY, ID, SKILLS, NAME', '2, 4, 3, 1', + 'extinout/emp.dat', null, null, null, 1) ; +Statement executed. +ij> --check query +select count(*) from imp_emp, ex_emp + where ex_emp.id = imp_emp.id and + (ex_emp.skills=imp_emp.skills or + (ex_emp.skills is NULL and imp_emp.skills is NULL)); +1 +----- +6 +ij> ---testing with different delimiters +----- single quote(') as character delimiter +call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , + null, '''', null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , + null, '''', null, 1) ; +Statement executed. +ij> select * from imp_emp ; +ID |NAME |SKILLS |SALARY +----- +99 |smith |tennis"p,l,ayer" |190.55 +100 |smith |tennis"player" |190.55 +101 |smith |tennis"player |190.55 +102 |smith |"tennis"player |190.55 +103 |smith |"tennis"player" |190.55 +104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL +105 |smith |"" |190.55 +106 |smith |""""""""""""""""""" |190.55 +107 |smith" |NULL |190.55 +108 |NULL |NULL |NULL +ij> -- single quote(') as column delimiter +call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , + '''',null, null) ; +Statement executed. +ij> delete from imp_emp ; +10 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , + '''', null, null, 0) ; +Statement executed. +ij> select * from imp_emp; +ID |NAME |SKILLS |SALARY +----- +99 |smith |tennis"p,l,ayer" |190.55 +100 |smith |tennis"player" |190.55 +101 |smith |tennis"player |190.55 +102 |smith |"tennis"player |190.55 +103 |smith |"tennis"player" |190.55 +104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL +105 |smith |"" |190.55 +106 |smith |""""""""""""""""""" |190.55 +107 |smith" |NULL |190.55 +108 |NULL |NULL |NULL +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , + '*', '%', null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , + '*', '%', null, 1) ; +Statement executed. +ij> select * from imp_emp ; +ID |NAME |SKILLS |SALARY +----- +99 |smith |tennis"p,l,ayer" |190.55 +100 |smith |tennis"player" |190.55 +101 |smith |tennis"player |190.55 +102 |smith |"tennis"player |190.55 +103 |smith |"tennis"player" |190.55 +104 |smith |"tennis"""""""""""""""""""""""""""""""""""""player" |NULL +105 |smith |"" |190.55 +106 |smith |""""""""""""""""""" |190.55 +107 |smith" |NULL |190.55 +108 |NULL |NULL |NULL +ij> --cases for identity columns +-----create table emp1(id int generated always as identity (start with 100), name char(7), +----- skills varchar(200), salary decimal(10,2),skills varchar(200)); +-----check import export with real and double that can not be explictitly +-----casted from VARCHAR type . +create table noncast(c1 double , c2 real ) ; +0 rows inserted/updated/deleted +ij> insert into noncast values(1.5 , 6.7 ) ; +1 row inserted/updated/deleted +ij> insert into noncast values(2.5 , 8.999) ; +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'NONCAST' , 'extinout/noncast.dat' , null , null , null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NONCAST' , 'extinout/noncast.dat' , null , null , null , 0) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NONCAST', 'C2 , C1' , '2, 1' , + 'extinout/noncast.dat' , null , null , null , 0) ; +Statement executed. +ij> select * from noncast ; +C1 |C2 +----- +1.5 |6.7 +2.5 |8.999 +1.5 |6.7 +2.5 |8.999 +1.5 |6.7 +2.5 |8.999 +ij> --check import/export of time types +CREATE TABLE TTYPES(DATETYPE DATE, TIMETYPE TIME, TSTAMPTYPE TIMESTAMP ); +0 rows inserted/updated/deleted +ij> insert into ttypes values('1999-09-09' , '12:15:19' , 'xxxxxxFILTERED-TIMESTAMPxxxxx); +1 row inserted/updated/deleted +ij> insert into ttypes values('2999-12-01' , '13:16:10' , 'xxxxxxFILTERED-TIMESTAMPxxxxx); +1 row inserted/updated/deleted +ij> insert into ttypes values('3000-11-02' , '14:17:21' , 'xxxxxxFILTERED-TIMESTAMPxxxxx); +1 row inserted/updated/deleted +ij> insert into ttypes values('2004-04-03' , '15:18:31' , 'xxxxxxFILTERED-TIMESTAMPxxxxx); +1 row inserted/updated/deleted +ij> insert into ttypes values(null , null , null); +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' , + null, null, null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' , + null, null, null, 0) ; +Statement executed. +ij> select * from ttypes; +DATETYPE |TIMETYPE |TSTAMPTYPE +----- +1999-09-09 |12:15:19 |xxxxxxFILTERED-TIMESTAMPxxxxx +2999-12-01 |13:16:10 |xxxxxxFILTERED-TIMESTAMPxxxxx +3000-11-02 |14:17:21 |xxxxxxFILTERED-TIMESTAMPxxxxx +2004-04-03 |15:18:31 |xxxxxxFILTERED-TIMESTAMPxxxxx +NULL |NULL |NULL +1999-09-09 |12:15:19 |xxxxxxFILTERED-TIMESTAMPxxxxx +2999-12-01 |13:16:10 |xxxxxxFILTERED-TIMESTAMPxxxxx +3000-11-02 |14:17:21 |xxxxxxFILTERED-TIMESTAMPxxxxx +2004-04-03 |15:18:31 |xxxxxxFILTERED-TIMESTAMPxxxxx +NULL |NULL |NULL +ij> ---Import should commit on success and rollback on any failures +autocommit off ; +ij> create table t1(a int ) ; +0 rows inserted/updated/deleted +ij> insert into t1 values(1) ; +1 row inserted/updated/deleted +ij> insert into t1 values(2) ; +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' , + null, null, null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' , + null, null, null, 0) ; +Statement executed. +ij> --above import should have committed , following rollback should be a noop. +rollback; +ij> select * from t1; +A +----- +1 +2 +1 +2 +ij> insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> --file not found error should rollback +call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' , + null, null, null, 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression.::SQLSTATE: 38000The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.::SQLSTATE: XJ001Java exception: ': java.lang.reflect.InvocationTargetException'.::SQLSTATE: XIE04Data file not found: extinout/nofile.del : +ij> commit; +ij> select * from t1 ; +A +----- +1 +2 +1 +2 +ij> insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> --table not found error should issue a implicit rollback +call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' , + null, null, null, 0) ; +ERROR XIE0M: Table 'NOTABLE' does not exist. : +ij> commit ; +ij> select * from t1 ; +A +----- +1 +2 +1 +2 +ij> delete from t1; +4 rows inserted/updated/deleted +ij> ---check commit/rollback with replace options using +insert into t1 values(1) ; +1 row inserted/updated/deleted +ij> insert into t1 values(2) ; +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' , + null, null, null) ; +Statement executed. +ij> --above export should have a commit.rollback below should be a noop +rollback; +ij> select * from t1; +A +----- +1 +2 +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' , + null, null, null, 1) ; +Statement executed. +ij> --above import should have committed , following rollback should be a noop. +rollback; +ij> select * from t1; +A +----- +1 +2 +ij> insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> --file not found error should rollback +call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' , + null, null, null, 1) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression.::SQLSTATE: 38000The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.::SQLSTATE: XJ001Java exception: ': java.lang.reflect.InvocationTargetException'.::SQLSTATE: XIE04Data file not found: extinout/nofile.del : +ij> commit; +ij> select * from t1 ; +A +----- +1 +2 +ij> insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> --table not found error should issue a implicit rollback +call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' , + null, null, null, 1) ; +ERROR XIE0M: Table 'NOTABLE' does not exist. : +ij> commit ; +ij> ---check IMPORT_DATA calls commit/rollback +select * from t1 ; +A +----- +1 +2 +ij> delete from t1; +2 rows inserted/updated/deleted +ij> ---check commit/rollback with replace options using +insert into t1 values(1) ; +1 row inserted/updated/deleted +ij> insert into t1 values(2) ; +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' , + null, null, null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' , + null, null, null, 0) ; +Statement executed. +ij> --above import should have committed , following rollback should be a noop. +rollback; +ij> select * from t1; +A +----- +1 +2 +1 +2 +ij> insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> --file not found error should rollback +call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1' , 'extinout/nofile.del' , + null, null, null, 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression.::SQLSTATE: 38000The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.::SQLSTATE: XJ001Java exception: ': java.lang.reflect.InvocationTargetException'.::SQLSTATE: XIE04Data file not found: extinout/nofile.del : +ij> commit; +ij> select * from t1 ; +A +----- +1 +2 +1 +2 +ij> insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> --table not found error should issue a implicit rollback +call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NOTABLE' , 'A' , '1', 'extinout/t1.del' , + null, null, null, 1) ; +ERROR XIE0M: Table 'NOTABLE' does not exist. : +ij> commit ; +ij> select * from t1 ; +A +----- +1 +2 +1 +2 +ij> autocommit on ; +ij> --make sure commit import code is ok in autcommit mode. +insert into t1 values(3) ; +1 row inserted/updated/deleted +ij> insert into t1 values(4) ; +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' , + null, null, null, 0) ; +Statement executed. +ij> select * from t1 ; +A +----- +1 +2 +1 +2 +3 +4 +1 +2 +ij> insert into t1 values(5) ; +1 row inserted/updated/deleted +ij> insert into t1 values(6) ; +1 row inserted/updated/deleted +ij> --following import will back , but should not have any impact on inserts +call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1' , 'extinout/nofile.del' , + null, null, null, 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression.::SQLSTATE: 38000The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.::SQLSTATE: XJ001Java exception: ': java.lang.reflect.InvocationTargetException'.::SQLSTATE: XIE04Data file not found: extinout/nofile.del : +ij> select * from t1 ; +A +----- +1 +2 +1 +2 +3 +4 +1 +2 +5 +6 +ij> --END IMPORT COMMIT/ROLLBACK TESTSING +-----all types supported by Derby import/export +create table alltypes(chartype char(20) , + biginttype bigint , + datetype date , + decimaltype decimal(10,5) , + doubletype double , + inttype integer , + lvartype long varchar , + realtype real , + sminttype smallint , + timetype time , + tstamptype timestamp , + vartype varchar(50)); +0 rows inserted/updated/deleted +ij> insert into alltypes values('chartype string' , + 9223372036854775807, + '1993-10-29' , + 12345.54321, + 10E307, + 2147483647, + 'long varchar testing', + 10E3, + 32767, + '09.39.43', + 'xxxxxxFILTERED-TIMESTAMPxxxxx, + 'varchar testing'); +1 row inserted/updated/deleted +ij> insert into alltypes values('chartype string' , + -9223372036854775808, + '1993-10-29' , + 0.0, + -10E307, + -2147483647, + 'long varchar testing', + -10E3, + 32767, + '09.39.43', + 'xxxxxxFILTERED-TIMESTAMPxxxxx, + 'varchar testing'); +1 row inserted/updated/deleted +ij> insert into alltypes values('"chartype" string' , + 9223372036854775807, + '1993-10-29' , + -12345.54321, + 10E307, + 2147483647, + 'long "varchar" testing', + 10E3, + 32767, + '09.39.43', + 'xxxxxxFILTERED-TIMESTAMPxxxxx, + '"varchar" testing'); +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , + null, null, null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , + null, null, null, 0) ; +Statement executed. +ij> select * from alltypes ; +CHARTYPE |BIGINTTYPE |DATETYPE |DECIMALTYPE |DOUBLETYPE |INTTYPE |LVARTYPE |REALTYPE |SMINT& |TIMETYPE |TSTAMPTYPE |VARTYPE +----- +chartype string |9223372036854775807 |1993-10-29 |12345.54321 |1.0E308 |2147483647 |long varchar testing |10000.0 |32767 |09:39:43 |xxxxxxFILTERED-TIMESTAMPxxxxx |varchar testing +chartype string |-9223372036854775808 |1993-10-29 |0.00000 |-1.0E308 |-2147483647 |long varchar testing |-10000.0 |32767 |09:39:43 |xxxxxxFILTERED-TIMESTAMPxxxxx |varchar testing +"chartype" string |9223372036854775807 |1993-10-29 |-12345.54321 |1.0E308 |2147483647 |long "varchar" testing |10000.0 |32767 |09:39:43 |xxxxxxFILTERED-TIMESTAMPxxxxx |"varchar" testing +chartype string |9223372036854775807 |1993-10-29 |12345.54321 |1.0E308 |2147483647 |long varchar testing |10000.0 |32767 |09:39:43 |xxxxxxFILTERED-TIMESTAMPxxxxx |varchar testing +chartype string |-9223372036854775808 |1993-10-29 |0.00000 |-1.0E308 |-2147483647 |long varchar testing |-10000.0 |32767 |09:39:43 |xxxxxxFILTERED-TIMESTAMPxxxxx |varchar testing +"chartype" string |9223372036854775807 |1993-10-29 |-12345.54321 |1.0E308 |2147483647 |long "varchar" testing |10000.0 |32767 |09:39:43 |xxxxxxFILTERED-TIMESTAMPxxxxx |"varchar" testing +ij> delete from alltypes; +6 rows inserted/updated/deleted +ij> --import should work with trigger enabled on append and should not work on replace +create table test1(a char(20)) ; +0 rows inserted/updated/deleted +ij> create trigger trig_import after INSERT on alltypes +referencing new as newrow +for each row mode db2sql +insert into test1 values(newrow.chartype); +0 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , + null, null, null, 0) ; +Statement executed. +ij> select count(*) from alltypes ; +1 +----- +3 +ij> select * from test1; +A +----- +chartype string +chartype string +"chartype" string +ij> delete from alltypes; +3 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , + null, null, null, 1) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).' was thrown while evaluating an expression.::SQLSTATE: 42Z08Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT). : +ij> select count(*) from alltypes; +1 +----- +0 +ij> drop trigger trig_import; +0 rows inserted/updated/deleted +ij> drop table test1; +0 rows inserted/updated/deleted +ij> --test importing to identity columns +create table table1(c1 char(30), + c2 int generated always as identity, + c3 real, + c4 char(1)); +0 rows inserted/updated/deleted +ij> create table table2(c1 char(30), + c2 int, + c3 real, + c4 char(1)); +0 rows inserted/updated/deleted +ij> insert into table2 values('Robert',100, 45.2, 'J'); +1 row inserted/updated/deleted +ij> insert into table2 values('Mike',101, 76.9, 'K'); +1 row inserted/updated/deleted +ij> insert into table2 values('Leo',102, 23.4, 'I'); +1 row inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select c1,c3,c4 from table2' , 'extinout/import.del' , + null, null, null) ; +Statement executed. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'TABLE1', 'C1,C3,C4' , null, 'extinout/import.del',null, null,null,0); +Statement executed. +ij> select * from table1; +C1 |C2 |C3 |C4 +----- +Robert |1 |45.2 |J +Mike |2 |76.9 |K +Leo |3 |23.4 |I +ij> delete from table1; +3 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del', null, null, null) ; +Statement executed. +ij> --following import should fail becuase of inserting into identity column. +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1); +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression.::SQLSTATE: 42Z23Attempt to modify an identity column 'C2'. : +ij> --following import should be succesful +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'TABLE1', 'C1,C3,C4' , '1,3,4', 'extinout/import.del',null, null, null,1); +Statement executed. +ij> select * from table1; +C1 |C2 |C3 |C4 +----- +Robert |1 |45.2 |J +Mike |2 |76.9 |K +Leo |3 |23.4 |I +ij> update table2 set c2=null; +3 rows inserted/updated/deleted +ij> --check null values import to identity columns should also fail +call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del' , + null, null, null) ; +Statement executed. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1); +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression.::SQLSTATE: 42Z23Attempt to modify an identity column 'C2'. : +ij> select * from table1; +C1 |C2 |C3 |C4 +----- +Robert |1 |45.2 |J +Mike |2 |76.9 |K +Leo |3 |23.4 |I +ij> --check that replace fails when there dependents and replaced data +-----does not violate foreign key constraints. +create table parent(a int not null primary key); +0 rows inserted/updated/deleted +ij> insert into parent values (1) , (2) , (3) , (4) ; +4 rows inserted/updated/deleted +ij> create table child(b int references parent(a)); +0 rows inserted/updated/deleted +ij> insert into child values (1) , (2) , (3) , (4) ; +4 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from parent where a < 3' , 'extinout/parent.del' , + null, null, null) ; +Statement executed. +ij> --replace should fail because of dependent table +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'PARENT', 'extinout/parent.del',null, null, null,1); +ERROR 38000: The exception 'java.sql.SQLIntegrityConstraintViolationException: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.' was thrown while evaluating an expression.::SQLSTATE: 23503INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back. : +ij> select * from parent; +A +----- +1 +2 +3 +4 +ij> ---test with a file which has a differen records seperators (\n, \r , \r\n) +create table nt1( a int , b char(30)); +0 rows inserted/updated/deleted +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'NT1', 'extin/mixednl.del',null, null, null,0); +Statement executed. +ij> select * from nt1; +A |B +----- +0 |XXXXXX0 +1 |XXXXXX1 +2 |XXXXXX2 +3 |XXXXXX3 +4 |XXXXXX4 +5 |YYYYY5 +6 |YYYYY6 +7 |YYYYY7 +8 |YYYYY8 +9 |YYYYY9 +10 |ZZZZZZ10 +11 |ZZZZZZ11 +12 |ZZZZZZ12 +13 |ZZZZZZ13 +14 |ZZZZZZ14 +ij> drop table nt1 ; +0 rows inserted/updated/deleted +ij> --test case for bug 5977;(with lot of text data) +create table position_info + ( + position_code varchar(10) not null , + literal_no int not null , + job_category_code varchar(10), + summary_description long varchar, + detail_description long varchar, + web_flag varchar(1) + ); +0 rows inserted/updated/deleted +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extin/position_info.del', + null, null, null, 1); +Statement executed. +ij> select count(*) from position_info ; +1 +----- +680 +ij> select detail_description from position_info where position_code='AG1000'; +DETAIL_DESCRIPTION +----- +Essential Duties and Responsibilities (include but not limited to): +*Assist the director in his work activities in leading the& +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del', + null, null, null); +Statement executed. +ij> delete from position_info; +680 rows inserted/updated/deleted +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del', + null, null, null, 1); +Statement executed. +ij> select count(*) from position_info ; +1 +----- +680 +ij> select detail_description from position_info where position_code='AG1000'; +DETAIL_DESCRIPTION +----- +Essential Duties and Responsibilities (include but not limited to): +*Assist the director in his work activities in leading the& +ij> --test for autoincrement values +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','extinout/autoinc.dat',null,null,null); +Statement executed. +ij> create table dest_always(i int generated always as identity); +0 rows inserted/updated/deleted +ij> create table dest_by_default(i int generated by default as identity); +0 rows inserted/updated/deleted +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,0); +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression.::SQLSTATE: 42Z23Attempt to modify an identity column 'I'. : +ij> select * from dest_always; +I +----- +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,0); +Statement executed. +ij> select * from dest_by_default; +I +----- +1 +2 +3 +ij> drop table dest_always; +0 rows inserted/updated/deleted +ij> drop table dest_by_default; +0 rows inserted/updated/deleted +ij> create table dest_always(i int generated always as identity); +0 rows inserted/updated/deleted +ij> create table dest_by_default(i int generated by default as identity); +0 rows inserted/updated/deleted +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,1); +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression.::SQLSTATE: 42Z23Attempt to modify an identity column 'I'. : +ij> select * from dest_always; +I +----- +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,1); +Statement executed. +ij> select * from dest_by_default; +I +----- +1 +2 +3 +ij> drop table dest_always; +0 rows inserted/updated/deleted +ij> drop table dest_by_default; +0 rows inserted/updated/deleted +ij> --test case for bug (DERBY-390) +-----test import/export with reserved words as table Name, column Names ..etc. +create schema "Group"; +0 rows inserted/updated/deleted +ij> create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ; +0 rows inserted/updated/deleted +ij> insert into "Group"."Order" values(1, 2, 'memory') ; +1 row inserted/updated/deleted +ij> insert into "Group"."Order" values(3, 4, 'disk') ; +1 row inserted/updated/deleted +ij> insert into "Group"."Order" values(5, 6, 'mouse') ; +1 row inserted/updated/deleted +ij> --following export should fail because schema name is not matching the way it is defined using delimited quotes. +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('GROUP', 'Order' , 'extinout/order.dat', null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Schema 'GROUP' does not exist' was thrown while evaluating an expression.::SQLSTATE: 42Y07Schema 'GROUP' does not exist : +ij> --following export should fail because table name is not matching the way it is defined in the quotes. +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'ORDER' , 'extinout/order.dat', null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Table 'Group.ORDER' does not exist.' was thrown while evaluating an expression.::SQLSTATE: 42X05Table 'Group.ORDER' does not exist. : +ij> --following export should fail because of unquoted table name that is a reserved word. +call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group".Order' , 'extinout/order.dat' , null , null , null ) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Syntax error: Encountered "Order" at line 1, column 23.' was thrown while evaluating an expression.::SQLSTATE: 42X01Syntax error: Encountered "Order" at line 1, column 23. : +ij> --following exports should pass. +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group"."Order"' , 'extinout/order.dat' , null , null , null ) ; +Statement executed. +ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select "select" , "delete" , itemName from "Group"."Order"' , + 'extinout/order.dat' , null , null , null ) ; +Statement executed. +ij> --following import should fail because schema name is not matching the way it is defined using delimited quotes. +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('GROUP', 'Order' , 'extinout/order.dat', null, null, null, 0) ; +ERROR XIE0M: Table 'GROUP.Order' does not exist. : +ij> --following import should fail because table name is not matching the way it is defined in the quotes. +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'ORDER' , 'extinout/order.dat', null, null, null, 0) ; +ERROR XIE0M: Table 'Group.ORDER' does not exist. : +ij> --following import should fail because table name is not matching the way it is defined in the quotes. +call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'ORDER' , null , null , 'extinout/order.dat' , null , null , null, 1) ; +ERROR XIE0M: Table 'Group.ORDER' does not exist. : +ij> --following import should fail because column name is not matching the way it is defined in the quotes. +call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'DELETE, ITEMNAME' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ; +ERROR XIE08: There is no column named: DELETE. : +ij> --following import should fail because undelimited column name is not in upper case. +call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete, itemName' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ; +ERROR XIE08: There is no column named: itemName. : +ij> --following imports should pass +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null, 0) ; +Statement executed. +ij> select * from "Group"."Order"; +select |delete |ITEMNAME +----- +1 |2 |memory +3 |4 |disk +5 |6 |mouse +1 |2 |memory +3 |4 |disk +5 |6 |mouse +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , null , null , 'extinout/order.dat' , null , null , null, 1) ; +Statement executed. +ij> select * from "Group"."Order"; +select |delete |ITEMNAME +----- +1 |2 |memory +3 |4 |disk +5 |6 |mouse +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete' , '2' , 'extinout/order.dat' , null , null , null, 1) ; +Statement executed. +ij> select * from "Group"."Order"; +select |delete |ITEMNAME +----- +NULL |2 |NULL +NULL |4 |NULL +NULL |6 |NULL +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'ITEMNAME, select, delete' , '3,2,1' , 'extinout/order.dat' , null , null , null, 1) ; +Statement executed. +ij> select * from "Group"."Order"; +select |delete |ITEMNAME +----- +2 |1 |memory +4 |3 |disk +6 |5 |mouse +ij> drop table "Group"."Order"; +0 rows inserted/updated/deleted +ij> ---test undelimited names( All unquoted SQL identfiers should be passed in upper case). +create schema inventory; +0 rows inserted/updated/deleted +ij> create table inventory.orderTable(id int, amount int, itemName char(20)) ; +0 rows inserted/updated/deleted +ij> insert into inventory.orderTable values(101, 5, 'pizza') ; +1 row inserted/updated/deleted +ij> insert into inventory.orderTable values(102, 6, 'coke') ; +1 row inserted/updated/deleted +ij> insert into inventory.orderTable values(103, 7, 'break sticks') ; +1 row inserted/updated/deleted +ij> insert into inventory.orderTable values(104, 8, 'buffolo wings') ; +1 row inserted/updated/deleted +ij> --following export should fail because schema name is not in upper case. +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('inventory', 'ORDERTABLE' , 'extinout/order.dat', null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Schema 'inventory' does not exist' was thrown while evaluating an expression.::SQLSTATE: 42Y07Schema 'inventory' does not exist : +ij> --following export should fail because table name is not in upper case. +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ordertable' , 'extinout/order.dat', null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Table 'INVENTORY.ordertable' does not exist.' was thrown while evaluating an expression.::SQLSTATE: 42X05Table 'INVENTORY.ordertable' does not exist. : +ij> --following export should pass. +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ORDERTABLE' , 'extinout/order.dat', null, null, null) ; +Statement executed. +ij> --following import should fail because schema name is not in upper case +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('inventory', 'ORDERTABLE' , 'extinout/order.dat', null, null, null, 0) ; +ERROR XIE0M: Table 'inventory.ORDERTABLE' does not exist. : +ij> --following import should fail because table name is not in upper case. +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('INVENTORY', 'ordertable' , 'extinout/order.dat', null, null, null, 0) ; +ERROR XIE0M: Table 'INVENTORY.ordertable' does not exist. : +ij> --following import should fail because table name is not in upper case . +call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ordertable' , null , null , 'extinout/order.dat' , null , null , null, 1) ; +ERROR XIE0M: Table 'INVENTORY.ordertable' does not exist. : +ij> --following import should fail because column name is not in upper case. +call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'amount, ITEMNAME' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ; +ERROR XIE08: There is no column named: amount. : +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , null , null , 'extinout/order.dat' , null , null , null, 1) ; +Statement executed. +ij> select * from inventory.orderTable; +ID |AMOUNT |ITEMNAME +----- +101 |5 |pizza +102 |6 |coke +103 |7 |break sticks +104 |8 |buffolo wings +ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'ITEMNAME, ID, AMOUNT' , '3,2,1' , 'extinout/order.dat' , null , null , null, 1) ; +Statement executed. +ij> select * from inventory.orderTable; +ID |AMOUNT |ITEMNAME +----- +5 |101 |pizza +6 |102 |coke +7 |103 |break sticks +8 |104 |buffolo wings +ij> drop table inventory.orderTable; +0 rows inserted/updated/deleted +ij> --end derby-390 related test cases. +; +ij>