You use the UNLOAD command to unload data and/or definitions from a Mimer SQL database to a file.
where file-format-spec is:
Any ODBC-based SQL interpreter or with the MIMLOAD program.
For information on MIMLOAD, see MIMLOAD.
The UNLOAD command generates data and/or definitions and places the result in a single file. You can use the options ALL (default), DEFINITIONS or DATA to 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.
UNLOAD and Data Escape Mode
UNLOAD generates data in escaped mode. This means that the data description header includes the data escape mode option.
When using data escape mode, the following characteristics are enabled, from the UNLOAD perspective:
- Data from a specific table is ended by the escape sequence '\_' to mark end-of-table.
- NULL values are indicated by the escape sequence '\-'.
- BLOB and BINARY columns are unloaded in HEX code with a leading '\x' escape sequence for each byte.
- BLOB and CLOB columns are unloaded so that the value length is given in front of the value as in the following CLOB example: '11:Abracadabra'
- For CHAR, NCHAR and CLOB columns, 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 STDOUT option, generated output is written to the standard output stream. This option is only allowed if the standard output stream is redirected to a file. For more information, see Using STDIN/STDOUT/STDERR.
The AS Option
By using the AS option together with a file name specification, you can select the character set of the generated file. You can choose: LATIN1 (default), UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.
UTFxxBE and UTFxxLE means UTFxx format with big or little endian byte order. UTFxx without endian notion means that the common endian for the current platform is assumed.
The default character set used, if you do not use the AS option, is the default used in your host operating system, i.e. on UNIX, LATIN1 or as indicated by the locale environment; on OpenVMS and Windows LATIN1.
For more information, see File Format Specifications.
The LOG Option
You can generate a log of the operation using the LOG option. The log file will include warnings and progress information about the operation. If you do not use the LOG option, warnings and progress information are suppressed.
The STDERR Option
When you use the LOG STDERR option, informational messages are written to the standard error stream. This option is only allowed if the standard error stream is redirected to a file. For more information, see Using STDIN/STDOUT/STDERR.
The USING and FROM Options
To specify the information to be unloaded, you use the USING or FROM options.
With the USING option, you can specify an SQL statement, such as SELECT * FROM T1; or a CALL to a procedure with output parameter markers 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.
With the FROM option, one or several databanks, tables or schemas can be used to form the source for the export operation. If using the FROM CURRENT USER option, 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 the result of the join is defined. The default name of the new table is table1.
The UNLOAD command runs until a major error is encountered. Minor problems are reported as warnings if LOG is 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 in ASCII format:UNLOAD DEFINITIONS TO 'table_t.def' FROM TABLE details;
The following example will export the CREATE statement for table details together with all data in the table to a file in UTF16 format. A log file is used:UNLOAD TO 'table_t.all' AS UTF16 LOG 'table_t.log' USING SELECT * FROM details;
The UNLOAD command is a Mimer SQL extension.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40