Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/dcl.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/dcl.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/dcl.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/dcl.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,398 @@ +ij> -- test database class loading. +maximumdisplaywidth 300; +ij> create schema emc; +0 rows inserted/updated/deleted +ij> set schema emc; +0 rows inserted/updated/deleted +ij> create table contacts (id int primary key, e_mail varchar(30)); +0 rows inserted/updated/deleted +ij> create procedure EMC.ADDCONTACT(id INT, e_mail VARCHAR(30)) +MODIFIES SQL DATA +external name 'org.apache.derbyTesting.databaseclassloader.emc.addContact' +language java parameter style java; +0 rows inserted/updated/deleted +ij> create function EMC.GETARTICLE(path VARCHAR(40)) RETURNS VARCHAR(256) +NO SQL +external name 'org.apache.derbyTesting.databaseclassloader.emc.getArticle' +language java parameter style java; +0 rows inserted/updated/deleted +ij> -- fails because no class in classpath, +CALL EMC.ADDCONTACT(1, 'bill@somecompany.com'); +ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public. +ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'. +ij> -- install the jar, copied there by the magic of supportfiles +-- in the test harness (dcl_app.properties). The source for +-- the class is contained within the jar for reference. +CALL SQLJ.INSTALL_JAR('file:extin/dcl_emc1.jar', 'EMC.MAIL_APP', 0); +0 rows inserted/updated/deleted +ij> -- fails because no class not in classpath, jar file not in database classpath. +CALL EMC.ADDCONTACT(1, 'bill@somecompany.com'); +ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public. +ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'. +ij> -- now add this into the database class path +call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP'); +0 rows inserted/updated/deleted +ij> -- all should work now +CALL EMC.ADDCONTACT(1, 'bill@ruletheworld.com'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(2, 'penguin@antartic.com'); +0 rows inserted/updated/deleted +ij> SELECT id, e_mail from EMC.CONTACTS; +ID |E_MAIL +------------------------------------------ +1 |bill@ruletheworld.com +2 |penguin@antartic.com +ij> -- Test resource loading from the jar file +-- Simple path should be prepended with the package name +-- of the class executing the code to find +-- /org/apache/derbyTesting/databaseclassloader/graduation.txt +VALUES EMC.GETARTICLE('graduate.txt'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +The Apache Foundation has released the first version of the open-source Derby database, which also gained support from Sun Microsystems. +ij> -- now an absolute path +VALUES EMC.GETARTICLE('/article/release.txt'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +The Apache Derby development community is pleased to announce its first release after graduating from the Apache Incubator, Apache Derby 10.1.1.0. +ij> -- no such resources +VALUES EMC.GETARTICLE('/article/fred.txt'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> VALUES EMC.GETARTICLE('barney.txt'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> -- try to read the class file should be disallowed +-- by returning null +VALUES EMC.GETARTICLE('emc.class'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> -- now the application needs to track if e-mails are valid +ALTER TABLE EMC.CONTACTS ADD COLUMN OK SMALLINT; +0 rows inserted/updated/deleted +ij> SELECT id, e_mail, ok from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +ij> -- well written application, INSERT used explicit column names +-- ok defaults to NULL +CALL EMC.ADDCONTACT(3, 'big@blue.com'); +0 rows inserted/updated/deleted +ij> SELECT id, e_mail, ok from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +ij> -- check the roll back of class loading. +-- install a new jar in a transaction, see +-- that the new class is used and then rollback +-- the old class should be used after the rollback. +AUTOCOMMIT OFF; +ij> CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(99, 'wormspam@soil.com'); +0 rows inserted/updated/deleted +ij> SELECT id, e_mail, ok from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +99 |wormspam@soil.com |0 +ij> rollback; +ij> AUTOCOMMIT ON; +ij> SELECT id, e_mail, ok from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +ij> CALL EMC.ADDCONTACT(99, 'wormspam2@soil.com'); +0 rows inserted/updated/deleted +ij> SELECT id, e_mail, ok from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +99 |wormspam2@soil.com |NULL +ij> DELETE FROM EMC.CONTACTS WHERE ID = 99; +1 row inserted/updated/deleted +ij> -- now change the application to run checks on the e-mail +-- address to ensure it is valid (in this case by seeing if +-- simply includes 'spam' in the title. +CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(4, 'spammer@ripoff.com'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(5, 'open@source.org'); +0 rows inserted/updated/deleted +ij> SELECT id, e_mail, ok from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +4 |spammer@ripoff.com |0 +5 |open@source.org |1 +ij> -- now add another jar in to test two jars and +-- a quoted identifer for the jar names. +create schema "emcAddOn"; +0 rows inserted/updated/deleted +ij> set schema emcAddOn; +ERROR 42Y07: Schema 'EMCADDON' does not exist +ij> set schema "emcAddOn"; +0 rows inserted/updated/deleted +ij> create function "emcAddOn".VALIDCONTACT(e_mail VARCHAR(30)) +RETURNS SMALLINT +READS SQL DATA +external name 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util.valid' +language java parameter style java; +0 rows inserted/updated/deleted +ij> CALL SQLJ.INSTALL_JAR('file:extin/dcl_emcaddon.jar', '"emcAddOn"."MailAddOn"', 0); +0 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP:"emcAddOn"."MailAddOn"'); +0 rows inserted/updated/deleted +ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS; +E_MAIL |2 +------------------------------------- +bill@ruletheworld.com |0 +penguin@antartic.com |0 +big@blue.com |0 +spammer@ripoff.com |0 +open@source.org |1 +ij> -- function that gets the signers of the class (loaded from the jar) +create function EMC.GETSIGNERS(CLASS_NAME VARCHAR(256)) +RETURNS VARCHAR(60) +NO SQL +external name 'org.apache.derbyTesting.databaseclassloader.emc.getSigners' +language java parameter style java; +0 rows inserted/updated/deleted +ij> -- at this point the jar is not signed, NULL expected +VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> -- Replace with a signed jar +-- (self signed certificate) +-- +-- keytool -delete -alias emccto -keystore emcks -storepass ab987c +-- keytool -genkey -dname "cn=EMC CTO, ou=EMC APP, o=Easy Mail Company, c=US" -alias emccto -keypass kpi135 -keystore emcks -storepass ab987c +-- keytool -selfcert -alias emccto -keypass kpi135 -validity 36500 -keystore emcks -storepass ab987c +-- keytool -keystore emcks -storepass ab987c -list -v +-- jarsigner -keystore emcks -storepass ab987c -keypass kpi135 -signedjar dcl_emc2s.jar dcl_emc2.jar emccto +-- keytool -delete -alias emccto -keystore emcks -storepass ab987c +-- +CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2s.jar', 'EMC.MAIL_APP'); +0 rows inserted/updated/deleted +ij> VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US +ij> -- other jar should not be signed +VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> -- Jar up this database (wombat) for use in database in a jar testing +-- at the end of this script. +disconnect; +ij> connect 'jdbc:derby:wombat;shutdown=true'; +ERROR 08006: Database 'wombat' shutdown. +ij> -- jar up the database +connect 'jdbc:derby:db1;create=true'; +ij> create procedure CREATEARCHIVE(jarName VARCHAR(20), path VARCHAR(20), dbName VARCHAR(20)) +LANGUAGE JAVA PARAMETER STYLE JAVA +NO SQL +EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.createArchive'; +0 rows inserted/updated/deleted +ij> call CREATEARCHIVE('ina.jar', 'wombat', 'db7'); +0 rows inserted/updated/deleted +ij> disconnect; +ij> connect 'jdbc:derby:wombat'; +ij> -- replace with a hacked jar file, emc.class modified to diable +-- valid e-mail address check but using same signatures. +-- ie direct replacement of the .class file. +CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2sm.jar', 'EMC.MAIL_APP'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(99, 'spamking@cracker.org'); +ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public. +ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc : Security exception thrown accessing class org.apache.derbyTesting.databaseclassloader.emc in jar "EMC"."MAIL_APP" : SHA1 digest error for org/apache/derbyTesting/databaseclassloader/emc.class: java.lang.ClassNotFoundException'. +ij> -- replace with a hacked jar file, emc.class modified to +-- be an invalid jar file (no signing on this jar). +CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2l.jar', 'EMC.MAIL_APP'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(999, 'spamking2@cracker.org'); +ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public. +ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc : org/apache/derbyTesting/databaseclassloader/emc : Unsupported major.minor version 32558.32639: java.lang.ClassNotFoundException'. +ij> -- cleanup +CALL SQLJ.REMOVE_JAR('EMC.MAIL_APP', 0); +ERROR X0X07: Cannot remove jar file '"EMC"."MAIL_APP"' because it is on your derby.database.classpath '"EMC"."MAIL_APP"'. +ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', '"emcAddOn"."MailAddOn"'); +0 rows inserted/updated/deleted +ij> CALL EMC.ADDCONTACT(99, 'cash@venture.com'); +ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public. +ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'. +ij> CALL SQLJ.REMOVE_JAR('EMC.MAIL_APP', 0); +0 rows inserted/updated/deleted +ij> DROP PROCEDURE EMC.ADDCONTACT; +0 rows inserted/updated/deleted +ij> DROP FUNCTION EMC.GETSIGNERS; +0 rows inserted/updated/deleted +ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS; +E_MAIL |2 +------------------------------------- +bill@ruletheworld.com |0 +penguin@antartic.com |0 +big@blue.com |0 +spammer@ripoff.com |0 +open@source.org |1 +ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', ''); +0 rows inserted/updated/deleted +ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS; +ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util' does not exist or is inaccessible. This can happen if the class is not public. +ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util: java.lang.ClassNotFoundException'. +ij> CALL SQLJ.REMOVE_JAR('"emcAddOn"."MailAddOn"', 0); +0 rows inserted/updated/deleted +ij> DROP FUNCTION "emcAddOn".VALIDCONTACT; +0 rows inserted/updated/deleted +ij> DROP TABLE EMC.CONTACTS; +0 rows inserted/updated/deleted +ij> disconnect; +ij> -- test reading a database from a jar file and loading +-- classes etc. from the jars within the database. +-- first using the jar protocol and then the classpath option. +connect 'jdbc:derby:jar:(ina.jar)db7' AS DB7; +ij> run resource '/org/apache/derbyTesting/functionTests/tests/lang/dcl_readOnly.sql'; +ij> -- common tests for read-only jarred database +select * from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +4 |spammer@ripoff.com |0 +5 |open@source.org |1 +ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS; +E_MAIL |2 +------------------------------------- +bill@ruletheworld.com |0 +penguin@antartic.com |0 +big@blue.com |0 +spammer@ripoff.com |0 +open@source.org |1 +ij> insert into EMC.CONTACTS values(3, 'no@is_read_only.gov', NULL); +ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. +ij> CALL EMC.ADDCONTACT(3, 'really@is_read_only.gov'); +ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. +ij> -- same set as dcl.sql for reading resources +-- VALUES EMC.GETARTICLE('graduate.txt'); +-- VALUES EMC.GETARTICLE('/article/release.txt'); +-- VALUES EMC.GETARTICLE('/article/fred.txt'); +-- VALUES EMC.GETARTICLE('barney.txt'); +-- VALUES EMC.GETARTICLE('emc.class'); +-- VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class'); +-- signed +VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US +ij> -- not signed +VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> -- ensure that a read-only database automatically gets table locking +autocommit off; +ij> select * from EMC.CONTACTS WITH RR; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +4 |spammer@ripoff.com |0 +5 |open@source.org |1 +ij> select TYPE, MODE, TABLENAME from syscs_diag.lock_table ORDER BY 1,2,3; +TYPE |MODE|TABLENAME +------------------------------------------------------------------------------------------------------------------------------------------- +TABLE|S |CONTACTS +ij> disconnect; +ij> -- connect to database in jar file via classpath +-- should fail as it is not on the classpath yet. +connect 'jdbc:derby:classpath:db7' AS DB7CLF; +ERROR XJ004: Database 'classpath:db7' not found. +ij> -- create a class loader for this current thread +connect 'jdbc:derby:db1'; +ij> create procedure setDBContextClassLoader(JARNAME VARCHAR(20)) +LANGUAGE JAVA PARAMETER STYLE JAVA +NO SQL +EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.setDBContextClassLoader'; +0 rows inserted/updated/deleted +ij> call setDBContextClassLoader('ina.jar'); +0 rows inserted/updated/deleted +ij> disconnect; +ij> connect 'jdbc:derby:classpath:db7' AS DB7CL; +ij> run resource '/org/apache/derbyTesting/functionTests/tests/lang/dcl_readOnly.sql'; +ij> -- common tests for read-only jarred database +select * from EMC.CONTACTS; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +4 |spammer@ripoff.com |0 +5 |open@source.org |1 +ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS; +E_MAIL |2 +------------------------------------- +bill@ruletheworld.com |0 +penguin@antartic.com |0 +big@blue.com |0 +spammer@ripoff.com |0 +open@source.org |1 +ij> insert into EMC.CONTACTS values(3, 'no@is_read_only.gov', NULL); +ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. +ij> CALL EMC.ADDCONTACT(3, 'really@is_read_only.gov'); +ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. +ij> -- same set as dcl.sql for reading resources +-- VALUES EMC.GETARTICLE('graduate.txt'); +-- VALUES EMC.GETARTICLE('/article/release.txt'); +-- VALUES EMC.GETARTICLE('/article/fred.txt'); +-- VALUES EMC.GETARTICLE('barney.txt'); +-- VALUES EMC.GETARTICLE('emc.class'); +-- VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class'); +-- signed +VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US +ij> -- not signed +VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util'); +1 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +NULL +ij> -- ensure that a read-only database automatically gets table locking +autocommit off; +ij> select * from EMC.CONTACTS WITH RR; +ID |E_MAIL |OK +------------------------------------------------- +1 |bill@ruletheworld.com |NULL +2 |penguin@antartic.com |NULL +3 |big@blue.com |NULL +4 |spammer@ripoff.com |0 +5 |open@source.org |1 +ij> select TYPE, MODE, TABLENAME from syscs_diag.lock_table ORDER BY 1,2,3; +TYPE |MODE|TABLENAME +------------------------------------------------------------------------------------------------------------------------------------------- +TABLE|S |CONTACTS +ij> disconnect; +ij> Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/iepnegativetests.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/iepnegativetests.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/iepnegativetests.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/iepnegativetests.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,323 @@ +ij> --testing error cases for import/export +create schema iep; +0 rows inserted/updated/deleted +ij> create table iep.t1(a int); +0 rows inserted/updated/deleted +ij> insert into iep.t1 values(100) , (101) , (102) , (103) , (104) , (105) , (106); +7 rows inserted/updated/deleted +ij> --export error cases +--export can not create file +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extout/nodir/t1.dat' , + null, null, null) ; +ERROR XIE0I: An IOException occurred while writing data to the file. +ij> --export table not found +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NOTABLE' , 'extinout/t1.dat' , + null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Table 'IEP.NOTABLE' does not exist.' was thrown while evaluating an expression. +ERROR 42X05: Table 'IEP.NOTABLE' does not exist. +ij> ---export schema is not valid +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , 'extinout/t1.dat' , + null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Schema 'XXXX' does not exist' was thrown while evaluating an expression. +ERROR 42Y07: Schema 'XXXX' does not exist +ij> --export query is invalid (syntax error) +call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select from t1', + 'extinout/t1.dat' , null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Syntax error: Encountered "from" at line 1, column 8.' was thrown while evaluating an expression. +ERROR 42X01: Syntax error: Encountered "from" at line 1, column 8. +ij> --export codeset is invalid +call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.t1', + 'extinout/t1.dat' , null, null, 'NOSUCHCODESET') ; +ERROR XIE0I: An IOException occurred while writing data to the file. +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , 'extinout/t1.dat' , + null, null, null) ; +ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: Schema 'XXXX' does not exist' was thrown while evaluating an expression. +ERROR 42Y07: Schema 'XXXX' does not exist +ij> --export delimiter errror cases +--period can not be used as character ot column delimiter +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + null, '.', null) ; +ERROR XIE0K: The period was specified as a character string delimiter. +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + '.', null, null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> --same delimter can not be used as character and column delimters +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + ';', ';', null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> --space character can not be a delimiter +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + ' ', ';', null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + null, ' ', null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> --if emtry strinng is passed actual value delimiter should be space +--and the that should become a invalid delimiter +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + '', ';', null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + null, '', null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> --more than one character passed to the delimiters get truncated to one +--following one should give error because eventually '\' delimiter +--is used a both for char and col +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + '\a', '\', null) ; +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> --DO A VALID EXPORT AND IMPORT +set schema iep; +0 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + null, null, 'utf-8') ; +0 rows inserted/updated/deleted +ij> delete from t1 ; +7 rows inserted/updated/deleted +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'T1' , 'extinout/t1.dat' , + null, null, 'utf-8', 0) ; +0 rows inserted/updated/deleted +ij> select * from t1; +A +----------- +100 +101 +102 +103 +104 +105 +106 +ij> --import error cases +--import can not find input file +call SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'T1' , 'extin/nodir/t1.dat' , + null, null, null, 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. +ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. +ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. +ERROR XIE04: Data file not found: extin/nodir/t1.dat +ij> --import table not found +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NOTABLE' , 'extinout/t1.dat' , + null, null, null, 0) ; +ERROR XIE0M: Table 'IEP.NOTABLE' does not exist. +ij> --import schema is not valid +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('XXXX', 'T1' , 'extinout/t1.dat' , + null, null, null, 0) ; +ERROR XIE0M: Table 'XXXX.T1' does not exist. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + null, null, 'INCORRECTCODESET', 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. +ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. +ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. +ERROR XJ001: Java exception: 'java.io.UnsupportedEncodingException: INCORRECTCODESET'. +ij> --check import with invalid delimiter usage +--if emtry strinng is passed actual value delimiter should be space +--and the that should become a invalid delimiter +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + '', ';', null, 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. +ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. +ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + null, '', null, 0) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. +ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. +ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> --same delimter can not be used as character and column delimters +call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , + ';', ';', null, 1) ; +ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. +ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. +ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. +ERROR XIE0J: A delimiter is not valid or is used more than once. +ij> autocommit off; +ij> create table v1(a int) ; +0 rows inserted/updated/deleted +ij> declare global temporary table session.temp1(c1 int) on commit preserve rows not logged; +0 rows inserted/updated/deleted +ij> insert into session.temp1 values(1) , (2) , (3) , (4) , (5) , (6); +6 rows inserted/updated/deleted +ij> select * from session.temp1; +C1 +----------- +1 +2 +3 +4 +5 +6 +ij> --export to from a temporary table +call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('SESSION', 'TEMP1' , 'extinout/temp1.dat' , + null, null, null) ; +0 rows inserted/updated/deleted +ij> -- because temporary table has on commit preserve rows, commit issued by export will not delete data from the temp table. +select * from session.temp1; +C1 +----------- +1 +2 +3 +4 +5 +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 'java.sql.SQLSyntaxErrorException: 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 'java.sql.SQLSyntaxErrorException: 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 'java.sql.SQLSyntaxErrorException: 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 'java.sql.SQLSyntaxErrorException: '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 'wombat' as c1; +ij(C1)> connect '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 'java.sql.SQLException: 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>