UNLOAD

Copies data from the database tables into a file.

Syntax

UNLOAD TO filename [ DELIMITER delimiter]
{
  select-statement
|
  select-string
}
  1. filename is a string expression containing the name of the file the data is written to.
  2. delimiter is the character used as the value delimiter. When not specified, default is | pipe, or DBDELIMITER environment variable when set. The DELIMITER clause can also specify "CSV" or "TSV", to get respectively a Comma Separated Values format, or a TAB Separated Values format.
  3. select-statement is static SELECT statement.
  4. 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.

A data file used by LOAD or UNLOAD instructions looks like this (when the delimiter is the pipe), and the record has an INTEGER, VARCHAR and DATE fields:
102|Mike|12/24/2020|
192|Tom|04/29/2019|
The UNLOAD command cannot be used in a PREPARE statement. However, the UNLOAD command accepts a string literal in place of a static SELECT statement: 
UNLOAD TO file-name
    select-string

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" or "TSV" as delimiter specification, the UNLOAD instruction will write the data in CSV or TSV 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 DELIMITER clause 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 integers of 4 bytes, while the Oracle INTEGER data type is actually a NUMBER(10,0) type. Therefore, be aware when using this instruction that if your application connects to different kinds of database servers, you may get data conversion errors.

Default UNLOAD format

A set of values in the 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 based on the current locale, depending on 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.

Rules for CSV/TSV format

The CSV (Comma Separated Values) or TSV (TAB Separated Values) format rules for stringification is similar to the default format when using a regular single-character delimiter, with the following differences:

  • Values might be surrounded with " double quotes.
  • Leading and trailing blanks are kept (no truncation).
  • No ending delimiter is expected at the end of the input record.
  • With CSV, a comma character in the value is not be escaped, and the value must be double-quoted.
  • With TSV, a TAB character in the value is not be escaped, and the value must be double-quoted.
  • A NEWLINE character in the value is not be escaped, and the value must be double-quoted.
  • A double-quote character in the value must be doubled, and the value must be double-quoted.
  • A \ backslash characters in the value is not escaped, and the value must be double-quoted.

Example

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