iepnegativetests.out

derby database source code.good for you.

6          
ij> --import back to a regualr table
call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'V1' , 'extinout/temp1.dat' , 
                                 null, null, null, 0) ;
0 rows inserted/updated/deleted
ij> select * from v1;
A          
-----------
1          
2          
3          
4          
5          
6          
ij> commit;
ij> --import to a temp table should fail with a table not found errror
declare global temporary table session.temp2(c1 int) not logged;
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SESSION', 'TEMP2' , 'extinout/temp1.dat' , 
                                 null, null, null, 0) ;
ERROR XIE0M: Table 'SESSION.TEMP2' does not exist.  
ij> select * from session.temp2 ;
ERROR 42X05: Table 'SESSION.TEMP2' does not exist.
ij> commit ;
ij> drop table v1;
0 rows inserted/updated/deleted
ij> autocommit on;
ij> create table t3(c1 int , c2 double , c3 decimal , c4 varchar(20) );
0 rows inserted/updated/deleted
ij> insert into t3 values(1 , 3.5 , 8.6 , 'test strings');
1 row inserted/updated/deleted
ij> insert into t3 values(2 , 3.5 , 8.6 , 'test strings');
1 row inserted/updated/deleted
ij> insert into t3 values(3 , 3.5 , 8.6 , 'test strings');
1 row inserted/updated/deleted
ij> insert into t3 values(4 , 3.5 , 8.6 , 'test strings');
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T3' , 'extinout/t3.dat' , 
                                 null, null, null) ;
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T3' , 'extinout/t3.dat' , 
                                 null, null, null, 0) ;
0 rows inserted/updated/deleted
ij> select * from t3;
C1         |C2                    |C3    |C4                  
--------------------------------------------------------------
1          |3.5                   |8     |test strings        
2          |3.5                   |8     |test strings        
3          |3.5                   |8     |test strings        
4          |3.5                   |8     |test strings        
1          |3.5                   |8     |test strings        
2          |3.5                   |8     |test strings        
3          |3.5                   |8     |test strings        
4          |3.5                   |8     |test strings        
ij> --import data column names are incorrect
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3, X4', null,
                                 'extinout/t3.dat' , 
                                 null, null, null, 0) ;
ERROR XIE08: There is no column named: X1.  
ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3', '1,2,3,4', 
                                 'extinout/t3.dat' , 
                                 null, null, null, 0) ;
ERROR XIE08: There is no column named: X1.  
ij> --import data insert column names count < column indexes does not match
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'C1, C2, C3', '1,2,3,4', 
                                 'extinout/t3.dat' , 
                                 null, null, null, 0) ;
0 rows inserted/updated/deleted
ij> --import data column indexes count > insert columns count
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'C1, C2, C3,C4', '1,2', 
                                 'extinout/t3.dat' , 
                                 null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The number of values assigned is not the same as the number of specified or implied columns.' was thrown while evaluating an expression.
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , null, '11,22,12,24', 
                                 'extinout/t3.dat' , 
				 null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: Column 'COLUMN11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'COLUMN11' is not a column in the target table.' was thrown while evaluating an expression.
ERROR 42X04: Column 'COLUMN11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'COLUMN11' is not a column in the target table.
ij> --repeat the above type cases with empty file and minor variation to paramters
delete from t3 ;
12 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T3' , 'extinout/t3.dat' , 
                                  ';', '^', 'utf-16') ;
0 rows inserted/updated/deleted
ij> --import data column names are incorrect
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3, X4', null, 
                                 'extinout/t3.dat' , 
                                 ';', '^', 'utf-16', 1) ;
ERROR XIE08: There is no column named: X1.  
ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3', '1,2,3,4', 
                                 'extinout/t3.dat' , 
				    ';', '^', 'utf-16', 1) ;
ERROR XIE08: There is no column named: X1.  
ij> --import data insert column names count < column indexes does not match
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'C1, C2, C3', null, 
                                 'extinout/t3.dat' , 
				    ';', '^', 'utf-16', 1) ;
0 rows inserted/updated/deleted
ij> --import data column indexes count > insert columns count
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , null, '1,2', 
                                 'extinout/t3.dat' , 
				    ';', '^', 'utf-16', 1) ;
ERROR 38000: The exception 'SQL Exception: The number of values assigned is not the same as the number of specified or implied columns.' was thrown while evaluating an expression.
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> --specify column indexes that are not there in the file that is being  imported
call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , null, '11,22,12,24', 
                                 'extinout/t3.dat' , 
				    ';', '^', 'utf-16', 1) ;
0 rows inserted/updated/deleted
ij> --import to a system table shoud fail
call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SYS', 'SYSTABLES' , 'extinout/t3.dat' , 
                                      ';', '^', 'utf-16', 1) ;
ERROR 38000: The exception 'SQL Exception: 'SYS.SYSTABLES' is a system table.  Users are not allowed to modify the contents of this table.' was thrown while evaluating an expression.
ERROR 42Y25: 'SYS.SYSTABLES' is a system table.  Users are not allowed to modify the contents of this table.
ij> ---not supported by db2 cloudscape import/export
create table ntype(a int , ct CLOB(1024));
0 rows inserted/updated/deleted
ij> create table ntype1(bt BLOB(1024) , a int);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' , 
                                 null, null, null) ;
ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by the import/export feature.   
ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.ntype1',
				   'extinout/ntype.dat' , 
                                   null, null, null) ;
ERROR XIE0B: Column 'BT' in the table is of type BLOB, it is not supported by the import/export feature.   
ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' , 
                                 null, null, null, 0) ;
ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by the import/export feature.   
ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'NTYPE1' , null , null, 
                                 'extinout/ntype.dat' , 
                                 null, null, null, 0) ;
ERROR XIE0B: Column 'BT' in the table is of type BLOB, it is not supported by the import/export feature.   
ij> --import should aquire a lock on the table
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> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from parent where a < 3' , 'extinout/parent.del' , 
                                 null, null, null) ;
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat' as c1;
ij(C1)> connect 'jdbc:derby:wombat' as c2;
ij(C2)> set connection c1;
ij(C1)> autocommit off;
ij(C1)> lock table iep.parent in share mode;
0 rows inserted/updated/deleted
ij(C1)> set connection c2;
ij(C2)> autocommit off;
ij(C2)> --following import should fail with lock time out
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout', '5');
0 rows inserted/updated/deleted
ij(C2)> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'PARENT', 'extinout/parent.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: Table 'IEP.PARENT' cannot be locked in 'EXCLUSIVE' mode.' was thrown while evaluating an expression.
ERROR X0X02: Table 'IEP.PARENT' cannot be locked in 'EXCLUSIVE' mode.
ERROR 40XL1: A lock could not be obtained within the time requested
ij(C2)> disconnect c1;
ij(C2)> disconnect c2;
ij> set connection connection0;
ij>