You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2005/07/01 18:33:58 UTC
svn commit: r208770 [1/3] - in /incubator/derby/code/trunk/java:
engine/org/apache/derby/impl/load/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/
testing/org/apache/derbyTesting/fun...
Author: mikem
Date: Fri Jul 1 09:33:55 2005
New Revision: 208770
URL: http://svn.apache.org/viewcvs?rev=208770&view=rev
Log:
patch to handle case-sensitive SQL identifiers correctly.
Import/export procedure parameters for table names, schema names, columns
names should be passed in the case-sensitive form if they are quoted identfiers
and in upper case if they are not quoted SQL identifiers. Import/export will
generate insert/select statements with quoted table names, schema name and
column names to be execute on the database after this patch.
committed on behalf of: Suresh Thalamati
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ieptests.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/I18NImportExport.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/UnicodeEscape_JP.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests_ES.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/importExportThruIJ.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/i18n/I18NImportExport.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/i18n/UnicodeEscape_JP.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/iepnegativetests.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java Fri Jul 1 09:33:55 2005
@@ -80,13 +80,14 @@
columnTypes = new ArrayList(1);
noOfColumns = 0;
this.conn = conn;
- this.schemaName = (sName !=null ? sName.toUpperCase(java.util.Locale.ENGLISH):sName);
- this.tableName = (tName !=null ? tName.toUpperCase(java.util.Locale.ENGLISH):tName);
+
+ this.schemaName = sName;
+ this.tableName = tName;
if(insertColumnList!=null)
{
//break the comma seperated column list and initialze column info
- //eg: c2 , c1 , c3
+ //eg: C2 , C1 , C3
StringTokenizer st = new StringTokenizer(insertColumnList , ",");
while (st.hasMoreTokens())
{
@@ -152,7 +153,7 @@
ResultSet rs = dmd.getColumns(null,
schemaName,
tableName,
- (columnPattern !=null ? columnPattern.toUpperCase(java.util.Locale.ENGLISH):columnPattern));
+ columnPattern);
boolean foundTheColumn=false;
while (rs.next())
{
@@ -306,8 +307,9 @@
return sb.toString();
}
- /* returns comma seperated column Names for insert statement
- * eg: c1, c2 , c3 , c4
+ /* returns comma seperated column Names delimited by quotes for the insert
+ * statement
+ * eg: "C1", "C2" , "C3" , "C4"
*/
public String getInsertColumnNames()
{
@@ -319,7 +321,11 @@
sb.append(", ");
else
first = false;
+ // column names can be SQL reserved words, so it
+ // is necessary delimit them using quotes for insert to work correctly.
+ sb.append("\"");
sb.append(insertColumnNames.get(index));
+ sb.append("\"");
}
//there is no column info available
@@ -354,9 +360,6 @@
}
}
-
-
-
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java Fri Jul 1 09:33:55 2005
@@ -24,93 +24,104 @@
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
+import java.sql.DatabaseMetaData;
+import java.sql.SQLException;
//uses the passed connection and table/view name to make the resultset on
//that entity. If the entity to be exported has non-sql types in it, an
//exception will be thrown
class ExportResultSetForObject {
- private Connection con;
- private String entityName;
- private String selectStatement;
- private ResultSet rs;
- private int columnCount;
- private String columnNames[];
- private String columnTypes[];
- private int columnLengths[];
-
- private Statement expStmt = null;
-
- //uses the passed connection and table/view name to make the resultset on
- //that entity.
+ private Connection con;
+ private String selectQuery;
+ private ResultSet rs;
+ private int columnCount;
+ private String columnNames[];
+ private String columnTypes[];
+ private int columnLengths[];
+
+ private Statement expStmt = null;
+ private String schemaName;
+ private String tableName;
+
+ /* set up the connection and table/view name or the select query
+ * to make the result set, whose data is exported.
+ **/
public ExportResultSetForObject(Connection con, String schemaName,
- String tableName, String selectStatement
+ String tableName, String selectQuery
)
{
this.con = con;
- if( selectStatement == null)
- this.entityName = (schemaName == null ? tableName : schemaName + "." + tableName);
- this.selectStatement = selectStatement;
+ if( selectQuery == null)
+ {
+ this.schemaName = schemaName;
+ this.tableName = tableName;
+
+ // delimit schema Name and table Name using quotes because
+ // they can be case-sensitive names or SQL reserved words. Export
+ // procedures are expected to be called with case-senisitive names.
+ // undelimited names are passed in upper case, because that is
+ // the form database stores them.
+
+ this.selectQuery = "select * from " +
+ (schemaName == null ? "\"" + tableName + "\"" :
+ "\"" + schemaName + "\"" + "." + "\"" + tableName + "\"");
+ }
+ else
+ {
+ this.selectQuery = selectQuery;
+ }
}
- public ResultSet getResultSet() throws Exception {
- rs = null;
- String queryString = getQuery();
- //execute select on passed enitity and keep it's meta data info ready
- Statement expStmt = con.createStatement();
- rs = expStmt.executeQuery(queryString);
- getMetaDataInfo();
- return rs;
- }
-
- public String getQuery(){
- if(selectStatement != null)
- return selectStatement;
- else
- {
- selectStatement = "select * from " + entityName;
- return selectStatement;
- }
- }
-
- public int getColumnCount() {
- return columnCount;
- }
-
- public String[] getColumnDefinition() {
- return columnNames;
- }
-
- public String[] getColumnTypes() {
- return columnTypes;
- }
-
- public int[] getColumnLengths() {
- return columnLengths;
- }
-
- //if the entity to be exported has non-sql types in it, an exception will be thrown
- private void getMetaDataInfo() throws Exception {
- ResultSetMetaData metaData = rs.getMetaData();
- columnCount = metaData.getColumnCount();
- int numColumns = columnCount;
- columnNames = new String[numColumns];
- columnTypes = new String[numColumns];
- columnLengths = new int[numColumns];
- for (int i=0; i<numColumns; i++) {
- int jdbcTypeId = metaData.getColumnType(i+1);
- columnNames[i] = metaData.getColumnName(i+1);
- columnTypes[i] = metaData.getColumnTypeName(i+1);
- if(!ColumnInfo.importExportSupportedType(jdbcTypeId))
- {
- throw LoadError.nonSupportedTypeColumn(columnNames[i],
- columnTypes[i]);
+ public ResultSet getResultSet() throws SQLException {
+ rs = null;
+ //execute the select query and keep it's meta data info ready
+ expStmt = con.createStatement();
+ rs = expStmt.executeQuery(selectQuery);
+ getMetaDataInfo();
+ return rs;
}
-
- columnLengths[i] = metaData.getColumnDisplaySize(i+1);
+
+
+ public int getColumnCount() {
+ return columnCount;
+ }
+
+ public String[] getColumnDefinition() {
+ return columnNames;
+ }
+
+ public String[] getColumnTypes() {
+ return columnTypes;
+ }
+
+ public int[] getColumnLengths() {
+ return columnLengths;
+ }
+
+ //if the entity to be exported has non-sql types in it, an exception will be thrown
+ private void getMetaDataInfo() throws SQLException {
+ ResultSetMetaData metaData = rs.getMetaData();
+ columnCount = metaData.getColumnCount();
+ int numColumns = columnCount;
+ columnNames = new String[numColumns];
+ columnTypes = new String[numColumns];
+ columnLengths = new int[numColumns];
+
+ for (int i=0; i<numColumns; i++) {
+ int jdbcTypeId = metaData.getColumnType(i+1);
+ columnNames[i] = metaData.getColumnName(i+1);
+ columnTypes[i] = metaData.getColumnTypeName(i+1);
+ if(!ColumnInfo.importExportSupportedType(jdbcTypeId))
+ {
+ throw LoadError.nonSupportedTypeColumn(
+ columnNames[i], columnTypes[i]);
+ }
+
+ columnLengths[i] = metaData.getColumnDisplaySize(i+1);
+ }
}
- }
public void close() throws Exception
{
@@ -118,8 +129,3 @@
expStmt.close();
}
}
-
-
-
-
-
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java Fri Jul 1 09:33:55 2005
@@ -160,9 +160,9 @@
if (connection == null)
throw LoadError.connectionNull();
- String entityName = (schemaName == null ? tableName : schemaName + "." + tableName);
+
- if (entityName == null)
+ if (tableName == null)
throw LoadError.entityNameMissing();
@@ -197,6 +197,18 @@
String importvti = sb.toString();
+ // delimit the table and schema names with quotes.
+ // because they might have been created as quoted
+ // identifiers(for example when reserved words are used, names are quoted)
+
+ // Import procedures are to be called with case-senisitive names.
+ // Incase of delimited table names, they need to be passed as defined
+ // and when they are not delimited, they need to be passed in upper
+ // case, because all undelimited names are stored in the upper case
+ // in the database.
+
+ String entityName = (schemaName == null ? "\""+ tableName + "\"" :
+ "\"" + schemaName + "\"" + "." + "\"" + tableName + "\"");
String insertModeValue;
if(replace > 0)
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ieptests.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ieptests.out?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ieptests.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ieptests.out Fri Jul 1 09:33:55 2005
@@ -11,11 +11,11 @@
insert into ex_emp values(99, 'smith' , 'tennis"p,l,ayer"', 190.55) ;
1 row inserted/updated/deleted
ij> -- Perform Export:
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
null, null, null) ;
Statement executed.
ij> -- Perform Import
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, null, null, 0) ;
Statement executed.
ij> insert into ex_emp values(100, 'smith' , 'tennis"player"', 190.55) ;
@@ -41,11 +41,11 @@
insert into ex_emp values(108, null , null, null) ;
1 row inserted/updated/deleted
ij> -- Perform Export:
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
null, null, null) ;
Statement executed.
ij> -- Perform Import
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, null, null, 0) ;
Statement executed.
ij> select * from ex_emp;
@@ -89,7 +89,7 @@
call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from ex_emp where id < 105',
'extinout/emp.dat' , null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, null, null, 0) ;
Statement executed.
ij> --checking query
@@ -105,7 +105,7 @@
'extinout/emp.dat' , null, null, null) ;
Statement executed.
ij> -- import them in to a with order different than in the table;
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_emp' ,'name, salary, skills, id', null,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'NAME, SALARY, SKILLS, ID', null,
'extinout/emp.dat', null, null, null, 1) ;
Statement executed.
ij> --check query
@@ -117,7 +117,7 @@
-----
6
ij> -- do import replace into the table with table order but using column indexes
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_emp' ,null, '4, 1, 3, 2',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,null, '4, 1, 3, 2',
'extinout/emp.dat', null, null, null, 1) ;
Statement executed.
ij> --check query
@@ -129,7 +129,7 @@
-----
6
ij> --replace using insert column names and column indexes
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_emp' ,'salary, id, skills, name', '2, 4, 3, 1',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'SALARY, ID, SKILLS, NAME', '2, 4, 3, 1',
'extinout/emp.dat', null, null, null, 1) ;
Statement executed.
ij> --check query
@@ -142,10 +142,10 @@
6
ij> ---testing with different delimiters
----- single quote(') as character delimiter
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
null, '''', null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, '''', null, 1) ;
Statement executed.
ij> select * from imp_emp ;
@@ -162,12 +162,12 @@
107 |smith" |NULL |190.55
108 |NULL |NULL |NULL
ij> -- single quote(') as column delimiter
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
'''',null, null) ;
Statement executed.
ij> delete from imp_emp ;
10 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
'''', null, null, 0) ;
Statement executed.
ij> select * from imp_emp;
@@ -183,10 +183,10 @@
106 |smith |""""""""""""""""""" |190.55
107 |smith" |NULL |190.55
108 |NULL |NULL |NULL
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
'*', '%', null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
'*', '%', null, 1) ;
Statement executed.
ij> select * from imp_emp ;
@@ -213,11 +213,11 @@
1 row inserted/updated/deleted
ij> insert into noncast values(2.5 , 8.999) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'noncast' , 'extinout/noncast.dat' , null , null , null) ;
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'NONCAST' , 'extinout/noncast.dat' , null , null , null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'noncast' , 'extinout/noncast.dat' , null , null , null , 0) ;
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NONCAST' , 'extinout/noncast.dat' , null , null , null , 0) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'noncast', 'c2 , c1' , '2, 1' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NONCAST', 'C2 , C1' , '2, 1' ,
'extinout/noncast.dat' , null , null , null , 0) ;
Statement executed.
ij> select * from noncast ;
@@ -242,10 +242,10 @@
1 row inserted/updated/deleted
ij> insert into ttypes values(null , null , null);
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ttypes' , 'extinout/ttypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ttypes' , 'extinout/ttypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' ,
null, null, null, 0) ;
Statement executed.
ij> select * from ttypes;
@@ -269,10 +269,10 @@
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null, 0) ;
Statement executed.
ij> --above import should have committed , following rollback should be a noop.
@@ -289,7 +289,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --file not found error should rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' ,
null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> commit;
@@ -305,7 +305,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --table not found error should issue a implicit rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'notable' , 'extinout/t1.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' ,
null, null, null, 0) ;
ERROR XIE0M: Table 'NOTABLE' does not exist.
ij> commit ;
@@ -323,7 +323,7 @@
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null) ;
Statement executed.
ij> --above export should have a commit.rollback below should be a noop
@@ -333,7 +333,7 @@
-----
1
2
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null, 1) ;
Statement executed.
ij> --above import should have committed , following rollback should be a noop.
@@ -348,7 +348,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --file not found error should rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' ,
null, null, null, 1) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> commit;
@@ -362,7 +362,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --table not found error should issue a implicit rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'notable' , 'extinout/t1.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' ,
null, null, null, 1) ;
ERROR XIE0M: Table 'NOTABLE' does not exist.
ij> commit ;
@@ -379,10 +379,10 @@
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1' , 'a' , '1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' ,
null, null, null, 0) ;
Statement executed.
ij> --above import should have committed , following rollback should be a noop.
@@ -399,7 +399,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --file not found error should rollback
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1', 'a' , '1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1' , 'extinout/nofile.del' ,
null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> commit;
@@ -415,7 +415,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --table not found error should issue a implicit rollback
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'notable' , 'a' , '1', 'extinout/t1.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NOTABLE' , 'A' , '1', 'extinout/t1.del' ,
null, null, null, 1) ;
ERROR XIE0M: Table 'NOTABLE' does not exist.
ij> commit ;
@@ -432,7 +432,7 @@
1 row inserted/updated/deleted
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1' , 'a' , '1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' ,
null, null, null, 0) ;
Statement executed.
ij> select * from t1 ;
@@ -451,7 +451,7 @@
ij> insert into t1 values(6) ;
1 row inserted/updated/deleted
ij> --following import will back , but should not have any impact on inserts
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1', 'a' , '1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1' , 'extinout/nofile.del' ,
null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> select * from t1 ;
@@ -468,7 +468,7 @@
5
6
ij> --END IMPORT COMMIT/ROLLBACK TESTSING
------all types supported by DB2 cloudscape import/export
+-----all types supported by Derby import/export
create table alltypes(chartype char(20) ,
biginttype bigint ,
datetype date ,
@@ -521,10 +521,10 @@
'xxxxxxFILTERED-TIMESTAMPxxxxx,
'"varchar" testing');
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null, 0) ;
Statement executed.
ij> select * from alltypes ;
@@ -546,7 +546,7 @@
for each row mode db2sql
insert into test1 values(newrow.chartype);
0 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null, 0) ;
Statement executed.
ij> select count(*) from alltypes ;
@@ -561,7 +561,7 @@
"chartype" string
ij> delete from alltypes;
3 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null, 1) ;
ERROR 38000: The exception 'SQL Exception: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).' was thrown while evaluating an expression. SQLSTATE: 42Z08: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).
ij> select count(*) from alltypes;
@@ -592,7 +592,7 @@
ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select c1,c3,c4 from table2' , 'extinout/import.del' ,
null, null, null) ;
Statement executed.
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'table1', 'c1,c3,c4' , null, 'extinout/import.del',null, null,null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'TABLE1', 'C1,C3,C4' , null, 'extinout/import.del',null, null,null,0);
Statement executed.
ij> select * from table1;
C1 |C2 |C3 |C4
@@ -602,13 +602,13 @@
Leo |3 |23.4 |I
ij> delete from table1;
3 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'table2' , 'extinout/import.del', null, null, null) ;
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del', null, null, null) ;
Statement executed.
ij> --following import should fail becuase of inserting into identity column.
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'table1', 'extinout/import.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'C2'.
ij> --following import should be succesful
-CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'table1', 'c1,c3,c4' , '1,3,4', 'extinout/import.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'TABLE1', 'C1,C3,C4' , '1,3,4', 'extinout/import.del',null, null, null,1);
Statement executed.
ij> select * from table1;
C1 |C2 |C3 |C4
@@ -619,10 +619,10 @@
ij> update table2 set c2=null;
3 rows inserted/updated/deleted
ij> --check null values import to identity columns should also fail
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'table2' , 'extinout/import.del' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del' ,
null, null, null) ;
Statement executed.
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'table1', 'extinout/import.del',null, null, null,1);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'C2'.
ij> select * from table1;
C1 |C2 |C3 |C4
@@ -644,7 +644,7 @@
null, null, null) ;
Statement executed.
ij> --replace should fail because of dependent table
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'parent', 'extinout/parent.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'PARENT', 'extinout/parent.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.' was thrown while evaluating an expression. SQLSTATE: 23503: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.
ij> select * from parent;
A
@@ -656,7 +656,7 @@
ij> ---test with a file which has a differen records seperators (\n, \r , \r\n)
create table nt1( a int , b char(30));
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'nt1', 'extin/mixednl.del',null, null, null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'NT1', 'extin/mixednl.del',null, null, null,0);
Statement executed.
ij> select * from nt1;
A |B
@@ -689,7 +689,7 @@
web_flag varchar(1)
);
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'position_info', 'extin/position_info.del',
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extin/position_info.del',
null, null, null, 1);
Statement executed.
ij> select count(*) from position_info ;
@@ -701,12 +701,12 @@
-----
Essential Duties and Responsibilities (include but not limited to):
*Assist the director in his work activities in leading the&
-ij> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'position_info', 'extinout/pinfo.del',
+ij> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del',
null, null, null);
Statement executed.
ij> delete from position_info;
680 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'position_info', 'extinout/pinfo.del',
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del',
null, null, null, 1);
Statement executed.
ij> select count(*) from position_info ;
@@ -725,12 +725,12 @@
0 rows inserted/updated/deleted
ij> create table dest_by_default(i int generated by default as identity);
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,0);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'I'.
ij> select * from dest_always;
I
-----
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,0);
Statement executed.
ij> select * from dest_by_default;
I
@@ -746,12 +746,12 @@
0 rows inserted/updated/deleted
ij> create table dest_by_default(i int generated by default as identity);
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,1);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,1);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'I'.
ij> select * from dest_always;
I
-----
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,1);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,1);
Statement executed.
ij> select * from dest_by_default;
I
@@ -763,4 +763,142 @@
0 rows inserted/updated/deleted
ij> drop table dest_by_default;
0 rows inserted/updated/deleted
+ij> --test case for bug (DERBY-390)
+-----test import/export with reserved words as table Name, column Names ..etc.
+create schema "Group";
+0 rows inserted/updated/deleted
+ij> create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
+0 rows inserted/updated/deleted
+ij> insert into "Group"."Order" values(1, 2, 'memory') ;
+1 row inserted/updated/deleted
+ij> insert into "Group"."Order" values(3, 4, 'disk') ;
+1 row inserted/updated/deleted
+ij> insert into "Group"."Order" values(5, 6, 'mouse') ;
+1 row inserted/updated/deleted
+ij> --following export should fail because schema name is not matching the way it is defined using delimited quotes.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('GROUP', 'Order' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Schema 'GROUP' does not exist' was thrown while evaluating an expression. SQLSTATE: 42Y07: Schema 'GROUP' does not exist
+ij> --following export should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'ORDER' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Table 'Group.ORDER' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table 'Group.ORDER' does not exist.
+ij> --following export should fail because of unquoted table name that is a reserved word.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group".Order' , 'extinout/order.dat' , null , null , null ) ;
+ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "Order" at line 1, column 23.' was thrown while evaluating an expression. SQLSTATE: 42X01: Syntax error: Encountered "Order" at line 1, column 23.
+ij> --following exports should pass.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null) ;
+Statement executed.
+ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group"."Order"' , 'extinout/order.dat' , null , null , null ) ;
+Statement executed.
+ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select "select" , "delete" , itemName from "Group"."Order"' ,
+ 'extinout/order.dat' , null , null , null ) ;
+Statement executed.
+ij> --following import should fail because schema name is not matching the way it is defined using delimited quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('GROUP', 'Order' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'GROUP.Order' does not exist.
+ij> --following import should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'ORDER' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'Group.ORDER' does not exist.
+ij> --following import should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'ORDER' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE0M: Table 'Group.ORDER' does not exist.
+ij> --following import should fail because column name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'DELETE, ITEMNAME' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE08: There is no column named: DELETE.
+ij> --following import should fail because undelimited column name is not in upper case.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete, itemName' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE08: There is no column named: itemName.
+ij> --following imports should pass
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null, 0) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+1 |2 |memory
+3 |4 |disk
+5 |6 |mouse
+1 |2 |memory
+3 |4 |disk
+5 |6 |mouse
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+1 |2 |memory
+3 |4 |disk
+5 |6 |mouse
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete' , '2' , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+NULL |2 |NULL
+NULL |4 |NULL
+NULL |6 |NULL
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'ITEMNAME, select, delete' , '3,2,1' , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+2 |1 |memory
+4 |3 |disk
+6 |5 |mouse
+ij> drop table "Group"."Order";
+0 rows inserted/updated/deleted
+ij> ---test undelimited names( All unquoted SQL identfiers should be passed in upper case).
+create schema inventory;
+0 rows inserted/updated/deleted
+ij> create table inventory.orderTable(id int, amount int, itemName char(20)) ;
+0 rows inserted/updated/deleted
+ij> insert into inventory.orderTable values(101, 5, 'pizza') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(102, 6, 'coke') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(103, 7, 'break sticks') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(104, 8, 'buffolo wings') ;
+1 row inserted/updated/deleted
+ij> --following export should fail because schema name is not in upper case.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('inventory', 'ORDERTABLE' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Schema 'inventory' does not exist' was thrown while evaluating an expression. SQLSTATE: 42Y07: Schema 'inventory' does not exist
+ij> --following export should fail because table name is not in upper case.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ordertable' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Table 'INVENTORY.ordertable' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table 'INVENTORY.ordertable' does not exist.
+ij> --following export should pass.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ORDERTABLE' , 'extinout/order.dat', null, null, null) ;
+Statement executed.
+ij> --following import should fail because schema name is not in upper case
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('inventory', 'ORDERTABLE' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'inventory.ORDERTABLE' does not exist.
+ij> --following import should fail because table name is not in upper case.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('INVENTORY', 'ordertable' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'INVENTORY.ordertable' does not exist.
+ij> --following import should fail because table name is not in upper case .
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ordertable' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE0M: Table 'INVENTORY.ordertable' does not exist.
+ij> --following import should fail because column name is not in upper case.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'amount, ITEMNAME' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE08: There is no column named: amount.
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from inventory.orderTable;
+ID |AMOUNT |ITEMNAME
+-----
+101 |5 |pizza
+102 |6 |coke
+103 |7 |break sticks
+104 |8 |buffolo wings
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'ITEMNAME, ID, AMOUNT' , '3,2,1' , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from inventory.orderTable;
+ID |AMOUNT |ITEMNAME
+-----
+5 |101 |pizza
+6 |102 |coke
+7 |103 |break sticks
+8 |104 |buffolo wings
+ij> drop table inventory.orderTable;
+0 rows inserted/updated/deleted
+ij> --end derby-390 related test cases.
+;
ij>
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out Fri Jul 1 09:33:55 2005
@@ -11,11 +11,11 @@
insert into ex_emp values(99, 'smith' , 'tennis"p,l,ayer"', 190.55) ;
1 row inserted/updated/deleted
ij> -- Perform Export:
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
null, null, null) ;
Statement executed.
ij> -- Perform Import
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, null, null, 0) ;
Statement executed.
ij> insert into ex_emp values(100, 'smith' , 'tennis"player"', 190.55) ;
@@ -41,11 +41,11 @@
insert into ex_emp values(108, null , null, null) ;
1 row inserted/updated/deleted
ij> -- Perform Export:
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
null, null, null) ;
Statement executed.
ij> -- Perform Import
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, null, null, 0) ;
Statement executed.
ij> select * from ex_emp;
@@ -89,7 +89,7 @@
call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from ex_emp where id < 105',
'extinout/emp.dat' , null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, null, null, 0) ;
Statement executed.
ij> --checking query
@@ -105,7 +105,7 @@
'extinout/emp.dat' , null, null, null) ;
Statement executed.
ij> -- import them in to a with order different than in the table;
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_emp' ,'name, salary, skills, id', null,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'NAME, SALARY, SKILLS, ID', null,
'extinout/emp.dat', null, null, null, 1) ;
Statement executed.
ij> --check query
@@ -117,7 +117,7 @@
-----
6
ij> -- do import replace into the table with table order but using column indexes
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_emp' ,null, '4, 1, 3, 2',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,null, '4, 1, 3, 2',
'extinout/emp.dat', null, null, null, 1) ;
Statement executed.
ij> --check query
@@ -129,7 +129,7 @@
-----
6
ij> --replace using insert column names and column indexes
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'imp_emp' ,'salary, id, skills, name', '2, 4, 3, 1',
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_EMP' ,'SALARY, ID, SKILLS, NAME', '2, 4, 3, 1',
'extinout/emp.dat', null, null, null, 1) ;
Statement executed.
ij> --check query
@@ -142,10 +142,10 @@
6
ij> ---testing with different delimiters
----- single quote(') as character delimiter
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
null, '''', null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
null, '''', null, 1) ;
Statement executed.
ij> select * from imp_emp ;
@@ -162,12 +162,12 @@
107 |smith" |NULL |190.55
108 |NULL |NULL |NULL
ij> -- single quote(') as column delimiter
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
'''',null, null) ;
Statement executed.
ij> delete from imp_emp ;
10 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'imp_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_EMP' , 'extinout/emp.dat' ,
'''', null, null, 0) ;
Statement executed.
ij> select * from imp_emp;
@@ -183,10 +183,10 @@
106 |smith |""""""""""""""""""" |190.55
107 |smith" |NULL |190.55
108 |NULL |NULL |NULL
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
'*', '%', null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ex_emp' , 'extinout/emp.dat' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EX_EMP' , 'extinout/emp.dat' ,
'*', '%', null, 1) ;
Statement executed.
ij> select * from imp_emp ;
@@ -213,11 +213,11 @@
1 row inserted/updated/deleted
ij> insert into noncast values(2.5 , 8.999) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'noncast' , 'extinout/noncast.dat' , null , null , null) ;
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP' , 'NONCAST' , 'extinout/noncast.dat' , null , null , null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'noncast' , 'extinout/noncast.dat' , null , null , null , 0) ;
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NONCAST' , 'extinout/noncast.dat' , null , null , null , 0) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'noncast', 'c2 , c1' , '2, 1' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NONCAST', 'C2 , C1' , '2, 1' ,
'extinout/noncast.dat' , null , null , null , 0) ;
Statement executed.
ij> select * from noncast ;
@@ -242,10 +242,10 @@
1 row inserted/updated/deleted
ij> insert into ttypes values(null , null , null);
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ttypes' , 'extinout/ttypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ttypes' , 'extinout/ttypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TTYPES' , 'extinout/ttypes.del' ,
null, null, null, 0) ;
Statement executed.
ij> select * from ttypes;
@@ -269,10 +269,10 @@
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null, 0) ;
Statement executed.
ij> --above import should have committed , following rollback should be a noop.
@@ -289,7 +289,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --file not found error should rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' ,
null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> commit;
@@ -305,7 +305,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --table not found error should issue a implicit rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'notable' , 'extinout/t1.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' ,
null, null, null, 0) ;
ERROR XIE0M: Table 'NOTABLE' does not exist.
ij> commit ;
@@ -323,7 +323,7 @@
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null) ;
Statement executed.
ij> --above export should have a commit.rollback below should be a noop
@@ -333,7 +333,7 @@
-----
1
2
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null, 1) ;
Statement executed.
ij> --above import should have committed , following rollback should be a noop.
@@ -348,7 +348,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --file not found error should rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 't1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extinout/nofile.del' ,
null, null, null, 1) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> commit;
@@ -362,7 +362,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --table not found error should issue a implicit rollback
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'notable' , 'extinout/t1.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'NOTABLE' , 'extinout/t1.del' ,
null, null, null, 1) ;
ERROR XIE0M: Table 'NOTABLE' does not exist.
ij> commit ;
@@ -379,10 +379,10 @@
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 't1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1' , 'a' , '1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' ,
null, null, null, 0) ;
Statement executed.
ij> --above import should have committed , following rollback should be a noop.
@@ -399,7 +399,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --file not found error should rollback
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1', 'a' , '1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1' , 'extinout/nofile.del' ,
null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> commit;
@@ -415,7 +415,7 @@
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> --table not found error should issue a implicit rollback
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'notable' , 'a' , '1', 'extinout/t1.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'NOTABLE' , 'A' , '1', 'extinout/t1.del' ,
null, null, null, 1) ;
ERROR XIE0M: Table 'NOTABLE' does not exist.
ij> commit ;
@@ -432,7 +432,7 @@
1 row inserted/updated/deleted
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1' , 'a' , '1' , 'extinout/t1.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1' , 'A' , '1' , 'extinout/t1.del' ,
null, null, null, 0) ;
Statement executed.
ij> select * from t1 ;
@@ -451,7 +451,7 @@
ij> insert into t1 values(6) ;
1 row inserted/updated/deleted
ij> --following import will back , but should not have any impact on inserts
-call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 't1', 'a' , '1' , 'extinout/nofile.del' ,
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T1', 'A' , '1' , 'extinout/nofile.del' ,
null, null, null, 0) ;
ERROR 38000: The exception 'SQL Exception: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. SQLSTATE: 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. SQLSTATE: XIE04: Data file not found: extinout/nofile.del
ij> select * from t1 ;
@@ -468,7 +468,7 @@
5
6
ij> --END IMPORT COMMIT/ROLLBACK TESTSING
------all types supported by DB2 cloudscape import/export
+-----all types supported by Derby import/export
create table alltypes(chartype char(20) ,
biginttype bigint ,
datetype date ,
@@ -521,10 +521,10 @@
'xxxxxxFILTERED-TIMESTAMPxxxxx,
'"varchar" testing');
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null) ;
Statement executed.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null, 0) ;
Statement executed.
ij> select * from alltypes ;
@@ -546,7 +546,7 @@
for each row mode db2sql
insert into test1 values(newrow.chartype);
0 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null, 0) ;
Statement executed.
ij> select count(*) from alltypes ;
@@ -561,7 +561,7 @@
"chartype" string
ij> delete from alltypes;
3 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'alltypes' , 'extinout/alltypes.del' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
null, null, null, 1) ;
ERROR 38000: The exception 'SQL Exception: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).' was thrown while evaluating an expression. SQLSTATE: 42Z08: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).
ij> select count(*) from alltypes;
@@ -592,7 +592,7 @@
ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select c1,c3,c4 from table2' , 'extinout/import.del' ,
null, null, null) ;
Statement executed.
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'table1', 'c1,c3,c4' , null, 'extinout/import.del',null, null,null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'TABLE1', 'C1,C3,C4' , null, 'extinout/import.del',null, null,null,0);
Statement executed.
ij> select * from table1;
C1 |C2 |C3 |C4
@@ -602,13 +602,13 @@
Leo |3 |23.4 |I
ij> delete from table1;
3 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'table2' , 'extinout/import.del', null, null, null) ;
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del', null, null, null) ;
Statement executed.
ij> --following import should fail becuase of inserting into identity column.
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'table1', 'extinout/import.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'C2'.
ij> --following import should be succesful
-CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'table1', 'c1,c3,c4' , '1,3,4', 'extinout/import.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'TABLE1', 'C1,C3,C4' , '1,3,4', 'extinout/import.del',null, null, null,1);
Statement executed.
ij> select * from table1;
C1 |C2 |C3 |C4
@@ -619,10 +619,10 @@
ij> update table2 set c2=null;
3 rows inserted/updated/deleted
ij> --check null values import to identity columns should also fail
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'table2' , 'extinout/import.del' ,
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del' ,
null, null, null) ;
Statement executed.
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'table1', 'extinout/import.del',null, null, null,1);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'C2'.
ij> select * from table1;
C1 |C2 |C3 |C4
@@ -644,7 +644,7 @@
null, null, null) ;
Statement executed.
ij> --replace should fail because of dependent table
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'parent', 'extinout/parent.del',null, null, null,1);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'PARENT', 'extinout/parent.del',null, null, null,1);
ERROR 38000: The exception 'SQL Exception: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.' was thrown while evaluating an expression. SQLSTATE: 23503: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.
ij> select * from parent;
A
@@ -656,7 +656,7 @@
ij> ---test with a file which has a differen records seperators (\n, \r , \r\n)
create table nt1( a int , b char(30));
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'nt1', 'extin/mixednl.del',null, null, null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'NT1', 'extin/mixednl.del',null, null, null,0);
Statement executed.
ij> select * from nt1;
A |B
@@ -689,7 +689,7 @@
web_flag varchar(1)
);
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'position_info', 'extin/position_info.del',
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extin/position_info.del',
null, null, null, 1);
Statement executed.
ij> select count(*) from position_info ;
@@ -701,12 +701,12 @@
-----
Essential Duties and Responsibilities (include but not limited to):
*Assist the director in his work activities in leading the&
-ij> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'position_info', 'extinout/pinfo.del',
+ij> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del',
null, null, null);
Statement executed.
ij> delete from position_info;
680 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'position_info', 'extinout/pinfo.del',
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('APP', 'POSITION_INFO', 'extinout/pinfo.del',
null, null, null, 1);
Statement executed.
ij> select count(*) from position_info ;
@@ -725,12 +725,12 @@
0 rows inserted/updated/deleted
ij> create table dest_by_default(i int generated by default as identity);
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,0);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'I'.
ij> select * from dest_always;
I
-----
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,0);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,0);
Statement executed.
ij> select * from dest_by_default;
I
@@ -746,12 +746,12 @@
0 rows inserted/updated/deleted
ij> create table dest_by_default(i int generated by default as identity);
0 rows inserted/updated/deleted
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,1);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_ALWAYS','extinout/autoinc.dat',null,null,null,1);
ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'I'.' was thrown while evaluating an expression. SQLSTATE: 42Z23: Attempt to modify an identity column 'I'.
ij> select * from dest_always;
I
-----
-ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,1);
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','DEST_BY_DEFAULT','extinout/autoinc.dat',null,null,null,1);
Statement executed.
ij> select * from dest_by_default;
I
@@ -763,4 +763,142 @@
0 rows inserted/updated/deleted
ij> drop table dest_by_default;
0 rows inserted/updated/deleted
+ij> --test case for bug (DERBY-390)
+-----test import/export with reserved words as table Name, column Names ..etc.
+create schema "Group";
+0 rows inserted/updated/deleted
+ij> create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
+0 rows inserted/updated/deleted
+ij> insert into "Group"."Order" values(1, 2, 'memory') ;
+1 row inserted/updated/deleted
+ij> insert into "Group"."Order" values(3, 4, 'disk') ;
+1 row inserted/updated/deleted
+ij> insert into "Group"."Order" values(5, 6, 'mouse') ;
+1 row inserted/updated/deleted
+ij> --following export should fail because schema name is not matching the way it is defined using delimited quotes.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('GROUP', 'Order' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Schema 'GROUP' does not exist' was thrown while evaluating an expression. SQLSTATE: 42Y07: Schema 'GROUP' does not exist
+ij> --following export should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'ORDER' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Table 'Group.ORDER' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table 'Group.ORDER' does not exist.
+ij> --following export should fail because of unquoted table name that is a reserved word.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group".Order' , 'extinout/order.dat' , null , null , null ) ;
+ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "Order" at line 1, column 23.' was thrown while evaluating an expression. SQLSTATE: 42X01: Syntax error: Encountered "Order" at line 1, column 23.
+ij> --following exports should pass.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null) ;
+Statement executed.
+ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from "Group"."Order"' , 'extinout/order.dat' , null , null , null ) ;
+Statement executed.
+ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select "select" , "delete" , itemName from "Group"."Order"' ,
+ 'extinout/order.dat' , null , null , null ) ;
+Statement executed.
+ij> --following import should fail because schema name is not matching the way it is defined using delimited quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('GROUP', 'Order' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'GROUP.Order' does not exist.
+ij> --following import should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'ORDER' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'Group.ORDER' does not exist.
+ij> --following import should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'ORDER' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE0M: Table 'Group.ORDER' does not exist.
+ij> --following import should fail because column name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'DELETE, ITEMNAME' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE08: There is no column named: DELETE.
+ij> --following import should fail because undelimited column name is not in upper case.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete, itemName' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE08: There is no column named: itemName.
+ij> --following imports should pass
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('Group', 'Order' , 'extinout/order.dat', null, null, null, 0) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+1 |2 |memory
+3 |4 |disk
+5 |6 |mouse
+1 |2 |memory
+3 |4 |disk
+5 |6 |mouse
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+1 |2 |memory
+3 |4 |disk
+5 |6 |mouse
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'delete' , '2' , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+NULL |2 |NULL
+NULL |4 |NULL
+NULL |6 |NULL
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('Group', 'Order' , 'ITEMNAME, select, delete' , '3,2,1' , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from "Group"."Order";
+select |delete |ITEMNAME
+-----
+2 |1 |memory
+4 |3 |disk
+6 |5 |mouse
+ij> drop table "Group"."Order";
+0 rows inserted/updated/deleted
+ij> ---test undelimited names( All unquoted SQL identfiers should be passed in upper case).
+create schema inventory;
+0 rows inserted/updated/deleted
+ij> create table inventory.orderTable(id int, amount int, itemName char(20)) ;
+0 rows inserted/updated/deleted
+ij> insert into inventory.orderTable values(101, 5, 'pizza') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(102, 6, 'coke') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(103, 7, 'break sticks') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(104, 8, 'buffolo wings') ;
+1 row inserted/updated/deleted
+ij> --following export should fail because schema name is not in upper case.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('inventory', 'ORDERTABLE' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Schema 'inventory' does not exist' was thrown while evaluating an expression. SQLSTATE: 42Y07: Schema 'inventory' does not exist
+ij> --following export should fail because table name is not in upper case.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ordertable' , 'extinout/order.dat', null, null, null) ;
+ERROR 38000: The exception 'SQL Exception: Table 'INVENTORY.ordertable' does not exist.' was thrown while evaluating an expression. SQLSTATE: 42X05: Table 'INVENTORY.ordertable' does not exist.
+ij> --following export should pass.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('INVENTORY', 'ORDERTABLE' , 'extinout/order.dat', null, null, null) ;
+Statement executed.
+ij> --following import should fail because schema name is not in upper case
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('inventory', 'ORDERTABLE' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'inventory.ORDERTABLE' does not exist.
+ij> --following import should fail because table name is not in upper case.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('INVENTORY', 'ordertable' , 'extinout/order.dat', null, null, null, 0) ;
+ERROR XIE0M: Table 'INVENTORY.ordertable' does not exist.
+ij> --following import should fail because table name is not in upper case .
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ordertable' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE0M: Table 'INVENTORY.ordertable' does not exist.
+ij> --following import should fail because column name is not in upper case.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'amount, ITEMNAME' , '2, 3' , 'extinout/order.dat' , null , null , null, 1) ;
+ERROR XIE08: There is no column named: amount.
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , null , null , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from inventory.orderTable;
+ID |AMOUNT |ITEMNAME
+-----
+101 |5 |pizza
+102 |6 |coke
+103 |7 |break sticks
+104 |8 |buffolo wings
+ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('INVENTORY', 'ORDERTABLE' , 'ITEMNAME, ID, AMOUNT' , '3,2,1' , 'extinout/order.dat' , null , null , null, 1) ;
+Statement executed.
+ij> select * from inventory.orderTable;
+ID |AMOUNT |ITEMNAME
+-----
+5 |101 |pizza
+6 |102 |coke
+7 |103 |break sticks
+8 |104 |buffolo wings
+ij> drop table inventory.orderTable;
+0 rows inserted/updated/deleted
+ij> --end derby-390 related test cases.
+;
ij>
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/I18NImportExport.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/I18NImportExport.out?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/I18NImportExport.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/I18NImportExport.out Fri Jul 1 09:33:55 2005
@@ -14,7 +14,7 @@
1 row inserted/updated/deleted
ij> insert into tab1 values(50.395, date('2000-11-29'), 'test row 6');
1 row inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'tab1' , 'extinout/tab1_fr.unl' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'TAB1' , 'extinout/tab1_fr.unl' ,
null, null, 'UTF8') ;
0 rows inserted/updated/deleted
ij> -- localized display is off
@@ -41,7 +41,7 @@
0 rows inserted/updated/deleted
ij> create table tab1( c1 decimal(5,3), c2 date, c3 char(20) );
0 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'tab1' , 'extinout/tab1_fr.unl' ,
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TAB1' , 'extinout/tab1_fr.unl' ,
null, null, 'UTF8', 0) ;
0 rows inserted/updated/deleted
ij> -- localized display is off
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/UnicodeEscape_JP.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/UnicodeEscape_JP.out?rev=208770&r1=208769&r2=208770&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/UnicodeEscape_JP.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/UnicodeEscape_JP.out Fri Jul 1 09:33:55 2005
@@ -50,7 +50,7 @@
13 |15:34:29|\u8ad6\u7406\u30ed\u30b0 3\u304c\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002
15 |15:35:35|sysmaster\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u4f5c\u6210\u306f\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002
16 |15:39:10|\u30c1\u30a7\u30c3\u30af\u30dd\u30a4\u30f3\u30c8\u304c\u5b8c\u4e86\u3057\u307e\u3057\u305f:\u7d99\u7d9a\u6642\u9593\u306f 8\u79&
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'jtest' , 'extout/jtest.unl' ,
+ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('APP', 'JTEST' , 'extout/jtest.unl' ,
null, null,'EUC_JP') ;
0 rows inserted/updated/deleted
ij>