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. -- --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: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data 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: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data 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: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data 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: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data 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.SQLException: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).' was thrown while evaluating an expression. SQLSTATE: 42Z08: Bulk 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.SQLException: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt 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.SQLException: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt 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.SQLException: 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: 23503: INSERT 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, 'UTF-8',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, 'US-ASCII', 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.SQLException: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt 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.SQLException: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt 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.SQLException: Schema 'GROUP' does not exist' was thrown while evaluating an expression. SQLSTATE: 42Y07: Schema '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.SQLException: Table/View 'Group.ORDER' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table/View '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.SQLException: Syntax error: Encountered "Order" at line 1, column 23.' was thrown while evaluating an expression. SQLSTATE: 42X01: Syntax 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.SQLException: Schema 'inventory' does not exist' was thrown while evaluating an expression. SQLSTATE: 42Y07: Schema '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.SQLException: Table/View 'INVENTORY.ordertable' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table/View '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>