📄 ieptests.out
字号:
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/deletedij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extin/position_info.del', null, null, 'US-ASCII', 1);0 rows inserted/updated/deletedij> 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);0 rows inserted/updated/deletedij> delete from position_info;680 rows inserted/updated/deletedij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del', null, null, null, 1);0 rows inserted/updated/deletedij> 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 valuesCALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','extinout/autoinc.dat',null,null,null);0 rows inserted/updated/deletedij> create table dest_always(i int generated always as identity);0 rows inserted/updated/deletedij> create table dest_by_default(i int generated by default as identity);0 rows inserted/updated/deletedij> 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.ERROR 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);0 rows inserted/updated/deletedij> select * from dest_by_default;I -----------1 2 3 ij> drop table dest_always;0 rows inserted/updated/deletedij> drop table dest_by_default;0 rows inserted/updated/deletedij> create table dest_always(i int generated always as identity);0 rows inserted/updated/deletedij> create table dest_by_default(i int generated by default as identity);0 rows inserted/updated/deletedij> 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.ERROR 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);0 rows inserted/updated/deletedij> select * from dest_by_default;I -----------1 2 3 ij> drop table dest_always;0 rows inserted/updated/deletedij> drop table dest_by_default;0 rows inserted/updated/deletedij> --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/deletedij> create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;0 rows inserted/updated/deletedij> insert into "Group"."Order" values(1, 2, 'memory') ;1 row inserted/updated/deletedij> insert into "Group"."Order" values(3, 4, 'disk') ;1 row inserted/updated/deletedij> insert into "Group"."Order" values(5, 6, 'mouse') ;1 row inserted/updated/deletedij> --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.ERROR 42Y07: Schema 'GROUP' does not existij> --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.ERROR 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.ERROR 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) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group"."Order"' , 'extinout/order.dat' , null , null , null ) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select "select" , "delete" , itemName from "Group"."Order"' , 'extinout/order.dat' , null , null , null ) ;0 rows inserted/updated/deletedij> --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 passcall SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null, 0) ;0 rows inserted/updated/deletedij> 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) ;0 rows inserted/updated/deletedij> 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) ;0 rows inserted/updated/deletedij> 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) ;0 rows inserted/updated/deletedij> 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/deletedij> ---test undelimited names( All unquoted SQL identfiers should be passed in upper case). create schema inventory;0 rows inserted/updated/deletedij> create table inventory.orderTable(id int, amount int, itemName char(20)) ;0 rows inserted/updated/deletedij> insert into inventory.orderTable values(101, 5, 'pizza') ;1 row inserted/updated/deletedij> insert into inventory.orderTable values(102, 6, 'coke') ;1 row inserted/updated/deletedij> insert into inventory.orderTable values(103, 7, 'break sticks') ;1 row inserted/updated/deletedij> insert into inventory.orderTable values(104, 8, 'buffolo wings') ;1 row inserted/updated/deletedij> --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.ERROR 42Y07: Schema 'inventory' does not existij> --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.ERROR 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) ;0 rows inserted/updated/deletedij> --following import should fail because schema name is not in upper casecall 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) ;0 rows inserted/updated/deletedij> 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) ;0 rows inserted/updated/deletedij> 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/deletedij> --end derby-390 related test cases.;ij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -