ieptests.out

derby database source code.good for you.

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 'SQL Exception: 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 'SQL Exception: 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 'SQL Exception: 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 'SQL Exception: 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 'SQL Exception: 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 'SQL Exception: 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 'SQL Exception: Table 'Group.ORDER' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table '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 'SQL Exception: 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 'SQL Exception: 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 'SQL Exception: Table 'INVENTORY.ordertable' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table '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>