DERBY-4555: Expand SYSCS_IMPORT_TABLE to accept CSV file with header lines
DERBY-6894: Enhance COLUMNINDEXES parsing for SYSCS_IMPORT_DATA
This patch was contributed by Danoja Dias (danojadias at gmail dot com)
The purpose of this patch is to allow the COLUMNINDEXES value to specify
columns in the input file by column header "name", as an alternate to
column index number. Column header names can be specified as double-quoted
strings, and you can mix-and-match indexes and names, so that
COLUMNINDEXES could be specified as:
1,3,"LastName","FirstName",7
During the preparation of the input file for import processing, the
new "readHeaders" method that is added to the Import class will,
if 'skip > 0', open the input file, read the first 'skip' number of lines,
parse the column structure, and construct an array of the column headers,
then close the input file and return the headers array.
Then, the COLUMNINDEXES value, if it contains any column names, rather
than column index numbers, is matched against the headers array to
determine which column(s) to use.
git-svn-id: https://svn.apache.org/repos/asf/db/derby/code/trunk@1752990 13f79535-47bb-0310-9956-ffa450edef68
diff --git a/java/engine/org/apache/derby/impl/load/ColumnInfo.java b/java/engine/org/apache/derby/impl/load/ColumnInfo.java
index a8af40f..97df55e 100644
--- a/java/engine/org/apache/derby/impl/load/ColumnInfo.java
+++ b/java/engine/org/apache/derby/impl/load/ColumnInfo.java
@@ -30,7 +30,9 @@
import java.sql.Types;
import java.util.*;
import org.apache.derby.iapi.jdbc.EngineConnection;
-
+import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.error.PublicAPI;
/**
*
* This class provides supportto create casting/conversions required to
@@ -56,6 +58,7 @@
private Connection conn;
private String tableName;
private String schemaName;
+ private String[] headerColumnNames;
private HashMap<String,String> udtClassNames;
/**
@@ -73,7 +76,8 @@
String tName,
String insertColumnList,
String vtiColumnIndexes,
- String vtiColumnPrefix)
+ String vtiColumnPrefix,
+ String[] headerColumnNames)
throws SQLException
{
@@ -84,7 +88,7 @@
udtClassNames = new HashMap<String,String>();
noOfColumns = 0;
this.conn = conn;
-
+ this.headerColumnNames=headerColumnNames;
if (sName == null) {
// Use the current schema if no schema is specified.
sName = ((EngineConnection) conn).getCurrentSchemaName();
@@ -133,9 +137,19 @@
StringTokenizer st = new StringTokenizer(vtiColumnIndexes, ",");
while (st.hasMoreTokens())
{
- String columnIndex = (st.nextToken()).trim();
- vtiColumnNames.add(vtiColumnPrefix + columnIndex);
- int cIndex = Integer.parseInt(columnIndex );
+ int cIndex;
+ String columnIndex = (st.nextToken()).trim();
+ if("\"".equals(columnIndex.substring(0,1))){
+
+ cIndex = readHeaders((columnIndex.replace('"', ' ')).trim());
+ vtiColumnNames.add(vtiColumnPrefix + cIndex);
+
+ }
+ else{
+
+ vtiColumnNames.add(vtiColumnPrefix + columnIndex);
+ cIndex = Integer.parseInt(columnIndex );
+ }
if(cIndex > expectedNumberOfCols )
expectedNumberOfCols= cIndex ;
}
@@ -154,6 +168,24 @@
}
}
+ //Check for matchng patterns of column names
+ private int readHeaders(String columnPattern)
+ throws SQLException
+ {
+ if(headerColumnNames!=null){
+
+ for(int i=0;i<headerColumnNames.length;i++){
+ if(headerColumnNames[i].equals(columnPattern)){
+ return i+1;
+ }
+ }
+
+ throw PublicAPI.wrapStandardException(StandardException.newException
+ (SQLState.LANG_INVALID_INPUT_COLUMN_NAME, columnPattern));
+ }
+ throw PublicAPI.wrapStandardException(StandardException.newException
+ (SQLState.LANG_INVALID_NUMBEROF_HEADER_LINES));
+ }
private boolean initializeColumnInfo(String columnPattern)
throws SQLException
diff --git a/java/engine/org/apache/derby/impl/load/Import.java b/java/engine/org/apache/derby/impl/load/Import.java
index 035e8f3..8267718 100644
--- a/java/engine/org/apache/derby/impl/load/Import.java
+++ b/java/engine/org/apache/derby/impl/load/Import.java
@@ -142,8 +142,12 @@
}
}
catch (StandardException se) { throw PublicAPI.wrapStandardException( se ); }
- if(extraArgs.length>0)
- skip=extraArgs[0];
+ if(extraArgs.length>0){
+ skip=extraArgs[0];
+ if(skip<0) throw PublicAPI.wrapStandardException(StandardException.newException
+ (SQLState.LANG_INVALID_NUMBEROF_HEADER_LINES));
+
+ }
else
skip=0;
@@ -197,8 +201,12 @@
}
}
catch (StandardException se) { throw PublicAPI.wrapStandardException( se ); }
- if(extraArgs.length>0)
- skip=extraArgs[0];
+ if(extraArgs.length>0){
+ skip=extraArgs[0];
+ if(skip<0) throw PublicAPI.wrapStandardException(StandardException.newException
+ (SQLState.LANG_INVALID_NUMBEROF_HEADER_LINES));
+
+ }
else
skip=0;
performImport(connection, schemaName, insertColumnList,columnIndexes,
@@ -243,7 +251,10 @@
ColumnInfo columnInfo = new ColumnInfo(connection , schemaName ,
tableName, insertColumnList,
- columnIndexes, COLUMNNAMEPREFIX);
+ columnIndexes, COLUMNNAMEPREFIX,readHeaders(inputFileName ,
+ characterDelimiter,
+ columnDelimiter,
+ codeset));
String columnTypeNames = null;
String udtClassNames = null;
@@ -343,6 +354,43 @@
_importers.remove( importCounter );
}
}
+
+ //Read the header lines to get column names to identify columns by name
+ private static String[] readHeaders(String inputFileName ,String characterDelimiter,String columnDelimiter,String codeset)
+ throws SQLException
+ {
+ try {
+ if(skip>0){
+ ControlInfo controlFR = new ControlInfo();
+ controlFR.setControlProperties(characterDelimiter,columnDelimiter, codeset);
+
+ ImportReadData importReadData = new ImportReadData(inputFileName, controlFR, (short)0);
+
+ String[] fullColumnName = new String[importReadData.numberOfColumns];
+ String[] temp = new String[importReadData.numberOfColumns];
+ for(int i=0; i< skip;i++){
+ importReadData.readNextRow(temp);
+ for(int j=0;j < importReadData.numberOfColumns;j++){
+ if(i==0)
+ fullColumnName[j]=temp[j];
+ else
+ {
+ if(temp[j]!=null)
+ fullColumnName[j]=fullColumnName[j] + " " + temp[j];
+
+ }
+ }
+ }
+ return fullColumnName;
+ }
+ return null;
+
+ }
+ catch(Exception e){
+
+ throw LoadError.unexpectedError(e);
+ }
+ }
/** virtual method from the abstract class
* @exception Exception on error
diff --git a/java/engine/org/apache/derby/impl/load/ImportReadData.java b/java/engine/org/apache/derby/impl/load/ImportReadData.java
index 50d86c8..c2a7df6 100644
--- a/java/engine/org/apache/derby/impl/load/ImportReadData.java
+++ b/java/engine/org/apache/derby/impl/load/ImportReadData.java
@@ -173,6 +173,19 @@
readNextToken(recordSeparator, 0, recordSeparatorLength, true);
}
+
+ /**if skipHeaderLines is greater than 0, ignore skipHeaderLines number of lines. The way to do that is to just
+ * look for the record separator
+ * @exception Exception if there is an error
+ */
+ protected void ignoreHeaderLines() throws Exception {
+ for(int i =0;i<skipLines;i++){
+ if(!readNextToken(recordSeparator, 0, recordSeparatorLength, true))
+ throw LoadError.unexpectedEndOfFile(lineNumber+1);
+ }
+ }
+
+
/** load the column types from the meta data line to be analyzed
* later in the constructor of the ImportResultSetMetaData.
*/
@@ -652,9 +665,7 @@
if (hasColumnDefinition){
ignoreFirstRow();
}
- for(int i =0;i<skipLines;i++){
- ignoreFirstRow();
- }
+ ignoreHeaderLines();
}
if (formatCode == DEFAULT_FORMAT_CODE)
readVal=readNextDelimitedRow(returnStringArray);
diff --git a/java/engine/org/apache/derby/loc/messages.xml b/java/engine/org/apache/derby/loc/messages.xml
index ba783d6..5f12ec8 100644
--- a/java/engine/org/apache/derby/loc/messages.xml
+++ b/java/engine/org/apache/derby/loc/messages.xml
@@ -2358,6 +2358,17 @@
<arg>scope</arg>
</msg>
+ <msg>
+ <name>42XAU</name>
+ <text>Invalid column name/names in the input file: '{0}' or Wrong number of header lines</text>
+ <arg>columnPattern</arg>
+ </msg>
+
+ <msg>
+ <name>42XAV</name>
+ <text>Number of header lines should be greater than 0.</text>
+ </msg>
+
<msg>
<name>42XBA</name>
<text>The schema, table or column does not exist or the column is not a string type.</text>
diff --git a/java/shared/org/apache/derby/shared/common/reference/SQLState.java b/java/shared/org/apache/derby/shared/common/reference/SQLState.java
index 3873cfb..51a446d 100644
--- a/java/shared/org/apache/derby/shared/common/reference/SQLState.java
+++ b/java/shared/org/apache/derby/shared/common/reference/SQLState.java
@@ -964,6 +964,8 @@
String LANG_SYSTEM_SEQUENCE = "42XAR";
String LANG_BAD_DISTINCT_AGG = "42XAS";
String LANG_INVALID_ROWID_SCOPE = "42XAT";
+ String LANG_INVALID_INPUT_COLUMN_NAME = "42XAU";
+ String LANG_INVALID_NUMBEROF_HEADER_LINES = "42XAV";
String LANG_INVALID_USER_AGGREGATE_DEFINITION2 = "42Y00";
String LANG_INVALID_CHECK_CONSTRAINT = "42Y01";
// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE = "42Y02";
diff --git a/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java b/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java
index dcd3621..12ae956 100644
--- a/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java
+++ b/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java
@@ -1881,10 +1881,572 @@
" drop table inventory.orderTable");
//end derby-390 related test cases.
-
+
getConnection().rollback();
st.close();
}
+
+ /*
+ *tests for Derby-4555
+ */
+ public void test4555ColumnIndexesParsing() throws Exception{
+
+ CallableStatement cSt;
+ Statement st = createStatement();
+
+ st.executeUpdate(" create table pet(C1 varchar(50), C2 varchar(50) , C3 varchar(50))");
+
+ st.executeUpdate("insert into pet values('Pet', 'Kind' , 'Age')");
+
+ st.executeUpdate("insert into pet values('Name', 'of' , null)");
+
+ st.executeUpdate("insert into pet values(null, 'Animal' , null)");
+
+ st.executeUpdate("insert into pet values('Rover', 'Dog' , '4')");
+
+ st.executeUpdate("insert into pet values('Spot', 'Cat' , '2')");
+
+ st.executeUpdate("insert into pet values('Squawky','Parrot','37')");
+
+ st.executeUpdate(" create table pet1(C1 varchar(50), C2 varchar(50) , C3 int NOT NULL)");
+
+ SupportFilesSetup.deleteFile("extinout/pet.dat");
+ st.executeUpdate("call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, "
+ + "'PET' , 'extinout/pet.dat', null, null, null) ");
+
+ st.executeUpdate("delete from pet");
+
+ // With both indexes and names of the columns for COLUMNINDEXES argument
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet"),
+ new String[][]
+ {
+ {"Rover","Dog","4"},
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Only with the names of the columns and multiline headernames for COLUMNINDEXES argument
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '\"Pet Name\",\"Kind of Animal\",\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet"),
+ new String[][]
+ {
+ {"Rover","Dog","4"},
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Changing the order of the header names.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , 'C3,C1,C2' , '\"Age\",1,2' , 'extinout/pet.dat' "
+ + " , null , null ,null , 0, 3) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from Pet"),
+ new String[][]
+ {
+ {"Rover","Dog","4"},
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+ st.executeUpdate("delete from pet");
+
+ //invalid column name
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '\"Pet\",2,3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertStatementError("42XAU", cSt);
+
+ //Skip argument is 0 and non-existent input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '\"Pet Name\",2,3' , 'extinout/petlist.dat' "
+ + " , null , null , null, 0, 0) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //Skip argument is 2 and non-existent input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '\"Pet Name\",2,3' , 'extinout/petlist.dat' "
+ + " , null , null , null, 0, 2) ");
+
+ assertStatementError("XIE04", cSt);
+
+ //Skip argument is 0
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 0) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //Invalid number of header lines of the input file causes NULL value error
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET1' , null , '1,2,\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 1) ");
+
+ assertStatementError("XIE0R", cSt);
+
+ //Invalid number of header lines of the input file causes NULL value error
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET1' , null , '\"Pet\"\"Kind\"\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 1) ");
+
+ assertStatementError("42XAU", cSt);
+
+ //Skip argument is 7 that is greater than number of rows in the file.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 7) ");
+
+ assertStatementError("42XAU", cSt);
+
+ //Column name in capital letters.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,\"AGE\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertStatementError("42XAU", cSt);
+
+ //Multiple header lines in the input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,\"Kind of Animal\",\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet"),
+ new String[][]
+ {
+ {"Rover","Dog","4"},
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Invalid ' " ' marks
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '\"Pet\"\"Name\",\"Kind\"\"of\"\"Animal\",\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+
+ assertStatementError("42XAU", cSt);
+
+ //Invalid number of header lines of the input file causes NULL value error
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET1' , null , '\"Pet Name\",\"Kind of\",\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 2) ");
+
+ assertStatementError("XIE0R", cSt);
+
+ //Skip=4
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '\"Pet Name Rover\",2,3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 4) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet"),
+ new String[][]
+ {
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Skip=4
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,\"Age 4\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 4) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet"),
+ new String[][]
+ {
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Skip=4
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,\"Kind of Animal Dog\",3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 4) ");
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet"),
+ new String[][]
+ {
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //The number of values in COLUMNINDEXES and INSERTCOLUMNS does not match
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertStatementError("42802", cSt);
+
+ //The number of values in COLUMNINDEXES and INSERTCOLUMNS does not match
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,\"Kind of Animal\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertStatementError("42802", cSt);
+
+ //The number of values in COLUMNINDEXES and INSERTCOLUMNS does not match
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,\"Age\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertStatementError("42802", cSt);
+
+ //Skip argument is -2
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, -2) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //Skip argument is -1
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , null , '1,2,3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, -1) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //Insert only two columns
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , 'C1,C2' , '1,2' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select C1,C2 from pet"),
+ new String[][]
+ {
+ {"Rover","Dog"},
+ { "Spot", "Cat"},
+ { "Squawky", "Parrot"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Wrong values for INSERTCOLUMNS argument causes NULL value error
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET1' , 'C1,C2' , '1,3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertStatementError("XIE0R", cSt);
+
+ //Insert only two columns, order is different
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , 'C1,C3' , '1,3' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select C1,C3 from pet"),
+ new String[][]
+ {
+ {"Rover","4"},
+ { "Spot", "2"},
+ { "Squawky", "37"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ //Insert only two columns, order is different, with column names
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, "
+ + "'PET' , 'C1,C2' , '\"Pet Name\",\"Kind of Animal\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0, 3) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select C1,C2 from pet"),
+ new String[][]
+ {
+ {"Rover","Dog"},
+ { "Spot", "Cat"},
+ { "Squawky", "Parrot"},
+ });
+
+ st.executeUpdate("delete from pet");
+
+ /*
+ *Tests for SYSCS_IMPORT_TABLE_BULK procedure
+ */
+ //Skip=0 and non-existent input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'PET', 'extinout/petlist.dat', null, null, null, 0, 0) ");
+
+ assertStatementError("38000", cSt);
+
+
+ //Skip=2 and non-existent input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'PET', 'extinout/petlist.dat', null, null, null, 0, 2) ");
+
+ assertStatementError("XJ001", cSt);
+
+ //Skip=-1
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'PET', 'extinout/pet.dat', null, null, null, 0, -1) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //Skip argument is 7 that is greater than number of rows in the file.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'PET', 'extinout/pet.dat', null, null, null, 0, 7) ");
+ assertStatementError("XIE0E", cSt);
+
+ //End of tests for SYSCS_IMPORT_TABLE_BULK procedure
+
+ /**
+ *Tests for SYSCS_IMPORT_DATA procedure
+ */
+
+ //COLUMNINDEXES 1,2,"Age". This should fail because no lines are skipped by this procedure
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, "
+ + "'PET1' , null , '1,2,\"AGE\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //COLUMNINDEXES 1,2,Age. This should fail because no ' " ' in the column name.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, "
+ + "'PET1' , null , '1,2,AGE' , 'extinout/pet.dat' "
+ + " , null , null , null, 0) ");
+
+ assertStatementError("38000", cSt);
+
+ //COLUMNINDEXES 1,2,4. This should fail because no column 4 in the column name.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, "
+ + "'PET1' , null , '1,2,4' , 'extinout/pet.dat' "
+ + " , null , null , null, 0) ");
+
+ assertStatementError("38000", cSt);
+
+ //File name is wrong.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, "
+ + "'PET' , null , '1,2,3' , 'extinout/petlist.dat' "
+ + " , null , null , null, 0) ");
+
+
+ assertStatementError("38000", cSt);
+
+ //End of tests for SYSCS_IMPORT_DATA procedure
+
+ /**
+ *Tests for SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedure
+ */
+
+ //COLUMNINDEXES 1,2,"Age". This should fail because no lines are skipped by this procedure
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(null, "
+ + "'PET1' , null , '1,2,\"AGE\"' , 'extinout/pet.dat' "
+ + " , null , null , null, 0) ");
+
+ assertStatementError("42XAV", cSt);
+
+ //COLUMNINDEXES 1,2,Age. This should fail because no ' " ' in the column name.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(null, "
+ + "'PET1' , null , '1,2,AGE' , 'extinout/pet.dat' "
+ + " , null , null , null, 0) ");
+
+ assertStatementError("38000", cSt);
+
+ //COLUMNINDEXES 1,2,4. This should fail because no column 4 in the column name.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(null, "
+ + "'PET1' , null , '1,2,4' , 'extinout/pet.dat' "
+ + " , null , null , null, 0) ");
+
+ assertStatementError("38000", cSt);
+
+ //File name is wrong.
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(null, "
+ + "'PET' , null , '1,2,3' , 'extinout/petlist.dat' "
+ + " , null , null , null, 0) ");
+
+
+ assertStatementError("38000", cSt);
+
+ //End of tests for SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedure
+
+
+ /**
+ *Tests for SYSCS_IMPORT_TABLE procedure
+ */
+ //Non-existent input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'PET', 'extinout/petlist.dat', null, null, null, 0) ");
+
+ assertStatementError("38000", cSt);
+
+ st.executeUpdate(" create table pet2(C1 varchar(50), C2 varchar(50))");
+
+ st.executeUpdate("insert into pet2 values('Rover', 'Dog')");
+
+ st.executeUpdate("insert into pet2 values('Spot', 'Cat')");
+
+ st.executeUpdate("insert into pet2 values('Squawky','Parrot')");
+
+ SupportFilesSetup.deleteFile("extinout/pet2.dat");
+ st.executeUpdate("call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, "
+ + "'PET2' , 'extinout/pet2.dat', null, null, null) ");
+
+ st.executeUpdate("delete from pet2");
+
+ //Table with only two columns
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'PET2', 'extinout/pet2.dat', null, null, null, 0) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet2"),
+ new String[][]
+ {
+ {"Rover","Dog"},
+ { "Spot", "Cat"},
+ { "Squawky", "Parrot"},
+ });
+
+ st.executeUpdate("delete from pet2");
+
+ st.executeUpdate(" create table pet3(C1 varchar(50),C2 int, C3 varchar(50))");
+
+ st.executeUpdate("insert into pet3 values('Rover', 4, 'Dog')");
+
+ st.executeUpdate("insert into pet3 values('Spot', 2,'Cat')");
+
+ st.executeUpdate("insert into pet3 values('Squawky',37,'Parrot')");
+
+ SupportFilesSetup.deleteFile("extinout/pet3.dat");
+ st.executeUpdate("call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, "
+ + "'PET3' , 'extinout/pet3.dat', null, null, null) ");
+
+ st.executeUpdate("delete from pet3");
+
+
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'PET3', 'extinout/pet3.dat', null, null, null, 0) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet3"),
+ new String[][]
+ {
+ {"Rover","4","Dog"},
+ { "Spot","2", "Cat"},
+ { "Squawky", "37","Parrot"},
+ });
+ st.executeUpdate("delete from pet3");
+ //End of tests for SYSCS_IMPORT_TABLE procedure
+
+
+ /**
+ *Tests for SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE procedure
+ */
+ //Non-existent input file
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(null, 'PET', 'extinout/petlist.dat', null, null, null, 0) ");
+
+ assertStatementError("38000", cSt);
+
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(null, 'PET2', 'extinout/pet2.dat', null, null, null, 0) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet2"),
+ new String[][]
+ {
+ {"Rover","Dog"},
+ { "Spot", "Cat"},
+ { "Squawky", "Parrot"},
+ });
+
+
+ st.executeUpdate("delete from pet2");
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(null, 'PET3', 'extinout/pet3.dat', null, null, null, 0) ");
+
+ assertUpdateCount(cSt, 0);
+
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from pet3"),
+ new String[][]
+ {
+ {"Rover","4","Dog"},
+ { "Spot","2", "Cat"},
+ { "Squawky", "37","Parrot"},
+ });
+ st.executeUpdate("delete from pet3");
+ //End of tests for SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE procedure
+
+
+ }
/**
* Converted from iepnegativetests.sql
