[top]
[prev]
[next]

Table of Contents
Index
Documentation Top
Global Index
|
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
- java.sql.Connection, TableNameString, InputFileURL
- java.sql.Connection, TableNameString, InputFileURL, ImportPropertiesObject
- java.sql.Connection, TableNameString, InputFileURL, ControlFileURL
- java.sql.Connection, TableNameString, InputFileURL, ControlFileURL, importPropertiesObject
Table 6-1 Parameters to the Import Methods
Parameter |
Description |
InputFileURL
|
The path name or URL of the ASCII input file accessible to the machine running Cloudscape. The input file must be of the default file format (see Default Input or Output File Format). If it is a literal, InputFileURL must be enclosed in single quotes. The path separator in the URL is / (forward slash), per the standard file:// URL protocol. Absolute or relative paths are accepted.
|
ControlFileURL
|
If it is a literal, the URL of the control file must be enclosed in single quotes.
For information about the control file, see About the Control File.
|
TableNameString
|
The name of the table into which you want to import data. If it is a literal, enclose it in single quotes.
|
ImportPropertiesObject
|
A Properties object representing import properties to use for the insert. See Import Properties.
|
java.sql.Connection
|
A connection to the pertinent database. Use getCurrentConnection() within an SQL-J statement.
|
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.
|