UNLOAD TO File
The UNLOAD TO file, as specified by the filename parameter, receives the retrieved rows.
You can use an UNLOAD TO file as input to a LOAD statement.
UNLOAD TO data formats in the default U.S. English locale
| Data Type | Output Format |
|---|---|
| BOOLEAN | BOOLEAN values appear as either t for TRUE or f for FALSE. |
| Character | If a character field contains the delimiter, Informix® products
automatically escape it with a backslash ( \ ) to
prevent interpretation as a special character. In the UNLOAD TO file,
a literal backslash character is represented as two consecutive backslash
characters ( \\ ). If you use the LOAD statement
of DB-Access to insert the rows into a table, the backslash escape
characters are automatically stripped from that table. |
| Collections | A collection is unloaded with its values between
braces ( { } ) and a delimiter between each element.
For more information, see Unloading Complex Types. |
| DATE | DATE values are represented as mm/dd/yyyy (or
the default format for the database locale), where mm is the
month (January = 1, and so on), dd is the
day, and yyyy is the year. If you have set the GL_DATE or DBDATE environment
variable, the UNLOAD statement uses the specified date format for
DATE values. |
| DATETIME, INTERVAL | Literal DATETIME and INTERVAL values appear as digits and delimiters, without keyword qualifiers, in the default format yyyy-mm-dd hh:mi:ss.fff. Time units outside the declared precision are omitted. If the GL_DATETIME or DBTIME environment variable is set, DATETIME values appear in the specified format. |
| DECIMAL, MONEY | Values are unloaded with no leading currency symbol. In the default locale, comma ( , ) is the thousands separator and period ( . ) is the decimal separator. If DBMONEY is set, UNLOAD uses its specified separators and currency format for MONEY values. |
| NULL | NULL appears as two delimiters with no characters between them. |
| Number | Values appear as literals, with no leading blanks.
For BIGINT, INTEGER, INT8, and SMALLINT data types, zero
appears as 0, For MONEY, FLOAT, SMALLFLOAT, and DECIMAL
data types, zero appears as 0.0. |
| ROW types (named and unnamed) | A ROW type is unloaded with its values enclosed between parentheses and a field delimiter separating each element. For more information, see Unloading Complex Types. |
| Simple large objects (TEXT, BYTE) | TEXT and BYTE columns are unloaded directly into the UNLOAD TO file. BYTE values appear in ASCII hexadecimal form, with no added white space or newline characters. For more information, see Unloading Simple Large Objects. |
| Smart large objects (CLOB, BLOB) | CLOB and BLOB columns are unloaded into a separate operating-system file (for each column) on the client computer. The CLOB or BLOB field in the UNLOAD TO file contains the name of this file. For more information, see Unloading Smart Large Objects. |
| User-defined data types (opaque types) | Opaque types must have an export( ) support function defined. They need special processing to copy data from the internal format of the opaque data type to the UNLOAD TO file format. An exportbinary( ) support function might also be required for data in binary format. The data in the UNLOAD TO file would correspond to the format that the export( ) or exportbinary( ) support function returns. |
UNLOAD TO data formats in nondefault locales
In nondefault locales, DATE, DATETIME, MONEY, and numeric column values have formats that the locale supports for these data types. For more information, see the Informix® GLS User's Guide. For more information on DBDATE, DBMONEY, and DBTIME environment variables, refer to the Informix® Guide to SQL: Reference.
In
databases that use a nondefault locale, if the GL_DATETIME environment
variable has a nondefault setting, the USE_DTENV environment
variable must be set to 1 before the UNLOAD statement
can correctly unload localized DATETIME values from the database table,
or from view, or from a table object defined by the EXTERNAL TABLE
statement. For more information on the GL_DATETIME, GL_DATE,
and USE_DTENV environment variables, refer to the Informix® GLS User's
Guide.