ieptests.out

derby database source code.good for you.

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 'SQL Exception: 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