Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 76130 invoked from network); 26 Apr 2007 20:41:42 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 26 Apr 2007 20:41:42 -0000 Received: (qmail 42097 invoked by uid 500); 26 Apr 2007 20:41:49 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 42073 invoked by uid 500); 26 Apr 2007 20:41:49 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 42055 invoked by uid 99); 26 Apr 2007 20:41:49 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 26 Apr 2007 13:41:48 -0700 X-ASF-Spam-Status: No, hits=-99.5 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 26 Apr 2007 13:41:39 -0700 Received: by eris.apache.org (Postfix, from userid 65534) id A57821A983E; Thu, 26 Apr 2007 13:41:19 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r532853 [2/2] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ master/DerbyNet/ master/DerbyNet/ibm14/ master/DerbyNet/ibm15/ master/DerbyNet/jdk14/ master/DerbyNet/jdk15/ master/DerbyNet/jdk16/ master/D... Date: Thu, 26 Apr 2007 20:41:18 -0000 To: derby-commits@db.apache.org From: fuzzylogic@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20070426204119.A57821A983E@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java?view=auto&rev=532853 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java Thu Apr 26 13:41:16 2007 @@ -0,0 +1,2273 @@ +package org.apache.derbyTesting.functionTests.tests.tools; + +import java.sql.CallableStatement; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLWarning; +import java.sql.Statement; + +import org.apache.derbyTesting.junit.BaseJDBCTestCase; +import org.apache.derbyTesting.junit.JDBC; +import org.apache.derbyTesting.junit.SupportFilesSetup; +import org.apache.derbyTesting.junit.TestConfiguration; + +import junit.framework.Test; +import junit.framework.TestSuite; + +/** + * Converted from ieptests.sql + * + */ +public final class ImportExportProcedureTest extends BaseJDBCTestCase { + + /** + * Public constructor required for running test as standalone JUnit. + */ + public ImportExportProcedureTest(String name) + { + super(name); + } + + public static Test suite() + { + TestSuite suite = new TestSuite("ImportExportProcedureTest"); + suite.addTest(TestConfiguration.defaultSuite(ImportExportProcedureTest.class)); + return new SupportFilesSetup(suite, new String[] { + "functionTests/testData/ImportExport/db2ttypes.del", + "functionTests/testData/ImportExport/mixednl.del", + "functionTests/testData/ImportExport/position_info.del" + }); + } + + public void testImportExportProcedures() throws Exception + { + ResultSet rs = null; + + CallableStatement cSt; + Statement st = createStatement(); + + String [][] expRS; + String [] expColNames; + + Connection conn = getConnection(); + + st.executeUpdate( + "create table ex_emp(id int , name char(7) , skills " + + "varchar(200), salary decimal(10,2)) "); + + //table used for import + + st.executeUpdate( + "create table imp_emp(id int , name char(7), skills " + + "varchar(200), salary decimal(10,2)) "); + + //After an export from ex_emp and import to imp_emp both + // tables should havesame data.double delimter cases with + // default character delimter "field seperator character + // inside a double delimited string as first line + + st.executeUpdate( + "insert into ex_emp values(99, 'smith' , " + + "'tennis\"p,l,ayer\"', 190.55) "); + + // Perform Export: + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' " + + ", 'extinout/emp.dat' , null, null, null) "); + assertUpdateCount(cSt, 0); + + // Perform Import + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' " + + ", 'extinout/emp.dat' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + st.executeUpdate( + " insert into ex_emp values(100, 'smith' , " + + "'tennis\"player\"', 190.55) "); + + st.executeUpdate( + " insert into ex_emp values(101, 'smith' , " + + "'tennis\"player', 190.55) "); + + st.executeUpdate( + " insert into ex_emp values(102, 'smith' , " + + "'\"tennis\"player', 190.55) "); + + st.executeUpdate( + " insert into ex_emp values(103, 'smith' , " + + "'\"tennis\"player\"', 190.55) "); + + st.executeUpdate( + " insert into ex_emp values(104, 'smith' , " + + "'\"tennis\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"" + + "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"player\"', null) "); + + //empty string + + st.executeUpdate( + "insert into ex_emp values(105, 'smith' , '\"\"', 190.55) "); + + //just delimeter inside + + st.executeUpdate( + "insert into ex_emp values(106, 'smith' , " + + "'\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"', 190.55)"); + + //null value + + st.executeUpdate( + "insert into ex_emp values(107, 'smith\"' , null, 190.55) "); + + //all values are nulls + + st.executeUpdate( + "insert into ex_emp values(108, null , null, null) "); + + // Perform Export: + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' " + + ", 'extinout/emp.dat' , null, null, null) "); + assertUpdateCount(cSt, 0); + + // Perform Import + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' " + + ", 'extinout/emp.dat' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from ex_emp"); + + expColNames = new String [] {"ID", "NAME", "SKILLS", "SALARY"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"99", "smith", "tennis\"p,l,ayer\"", "190.55"}, + {"100", "smith", "tennis\"player\"", "190.55"}, + {"101", "smith", "tennis\"player", "190.55"}, + {"102", "smith", "\"tennis\"player", "190.55"}, + {"103", "smith", "\"tennis\"player\"", "190.55"}, + {"104", "smith", "\"tennis\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"" + + "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"player\"", null}, + {"105", "smith", "\"\"", "190.55"}, + {"106", "smith", "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"", "190.55"}, + {"107", "smith\"", null, "190.55"}, + {"108", null, null, null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + rs = st.executeQuery( + " select * from imp_emp"); + + expColNames = new String [] {"ID", "NAME", "SKILLS", "SALARY"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"99", "smith", "tennis\"p,l,ayer\"", "190.55"}, + {"99", "smith", "tennis\"p,l,ayer\"", "190.55"}, + {"100", "smith", "tennis\"player\"", "190.55"}, + {"101", "smith", "tennis\"player", "190.55"}, + {"102", "smith", "\"tennis\"player", "190.55"}, + {"103", "smith", "\"tennis\"player\"", "190.55"}, + {"104", "smith", "\"tennis\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"" + + "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"player\"", null}, + {"105", "smith", "\"\"", "190.55"}, + {"106", "smith", "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"", "190.55"}, + {"107", "smith\"", null, "190.55"}, + {"108", null, null, null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //checking query + + rs = st.executeQuery( + "select count(*) from imp_emp, ex_emp where " + + "ex_emp.id = imp_emp.id and " + + "(ex_emp.skills=imp_emp.skills or (ex_emp.skills is " + + "NULL and imp_emp.skills is NULL))"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"11"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 7, + " delete from imp_emp where id < 105"); + + //export from ex_emp using the a query only rows that got + // deleted in imp_emp + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from " + + "ex_emp where id < 105', 'extinout/emp.dat' , null, " + + "null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'IMP_EMP' , 'extinout/emp.dat' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + //checking query + + rs = st.executeQuery( + "select count(*) from imp_emp, ex_emp where " + + "ex_emp.id = imp_emp.id and " + + "(ex_emp.skills=imp_emp.skills or (ex_emp.skills is " + + "NULL and imp_emp.skills is NULL))"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"10"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //export the columns in different column order than in the + // table. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select name , " + + "salary , skills, id from ex_emp where id < 105', " + + "'extinout/emp.dat' , null, null, null) "); + assertUpdateCount(cSt, 0); + + // import them in to a with order different than in the table + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' " + + ",'NAME, SALARY, SKILLS, ID', null, " + + "'extinout/emp.dat', null, null, null, 1) "); + assertUpdateCount(cSt, 0); + + //check query + + rs = st.executeQuery( + "select count(*) from imp_emp, ex_emp where " + + "ex_emp.id = imp_emp.id and " + + "(ex_emp.skills=imp_emp.skills or (ex_emp.skills is " + + "NULL and imp_emp.skills is NULL))"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + // do import replace into the table with table order but + // using column indexes + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' " + + ",null, '4, 1, 3, 2', 'extinout/emp.dat', null, " + + "null, null, 1) "); + assertUpdateCount(cSt, 0); + + //check query + + rs = st.executeQuery( + "select count(*) from imp_emp, ex_emp where " + + "ex_emp.id = imp_emp.id and " + + "(ex_emp.skills=imp_emp.skills or (ex_emp.skills is " + + "NULL and imp_emp.skills is NULL))"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //replace using insert column names and column indexes + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' " + + ",'SALARY, ID, SKILLS, NAME', '2, 4, 3, 1', " + + "'extinout/emp.dat', null, null, null, 1) "); + assertUpdateCount(cSt, 0); + + //check query + + rs = st.executeQuery( + "select count(*) from imp_emp, ex_emp where " + + "ex_emp.id = imp_emp.id and " + + "(ex_emp.skills=imp_emp.skills or (ex_emp.skills is " + + "NULL and imp_emp.skills is NULL))"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //-testing with different delimiters single quote(') as + // character delimiter + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' " + + ", 'extinout/emp.dat' , null, '''', null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'IMP_EMP' , 'extinout/emp.dat' , null, '''', null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from imp_emp "); + + expColNames = new String [] {"ID", "NAME", "SKILLS", "SALARY"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"99", "smith", "tennis\"p,l,ayer\"", "190.55"}, + {"100", "smith", "tennis\"player\"", "190.55"}, + {"101", "smith", "tennis\"player", "190.55"}, + {"102", "smith", "\"tennis\"player", "190.55"}, + {"103", "smith", "\"tennis\"player\"", "190.55"}, + {"104", "smith", "\"tennis\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"" + + "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"player\"", null}, + {"105", "smith", "\"\"", "190.55"}, + {"106", "smith", "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"", "190.55"}, + {"107", "smith\"", null, "190.55"}, + {"108", null, null, null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + // single quote(') as column delimiter + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' " + + ", 'extinout/emp.dat' , '''',null, null) "); + assertUpdateCount(cSt, 0); + + assertUpdateCount(st, 10, + " delete from imp_emp "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'IMP_EMP' , 'extinout/emp.dat' , '''', null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from imp_emp"); + + expColNames = new String [] {"ID", "NAME", "SKILLS", "SALARY"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"99", "smith", "tennis\"p,l,ayer\"", "190.55"}, + {"100", "smith", "tennis\"player\"", "190.55"}, + {"101", "smith", "tennis\"player", "190.55"}, + {"102", "smith", "\"tennis\"player", "190.55"}, + {"103", "smith", "\"tennis\"player\"", "190.55"}, + {"104", "smith", "\"tennis\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"" + + "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"player\"", null}, + {"105", "smith", "\"\"", "190.55"}, + {"106", "smith", "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"", "190.55"}, + {"107", "smith\"", null, "190.55"}, + {"108", null, null, null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' " + + ", 'extinout/emp.dat' , '*', '%', null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EX_EMP' " + + ", 'extinout/emp.dat' , '*', '%', null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from imp_emp "); + + expColNames = new String [] {"ID", "NAME", "SKILLS", "SALARY"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"99", "smith", "tennis\"p,l,ayer\"", "190.55"}, + {"100", "smith", "tennis\"player\"", "190.55"}, + {"101", "smith", "tennis\"player", "190.55"}, + {"102", "smith", "\"tennis\"player", "190.55"}, + {"103", "smith", "\"tennis\"player\"", "190.55"}, + {"104", "smith", "\"tennis\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"" + + "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"player\"", null}, + {"105", "smith", "\"\"", "190.55"}, + {"106", "smith", "\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"", "190.55"}, + {"107", "smith\"", null, "190.55"}, + {"108", null, null, null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //cases for identity columnscreate table emp1(id int + // generated always as identity (start with 100), name + // char(7), skills varchar(200), salary + // decimal(10,2),skills varchar(200))check import export + // with real and double that can not be explictitlycasted + // from VARCHAR type . + + st.executeUpdate( + "create table noncast(c1 double , c2 real ) "); + + st.executeUpdate( + " insert into noncast values(1.5 , 6.7 ) "); + + st.executeUpdate( + " insert into noncast values(2.5 , 8.999) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , " + + "'NONCAST' , 'extinout/noncast.dat' , null , null , null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'NONCAST' , 'extinout/noncast.dat' , null , null , " + + "null , 0) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NONCAST', " + + "'C2 , C1' , '2, 1' , 'extinout/noncast.dat' , null " + + ", null , null , 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from noncast "); + + expColNames = new String [] {"C1", "C2"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1.5", "6.7"}, + {"2.5", "8.999"}, + {"1.5", "6.7"}, + {"2.5", "8.999"}, + {"1.5", "6.7"}, + {"2.5", "8.999"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //check import/export of time types + + st.executeUpdate( + "CREATE TABLE TTYPES(DATETYPE DATE, TIMETYPE TIME, " + + "TSTAMPTYPE TIMESTAMP )"); + + st.executeUpdate( + " insert into ttypes values('1999-09-09' , " + + "'12:15:19' , '1999-09-09 11:11:11')"); + + st.executeUpdate( + " insert into ttypes values('2999-12-01' , " + + "'13:16:10' , '2999-09-09 11:12:11')"); + + st.executeUpdate( + " insert into ttypes values('3000-11-02' , " + + "'14:17:21' , '4999-09-09 11:13:11')"); + + st.executeUpdate( + " insert into ttypes values('2004-04-03' , " + + "'15:18:31' , '2004-09-09 11:14:11')"); + + st.executeUpdate( + " insert into ttypes values(null , null , null)"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'TTYPES' " + + ", 'extinout/ttypes.del' , null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TTYPES' " + + ", 'extinout/ttypes.del' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from ttypes"); + + expColNames = new String [] {"DATETYPE", "TIMETYPE", "TSTAMPTYPE"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1999-09-09", "12:15:19", "1999-09-09 11:11:11.0"}, + {"2999-12-01", "13:16:10", "2999-09-09 11:12:11.0"}, + {"3000-11-02", "14:17:21", "4999-09-09 11:13:11.0"}, + {"2004-04-03", "15:18:31", "2004-09-09 11:14:11.0"}, + {null, null, null}, + {"1999-09-09", "12:15:19", "1999-09-09 11:11:11.0"}, + {"2999-12-01", "13:16:10", "2999-09-09 11:12:11.0"}, + {"3000-11-02", "14:17:21", "4999-09-09 11:13:11.0"}, + {"2004-04-03", "15:18:31", "2004-09-09 11:14:11.0"}, + {null, null, null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //-Import should commit on success and rollback on any + // failures + + conn.setAutoCommit(false); + + st.executeUpdate( + " create table t1(a int ) "); + + st.executeUpdate( + " insert into t1 values(1) "); + + st.executeUpdate( + " insert into t1 values(2) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , " + + "'extinout/t1.del' , null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , " + + "'extinout/t1.del' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + //above import should have committed , following rollback + // should be a noop. + + conn.rollback(); + + rs = st.executeQuery( + " select * from t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + //file not found error should rollback + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , " + + "'extinout/nofile.del' , null, null, null, 0) "); + assertStatementError("38000", cSt); + + conn.commit(); + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + //table not found error should issue a implicit rollback + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' " + + ", 'extinout/t1.del' , null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + conn.commit(); + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 4, + " delete from t1"); + + //-check commit/rollback with replace options using + + st.executeUpdate( + "insert into t1 values(1) "); + + st.executeUpdate( + " insert into t1 values(2) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , " + + "'extinout/t1.del' , null, null, null) "); + assertUpdateCount(cSt, 0); + + //above export should have a commit.rollback below should + // be a noop + + conn.rollback(); + + rs = st.executeQuery( + " select * from t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , " + + "'extinout/t1.del' , null, null, null, 1) "); + assertUpdateCount(cSt, 0); + + //above import should have committed , following rollback + // should be a noop. + + conn.rollback(); + + rs = st.executeQuery( + " select * from t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + //file not found error should rollback + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , " + + "'extinout/nofile.del' , null, null, null, 1) "); + assertStatementError("38000", cSt); + + conn.commit(); + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + //table not found error should issue a implicit rollback + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' " + + ", 'extinout/t1.del' , null, null, null, 1) "); + assertStatementError("XIE0M", cSt); + + conn.commit(); + //-check IMPORT_DATA calls commit/rollback + + rs = st.executeQuery( + "select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 2, + " delete from t1"); + + //-check commit/rollback with replace options using + + st.executeUpdate( + "insert into t1 values(1) "); + + st.executeUpdate( + " insert into t1 values(2) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , " + + "'extinout/t1.del' , null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' " + + ", '1' , 'extinout/t1.del' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + //above import should have committed , following rollback + // should be a noop. + + conn.rollback(); + + rs = st.executeQuery( + " select * from t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + //file not found error should rollback + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , " + + "'1' , 'extinout/nofile.del' , null, null, null, 0) "); + assertStatementError("38000", cSt); + + conn.commit(); + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + //table not found error should issue a implicit rollback + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NOTABLE' , " + + "'A' , '1', 'extinout/t1.del' , null, null, null, 1) "); + assertStatementError("XIE0M", cSt); + + conn.commit(); + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + conn.setAutoCommit(true); + + //make sure commit import code is ok in autcommit mode. + + st.executeUpdate( + "insert into t1 values(3) "); + + st.executeUpdate( + " insert into t1 values(4) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' " + + ", '1' , 'extinout/t1.del' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"}, + {"3"}, + {"4"}, + {"1"}, + {"2"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " insert into t1 values(5) "); + + st.executeUpdate( + " insert into t1 values(6) "); + + //following import will back , but should not have any + // impact on inserts + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , " + + "'1' , 'extinout/nofile.del' , null, null, null, 0) "); + assertStatementError("38000", cSt); + + rs = st.executeQuery( + " select * from t1 "); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"1"}, + {"2"}, + {"3"}, + {"4"}, + {"1"}, + {"2"}, + {"5"}, + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //END IMPORT COMMIT/ROLLBACK TESTSING-all types supported + // by Derby import/export + + st.executeUpdate( + "create table alltypes(chartype char(20) , " + + "biginttype bigint , datetype date , decimaltype " + + "decimal(10,5) , doubletype double , inttype integer " + + ", lvartype long varchar , realtype real , sminttype " + + "smallint , timetype time , tstamptype timestamp , " + + "vartype varchar(50))"); + + st.executeUpdate( + " insert into alltypes values('chartype string' , " + + "9223372036854775807, '1993-10-29' , 12345.54321, " + + "10E307, 2147483647, 'long varchar testing', 10E3, " + + "32767, '09.39.43', '2004-09-09 11:14:11', " + + "'varchar testing')"); + + st.executeUpdate( + " insert into alltypes values('chartype string' , " + + "-9223372036854775808, '1993-10-29' , 0.0, -10E307, " + + "-2147483647, 'long varchar testing', -10E3, 32767, " + + "'09.39.43', '2004-09-09 11:14:11', " + + "'varchar testing')"); + + st.executeUpdate( + " insert into alltypes values('\"chartype\" string' " + + ", 9223372036854775807, '1993-10-29' , -12345.54321, " + + "10E307, 2147483647, 'long \"varchar\" testing', " + + "10E3, 32767, '09.39.43', " + + "'2004-09-09 11:14:11', '\"varchar\" testing')"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, " + + "'ALLTYPES' , 'extinout/alltypes.del' , null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'ALLTYPES' , 'extinout/alltypes.del' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from alltypes "); + + expColNames = new String [] {"CHARTYPE", "BIGINTTYPE", "DATETYPE", "DECIMALTYPE", "DOUBLETYPE", "INTTYPE", "LVARTYPE", "REALTYPE", "SMINTTYPE", "TIMETYPE", "TSTAMPTYPE", "VARTYPE"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"chartype string", "9223372036854775807", "1993-10-29", "12345.54321", "1.0E308", "2147483647", "long varchar testing", "10000.0", "32767", "09:39:43", "2004-09-09 11:14:11.0", "varchar testing"}, + {"chartype string", "-9223372036854775808", "1993-10-29", "0.00000", "-1.0E308", "-2147483647", "long varchar testing", "-10000.0", "32767", "09:39:43", "2004-09-09 11:14:11.0", "varchar testing"}, + {"\"chartype\" string", "9223372036854775807", "1993-10-29", "-12345.54321", "1.0E308", "2147483647", "long \"varchar\" testing", "10000.0", "32767", "09:39:43", "2004-09-09 11:14:11.0", "\"varchar\" testing"}, + {"chartype string", "9223372036854775807", "1993-10-29", "12345.54321", "1.0E308", "2147483647", "long varchar testing", "10000.0", "32767", "09:39:43", "2004-09-09 11:14:11.0", "varchar testing"}, + {"chartype string", "-9223372036854775808", "1993-10-29", "0.00000", "-1.0E308", "-2147483647", "long varchar testing", "-10000.0", "32767", "09:39:43", "2004-09-09 11:14:11.0", "varchar testing"}, + {"\"chartype\" string", "9223372036854775807", "1993-10-29", "-12345.54321", "1.0E308", "2147483647", "long \"varchar\" testing", "10000.0", "32767", "09:39:43", "2004-09-09 11:14:11.0", "\"varchar\" testing"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 6, + " delete from alltypes"); + + //import should work with trigger enabled on append and + // should not work on replace + + st.executeUpdate( + "create table test1(a char(20)) "); + + st.executeUpdate( + " create trigger trig_import after INSERT on " + + "alltypes referencing new as newrow for each row " + + "insert into test1 values(newrow.chartype)"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'ALLTYPES' , 'extinout/alltypes.del' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select count(*) from alltypes "); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"3"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + rs = st.executeQuery( + " select * from test1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"chartype string"}, + {"chartype string"}, + {"\"chartype\" string"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 3, + " delete from alltypes"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, " + + "'ALLTYPES' , 'extinout/alltypes.del' , null, null, null, 1) "); + assertStatementError("38000", cSt); + + rs = st.executeQuery( + " select count(*) from alltypes"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"0"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " drop trigger trig_import"); + + st.executeUpdate( + " drop table test1"); + + //test importing to identity columns + + st.executeUpdate( + "create table table1(c1 char(30), c2 int generated " + + "always as identity, c3 real, c4 char(1))"); + + st.executeUpdate( + " create table table2(c1 char(30), c2 int, c3 real, " + + "c4 char(1))"); + + st.executeUpdate( + " insert into table2 values('Robert',100, 45.2, 'J')"); + + st.executeUpdate( + " insert into table2 values('Mike',101, 76.9, 'K')"); + + st.executeUpdate( + " insert into table2 values('Leo',102, 23.4, 'I')"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select " + + "c1,c3,c4 from table2' , 'extinout/import.del' , " + + "null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'TABLE1', " + + "'C1,C3,C4' , null, 'extinout/import.del',null, null,null,0)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from table1"); + + expColNames = new String [] {"C1", "C2", "C3", "C4"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"Robert", "1", "45.2", "J"}, + {"Mike", "2", "76.9", "K"}, + {"Leo", "3", "23.4", "I"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 3, + " delete from table1"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' " + + ", 'extinout/import.del', null, null, null) "); + assertUpdateCount(cSt, 0); + + //following import should fail becuase of inserting into + // identity column. + + cSt = prepareCall( + "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', " + + "'extinout/import.del',null, null, null,1)"); + assertStatementError("38000", cSt); + + //following import should be succesful + + cSt = prepareCall( + "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'TABLE1', " + + "'C1,C3,C4' , '1,3,4', 'extinout/import.del',null, " + + "null, null,1)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from table1"); + + expColNames = new String [] {"C1", "C2", "C3", "C4"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"Robert", "1", "45.2", "J"}, + {"Mike", "2", "76.9", "K"}, + {"Leo", "3", "23.4", "I"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 3, + " update table2 set c2=null"); + + //check null values import to identity columns should also + // fail + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' " + + ", 'extinout/import.del' , null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', " + + "'extinout/import.del',null, null, null,1)"); + assertStatementError("38000", cSt); + + rs = st.executeQuery( + " select * from table1"); + + expColNames = new String [] {"C1", "C2", "C3", "C4"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"Robert", "1", "45.2", "J"}, + {"Mike", "2", "76.9", "K"}, + {"Leo", "3", "23.4", "I"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //check that replace fails when there dependents and + // replaced datadoes not violate foreign key constraints. + + st.executeUpdate( + "create table parent(a int not null primary key)"); + + st.executeUpdate( + " insert into parent values (1) , (2) , (3) , (4) "); + + st.executeUpdate( + " create table child(b int references parent(a))"); + + st.executeUpdate( + " insert into child values (1) , (2) , (3) , (4) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from " + + "parent where a < 3' , 'extinout/parent.del' , null, " + + "null, null) "); + assertUpdateCount(cSt, 0); + + //replace should fail because of dependent table + + cSt = prepareCall( + "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'PARENT', " + + "'extinout/parent.del',null, null, null,1)"); + assertStatementError("XIE0R", cSt); + + rs = st.executeQuery( + " select * from parent"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"3"}, + {"4"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //-test with a file which has a differen records + // seperators (\n, \r , \r\n) + + st.executeUpdate( + "create table nt1( a int , b char(30))"); + + cSt = prepareCall( + " CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'NT1', " + + "'extin/mixednl.del',null, null, 'UTF-8',0)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from nt1"); + + expColNames = new String [] {"A", "B"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"0", "XXXXXX0"}, + {"1", "XXXXXX1"}, + {"2", "XXXXXX2"}, + {"3", "XXXXXX3"}, + {"4", "XXXXXX4"}, + {"5", "YYYYY5"}, + {"6", "YYYYY6"}, + {"7", "YYYYY7"}, + {"8", "YYYYY8"}, + {"9", "YYYYY9"}, + {"10", "ZZZZZZ10"}, + {"11", "ZZZZZZ11"}, + {"12", "ZZZZZZ12"}, + {"13", "ZZZZZZ13"}, + {"14", "ZZZZZZ14"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " drop table nt1 "); + + //test case for bug 5977;(with lot of text data) + + st.executeUpdate( + "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) )"); + + cSt = prepareCall( + " CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', " + + "'POSITION_INFO', 'extin/position_info.del', null, " + + "null, 'US-ASCII', 1)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select count(*) from position_info "); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"680"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + rs = st.executeQuery( + " select detail_description from position_info where " + + "position_code='AG1000'"); + + expColNames = new String [] {"DETAIL_DESCRIPTION"}; + JDBC.assertColumnNames(rs, expColNames); + + rs.next(); + String expected = rs.getString(1); + assertTrue(expected.startsWith("Essential Duties and Responsibilities (include")); + + cSt = prepareCall( + " CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', " + + "'POSITION_INFO', 'extinout/pinfo.del', null, null, null)"); + assertUpdateCount(cSt, 0); + + assertUpdateCount(st, 680, + " delete from position_info"); + + cSt = prepareCall( + " CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', " + + "'POSITION_INFO', 'extinout/pinfo.del', null, null, null, 1)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select count(*) from position_info "); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"680"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + rs = st.executeQuery( + " select detail_description from position_info where " + + "position_code='AG1000'"); + + expColNames = new String [] {"DETAIL_DESCRIPTION"}; + JDBC.assertColumnNames(rs, expColNames); + + rs.next(); + expected = rs.getString(1); + assertTrue(expected.startsWith("Essential Duties and Responsibilities (include")); + + //test for autoincrement values + + cSt = prepareCall( + "CALL " + + "SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','e" + + "xtinout/autoinc.dat',null,null,null)"); + assertUpdateCount(cSt, 0); + + st.executeUpdate( + " create table dest_always(i int generated always as " + + "identity)"); + + st.executeUpdate( + " create table dest_by_default(i int generated by " + + "default as identity)"); + + cSt = prepareCall( + " CALL " + + "SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','e" + + "xtinout/autoinc.dat',null,null,null,0)"); + assertStatementError("38000", cSt); + + rs = st.executeQuery( + " select * from dest_always"); + + expColNames = new String [] {"I"}; + JDBC.assertColumnNames(rs, expColNames); + JDBC.assertDrainResults(rs, 0); + + cSt = prepareCall( + " CALL " + + "SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT" + + "','extinout/autoinc.dat',null,null,null,0)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from dest_by_default"); + + expColNames = new String [] {"I"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"3"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " drop table dest_always"); + + st.executeUpdate( + " drop table dest_by_default"); + + st.executeUpdate( + " create table dest_always(i int generated always as " + + "identity)"); + + st.executeUpdate( + " create table dest_by_default(i int generated by " + + "default as identity)"); + + cSt = prepareCall( + " CALL " + + "SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','e" + + "xtinout/autoinc.dat',null,null,null,1)"); + assertStatementError("38000", cSt); + + rs = st.executeQuery( + " select * from dest_always"); + + expColNames = new String [] {"I"}; + JDBC.assertColumnNames(rs, expColNames); + JDBC.assertDrainResults(rs, 0); + + cSt = prepareCall( + " CALL " + + "SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT" + + "','extinout/autoinc.dat',null,null,null,1)"); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from dest_by_default"); + + expColNames = new String [] {"I"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"3"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " drop table dest_always"); + + st.executeUpdate( + " drop table dest_by_default"); + + //test case for bug (DERBY-390)test import/export with + // reserved words as table Name, column Names ..etc. + + st.executeUpdate( + "create schema \"Group\""); + + st.executeUpdate( + " create table \"Group\".\"Order\"(\"select\" int, " + + "\"delete\" int, itemName char(20)) "); + + st.executeUpdate( + " insert into \"Group\".\"Order\" values(1, 2, 'memory') "); + + st.executeUpdate( + " insert into \"Group\".\"Order\" values(3, 4, 'disk') "); + + st.executeUpdate( + " 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. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('GROUP', " + + "'Order' , 'extinout/order.dat', null, null, null) "); + assertStatementError("38000", cSt); + + //following export should fail because table name is not + // matching the way it is defined in the quotes. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', " + + "'ORDER' , 'extinout/order.dat', null, null, null) "); + assertStatementError("38000", cSt); + + //following export should fail because of unquoted table + // name that is a reserved word. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from " + + "\"Group\".Order' , 'extinout/order.dat' , null , " + + "null , null ) "); + assertStatementError("38000", cSt); + + //following exports should pass. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', " + + "'Order' , 'extinout/order.dat', null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from " + + "\"Group\".\"Order\"' , 'extinout/order.dat' , " + + "null , null , null ) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select " + + "\"select\" , \"delete\" , itemName from " + + "\"Group\".\"Order\"' , 'extinout/order.dat' , " + + "null , null , null ) "); + assertUpdateCount(cSt, 0); + + //following import should fail because schema name is not + // matching the way it is defined using delimited quotes. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('GROUP', " + + "'Order' , 'extinout/order.dat', null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + //following import should fail because table name is not + // matching the way it is defined in the quotes. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', " + + "'ORDER' , 'extinout/order.dat', null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + //following import should fail because table name is not + // matching the way it is defined in the quotes. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'ORDER' " + + ", null , null , 'extinout/order.dat' , null , " + + "null , null, 1) "); + assertStatementError("XIE0M", cSt); + + //following import should fail because column name is not + // matching the way it is defined in the quotes. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' " + + ", 'DELETE, ITEMNAME' , '2, 3' , " + + "'extinout/order.dat' , null , null , null, 1) "); + assertStatementError("XIE08", cSt); + + //following import should fail because undelimited column + // name is not in upper case. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' " + + ", 'delete, itemName' , '2, 3' , " + + "'extinout/order.dat' , null , null , null, 1) "); + assertStatementError("XIE08", cSt); + + //following imports should pass + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', " + + "'Order' , 'extinout/order.dat', null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from \"Group\".\"Order\""); + + expColNames = new String [] {"select", "delete", "ITEMNAME"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1", "2", "memory"}, + {"3", "4", "disk"}, + {"5", "6", "mouse"}, + {"1", "2", "memory"}, + {"3", "4", "disk"}, + {"5", "6", "mouse"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' " + + ", null , null , 'extinout/order.dat' , null , " + + "null , null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from \"Group\".\"Order\""); + + expColNames = new String [] {"select", "delete", "ITEMNAME"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1", "2", "memory"}, + {"3", "4", "disk"}, + {"5", "6", "mouse"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' " + + ", 'delete' , '2' , 'extinout/order.dat' , null " + + ", null , null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from \"Group\".\"Order\""); + + expColNames = new String [] {"select", "delete", "ITEMNAME"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {null, "2", null}, + {null, "4", null}, + {null, "6", null} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' " + + ", 'ITEMNAME, select, delete' , '3,2,1' , " + + "'extinout/order.dat' , null , null , null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from \"Group\".\"Order\""); + + expColNames = new String [] {"select", "delete", "ITEMNAME"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"2", "1", "memory"}, + {"4", "3", "disk"}, + {"6", "5", "mouse"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //-test undelimited names( All unquoted SQL identfiers + // should be passed in upper case). + + st.executeUpdate( + "create schema inventory"); + + st.executeUpdate( + " create table inventory.orderTable(id int, amount " + + "int, itemName char(20)) "); + + st.executeUpdate( + " insert into inventory.orderTable values(101, 5, 'pizza') "); + + st.executeUpdate( + " insert into inventory.orderTable values(102, 6, 'coke') "); + + st.executeUpdate( + " insert into inventory.orderTable values(103, 7, " + + "'break sticks') "); + + st.executeUpdate( + " insert into inventory.orderTable values(104, 8, " + + "'buffolo wings') "); + + //following export should fail because schema name is not + // in upper case. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('inventory', " + + "'ORDERTABLE' , 'extinout/order.dat', null, null, null) "); + assertStatementError("38000", cSt); + + //following export should fail because table name is not + // in upper case. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', " + + "'ordertable' , 'extinout/order.dat', null, null, null) "); + assertStatementError("38000", cSt); + + //following export should pass. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', " + + "'ORDERTABLE' , 'extinout/order.dat', null, null, null) "); + assertUpdateCount(cSt, 0); + + //following import should fail because schema name is not + // in upper case + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('inventory', " + + "'ORDERTABLE' , 'extinout/order.dat', null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + //following import should fail because table name is not + // in upper case. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('INVENTORY', " + + "'ordertable' , 'extinout/order.dat', null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + //following import should fail because table name is not + // in upper case . + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', " + + "'ordertable' , null , null , 'extinout/order.dat' " + + " , null , null , null, 1) "); + assertStatementError("XIE0M", cSt); + + //following import should fail because column name is not + // in upper case. + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', " + + "'ORDERTABLE' , 'amount, ITEMNAME' , '2, 3' , " + + "'extinout/order.dat' , null , null , null, 1) "); + assertStatementError("XIE08", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', " + + "'ORDERTABLE' , null , null , 'extinout/order.dat' " + + " , null , null , null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from inventory.orderTable"); + + expColNames = new String [] {"ID", "AMOUNT", "ITEMNAME"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"101", "5", "pizza"}, + {"102", "6", "coke"}, + {"103", "7", "break sticks"}, + {"104", "8", "buffolo wings"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', " + + "'ORDERTABLE' , 'ITEMNAME, ID, AMOUNT' , '3,2,1' , " + + "'extinout/order.dat' , null , null , null, 1) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from inventory.orderTable"); + + expColNames = new String [] {"ID", "AMOUNT", "ITEMNAME"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"5", "101", "pizza"}, + {"6", "102", "coke"}, + {"7", "103", "break sticks"}, + {"8", "104", "buffolo wings"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate( + " drop table inventory.orderTable"); + + //end derby-390 related test cases. + + getConnection().rollback(); + st.close(); + } + + /** + * Converted from iepnegativetests.sql + */ + public void testImportExportProcedureNegative() throws Exception + { + ResultSet rs = null; + + CallableStatement cSt; + Statement st = createStatement(); + + String [][] expRS; + String [] expColNames; + + st.executeUpdate( + "create schema iep"); + + st.executeUpdate( + " create table iep.t1(a int)"); + + st.executeUpdate( + " insert into iep.t1 values(100) , (101) , (102) , " + + "(103) , (104) , (105) , (106)"); + + //export error casesexport can not create file + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extout/nodir/t1.dat' , null, null, null) "); + assertStatementError("XIE0I", cSt); + + //export table not found + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', " + + "'NOTABLE' , 'extinout/t1.dat' , null, null, null) "); + assertStatementError("38000", cSt); + + //-export schema is not valid + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , " + + "'extinout/t1.dat' , null, null, null) "); + assertStatementError("38000", cSt); + + //export query is invalid (syntax error) + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select from " + + "t1', 'extinout/t1.dat' , null, null, null) "); + assertStatementError("38000", cSt); + + //export codeset is invalid + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from " + + "iep.t1', 'extinout/t1.dat' , null, null, 'NOSUCHCODESET') "); + assertStatementError("XIE0I", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , " + + "'extinout/t1.dat' , null, null, null) "); + assertStatementError("38000", cSt); + + //export delimiter errror casesperiod can not be used as + // character ot column delimiter + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , null, '.', null) "); + assertStatementError("XIE0K", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , '.', null, null) "); + assertStatementError("XIE0J", cSt); + + //same delimter can not be used as character and column + // delimters + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , ';', ';', null) "); + assertStatementError("XIE0J", cSt); + + //space character can not be a delimiter + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , ' ', ';', null) "); + assertStatementError("XIE0J", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , null, ' ', null) "); + assertStatementError("XIE0J", cSt); + + //if emtry strinng is passed actual value delimiter should + // be spaceand the that should become a invalid delimiter + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , '', ';', null) "); + assertStatementError("XIE0J", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , null, '', null) "); + assertStatementError("XIE0J", cSt); + + //more than one character passed to the delimiters get + // truncated to onefollowing one should give error because + // eventually '\' delimiteris used a both for char and col + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , '\\a', '\\', null) "); + assertStatementError("XIE0J", cSt); + + //DO A VALID EXPORT AND IMPORT + + st.executeUpdate( + "set schema iep"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , null, null, 'utf-8') "); + assertUpdateCount(cSt, 0); + + assertUpdateCount(st, 7, + " delete from t1 "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'T1' , " + + "'extinout/t1.dat' , null, null, 'utf-8', 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"100"}, + {"101"}, + {"102"}, + {"103"}, + {"104"}, + {"105"}, + {"106"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //import error casesimport can not find input file + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'T1' , " + + "'extin/nodir/t1.dat' , null, null, null, 0) "); + assertStatementError("38000", cSt); + + //import table not found + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', " + + "'NOTABLE' , 'extinout/t1.dat' , null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + //import schema is not valid + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('XXXX', 'T1' , " + + "'extinout/t1.dat' , null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , null, null, 'INCORRECTCODESET', 0) "); + assertStatementError("38000", cSt); + + //check import with invalid delimiter usageif emtry + // strinng is passed actual value delimiter should be + // spaceand the that should become a invalid delimiter + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , '', ';', null, 0) "); + assertStatementError("38000", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , null, '', null, 0) "); + assertStatementError("38000", cSt); + + //same delimter can not be used as character and column + // delimters + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , " + + "'extinout/t1.dat' , ';', ';', null, 1) "); + assertStatementError("38000", cSt); + + Connection conn = getConnection(); + conn.setAutoCommit(false); + + st.executeUpdate( + " create table v1(a int) "); + + assertUpdateCount(st, 0, + " declare global temporary table session.temp1(c1 " + + "int) on commit preserve rows not logged"); + + st.executeUpdate( + " insert into session.temp1 values(1) , (2) , (3) , " + + "(4) , (5) , (6)"); + + rs = st.executeQuery( + " select * from session.temp1"); + + expColNames = new String [] {"C1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"3"}, + {"4"}, + {"5"}, + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //export to from a temporary table + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('SESSION', " + + "'TEMP1' , 'extinout/temp1.dat' , null, null, null) "); + assertUpdateCount(cSt, 0); + + // because temporary table has on commit preserve rows, + // commit issued by export will not delete data from the + // temp table. + + rs = st.executeQuery( + "select * from session.temp1"); + + expColNames = new String [] {"C1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"3"}, + {"4"}, + {"5"}, + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //import back to a regualr table + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'V1' , " + + "'extinout/temp1.dat' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from v1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1"}, + {"2"}, + {"3"}, + {"4"}, + {"5"}, + {"6"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + conn.commit(); + //import to a temp table should fail with a table not + // found errror + + assertUpdateCount(st, 0, + "declare global temporary table session.temp2(c1 " + + "int) not logged"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SESSION', " + + "'TEMP2' , 'extinout/temp1.dat' , null, null, null, 0) "); + assertStatementError("XIE0M", cSt); + + assertStatementError("42X05", st, + " select * from session.temp2 "); + + conn.commit(); + st.executeUpdate( + " drop table v1"); + + conn.setAutoCommit(true); + + st.executeUpdate( + " create table t3(c1 int , c2 double , c3 decimal , " + + "c4 varchar(20) )"); + + st.executeUpdate( + " insert into t3 values(1 , 3.5 , 8.6 , 'test strings')"); + + st.executeUpdate( + " insert into t3 values(2 , 3.5 , 8.6 , 'test strings')"); + + st.executeUpdate( + " insert into t3 values(3 , 3.5 , 8.6 , 'test strings')"); + + st.executeUpdate( + " insert into t3 values(4 , 3.5 , 8.6 , 'test strings')"); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T3' , " + + "'extinout/t3.dat' , null, null, null) "); + assertUpdateCount(cSt, 0); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T3' , " + + "'extinout/t3.dat' , null, null, null, 0) "); + assertUpdateCount(cSt, 0); + + rs = st.executeQuery( + " select * from t3"); + + expColNames = new String [] {"C1", "C2", "C3", "C4"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"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"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + //import data column names are incorrect + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'X1, X2, X3, X4', null, 'extinout/t3.dat' , null, " + + "null, null, 0) "); + assertStatementError("XIE08", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'X1, X2, X3', '1,2,3,4', 'extinout/t3.dat' , null, " + + "null, null, 0) "); + assertStatementError("XIE08", cSt); + + //import data insert column names count < column indexes + // does not match + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'C1, C2, C3', '1,2,3,4', 'extinout/t3.dat' , null, " + + "null, null, 0) "); + assertUpdateCount(cSt, 0); + + //import data column indexes count > insert columns count + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'C1, C2, C3,C4', '1,2', 'extinout/t3.dat' , null, " + + "null, null, 0) "); + assertStatementError("38000", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "null, '11,22,12,24', 'extinout/t3.dat' , null, " + + "null, null, 0) "); + assertStatementError("38000", cSt); + + //repeat the above type cases with empty file and minor + // variation to paramters + + assertUpdateCount(st, 12, + "delete from t3 "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T3' , " + + "'extinout/t3.dat' , ';', '^', 'utf-16') "); + assertUpdateCount(cSt, 0); + + //import data column names are incorrect + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'X1, X2, X3, X4', null, 'extinout/t3.dat' , ';', " + + "'^', 'utf-16', 1) "); + assertStatementError("XIE08", cSt); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'X1, X2, X3', '1,2,3,4', 'extinout/t3.dat' , ';', " + + "'^', 'utf-16', 1) "); + assertStatementError("XIE08", cSt); + + //import data insert column names count < column indexes + // does not match + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "'C1, C2, C3', null, 'extinout/t3.dat' , ';', '^', " + + "'utf-16', 1) "); + assertUpdateCount(cSt, 0); + + //import data column indexes count > insert columns count + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "null, '1,2', 'extinout/t3.dat' , ';', '^', 'utf-16', 1) "); + assertStatementError("38000", cSt); + + //specify column indexes that are not there in the file + // that is being imported + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , " + + "null, '11,22,12,24', 'extinout/t3.dat' , ';', '^', " + + "'utf-16', 1) "); + assertUpdateCount(cSt, 0); + + //import to a system table shoud fail + + cSt = prepareCall( + "call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SYS', " + + "'SYSTABLES' , 'extinout/t3.dat' , ';', '^', 'utf-16', 1) "); + assertStatementError("38000", cSt); + + //import should aquire a lock on the table + + st.executeUpdate( + "create table parent(a int not null primary key)"); + + st.executeUpdate( + " insert into parent values (1) , (2) , (3) , (4) "); + + cSt = prepareCall( + " call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from " + + "parent where a < 3' , 'extinout/parent.del' , null, " + + "null, null) "); + assertUpdateCount(cSt, 0); + + Connection c1 = openDefaultConnection(); + Statement st_c1 = c1.createStatement(); + c1.setAutoCommit(false); + st_c1.executeUpdate("lock table iep.parent in share mode"); + + conn.setAutoCommit(false); + + cSt = prepareCall( + "call " + + "SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks." + + "waitTimeout', '5')"); + assertUpdateCount(cSt, 0); + + assertStatementError("38000", st, "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'PARENT', 'extinout/parent.del',null, null, null,1)"); + + c1.rollback(); + c1.close(); + conn.setAutoCommit(true); + + getConnection().rollback(); + st.close(); + } +} \ No newline at end of file Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java?view=diff&rev=532853&r1=532852&r2=532853 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java Thu Apr 26 13:41:16 2007 @@ -49,6 +49,7 @@ suite.addTest(ImportExportTest.suite()); suite.addTest(ImportExportBinaryDataTest.suite()); suite.addTest(ImportExportLobTest.suite()); + suite.addTest(ImportExportProcedureTest.suite()); // SysinfoAPITest currently fails when run against jars, so is // disabled. Only the first jar file on the classpath properly Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/SQLToJUnit.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/SQLToJUnit.java?view=diff&rev=532853&r1=532852&r2=532853 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/SQLToJUnit.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/SQLToJUnit.java Thu Apr 26 13:41:16 2007 @@ -1214,13 +1214,16 @@ } } else { targetBuf.append(nextline); - if (gotCommand) + if (gotCommand){ // multi-line command or comment, keep going till - // we get a semicolon + // we get a semicolon. Add a space to avoid glomming + // multiple trimmed strings together. + targetBuf.append(" "); continue; - else + } else{ // single line warning or error, finished break; + } } } }