/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.tools.ImportExportProcedureTest

   Licensed to the Apache Software Foundation (ASF) under one
   or more contributor license agreements.  See the NOTICE file
   distributed with this work for additional information
   regarding copyright ownership.  The ASF licenses this file
   to you under the Apache License, Version 2.0 (the
   "License"); you may not use this file except in compliance
   with the License.  You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing,
   software distributed under the License is distributed on an
   "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   KIND, either express or implied.  See the License for the
   specific language governing permissions and limitations
   under the License.

 */
package org.apache.derbyTesting.functionTests.tests.tools;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Converted from ieptests.sql
 *
 */
public class ImportExportProcedureTest extends BaseJDBCTestCase {

    private static final String INVALID_DELIMITER = "XIE0J";

    /**
     * Public constructor required for running test as standalone JUnit.
     */
    public ImportExportProcedureTest(String name)
    {
        super(name);
    }

    public static Test suite()
    {
        BaseTestSuite suite = new BaseTestSuite("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:

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");        

        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:
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");

        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");

        //DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");

        //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);
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");

        //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);
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");

        //-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);
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");

        // 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);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/emp.dat");
 
        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) ");
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/noncast.dat");
 
        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)");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/ttypes.del");

        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) ");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.del");

        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) ");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.del");

        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) ");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.del");

        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')");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/alltypes.del");

        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')");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/import.del");

        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");
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/import.del");
 
        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
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/import.del");
 
        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) ");
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/parent.del");

        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"));
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/pinfo.del");

        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"));
      
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/autoinc.dat");
	
        //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') ");
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
 
        //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);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
 
        //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);
        
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");

        //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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
        
        //following exports should pass.
        
        cSt = prepareCall(
            "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', "
            + "'Order' , 'extinout/order.dat', null, null, null) ");
        assertUpdateCount(cSt, 0);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
        
        cSt = prepareCall(
            " call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "
            + "\"Group\".\"Order\"' , 'extinout/order.dat' ,    "
            + "null , null , null ) ");
        assertUpdateCount(cSt, 0);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
        
        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') ");
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
 
        //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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
        
        //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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/order.dat");
        
        //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)");

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extout/nodir/t1.dat");
        
        //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);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
 
        //-export schema is not valid
        
        cSt = prepareCall(
            "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , "
            + "'extinout/t1.dat' , null, null, null) ");
        assertStatementError("38000", cSt);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
 
        //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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        //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);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
 
        cSt = prepareCall(
            " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , "
            + "'extinout/t1.dat' , null, null, null) ");
        assertStatementError("38000", cSt);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
 
        //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);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
 
        cSt = prepareCall(
            " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , "
            + "'extinout/t1.dat' , '.', null, null) ");
        assertStatementError("XIE0J", cSt);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        //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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        //space character can not be a delimiter
        
        cSt = prepareCall(
            "call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , "
            + "'extinout/t1.dat' , ' ', ';', null) ");
        assertStatementError("XIE0J", cSt);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        cSt = prepareCall(
            " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , "
            + "'extinout/t1.dat' , null, ' ', null) ");
        assertStatementError("XIE0J", cSt);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        //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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        cSt = prepareCall(
            " call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , "
            + "'extinout/t1.dat' , null, '', null) ");
        assertStatementError("XIE0J", cSt);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        //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' , '\\', '\\', null) ");
        assertStatementError("XIE0J", cSt);
                
        //DO A VALID EXPORT AND  IMPORT
        
        st.executeUpdate(
            "set schema iep");

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        
        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);
        
        //  DERBY-2925: need to delete existing files 
        SupportFilesSetup.deleteFile("extinout/t1.dat");
        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);

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extin/nodir/t1.dat");
        
        //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(INVALID_DELIMITER, cSt);
        
        cSt = prepareCall(
            " call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , "
            + "'extinout/t1.dat' , null, '', null, 0) ");
        assertStatementError(INVALID_DELIMITER, 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(INVALID_DELIMITER, 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);
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/temp1.dat");
 
        //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')");

	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t3.dat");

        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 ");
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/t3.dat");
 
        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) ");
       
	//DERBY-2925: need to delete existing files first.
        SupportFilesSetup.deleteFile("extinout/parent.del");
 
        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();
    }
}