db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fuzzylo...@apache.org
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 GMT
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;
+      			    }
         		}
         	}
         }



Mime
View raw message