UNLOAD

Copies data from the database tables into a file.

Syntax

UNLOAD TO filename [ DELIMITER delimiter]
{
  select-statement
|
  select-string
}

filename is a string expression containing the name of the file the data is written to.
delimiter is the character used as the value delimiter.
select-statement is static SELECT statement.
select-string is string expression containing the SELECT statement.

Usage

The UNLOAD instruction serializes into a file the SQL data produced by a SELECT statement.

You cannot use the PREPARE statement to pre-process an UNLOAD statement, you can however use a string literal to build the SELECT statement at runtime.

The filename after the TO keyword identifies an output file in which to store the rows retrieved from the database by the SELECT statement. In the default (U.S. English) locale, this file contains only ASCII characters. (In other locales, output from UNLOAD can contain characters from the codeset of the locale.)

The UNLOAD statement must include a SELECT statement (directly, or in a variable) to specify what rows to copy into filename. UNLOAD does not delete the copied data.

A single character delimiter instruct UNLOAD to write data in the default format. When using "CSV" as delimiter specification, the UNLOAD instruction will write the data in CSV format. If the DELIMITER clause is not specified, the delimiter is defined by the DBDELIMITER environment variable. If the DBDELIMITER environment variable is not set, the default is a | pipe. The field delimiter can be a blank character. It cannot be backslash or any hexadecimal digit (0-9, A-F, a-f). If the delimiter specified in the UNLOAD command is NULL, the runtime system will use the default delimiter or DBDELIMITER if the variable is defined.

When using a select-string , do not attempt to substitute question marks (?) in place of host variables to make the SELECT statement dynamic, because this usage has binding problems.

At this time, data type description of the output file fields is implicit; in order to create the fetch buffers to hold the column values, the UNLOAD instruction uses the current database connection to get the column data types of the generated result set. Those data types depend on the type of database server. For example, IBM^® Informix^® INTEGER columns are 4-bytes integers, while the Oracle^® INTEGER data type is actually a NUMBER(10,0) type. Therefore, you should take care when using this instruction; if your application connects to different kinds of database servers, you may get data conversion errors.

Default UNLOAD format

A set of values in output representing a row from the database is called an output record. A NEWLINE character (ASCII 10) terminates each output record.

The UNLOAD statement represents each value in the output file as a character string by using the current locale, according to the data type of the database column:

Table 1. Default UNLOAD format
Data type	Output Format
`CHAR, VARCHAR, TEXT`	Trailing blanks are dropped from `CHAR` and `TEXT` (but not from `VARCHAR`) values. A backslash ( \ ) is inserted before any literal backslash or delimiter character and before a NEWLINE character in a character value.
`DECIMAL, FLOAT, INTEGER, MONEY, SMALLFLOAT, SMALLINT`	Values are written as literals with no leading blanks. `MONEY` values are represented with no leading currency symbol. Zero values are represented as 0 for `INTEGER` or `SMALLINT` columns, and as 0.00 for `FLOAT`, `SMALLFLOAT`, `DECIMAL`, and `MONEY` columns.
`DATE`	Values are written in the format `month/day/year` unless some other format is specified by the `DBDATE` environment variable.
`DATETIME`	`DATETIME` values are formatted `year-month-day hour:minute:second.fraction` or a contiguous subset, without `DATETIME` keyword or qualifiers. Time units outside the declared precision of the database column are omitted.
`INTERVAL`	INTERVAL values are formatted `year-month` or `day hour:minute:second.fraction` or a contiguous subset, without `INTERVAL` keyword or qualifiers. Time units outside the declared precision of the database column are omitted.
`BYTE`	`BYTE` Values are written in ASCII hexadecimal form, without any added blank or NEWLINE characters. The logical record length of an output file that contains `BYTE` values can be very long, and thus might be very difficult to print or to edit.

NULL values of any data type are represented by consecutive delimiters in the output file, without any characters between the delimiter symbols.

The backslash symbol (\) serves as an escape character in the output file to indicate that the next character in a data value is a literal. The UNLOADstatement automatically inserts a preceding backslash to prevent literal characters from being interpreted as special characters in the following contexts:

The backslash character appears anywhere in the value.
The delimiter character appears anywhere in the value.
The NEWLINE character appears anywhere in a value.

CSV UNLOAD format

The CSV (comma separated values) format is similar to the standard format when using a simple comma delimiter, with the following differences:

A comma character generates a quoted output value, and the comma is written as is (not escaped).
A " double-quote character generate quoted output value and the quote in the value is doubled.
NEWLINE characters generate a quoted output value, and the NEWLINE is written as is (not escaped).
Backslash characters are written as is in the output value (i.e. not escaped).
Leading and trailing blanks are not truncated in the output value.
No ending delimiter is written at the end of the output record.

Example

MAIN
  DEFINE var INTEGER
  DATABASE stores
  LET var = 123
  UNLOAD TO "items.unl"
    SELECT * FROM items WHERE item_num > var 
END MAIN