Using the Bulk Import and Export Utilities
Page 5 of 12

Bulk Import and Export From an SQL-J Statement

You can use the bulk import and bulk export utilities within an SQL-J statement, which is executable from ij, Cloudview, or any Java program.

The reason you can run these utilities from an SQL-J statement is because the class COM.cloudscape.tools.FileImport fulfills the Virtual Table Interface (VTI) requirements. This class opens an ASCII text file and presents the content of the file to Cloudscape as a virtual external table that can be accessed within the FROM clause of a SELECT statement. COM.cloudscape.tools.FileExport exports the result set from a query to an ASCII text file.

To Bulk-Import

There are two ways to bulk-import within an SQL-J statement:

  • Create a new instance of the COM.cloudscape.tools.FileImport class (aliased as FileImport) in the FROM clause of an insert. This instance constitutes an ExternalVirtualTable (described in the Cloudscape Reference Manual) which can be used in a FROM clause in place of a tableExpression.

INSERT INTO tableName [ PROPERTIES propertyList ]
SELECT selectItems
FROM NEW FileImport( constructorParameters)
[ AS ] correlationName [ derivedColumnList ]

  • Call the static method Import, specifying the table name, file name, and control file name as parameters. (The method constructs and executes the SQL-J statement.)

CALL FileImport.Import( staticMethodParameters)

Arguments to the Import Command

  • tableName

    The name of the table into which you want to bulk-import the data (qualified by the schema name if necessary).

  • propertyList

    Properties for the bulk import. See Import Properties.

  • selectItems

    Projects one or more columns to be inserted in the form of derivedColumnName [, derivedColumnName]*.

  • constructorParameters

    The parameters taken by the COM.cloudscape.tools.FileImport constructor. See Parameter Sets for the FileImport Constructor.

  • staticMethodParameters

    The parameters taken by static Import method of COM.cloudscape.tools.FileImport. See Parameter Sets for the Static Import Method in FileImport.

  • correlationName

    A correlation name for the table, required by the FROM clause.

  • derived column list

    Optional names of the columns returned by the utility. By default, the import utility names the columns COLUMN1, COLUMN2, ... , COLUMNX. These are the names that you reference in selectItems.

Parameter Sets for the FileImport Constructor

Parameter Sets for the Static Import Method in FileImport

For examples, see Examples of Bulk Import and Export Within SQL-J Statements.

To Bulk-Export

Use an instance of the COM.cloudscape.tools.FileExport class (aliased as FileExport), specifying the table or view from which to export and the name of the output file. If you need to use a format different from the default format, also specify the name of the control file created with Cloudview.

To export the entire table, specify a table name. To export data from any select statement, define a view as the SQL-J statement from which you want to export data. For example, to export data from a join, create a view to represent the join:

CREATE VIEW joinViewForExport AS
SELECT country, city.getName()
FROM Countries JOIN Cities
USING (country_ISO_code)

Then export from the view.

CALL FileExport.Export( constructorParameters)

Parameter Sets for COM.cloudscape.tools.FileExport.Export

  • (java.sql.Connection, TableOrViewNameString, OutputFileURL)
  • (java.sql.Connection, TableOrViewNameString, OutputFileURL, ControlFileURL)

    java.sql.Connection is the connection to the Cloudscape database. TableOrViewName is the name of a table or a view in Cloudscape from which to export data. Qualify the table or view name with the schema name if it is not the default schema, APP.

    OutputFileURL is the URL (of type file:// only) of the ASCII output file accessible to the machine running Cloudscape without going through any network transport protocol. The output file must be of the default file format (see Default Input or Output File Format). OutputFileURL must be enclosed in single quotes.

    ControlFileURL is the URL of the control file. It must be accessible to the machine running Cloudscape without going through any network transport protocol. ControlFileURL must be enclosed in single quotes.

    See About the Control File.

    TableOrViewName, OutputFileURL, and ControlFileURL must be enclosed in single quotes if they are constants.

For examples, see Examples of Bulk Import and Export Within SQL-J Statements.