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 su...@apache.org on 2007/03/15 02:27:00 UTC
svn commit: r518427 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/load/
testing/org/apache/derbyTesting/functionTests/tests/tools/
testing/org/apache/derbyTesting/junit/
Author: suresht
Date: Wed Mar 14 18:26:59 2007
New Revision: 518427
URL: http://svn.apache.org/viewvc?view=rev&rev=518427
Log:
DERBY-378 (partial)
This patch adds code to handles NULL (SQL NULL) data while
performing import/export of table with column types blob, clob.
Checks for invalid hex strings in the import file while performing
import into a table with Blob column. Import will throw an exception
if it detects any invalid hex strings in the import file for blob column.
Tests:
1) Added a new junit test ImportExportLobTest.java to
tests import/export of clobs and blob data.
2) Wrapped BufferInputStream/BufferedReader around the streams
used in BaseJDBCTestCase.java:assertEquals() methods to compare
clobs/blobs. Without buffering these assert method were really slow.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBaseTest.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportLobTest.java (with props)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportWriteData.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportAbstract.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportBlob.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBinaryDataTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportWriteData.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportWriteData.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportWriteData.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportWriteData.java Wed Mar 14 18:26:59 2007
@@ -203,30 +203,41 @@
long blobSize = 0;
int noBytes = 0 ;
- noBytes = istream.read(byteBuf) ;
- while(noBytes != -1)
- {
- lobOutBinaryStream.write(byteBuf, 0 , noBytes);
- blobSize += noBytes;
+ if (istream != null ) {
noBytes = istream.read(byteBuf) ;
+ while(noBytes != -1)
+ {
+ lobOutBinaryStream.write(byteBuf, 0 , noBytes);
+ blobSize += noBytes;
+ noBytes = istream.read(byteBuf) ;
+ }
+
+ // close the input stream.
+ istream.close();
+
+ // flush the output binary stream.
+ lobOutBinaryStream.flush();
+ } else {
+ // stream is null, column value must be SQL NULL.
+ // set the size to -1, on import columns will
+ // be interepreted as NULL, filename and offset are
+ // ignored.
+ blobSize = -1;
}
-
- // close the input stream.
- istream.close();
-
- // flush the output binary stream.
- lobOutBinaryStream.flush();
// Encode a lob location information as string. This is
// stored in the main export file. It will be used
// to retrive this blob data on import.
- // Format is : <fileName> : <lobOffset> : <size of lob>
- String lobLocation = lobsFileName + ":" +
- lobFileOffset + ":" + blobSize;
+ // Format is : <code > <fileName>.<lobOffset>.<size of lob>/ </code>.
+ // For a NULL blob, size will be written as -1
+
+ String lobLocation = lobsFileName + "." +
+ lobFileOffset + "." + blobSize + "/";
// update the offset, this will be where next
// large object data will be written.
- lobFileOffset += blobSize;
+ if (blobSize != -1)
+ lobFileOffset += blobSize;
return lobLocation;
}
@@ -250,35 +261,45 @@
long clobSize = 0;
int noChars = 0 ;
- noChars = ir.read(charBuf) ;
- while(noChars != -1)
- {
- // characters data is converted to bytes using
- // the user specified code set.
- lobByteArrayStream.reset();
- lobCharStream.write(charBuf, 0 , noChars);
- lobCharStream.flush();
+ if (ir != null ) {
+ noChars = ir.read(charBuf) ;
+ while(noChars != -1)
+ {
+ // characters data is converted to bytes using
+ // the user specified code set.
+ lobByteArrayStream.reset();
+ lobCharStream.write(charBuf, 0 , noChars);
+ lobCharStream.flush();
- clobSize += lobByteArrayStream.size();
- lobByteArrayStream.writeTo(lobOutBinaryStream);
- noChars = ir.read(charBuf) ;
+ clobSize += lobByteArrayStream.size();
+ lobByteArrayStream.writeTo(lobOutBinaryStream);
+ noChars = ir.read(charBuf) ;
+ }
+
+ // close the input reader.
+ ir.close();
+ // flush the output binary stream.
+ lobOutBinaryStream.flush();
+ } else {
+ // reader is null, the column value must be SQL NULL.
+ // set the size to -1, on import columns will
+ // be interepreted as NULL, filename and offset are
+ // ignored.
+ clobSize = -1;
}
- // close the input reader.
- ir.close();
- // flush the output binary stream.
- lobOutBinaryStream.flush();
-
// Encode this lob location information as string. This will
// be written to the main export file. It will be used
// to retrive this blob data on import.
- // Format is : <fileName> : <lobOffset> : <size of lob>
- String lobLocation = lobsFileName + ":" +
- lobFileOffset + ":" + clobSize;
+ // Format is : <code > <fileName>.<lobOffset>.<size of lob>/ </code>.
+ // For a NULL blob, size will be written as -1
+ String lobLocation = lobsFileName + "." +
+ lobFileOffset + "." + clobSize + "/";
// update the offset, this will be where next
// large object data will be written.
- lobFileOffset += clobSize;
+ if (clobSize != -1)
+ lobFileOffset += clobSize;
return lobLocation;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportAbstract.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportAbstract.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportAbstract.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportAbstract.java Wed Mar 14 18:26:59 2007
@@ -169,15 +169,47 @@
*/
public java.sql.Blob getBlob(int columnIndex) throws SQLException {
+ java.sql.Blob blob = null;
if (lobsInExtFile)
{
// lob data is in another file, read from the external file.
- return importReadData.getBlobColumnFromExtFile(
+ blob = importReadData.getBlobColumnFromExtFile(
nextRow[columnIndex-1]);
} else {
// data is in the main export file, stored in hex format.
- return new ImportBlob(nextRow[columnIndex-1]);
+ String hexData = nextRow[columnIndex-1];
+ byte[] data = null;
+ if (hexData != null) {
+ // Derby export calls Resultset.getString() method
+ // when blob column data is not exported to an
+ // external file. Derby getString() method return
+ // the data in hex format for binary types, by
+ // calling StringUtil.toHexString(). If the data
+ // is being imported from a file that exported
+ // from non-derby source, hex data is expected to be
+ // same format as one written using
+ // StringUtil.toHexString(). StringUtil.fromHexString()
+ // is used to covert the hex data to byte array.
+
+ data = StringUtil.fromHexString(
+ hexData, 0, hexData.length());
+ // fromHexString() returns null if the hex string
+ // is invalid one. It is invalid if the data string
+ // length is not multiple of 2 or the data string
+ // contains non-hex characters.
+ if (data == null) {
+ throw PublicAPI.wrapStandardException(
+ StandardException.newException(
+ SQLState.IMPORTFILE_HAS_INVALID_HEXSTRING,
+ hexData));
+ }
+
+ blob = new ImportBlob(data);
+ }
}
+
+ wasNull = (blob == null);
+ return blob;
}
@@ -201,6 +233,16 @@
wasNull = (hexData == null);
byte[] data = null;
if (hexData != null) {
+ // Derby export calls Resultset.getString() method
+ // to write binary data types. Derby getString()
+ // method return the data in hex format for binary types,
+ // by calling StringUtil.toHexString(). If the data
+ // is being imported from a file that is exported
+ // from non-derby source, hex data is expected to be
+ // same format as one written using
+ // StringUtil.toHexString(). StringUtil.fromHexString()
+ // is used to covert the hex data to byte array.
+
data = StringUtil.fromHexString(hexData, 0, hexData.length());
// fromHexString() returns null if the hex string is invalid one.
// It is invalid if the data string length is not multiple of 2
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportBlob.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportBlob.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportBlob.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportBlob.java Wed Mar 14 18:26:59 2007
@@ -62,13 +62,12 @@
/**
* Create a import Blob object, whose value is the give hex data string.
- * @param hexData data in the hex format, that this blob object contains.
+ * @param data byte array that contains the blob data.
*/
- public ImportBlob(String hexData)
+ public ImportBlob(byte[] data)
{
- // convert the given blob data in the hex format to a byte array.
- blobData = StringUtil.fromHexString(hexData, 0, hexData.length());
- blobLength = blobData.length;
+ blobData = data;
+ blobLength = data.length;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java Wed Mar 14 18:26:59 2007
@@ -944,7 +944,14 @@
{
try {
initExternalLobFile(lobLocationStr);
- return lobFile.getString(lobOffset,lobLength);
+ if (lobLength == -1 ){
+ // lob length -1 indicates columnn value is a NULL,
+ // just return null.
+ return null;
+ } else {
+ return lobFile.getString(lobOffset,lobLength);
+ }
+
}catch(Exception ex) {
throw LoadError.unexpectedError(ex);
}
@@ -960,7 +967,14 @@
throws SQLException
{
initExternalLobFile(lobLocationStr);
- return new ImportBlob(lobFile, lobOffset, lobLength);
+ if (lobLength == -1) {
+ // lob length -1 indicates columnn value is a NULL,
+ // just return null.
+ return null;
+ }
+ else {
+ return new ImportBlob(lobFile, lobOffset, lobLength);
+ }
}
/**
@@ -974,19 +988,22 @@
private void initExternalLobFile(String lobLocationStr)
throws SQLException
{
-
// extract file name, offset, and the length from the
// given lob location. Lob location string format is
- // fileName:offset:length
+ // <code > <fileName>.<lobOffset>.<size of lob>/ </code>.
+ // For a NULL blob, size will be -1
- int lengthIndex = lobLocationStr.lastIndexOf(":") ;
- int offsetIndex = lobLocationStr.lastIndexOf(":", lengthIndex -1);
-
- lobLength = Integer.parseInt(lobLocationStr.substring(lengthIndex + 1));
- lobOffset = Integer.parseInt(lobLocationStr.substring(offsetIndex+1,
- lengthIndex));
- lobFileName = lobLocationStr.substring(0 , offsetIndex);
+ int lengthIndex = lobLocationStr.lastIndexOf(".") ;
+ int offsetIndex = lobLocationStr.lastIndexOf(".",
+ lengthIndex -1);
+ lobLength = Integer.parseInt(lobLocationStr.substring(
+ lengthIndex + 1,
+ lobLocationStr.length() -1));
+ lobOffset = Integer.parseInt(lobLocationStr.substring(
+ offsetIndex+1,
+ lengthIndex));
+ lobFileName = lobLocationStr.substring(0 , offsetIndex);
if (lobFile == null) {
// open external file where the lobs are stored.
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBaseTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBaseTest.java?view=auto&rev=518427
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBaseTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBaseTest.java Wed Mar 14 18:26:59 2007
@@ -0,0 +1,261 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.
+ tools.ImportExportBaseTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+package org.apache.derbyTesting.functionTests.tests.tools;
+
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+
+
+/**
+ * This is base class for some of the import tests, provides
+ * methods to perform import/export using different procedures.
+ */
+
+public abstract class ImportExportBaseTest extends BaseJDBCTestCase {
+
+
+ public ImportExportBaseTest(String name) {
+ super(name);
+ }
+
+
+ /**
+ * Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
+ */
+ protected void doExportTable(String schemaName,
+ String tableName,
+ String fileName,
+ String colDel ,
+ String charDel,
+ String codeset) throws SQLException
+ {
+ String expsql =
+ "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)";
+ PreparedStatement ps = prepareStatement(expsql);
+ ps.setString(1, schemaName);
+ ps.setString(2, tableName);
+ ps.setString(3, fileName);
+ ps.setString(4, colDel);
+ ps.setString(5, charDel);
+ ps.setString(6, codeset);
+ ps.execute();
+ ps.close();
+ }
+
+
+
+ /**
+ * Perform export using SYSCS_UTIL.SYSCS_EXPORT_QUERY procedure.
+ */
+ protected void doExportQuery(String query,
+ String fileName,
+ String colDel ,
+ String charDel,
+ String codeset)
+ throws SQLException
+ {
+ String expsql =
+ "call SYSCS_UTIL.SYSCS_EXPORT_QUERY(? , ? , ? , ?, ?)";
+ PreparedStatement ps = prepareStatement(expsql);
+ ps.setString(1, query);
+ ps.setString(2, fileName);
+ ps.setString(3, colDel);
+ ps.setString(4, charDel);
+ ps.setString(5, codeset);
+ ps.execute();
+ ps.close();
+ }
+
+ /**
+ * Perform import using SYSCS_UTIL.SYSCS_IMPORT_TABLE procedure.
+ */
+ protected void doImportTable(String schemaName,
+ String tableName,
+ String fileName,
+ String colDel,
+ String charDel ,
+ String codeset,
+ int replace) throws SQLException
+ {
+ String impsql =
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)";
+ PreparedStatement ps = prepareStatement(impsql);
+ ps.setString(1 , schemaName);
+ ps.setString(2, tableName);
+ ps.setString(3, fileName);
+ ps.setString(4 , colDel);
+ ps.setString(5 , charDel);
+ ps.setString(6 , codeset);
+ ps.setInt(7, replace);
+ ps.execute();
+ ps.close();
+ }
+
+
+ /**
+ * Perform import using SYSCS_UTIL.SYSCS_IMPORT_DATA procedure.
+ */
+ protected void doImportData(String schemaName,
+ String tableName,
+ String insertCols,
+ String colIndexes,
+ String fileName,
+ String colDel,
+ String charDel ,
+ String codeset,
+ int replace) throws SQLException
+ {
+ String impsql =
+ "call SYSCS_UTIL.SYSCS_IMPORT_DATA(?, ?, ?, ?, ?, ?, ?, ?, ?)";
+ PreparedStatement ps = prepareStatement(impsql);
+ ps.setString(1, schemaName);
+ ps.setString(2, tableName);
+ ps.setString(3, insertCols);
+ ps.setString(4, colIndexes);
+ ps.setString(5, fileName);
+ ps.setString(6 , colDel);
+ ps.setString(7 , charDel);
+ ps.setString(8 , codeset);
+ ps.setInt(9, replace);
+ ps.execute();
+ ps.close();
+ }
+
+
+ /**
+ * Perform export using
+ * SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE procedure.
+ */
+ protected void doExportTableLobsInExtFile(String schemaName,
+ String tableName,
+ String fileName,
+ String colDel ,
+ String charDel,
+ String codeset,
+ String lobsFileName)
+ throws SQLException
+ {
+ String expsql =
+ "call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE" +
+ "(? , ? , ? , ?, ?, ?, ?)";
+ PreparedStatement ps = prepareStatement(expsql);
+ ps.setString(1, schemaName);
+ ps.setString(2, tableName);
+ ps.setString(3, fileName);
+ ps.setString(4, colDel);
+ ps.setString(5, charDel);
+ ps.setString(6, codeset);
+ ps.setString(7, lobsFileName);
+ ps.execute();
+ ps.close();
+ }
+
+
+
+ /**
+ * Perform export using
+ * SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE procedure.
+ */
+ protected void doExportQueryLobsInExtFile(String query,
+ String fileName,
+ String colDel ,
+ String charDel,
+ String codeset,
+ String lobsFileName)
+ throws SQLException
+ {
+ String expsql =
+ "call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE" +
+ "(? , ? , ? , ?, ?, ?)";
+ PreparedStatement ps = prepareStatement(expsql);
+ ps.setString(1, query);
+ ps.setString(2, fileName);
+ ps.setString(3, colDel);
+ ps.setString(4, charDel);
+ ps.setString(5, codeset);
+ ps.setString(6, lobsFileName);
+ ps.execute();
+ ps.close();
+ }
+
+ /**
+ * Perform import using
+ * SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE procedure.
+ */
+ protected void doImportTableLobsInExtFile(String schemaName,
+ String tableName,
+ String fileName,
+ String colDel,
+ String charDel ,
+ String codeset,
+ int replace)
+ throws SQLException
+ {
+ String impsql =
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE" +
+ "(?, ?, ?, ?, ?, ?, ?)";
+ PreparedStatement ps = prepareStatement(impsql);
+ ps.setString(1 , schemaName);
+ ps.setString(2, tableName);
+ ps.setString(3, fileName);
+ ps.setString(4 , colDel);
+ ps.setString(5 , charDel);
+ ps.setString(6 , codeset);
+ ps.setInt(7, replace);
+ ps.execute();
+ ps.close();
+ }
+
+
+ /**
+ * Perform import using
+ * SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE procedure.
+ */
+ protected void doImportDataLobsInExtFile(String schemaName,
+ String tableName,
+ String insertCols,
+ String colIndexes,
+ String fileName,
+ String colDel,
+ String charDel ,
+ String codeset,
+ int replace)
+ throws SQLException
+ {
+ String impsql =
+ "call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE" +
+ "(?, ?, ?, ?, ?, ?, ?, ?, ?)";
+ PreparedStatement ps = prepareStatement(impsql);
+ ps.setString(1, schemaName);
+ ps.setString(2, tableName);
+ ps.setString(3, insertCols);
+ ps.setString(4, colIndexes);
+ ps.setString(5, fileName);
+ ps.setString(6 , colDel);
+ ps.setString(7 , charDel);
+ ps.setString(8 , codeset);
+ ps.setInt(9, replace);
+ ps.execute();
+ ps.close();
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBaseTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBinaryDataTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBinaryDataTest.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBinaryDataTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportBinaryDataTest.java Wed Mar 14 18:26:59 2007
@@ -41,7 +41,7 @@
* CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA.
*/
-public class ImportExportBinaryDataTest extends BaseJDBCTestCase {
+public class ImportExportBinaryDataTest extends ImportExportBaseTest {
String fileName; // file used to perform import/export.
@@ -306,109 +306,6 @@
s2.close();
}
-
- /**
- * Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
- */
- private void doExportTable(String schemaName,
- String tableName,
- String fileName,
- String colDel ,
- String charDel,
- String codeset) throws SQLException
- {
- String expsql =
- "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)";
- PreparedStatement ps = prepareStatement(expsql);
- ps.setString(1, schemaName);
- ps.setString(2, tableName);
- ps.setString(3, fileName);
- ps.setString(4, colDel);
- ps.setString(5, charDel);
- ps.setString(6, codeset);
- ps.execute();
- ps.close();
- }
-
-
-
- /**
- * Perform export using SYSCS_UTIL.SYSCS_EXPORT_QUERY procedure.
- */
- private void doExportQuery(String query,
- String fileName,
- String colDel ,
- String charDel,
- String codeset)
- throws SQLException
- {
- String expsql =
- "call SYSCS_UTIL.SYSCS_EXPORT_QUERY(? , ? , ? , ?, ?)";
- PreparedStatement ps = prepareStatement(expsql);
- ps.setString(1, query);
- ps.setString(2, fileName);
- ps.setString(3, colDel);
- ps.setString(4, charDel);
- ps.setString(5, codeset);
- ps.execute();
- ps.close();
- }
-
- /**
- * Perform import using SYSCS_UTIL.SYSCS_IMPORT_TABLE procedure.
- */
- private void doImportTable(String schemaName,
- String tableName,
- String fileName,
- String colDel,
- String charDel ,
- String codeset,
- int replace) throws SQLException
- {
- String impsql =
- "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)";
- PreparedStatement ps = prepareStatement(impsql);
- ps.setString(1 , schemaName);
- ps.setString(2, tableName);
- ps.setString(3, fileName);
- ps.setString(4 , colDel);
- ps.setString(5 , charDel);
- ps.setString(6 , codeset);
- ps.setInt(7, replace);
- ps.execute();
- ps.close();
- }
-
-
- /**
- * Perform import using SYSCS_UTIL.SYSCS_IMPORT_DATA procedure.
- */
- private void doImportData(String schemaName,
- String tableName,
- String insertCols,
- String colIndexes,
- String fileName,
- String colDel,
- String charDel ,
- String codeset,
- int replace) throws SQLException
- {
- String impsql =
- "call SYSCS_UTIL.SYSCS_IMPORT_DATA(?, ?, ?, ?, ?, ?, ?, ?, ?)";
- PreparedStatement ps = prepareStatement(impsql);
- ps.setString(1, schemaName);
- ps.setString(2, tableName);
- ps.setString(3, insertCols);
- ps.setString(4, colIndexes);
- ps.setString(5, fileName);
- ps.setString(6 , colDel);
- ps.setString(7 , charDel);
- ps.setString(8 , codeset);
- ps.setInt(9, replace);
- ps.execute();
- ps.close();
- }
-
/*
* Insert data to the into the table, whose data will be exported.
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportLobTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportLobTest.java?view=auto&rev=518427
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportLobTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportLobTest.java Wed Mar 14 18:26:59 2007
@@ -0,0 +1,463 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.
+ tools.ImportExportLobTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+package org.apache.derbyTesting.functionTests.tests.tools;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Connection;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.Reader;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+import org.apache.derbyTesting.junit.JDBC;
+
+import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader;
+import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
+
+
+/**
+ * This class tests import/export of a table with data types clob and blob.
+ */
+
+public class ImportExportLobTest extends ImportExportBaseTest
+{
+
+ String fileName; // main file used to perform import/export.
+ String lobsFileName; // file name used to store lobs.
+
+ public ImportExportLobTest(String name) throws SQLException {
+ super(name);
+ // set the file that is used by the import/export test cases.
+ fileName =
+ (SupportFilesSetup.getReadWrite("books.del")).getPath();
+ lobsFileName =
+ (SupportFilesSetup.getReadWrite("books_lobs.dat")).getPath();
+ }
+
+ /**
+ * Runs the tests in the default embedded configuration and then
+ * the client server configuration.
+ */
+ public static Test suite()
+ {
+ TestSuite suite = new TestSuite(ImportExportLobTest.class);
+ suite.addTest(TestConfiguration.clientServerSuite(
+ ImportExportLobTest.class));
+ Test test = suite;
+ test = new SupportFilesSetup(test);
+ return new CleanDatabaseTestSetup(test) {
+ protected void decorateSQL(Statement s) throws SQLException {
+ // table used to test export.
+ s.execute("CREATE TABLE BOOKS(id int," +
+ "name varchar(30)," +
+ "content clob, " +
+ "pic blob )");
+ // load some data into the above table.
+ loadData(s);
+ // table used to test import.
+ s.execute("CREATE TABLE BOOKS_IMP(id int," +
+ "name varchar(30)," +
+ "content clob," +
+ "pic blob )");
+ // table that holds some invalid hex strings.
+ s.execute("CREATE TABLE hex_tab(id int," +
+ "C1 varchar(20)," +
+ "C2 varchar(20)," +
+ "C3 varchar(20))");
+ }
+ };
+ }
+
+
+ /**
+ * Simple set up, just empty the import table.
+ * @throws SQLException
+ */
+ protected void setUp() throws SQLException
+ {
+ Statement s = createStatement();
+ // delete the rows from the import table.
+ s.executeUpdate("DELETE FROM BOOKS_IMP");
+ s.close();
+ }
+
+
+
+ /**
+ * Test import/export of a table, using
+ * SYSCS_EXPORT_TABLE and SYSCS_IMPORT_TABLE procedures.
+ */
+ public void testImportTableExportTable()
+ throws SQLException, IOException
+ {
+ doExportTable("APP", "BOOKS", fileName, null, null , null);
+ doImportTable("APP", "BOOKS_IMP", fileName, null, null, null, 0);
+ verifyData(" * ");
+ }
+
+
+ /*
+ * Test import/export of all the columns using
+ * SYSCS_EXPORT_QUERY and SYSCS_IMPORT_DATA procedures.
+ */
+ public void testImportDataExportQuery()
+ throws SQLException, IOException
+ {
+ doExportQuery("select * from BOOKS", fileName,
+ null, null , null);
+ doImportData(null, "BOOKS_IMP", null, null, fileName,
+ null, null, null, 0);
+ verifyData(" * ");
+
+ // perform import with column names specified in random order.
+ doImportData(null, "BOOKS_IMP", "PIC, CONTENT, NAME, ID",
+ "4, 3, 2, 1", fileName, null, null, null, 1);
+ verifyData("PIC, CONTENT, NAME, ID");
+
+ // test with non-default delimiters.
+ doExportQuery("select * from BOOKS_IMP", fileName,
+ ";", "%" , null);
+ doImportData(null, "BOOKS_IMP", null, null, fileName,
+ ";", "%", null, 1);
+
+ }
+
+
+ /*
+ * Test import of only some columns of the table
+ * using SYSCS_EXPOR_QUERY and IMPORT_DATA procedures.
+ */
+ public void testImportDataExportQueryWithFewColumns()
+ throws SQLException, IOException
+ {
+ doExportQuery("select id, name, content, pic from BOOKS",
+ fileName, null, null, null);
+ doImportData(null, "BOOKS_IMP", "ID,PIC", "1 , 4",
+ fileName, null, null, null, 0);
+ verifyData("ID,PIC");
+ doImportData(null, "BOOKS_IMP", "ID, PIC, NAME", "1, 4, 2",
+ fileName, null, null, null, 1);
+ verifyData("ID, PIC, NAME");
+ doImportData(null, "BOOKS_IMP", "ID, CONTENT, NAME", "1, 3, 2",
+ fileName, null, null, null, 1);
+ verifyData("ID, CONTENT, NAME");
+
+ // test with non-default delimiters.
+ doExportQuery("select id, name, content, pic from BOOKS",
+ fileName, "$", "!" , null);
+ doImportData(null, "BOOKS_IMP", "ID,PIC", "1 , 4",
+ fileName, "$", "!", null, 0);
+ }
+
+
+
+
+ /*
+ * Test import procedures with invalid hex strings in
+ * the import file for the blob column.
+ */
+ public void testImportWithInvalidHexStrings()
+ throws SQLException
+ {
+ Statement s = createStatement();
+ // Insert row with correctly formed hex strings.
+ s.executeUpdate("insert into hex_tab " +
+ "values(1, 'row 1', 'clob 1', 'B1C201DA')");
+
+ // Insert row with an invalid hex string, because
+ // it's length is not a multiple of 2 (B1C201A) ,
+ s.executeUpdate("insert into hex_tab " +
+ "values(2, 'row 2', 'clob2 ', 'B1C201A')");
+
+ // Insert row with an invalid hex string that contains
+ // a non-hex character (3122A1F20Z).
+ s.executeUpdate("insert into hex_tab " +
+ "values(3, '', 'clobs 3', '3122A1F20Z')");
+
+ // Insert row with an invalid hex string that contains
+ // a delimiter character (B1C2\"01DA).
+ s.executeUpdate("insert into hex_tab " +
+ "values(4, 'row \"4', '3122A1F20Z', 'B1C2\"01DA')");
+ s.close();
+
+ // export the invalid hex strings from the table to a file.
+ doExportTable("APP", "HEX_TAB", fileName, null, null , null);
+
+
+ // attempt to import the invalid hex string data into a table
+ // with binary columns. It should fail.
+
+ try {
+
+ doExportQuery("select * from hex_tab where id <= 2",
+ fileName, null, null, null);
+ // import should fail because of invalied hex string length
+ doImportTable("APP", "BOOKS_IMP", fileName, null, null, null, 0);
+ fail("import did not fail on data with invalid hex string");
+ } catch (SQLException e) {
+ assertSQLState(JDBC.vmSupportsJDBC4() ? "38000": "XIE0N", e);
+ }
+
+ try {
+ doExportQuery("select * from hex_tab where id = 3",
+ fileName, null, null, null);
+ // import should fail because hex string contains invalid
+ // hex chatacters.
+ doImportData(null, "BOOKS_IMP", "ID, PIC", "1,4",
+ fileName, null, null, null, 1);
+ fail("import did not fail on data with invalid hex strings");
+ } catch (SQLException e) {
+ assertSQLState(JDBC.vmSupportsJDBC4() ? "38000": "XIE0N", e);
+ }
+
+ try {
+ doExportQuery("select * from hex_tab where id = 4",
+ fileName, null, null, null);
+ // import should fail because hex string contains invalid
+ // hex chatacters.
+ doImportData(null, "BOOKS_IMP", "ID, PIC", "1,4",
+ fileName, null, null, null, 1);
+ fail("import did not fail on data with invalid hex strings");
+ } catch (SQLException e) {
+ assertSQLState(JDBC.vmSupportsJDBC4() ? "38000": "XIE0N", e);
+ }
+ }
+
+
+ /**
+ * Test import/export of a table, using
+ * SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE and
+ * SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE procedures.
+ */
+ public void testImportTableExportTableLobsInExtFile()
+ throws SQLException, IOException
+ {
+ doExportTableLobsInExtFile("APP", "BOOKS", fileName,
+ null, null , null, lobsFileName);
+ doImportTableLobsInExtFile("APP", "BOOKS_IMP", fileName,
+ null, null, null, 0);
+ verifyData(" * ");
+ }
+
+
+ /*
+ * Test import/export of all the columns using
+ * SYSCS_EXPORT_QUERY and SYSCS_IMPORT_DATA procedures.
+ */
+ public void testImportDataExportQueryLobsInExtFile()
+ throws SQLException, IOException
+ {
+ doExportQueryLobsInExtFile("select * from BOOKS", fileName,
+ null, null , null, lobsFileName);
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", null, null, fileName,
+ null, null, null, 0);
+ verifyData(" * ");
+
+ // perform import with column names specified in random order.
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", "PIC, CONTENT, NAME, ID",
+ "4, 3, 2, 1", fileName, null, null, null, 1);
+ verifyData("PIC, CONTENT, NAME, ID");
+
+ // test with non-default delimiters.
+ doExportQueryLobsInExtFile("select * from BOOKS_IMP", fileName,
+ ";", "%" , null, lobsFileName);
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", null, null, fileName,
+ ";", "%", null, 1);
+
+ }
+
+
+ /*
+ * Test import of only some columns of the table
+ * using SYSCS_EXPOR_QUERY and IMPORT_DATA procedures.
+ */
+ public void testImportDataExportQueryWithFewColsLobsInExtFile()
+ throws SQLException, IOException
+ {
+ doExportQueryLobsInExtFile("select id, name, content, pic from BOOKS",
+ fileName, null, null, null, lobsFileName);
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", "ID,PIC", "1 , 4",
+ fileName, null, null, null, 0);
+ verifyData("ID,PIC");
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", "ID, PIC, NAME", "1, 4, 2",
+ fileName, null, null, null, 1);
+ verifyData("ID, PIC, NAME");
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", "ID, CONTENT, NAME",
+ "1, 3, 2", fileName, null, null, null, 1);
+ verifyData("ID, CONTENT, NAME");
+
+ // test with non-default delimiters.
+ doExportQueryLobsInExtFile("select id, name, content, pic from BOOKS",
+ fileName, "$", "!" , null, lobsFileName);
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", "ID,PIC", "1 , 4",
+ fileName, "$", "!", null, 0);
+ }
+
+
+ /*
+ * Test lobs in exteranl file import/export procedures
+ * with invalid delimiters.
+ */
+ public void testImportExportInvalideDelimiters()
+ throws SQLException, IOException
+ {
+ try {
+ doExportTableLobsInExtFile("APP", "BOOKS", fileName,
+ null, "9" , null, lobsFileName);
+ } catch (SQLException e) {
+ assertSQLState("XIE0J", e);
+ }
+
+ try {
+ doExportQueryLobsInExtFile("select * from BOOKS", fileName,
+ "|", "f", null, lobsFileName);
+ } catch (SQLException e) {
+ assertSQLState("XIE0J", e);
+ }
+
+ doExportQueryLobsInExtFile("select * from BOOKS where id < 10",
+ fileName, null, null, null, lobsFileName);
+
+
+ /* Currently BaseJDBCTestCase.assertSQLState() is unable
+ * to find nested SQLSTATEs with 1.6 JVMs, so we have to
+ * check for the top-level SQLSTATE in that case. When
+ * that changes the "JDBC.vmSupportsJDBC4()" call can be
+ * removed from the following assertSQLState() calls.
+ * (DERBY-1440)
+ */
+
+ try {
+ doImportTableLobsInExtFile("APP", "BOOKS_IMP", fileName, "2",
+ null, null, 0);
+ } catch (SQLException e) {
+ assertSQLState(JDBC.vmSupportsJDBC4() ? "38000": "XIE0J", e);
+ }
+
+ try {
+ doImportDataLobsInExtFile(null, "BOOKS_IMP", null,
+ null, fileName, null, "c", null, 1);
+ } catch (SQLException e) {
+ assertSQLState(JDBC.vmSupportsJDBC4() ? "38000": "XIE0J", e);
+ }
+ }
+
+
+ /*
+ * Verifies data in the import test table (BOOKS_IMP) is same
+ * as the test table from which the data was exported earlier(BOOKS).
+ * @param cols imported columns , if all then " * ", otherwise
+ * comma separated column list.
+ * @exception SQLException if the data does match or if
+ * any other error during comparision.
+ */
+ private void verifyData(String cols)
+ throws SQLException, IOException
+ {
+ Statement s1 = createStatement();
+ ResultSet rsExport = s1.executeQuery("SELECT " + cols +
+ " FROM BOOKS order by id");
+ Statement s2 = createStatement();
+ ResultSet rsImport = s2.executeQuery("SELECT " + cols +
+ " FROM BOOKS_IMP order by id");
+ JDBC.assertSameContents(rsExport, rsImport);
+
+ s1.close();
+ s2.close();
+ }
+
+
+ /*
+ * Insert data to the into the table, whose data will be exported.
+ */
+ private static void loadData(Statement s) throws SQLException {
+
+ s.executeUpdate("insert into books values " +
+ "(1, 'book 1', 'clob 1'," +
+ "cast(X'3743640ADE12337610' as blob))");
+ // rows with empty strings.
+ s.executeUpdate("insert into books values " +
+ "(2, 'book 2', 'clob 2', cast (X'' as blob))");
+ s.executeUpdate("insert into books values " +
+ "(3, 'book 3', '', cast(X'42' as blob))");
+ s.executeUpdate("insert into books values " +
+ "(4, 'book 4', 'clob 4', " +
+ "cast (X'3233445578990122558820' as blob))");
+
+ // rows with a null
+ s.executeUpdate("insert into books values " +
+ "(5, null, 'clob 5'," +
+ "cast(X'3843640ADE12337610' as blob))");
+ s.executeUpdate("insert into books values " +
+ "(6, 'book 6', null, " +
+ "cast(X'3843640ADE12337610' as blob))");
+ s.executeUpdate("insert into books values " +
+ "(7, 'book 7', 'clob 7' , null)");
+ s.executeUpdate("insert into books values " +
+ "(8, '', null, cast (X'3843640ADE12' as blob))");
+ s.executeUpdate("insert into books values " +
+ "(9, 'book 9', null, cast (X'' as blob))");
+
+ // insert data that contains some delimiter characters
+ // ( "(x22) ,(x2C) %(x25) ;(x3B) , tab(9) LF(A) )
+ s.executeUpdate("insert into books values " +
+ "(10, 'book ;10', '%asdadasdasd'," +
+ " cast (X'222C23B90A' as blob))");
+ // !(x21) $(24)
+ s.executeUpdate("insert into books values " +
+ "(11, '212C3B24', '2422412221', " +
+ " cast (X'212421222C23B90A2124' as blob))");
+
+ // insert some more randomly genrated data.
+ Connection conn = s.getConnection();
+ String sql = "insert into books values(? , ? , ? , ?)";
+ PreparedStatement ps = conn.prepareStatement(sql);
+ int blobSize = 0;
+ int id = 12;
+ for (int i = 0 ; i < 17 ; i++) {
+ ps.setInt(1 , id++);
+ ps.setString(2 , "book" +i);
+ blobSize += 1024 * i;
+ int clobSize = 1024 * i;
+ Reader reader = new LoopingAlphabetReader(clobSize);
+ ps.setCharacterStream(3, reader, clobSize);
+ InputStream stream = new LoopingAlphabetStream(blobSize);
+ ps.setBinaryStream(4, stream, blobSize);
+ ps.executeUpdate();
+
+ if ((i % 10) == 0)
+ conn.commit();
+ }
+
+ ps.close();
+ conn.commit();
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportLobTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/_Suite.java Wed Mar 14 18:26:59 2007
@@ -48,6 +48,7 @@
suite.addTest(IJRunScriptTest.suite());
suite.addTest(ImportExportTest.suite());
suite.addTest(ImportExportBinaryDataTest.suite());
+ suite.addTest(ImportExportLobTest.suite());
// SysinfoAPITest currently fails when run against jars, so is
// disabled. Only the first jar file on the classpath properly
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?view=diff&rev=518427&r1=518426&r2=518427
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Wed Mar 14 18:26:59 2007
@@ -23,6 +23,8 @@
import java.io.InputStream;
import java.io.IOException;
import java.io.OutputStream;
+import java.io.BufferedInputStream;
+import java.io.BufferedReader;
import java.io.PrintStream;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
@@ -401,11 +403,17 @@
b1.length(), b2.length());
InputStream is1 = b1.getBinaryStream();
InputStream is2 = b2.getBinaryStream();
+
if (is1 == null || is2 == null) {
assertNull("Blob b2 has null-stream, blob b1 doesn't", is1);
assertNull("Blob b1 has null-stream, blob b2 doesn't", is2);
return;
}
+
+ // wrap buffered stream around the binary stream
+ is1 = new BufferedInputStream(is1);
+ is2 = new BufferedInputStream(is2);
+
long index = 1;
int by1 = is1.read();
int by2 = is2.read();
@@ -447,6 +455,10 @@
assertNotNull(r1); // java.sql.Blob object cannot represent NULL
Reader r2 = c2.getCharacterStream();
assertNotNull(r2); // java.sql.Blob object cannot represent NULL
+
+ // wrap buffered reader around the character stream
+ r1 = new BufferedReader(r1);
+ r2 = new BufferedReader(r2);
long index = 1;
int ch1 = r1.read();