Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


UNLOAD - Unloading Data


You use the UNLOAD command to unload data and/or definitions from a Mimer SQL database to a file.

Syntax



where file-format-spec is:



Usage

Any ODBC-based SQL interpreter or with the MIMLOAD program.

For information on MIMLOAD, see EXPTOLOAD - Convert Old Format Export Files.

Description

The UNLOAD command generates data and/or definitions and places the result in a file.

If one file is specified in the UNLOAD command, 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 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.

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:

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. 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, 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 character set used, if you do not use the AS option, is UTF8 with BOM.

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. 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, an SQL statement, such as SELECT * FROM T1; or a CALL to 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.

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 of the result of the join is defined. The default name of the new table is table1.

Error Management

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.

Examples

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 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 following example will export the CREATE statement for table details to 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:

Element
Usage
Description
#data
Required
Data description header start identifier.
escape mode
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, LOAD assumes 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, LOAD assumes 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, LOAD assumes the empty string, i.e. two consecutive column separators, as a NULL value.
In data escape mode, '\-' is treated as the NULL value.
using insert-statement
Optional
The SQL INSERT statement that indicates where, and in what way, data should be loaded.
This statement is used in the situation where the LOAD statement itself does not include a USING clause.
;
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 LOAD:

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 t1 and the columns c1, c2 and c3 are supposed to exist when starting the data load. Specific characters for column separator, text qualifier and null indicator are 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 'x', 'u', '-' or '_'. See the following table for a description of valid escape character sequences:

Escape character sequence
Usage Description
Example
\x (lower case letter 'x')
Preceding a hexadecimal byte value. A HEX value is assumed to be two HEX value digits, i.e. 0-F.
\x1A
\u (lower case letter 'u')
Preceding a unicode value. A Unicode value is assumed to be eight HEX value digits, i.e. 0-F.
\u12345678
\- (dash)
Null value
\-
\_ (underscore)
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:

File Format
Description
latin1
ISO 8859-1, i.e. ISO's 8-bit single-byte coded graphic character set for Western languages.
 utf8
utf
16
utf32
Unicode Transformation Formats, standard character encoding schemes in accordance with ISO 10646.
For more information, see http://www.unicode.org
 utf16be

UTF16 format with big endian byte order.

 utf16le

UTF16 format with little endian byte order.

 utf32be

UTF32 format with big endian byte order.

 utf32le

UTF32 format with little endian byte order.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX