importexportthruij.out

derby database source code.good for you.

ij> -- test for import export thru command line interface
-- first test basic import functionality
-- ascii delimited default format
drop table T1;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist.
ij> create table T1 (	Account	int,
			Fname	char(30),
			Lname	char(30),
			Company varchar(35),
			Address varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Payment	decimal(8,2),
			Balance decimal(8,2));
0 rows inserted/updated/deleted
ij> create index T1_IndexBalance on T1 (Balance, Account, Company);
0 rows inserted/updated/deleted
ij> create index T1_IndexFname on T1 (Fname, Account);
0 rows inserted/updated/deleted
ij> create index T1_IndexLname on T1 (Lname, Account);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extin/EndOfFile.txt' , 
                                    null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: Read endOfFile at unexpected place on line 2.' was thrown while evaluating an expression.
ERROR XIE0E: Read endOfFile at unexpected place on line 2.
ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extin/Tutor1.asc' , 
                                    null, null, null, 0) ;
0 rows inserted/updated/deleted
ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T1'));
1          
-----------
1          
ij> -- ascii Fixed
drop table T2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist.
ij> autocommit off;
ij> create table T2 (	Account	int,
			Fname	char(30),
			Lname	char(30),
			Company	varchar(35),
			Address	varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Payment	decimal(8,2),
			Balance	decimal(8,2));
0 rows inserted/updated/deleted
ij> create index T2_IndexBalance on T2 (Balance, Account, Company);
0 rows inserted/updated/deleted
ij> create index T2_IndexFname on T2 (Fname, Account);
0 rows inserted/updated/deleted
ij> create index T2_IndexLname on T2 (Lname, Account);
0 rows inserted/updated/deleted
ij> commit;
ij> --this one should fail becuase this is not the right command to handle fixed formats
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T2' , 'extin/Tutor2.asc' , 
                                    null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: Column 'COLUMN2' 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 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression.
ERROR 42X04: Column 'COLUMN2' 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 'COLUMN2' is not a column in the target table.
ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T2'));
1          
-----------
1          
ij> commit;
ij> -- test remapping
drop table T3;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T3' because it does not exist.
ij> create table T3 (	Lname	char(30),
			Fname	char(30),
			Account	int not null primary key,
			Company varchar(35),
			Payment	decimal(8,2),
			Balance decimal(8,2));
0 rows inserted/updated/deleted
ij> create index T3_indexBalance on T3 (Balance, Company, Account);
0 rows inserted/updated/deleted
ij> create index T3_indexPayment on T3 (Payment, Company, Account);
0 rows inserted/updated/deleted
ij> --icorrect mapping of file to table ; should give error and rollback
call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T3' , 
                                  null , '3, 2, 1, 4, 9, 200' ,
				  'extin/Tutor3.asc' , 
				  null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: Column 'COLUMN200' 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 'COLUMN200' is not a column in the target table.' was thrown while evaluating an expression.
ERROR 42X04: Column 'COLUMN200' 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 'COLUMN200' is not a column in the target table.
ij> rollback;
ij> -- table should not be there
select count(*) from T3;
ERROR 42X05: Table 'T3' does not exist.
ij> create table T3 (	Lname	char(30),
			Fname	char(30),
			Account	int not null primary key,
			Company varchar(35),
			Payment	decimal(8,2),
			Balance decimal(8,2));
0 rows inserted/updated/deleted
ij> create index T3_indexBalance on T3 (Balance, Company, Account);
0 rows inserted/updated/deleted
ij> create index T3_indexPayment on T3 (Payment, Company, Account);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T3' ,  
                                  null , '3, 2, 1, 4, 9, 10' ,
				  'extin/Tutor3.asc' ,
				   null, null, null, 0) ;
0 rows inserted/updated/deleted
ij> commit;
ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T3'));
1          
-----------
1          
ij> -- now check results
select count(*) from T1;
1          
-----------
102        
ij> select count(*) from T2;
1          
-----------
0          
ij> select count(*) from T3;
1          
-----------
102        
ij> select * from T1 where State = 'CA';
ACCOUNT    |FNAME                         |LNAME                         |COMPANY                            |ADDRESS                                 |CITY                |STATE|ZIP       |PAYMENT    |BALANCE    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10000      |Bugs                          |Bunny                         |Warner Brothers                    |  -- Rabbit Hole --                     |Los Angeles         |CA   |94001     |0.00       |100.00     
10129      |Mickey                        |Mouse                         |Disneyland                         |Magic Kingdom                           |Los Angeles         |CA   |94000     |0.00       |1.00       
ij> select * from T2 where State = 'CA';
ACCOUNT    |FNAME                         |LNAME                         |COMPANY                            |ADDRESS                                 |CITY                |STATE|ZIP       |PAYMENT    |BALANCE    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------