db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r208770 [3/3] - in /incubator/derby/code/trunk/java: engine/org/apache/derby/impl/load/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ testing/org/apache/derbyTesting/fun...
Date Fri, 01 Jul 2005 16:33:58 GMT
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
Fri Jul  1 09:33:55 2005
@@ -9,10 +9,10 @@
 insert into ex_emp values(99, 'smith' , 'tennis"p,l,ayer"', 190.55) ;
 -- Perform Export:
 
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , 
                                  null, null, null) ;
 -- Perform Import
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , 
                                       null, null, null, 0) ;
 
 insert into ex_emp values(100, 'smith' , 'tennis"player"', 190.55) ;
@@ -31,10 +31,10 @@
 
 -- Perform Export:
 
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , 
                                  null, null, null) ;
 -- Perform Import
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , 
                                       null, null, null, 0) ;
 
 select * from ex_emp;
@@ -52,7 +52,7 @@
 call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from ex_emp where id < 105', 
                                     'extinout/emp.dat' , null, null, null) ;
 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , 
                                       null, null, null, 0) ;
 
 --checking query
@@ -68,7 +68,7 @@
 
 -- 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,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'NAME, SALARY, SKILLS, ID', null,
                                     'extinout/emp.dat', null, null, null, 1) ;
 --check query
 select count(*) from imp_emp, ex_emp
@@ -78,7 +78,7 @@
 
 
 -- 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',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,null, '4, 1, 3, 2',
                                     'extinout/emp.dat', null, null, null, 1) ;
 
 --check query
@@ -88,7 +88,7 @@
       (ex_emp.skills is NULL and imp_emp.skills is NULL));
 
 --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',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'SALARY, ID, SKILLS, NAME', '2, 4, 3,
1',
                                     'extinout/emp.dat', null, null, null, 1) ;
 
 --check query
@@ -100,26 +100,26 @@
 
 ---testing with different delimiters
 -- single quote(') as character delimiter
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , 
                                     null, '''', null) ;
 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , 
                                     null, '''', null, 1) ;
 
 select * from imp_emp ;
 
 -- single quote(') as column delimiter
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , 
                                     '''',null, null) ;
 delete from imp_emp ;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' , 
                                     '''', null, null, 0) ;
 select * from imp_emp;
 
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , 
                                  '*', '%', null) ;
 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' , 
                                  '*', '%', null, 1) ;
 
 select * from imp_emp ;
@@ -135,9 +135,9 @@
 create table noncast(c1 double , c2 real ) ;
 insert into noncast values(1.5 , 6.7 ) ;
 insert into noncast values(2.5 , 8.999) ;
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'noncast' , 'extinout/noncast.dat'  , null ,
null , null) ;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'noncast' , 'extinout/noncast.dat'  , null , null
, null , 0) ;
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'noncast', 'c2 , c1' , '2, 1' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'NONCAST' , 'extinout/noncast.dat'  , null ,
null , null) ;
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NONCAST' , 'extinout/noncast.dat'  , null , null
, null , 0) ;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NONCAST', 'C2 , C1' , '2, 1' , 
                                    'extinout/noncast.dat'  , null , null , null , 0) ;
 select * from noncast ;
 
@@ -150,9 +150,9 @@
 insert into ttypes values('2004-04-03' , '15:18:31' , '2004-09-09 11:14:11' );
 insert into ttypes values(null , null , null);
 
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ttypes' , 'extinout/ttypes.del' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' , 
                                  null, null, null) ;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ttypes' , 'extinout/ttypes.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' , 
                                  null, null, null, 0) ;
 
 select * from ttypes;
@@ -165,9 +165,9 @@
 create table t1(a int ) ;
 insert into t1 values(1) ;
 insert into t1 values(2) ;
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' , 
                                  null, null, null) ;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' , 
                                  null, null, null, 0) ;
 --above import should have committed , following rollback should be a noop.
 rollback;
@@ -175,14 +175,14 @@
 insert into t1 values(3) ;
 insert into t1 values(4) ;
 --file not found error should rollback 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/nofile.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' , 
                                  null, null, null, 0) ;
 commit;
 select * from t1 ;
 insert into t1 values(3) ;
 insert into t1 values(4) ;
 --table not found error should issue a implicit rollback 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'notable' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' , 
                                  null, null, null, 0) ;
 commit ;
 select * from t1 ;
@@ -190,12 +190,12 @@
 ---check commit/rollback with replace options using 
 insert into t1 values(1) ;
 insert into t1 values(2) ;
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' , 
                                  null, null, null) ;
 --above export should have a commit.rollback below should be a noop
 rollback;
 select * from t1;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' , 
                                  null, null, null, 1) ;
 --above import should have committed , following rollback should be a noop.
 rollback;
@@ -203,14 +203,14 @@
 insert into t1 values(3) ;
 insert into t1 values(4) ;
 --file not found error should rollback 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/nofile.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' , 
                                  null, null, null, 1) ;
 commit;
 select * from t1 ;
 insert into t1 values(3) ;
 insert into t1 values(4) ;
 --table not found error should issue a implicit rollback 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'notable' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' , 
                                  null, null, null, 1) ;
 commit ;
 ---check IMPORT_DATA calls commit/rollback
@@ -219,9 +219,9 @@
 ---check commit/rollback with replace options using 
 insert into t1 values(1) ;
 insert into t1 values(2) ;
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' , 
                                  null, null, null) ;
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1' , 'a' , '1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' , 
                                  null, null, null, 0) ;
 --above import should have committed , following rollback should be a noop.
 rollback;
@@ -229,14 +229,14 @@
 insert into t1 values(3) ;
 insert into t1 values(4) ;
 --file not found error should rollback 
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1', 'a' , '1'  , 'extinout/nofile.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1'  , 'extinout/nofile.del' , 
                                  null, null, null, 0) ;
 commit;
 select * from t1 ;
 insert into t1 values(3) ;
 insert into t1 values(4) ;
 --table not found error should issue a implicit rollback 
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'notable' , 'a' , '1', 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NOTABLE' , 'A' , '1', 'extinout/t1.del' , 
                                  null, null, null, 1) ;
 commit ;
 select * from t1 ;
@@ -245,18 +245,18 @@
 --make sure commit import code is ok in autcommit mode.
 insert into t1 values(3) ;
 insert into t1 values(4) ;
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1' , 'a' , '1' , 'extinout/t1.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' , 
                                  null, null, null, 0) ;
 select * from t1 ;
 insert into t1 values(5) ;
 insert into t1 values(6) ;
 --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' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1'  , 'extinout/nofile.del' , 
                                  null, null, null, 0) ;
 select * from t1 ;
 --END IMPORT COMMIT/ROLLBACK TESTSING
 
----all types supported by DB2 cloudscape import/export
+---all types supported by Derby import/export
 create table alltypes(chartype char(20) , 
 	          biginttype bigint , 
 		  datetype date , 
@@ -309,9 +309,9 @@
                               '2004-09-09 11:14:11',
                               '"varchar" testing');
                               
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , 
                                  null, null, null) ;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , 
                                  null, null, null, 0) ;
 select * from alltypes ;                          
 delete from alltypes;
@@ -323,12 +323,12 @@
 for each  row mode db2sql
 insert into test1 values(newrow.chartype);
 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , 
                                  null, null, null, 0) ;
 select count(*) from alltypes ;
 select * from test1;
 delete from alltypes;
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , 
                                  null, null, null, 1) ;
 select count(*) from alltypes;
 
@@ -352,24 +352,24 @@
 
 call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select c1,c3,c4 from table2' , 'extinout/import.del'
, 
                                  null, null, null) ;
-CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'table1', 'c1,c3,c4' , null, 'extinout/import.del',null,
null,null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'TABLE1', 'C1,C3,C4' , null, 'extinout/import.del',null,
null,null,0);
 
 select * from table1;
 delete from table1;
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'table2' , 'extinout/import.del',  null, null,
null) ;
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del',  null, null,
null) ;
 
 --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);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);
 
 --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);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'TABLE1', 'C1,C3,C4' , '1,3,4', 'extinout/import.del',null,
null, null,1);
 select * from table1;
 
 update table2 set c2=null;
 --check null values import to identity columns should also fail
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'table2' , 'extinout/import.del' , 
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del' , 
                                  null, null, null) ;
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'table1', 'extinout/import.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);
 select * from table1;
 
 --check that replace fails when there dependents and replaced data 
@@ -381,12 +381,12 @@
 call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from parent where a < 3' , 'extinout/parent.del'
, 
                                  null, null, null) ;
 --replace should fail because of dependent table
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'parent', 'extinout/parent.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'PARENT', 'extinout/parent.del',null, null, null,1);
 select * from parent;
 
 ---test with a file which has a differen records seperators (\n, \r , \r\n)
 create table nt1( a int , b char(30));
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'nt1', 'extin/mixednl.del',null, null, null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'NT1', 'extin/mixednl.del',null, null, null,0);
 select * from nt1;
 drop table nt1 ;
 
@@ -400,14 +400,14 @@
        detail_description long varchar,
        web_flag varchar(1)
     );
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'position_info', 'extin/position_info.del',
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extin/position_info.del',
                                     null, null, null, 1);
 select count(*) from position_info ;
 select detail_description from position_info where position_code='AG1000';
-CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'position_info', 'extinout/pinfo.del',
+CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del',
                                     null, null, null);
 delete from position_info;
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'position_info', 'extinout/pinfo.del',
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del',
                                     null, null, null, 1);
 select count(*) from position_info ;
 select detail_description from position_info where position_code='AG1000';
@@ -420,9 +420,9 @@
 create table dest_always(i int generated always as identity);
 create table dest_by_default(i int generated by default as identity);
 
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,0);
 select * from dest_always;
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,0);
 select * from dest_by_default;
 
 drop table dest_always;
@@ -432,11 +432,85 @@
 create table dest_always(i int generated always as identity);
 create table dest_by_default(i int generated by default as identity);
 
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,1);
 select * from dest_always;
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,1);
 select * from dest_by_default;
 
 drop table dest_always;
 drop table dest_by_default;
+--test case for bug (DERBY-390)
+--test import/export with reserved words as table Name, column Names ..etc.
+create schema "Group";
+create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
+insert into "Group"."Order" values(1, 2, 'memory') ;
+insert into "Group"."Order" values(3, 4, 'disk') ;
+insert into "Group"."Order" values(5, 6, 'mouse') ;
+
+--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) ;
+--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) ;
+--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 ) ;
+--following exports should pass.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null,
null) ;
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group"."Order"' , 'extinout/order.dat'
,    null , null , null ) ;
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select "select" , "delete" , itemName from "Group"."Order"'
, 
+                                                 'extinout/order.dat' ,    null , null ,
null ) ;
+
+--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) ;
+--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) ;
+--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) ; 
+--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) ; 
+--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) ; 
+
+--following imports should pass
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null,
null, 0) ;
+select * from "Group"."Order";
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , null , null ,   'extinout/order.dat'
  , null , null , null, 1) ; 
+select * from "Group"."Order";
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete' , '2' ,   'extinout/order.dat'
  , null , null , null, 1) ; 
+select * from "Group"."Order";
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'ITEMNAME, select, delete' , '3,2,1'
,   'extinout/order.dat'   , null , null , null, 1) ; 
+select * from "Group"."Order";
+drop table "Group"."Order";
+
+---test undelimited names( All unquoted SQL identfiers should be passed in upper case). 
+create schema inventory;
+create table inventory.orderTable(id int, amount int, itemName char(20)) ;
+insert into inventory.orderTable values(101, 5, 'pizza') ;
+insert into inventory.orderTable values(102, 6, 'coke') ;
+insert into inventory.orderTable values(103, 7, 'break sticks') ;
+insert into inventory.orderTable values(104, 8, 'buffolo wings') ;
+
+--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) ;
+--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) ;
+
+--following export should pass.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ORDERTABLE' , 'extinout/order.dat', null,
null, null) ;
+
+--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) ;
+--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) ;
+--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) ; 
+--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) ; 
+
+
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , null , null ,   'extinout/order.dat'
  , null , null , null, 1) ; 
+select * from inventory.orderTable;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'ITEMNAME, ID, AMOUNT' , '3,2,1'
,   'extinout/order.dat'   , null , null , null, 1) ; 
+select * from inventory.orderTable;
 
+drop table inventory.orderTable;
+--end derby-390 related test cases.

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql
Fri Jul  1 09:33:55 2005
@@ -123,7 +123,7 @@
 			Balance decimal(8,2));
 
 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'Alt1' , 'extin/Alt1.asc' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT1' , 'extin/Alt1.asc' , 
                                       null, null, null, 0) ;
 
 
@@ -147,7 +147,7 @@
 
 
 
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'Alt3' , 'extin/Alt3.asc' , 
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT3' , 'extin/Alt3.asc' , 
                                       null, null, null, 0) ;
 
 
@@ -185,7 +185,7 @@
 	email	varchar(80),
 	web	varchar(50));
 
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'sqlAnywhere1' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'SQLANYWHERE1' ,
                                   null , '1,2,3,4,5,7,8,9,10,11,12,13,14', 
 	 		          'extin/sqlAnywhere1.txt' , 
 				  '|', '''', 'ASCII', 0) ;
@@ -200,7 +200,7 @@
 	email varchar(40),
 	phone varchar(30));
 
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'sqlAnywhere2' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'SQLANYWHERE2' , 
 		                  null , '3, 4, 7, 5',
 			          'extin/sqlAnywhere2.txt' , 
 				  '|', '''', 'ASCII', 0) ;
@@ -230,7 +230,7 @@
 -- import it first with just LF as record seperator, we should be seeing 
 -- ^M's at the end of note field.
 
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'HouseHoldItem' , 
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'HOUSEHOLDITEM' , 
 				  null , '2,3,4,5,6,7,8,11,14', 
 				  'extin/Access1.txt' , 
 				  null, null, null, 0) ;
@@ -252,7 +252,7 @@
                   column5 varchar(200),
                   column6 varchar(200));
 
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_temp' ,null, '2, 3, 4, 5, 6',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_TEMP' ,null, '2, 3, 4, 5, 6',
                                     'extinout/t1.dump', '|', '''', 'ASCII', 0) ;
 
 select * from imp_temp ;



Mime
View raw message