To meet your needs, we constantly work to improve Querix products.
This means that Lycia documentation is developing as well.
In case you have found a certain dissonance between the provided information and the actual behavior of Lycia 3 and/or your applications, please, let us know about this via documentation@querix.com so that we can introduce the necessary changes to our documentation.
Thank you for your attention and cooperation.
UNLOAD statement unloads the data from a table in the current database to an output file.
|
Element |
Description |
|
Output File |
A character expression that returns the name of the output file and its relative path if necessary |
|
Delimiter |
is a quoted string (or a CHAR, STRING, or VARCHAR variable) containing a delimiter symbol |
|
Variable |
A CHAR, STRING or VARCHAR variable containing an SELECT clause. |
|
SELECT Clause |
A CHAR, STRING, or VARCHAR variable containing an INSERT clause. |
The UNLOAD statement should include a SELECT statement, which specifies the rows in a database that should be copied to a file. The values in the database selected and copied by the UNLOAD statement are not altered or deleted from the table. To execute the UNLOAD statement, the user must have the SELECT privileges on every column from which the values are selected. (The privileges are set by means of the SQL statement GRANT).
The DATABASE or CONNECT statement must precede the UNLOAD statement. You cannot prepare the UNLOAD statement using the PREPARE statement.
Output of the rows selected from a table is performed into an output file, which is specified in a form of a character string enclosed in quotation marks or in the form of a character variable returning such string, the path name can be also included. The output file can contain only ASCII characters (in default U.S. English locale). In other locales, the output file can consist of the characters that belong to the code set of the locale.
Each row retrieved from the database is sent to the output file in a form of an output record, each of the output records is terminated by the new line character (ASCII 10). Each value within an output record is stored as string of ASCII characters, the data types of the values correspond to the data types of the columns from which the values have been retrieved.
|
Column data type |
Format of the retrieved value |
|
CHAR / VARCHAR / STRING / TEXT |
All the trailing blanks are truncated from the values of CHAR and TEXT data types, but they are not deleted from the VARCHAR or STRING values. Each literal delimiter sign, a literal backlash (\) or a newline symbol is preceded by one more backlash symbol in the values of the VARCHAR data type. The backlash symbol is also used as the last character in the values of the TEXT data type. |
|
DECIMAL / FLOAT / INTEGER / MONEY / SMALLFLOAT / SMALLINT |
The leading blanks are discarded, the values are written as literals. MONEY values are written without currency symbols. 0 stands for zero values of INT and SMALLINT data types, 0.00 is used for FLOAT, SMALLFLOAT, DECIMAL and MONEY zero values. The values retrieved from columns of SERIAL data type are represented by literal integers. |
|
DATE |
The values are written in the mm/dd/yyyy format, if other format is not specified in the DBDATE environment variable settings. |
|
DATETIME / INTERVAL |
The DATETIME values are written in the format yyyy-mm-dd hh:mm:ss.fff with all the time units present or as a continuous subset of the time units. The INTERVAL values are written in the format yyyy-mm or dd hh:mm:ss.fff with all the time units present or as a continuous subset of the time units. Both values are written without the DATETIME and INTERVAL keywords. The time units outside the continuous subset of the time units declared by means of the qualifiers are omitted. |
|
BYTE |
The values are written in ASCII hexadecimal format. No whitespaces or newline characters are added. The ASCII hexadecimal form of a BYTE value might be very long and thus difficult to edit or print. |
The NULL values of any data type are represented by double delimiter symbol without any blank spaces between the delimiters:
12|John|Smith||USA
The following UNLOAD statement copies values from all the columns of a table which correspond to the condition of a WHERE clause into the unld_file:
UNLOAD TO "unld_file"
SELECT * FROM ord_inf WHERE ord_date>01/01/2009
The content of the unld_file will look like the following text:
1034|TransCorp|05/10/2009|1500|2.984
1034|PlatoCorp|06/23/2009|200|484
The formats of the values that are copied to a file are determined by the settings of the DBFORMAT, DBMONEY, DBDATE, GL_DATE, and GL_DATETIME environment variables.
Default delimiter which separates one value from another in an output record is a pipe symbol (|). This default setting can be changed by means of the DELIMITER clause of the UNLOAD statement. The delimiter symbol must terminate an input record only if the last value of this record is NULL.
Below is an example of the UNLOAD statement with the DELIMITER clause which sets the exclamation mark (!) symbol as the delimiter. The UNLOAD statement copies the data contained in all the columns of the id_num table into a file. This way the table can be populated by loading this file:
UNLOAD TO "/source/unl/unld_info" DELIMITER "!"
SELECT * FROM id_num
If the DELIMITER clause is omitted, the delimiter set by the DBDELIMITER environment variable, if it is set, otherwise the default delimiter symbol pipe (|) is used.
The following symbols cannot serve as delimiters:
Hexadecimal numbers (0 through 9, a through f, or A through F)
NEWLINE or CONTROL-J
Backlash symbol (\)
You cannot substitute host variables with question mark symbols (?) in the SELECT statement, it will lead to the binding problems.
Backlash symbol (\) is used as an escape character to indicate that the next character used in the output file should be interpreted as a literal. The backlashes are automatically inserted by the UNLOAD statement to prevent some characters from being interpreted as special characters and not literals.
It is inserted in the following situations:
If a backlash character is placed in a value of the CHAR, VARCHAR , STRING or TEXT data types
If a delimiter character is placed in a value of the CHAR, VARCHAR, STRING or TEXT data types (the delimiter character is either the default delimiter - pipe symbol, or the delimiter specified in the DELIMITER clause)
If a newline character is placed in a value of the VARCHAR data type
If a newline character is inserted as the last character of a TEXT value
If the LOAD statement is used to insert the values from the file previously created by the UNLOAD statement into a database, all the automatically inserted backlash symbols are deleted.