UNLOAD - Unloading Data
You use the
UNLOADcommand to unload data and/or definitions from a Mimer SQL database to a file.
Any ODBC-based SQL interpreter or with the
For information on
MIMLOAD, see MIMLOAD - Data Load and Unload.
UNLOADcommand generates data and/or definitions and places the result in a file.
If one file is specified in the
UNLOADcommand, both data and definitions will be placed in that file. If two files are specified, definitions will be placed in the first file, and data in the second file. (This makes it easier to change the table name before creating and loading the table.)
You can use the options
DATAto specify the information you want.
When generating the data and definitions, a data description header is created before information is written to the file. If information from several tables is generated, a data description header for each data section is created.
A data description header contains escaping information and column separator information. For more information, see Data Description Headers and Files.
Data Escape Mode
UNLOADgenerates data in escaped mode. This means that the data description header includes the
data escape modeoption.
data escape mode, the following characteristics are enabled, from the
- Data from a specific table is ended by the escape sequence '
\_' to mark end-of-table.
- Null values are indicated by the escape sequence '
BINARYcolumns are unloaded in HEX code with a leading '
\x' escape sequence for each byte.
NCLOBcolumns are unloaded so that the value length is given in front of the value as in the following
NCLOBcolumns, the escape sequence '
\x' is used only when there is binary data, such as ISO control codes, new-line characters, etc. in the data.
- The '
\u' escape sequence is used only when Unicode data is to be written to Latin1 files.
For information on escape sequences, see Escape Character Sequences.
The STDOUT Option
When you use the
STDOUToption, generated output is written to the standard output stream. See Using STDIN/STDOUT/STDERR.
The AS Option
By using the
ASoption together with a file name specification, you can select the character set of the generated file. You can choose:
UTFxxformat with big or little endian byte order.
UTFxxwithout endian notion means that the common endian for the current platform is assumed.
The character set used, if you do not use the AS option, is
For more information, see File Format Specifications.
The LOG Option
You can generate a log of the operation using the
LOGoption. The log file will include warnings and progress information about the operation. If you do not use the
LOGoption, warnings and progress information are suppressed.
The STDERR Option
When you use the
LOG STDERRoption, informational messages are written to the standard error stream. See Using STDIN/STDOUT/STDERR.
The USING and FROM Options
To specify the information to be unloaded, you use the
USINGoption, an SQL statement, such as
SELECT * FROM T1; or a
CALLto a procedure with parameter markers (
?) for output parameters only, can be used to specify the source.
By using an SQL statement to form the source for the export operation, there are many possibilities available to format and customize the output.
FROMoption, one or several databanks, tables or schemas can be used to form the source for the export operation. If using the
FROM CURRENT USERoption, the current ident is exported.
If tables are joined in the SQL statement used, and definitions are generated, a new table that is a reflection of the result of the join is defined. The default name of the new table is
UNLOADcommand runs until a major error is encountered. Minor problems are reported as warnings if
LOGis enabled. If a fatal error occurs, an error message is displayed and the operation is aborted.
The following example will export the table
details, with all related definitions, to a file:UNLOAD DEFINITIONS TO 'table_t.def' FROM TABLE details;
The following example will export the
CREATEstatement for table
detailstogether with all data in the table to a file in
UTF16format. A log file is used:UNLOAD TO 'table_t.all' AS UTF16 LOG 'table_t.log' USING SELECT * FROM details;
The following example will export the
CREATEstatement for table
detailsto the definitions file createtable.dat, and its data to another file tabledata.dat:UNLOAD ALL TO 'createtable.dat', 'tabledata.dat' FROM TABLE details;
Data Description Headers and Files
Data description headers and files are used to describe the data that follows.
The following table describes data description header elements:
Required Data description header start identifier.
Optional Indicates that the data is escaped, i.e. that some elements of the data are tagged for secure recognition at LOAD.See the table below. When using UNLOAD, data escape mode is always used.
column separator 'x
Optional Indicates which character is the column separator when reading the data.The default is the comma character (
,).If this option is not used,
LOADassumes that the comma character is the column separator.
text qualifier 'x
Optional Indicates which character is the qualifier for text strings in the data.The default is the double quote character (
").If this option is not used,
LOADassumes the double quote character as the text qualifier or unqualified data.
null indicator 'x
Optional Indicates which character is the null value if found in a data field.If this option is not stated,
LOADassumes the empty string, i.e. two consecutive column separators, as a null value.In data escape mode, '
\-' is treated as the null value.
Optional The SQL
INSERTstatement that indicates where, and in what way, data should be loaded.This statement is used in the situation where the
LOADstatement itself does not include a
Required Data description header end identifier.
As shown in the table above, the characters used to specify column separators, text qualifiers and null indicators must be enclosed in single quotes. If you use a single quote to specify a column separator, text qualifier or null indicator, you must enter it twice, for example, you would specify a single quote as a column separator as
Data Description Header Examples
For data unloaded from a Mimer SQL database using
UNLOAD, the data description header generated could look as follows:#data escape mode using insert into t (c) values (?);
The example above implies the following for
- The column separator is the comma character (default).
- Text strings are presumed to be unqualified or qualified with the double quote character.
- Data escaping is assumed (see the table below).
USINGstatement in the header will be used if no
USINGclause is given in the
The following is another example of a data description header where all optional elements mentioned above, except
data escape mode, are used:#data column separator ':' text qualifier '!' null indicator '§' using insert into t1 (c1,c2,c3) values (?,?,?);
In the example above, the table
t1and the columns
c3are supposed to exist when starting the data load. Specific characters for
null indicatorare defined.
Escape Character Sequences
If data escape mode is specified, the back-slash character (
\) is used as the escape character. The character following the escape character can be one of '
-' or '
_'. See the following table for a description of valid escape character sequences:
\x(lower case letter
Preceding a hexadecimal byte value. A HEX value is assumed to be two HEX value digits, i.e. 0-F.
\u(lower case letter
Preceding a unicode value. A Unicode value is assumed to be eight HEX value digits, i.e. 0-F.
End of table, including end of stream or file
Note: If you do not use data escape mode, end of file is treated as end of table. This means that such a data file only can contain data for one table.
File Format Specifications
The various file formats that can be used are described briefly in the following table:
ISO 8859-1, i.e. ISO's 8-bit single-byte coded graphic character set for Western languages.
Unicode Transformation Formats, standard character encoding schemes in accordance with ISO 10646.For more information, see http://www.unicode.org
UTF16format with big endian byte order.
UTF16format with little endian byte order.
UTF32format with big endian byte order.
UTF32format with little endian byte order.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40