ieptests.out

derby database source code.good for you.

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