OUTPUT statement [DBISQL]

Description

Writes the current query results to a file.

Syntax

OUTPUT TO filename 
[ APPEND ] [ VERBOSE ]
[ FORMAT output-format ] 
[ ESCAPE CHARACTER character ] 
[ DELIMITED BY string ] 
[ QUOTE string [ ALL ] ] 
[ COLUMN WIDTHS (integer, …) ]
[ HEXADECIMAL { ON | OFF | ASIS } ]
[ ENCODING encoding ]

Parameters

output-format:

ASCII| DBASEII | DBASEIII | EXCEL | FIXED | FOXPRO | HTML | LOTUS | SQL | XML

encoding:

string or identifier

Examples

Example 1

Places the contents of the employee table in a file in ASCII format:

SELECT * FROM employee; OUTPUT TO employee.txt FORMAT ASCII

Example 2

Places the contents of the employee table at the end of an existing file, and includes any messages about the query in this file as well:

SELECT * FROM employee; OUTPUT TO employee.txt APPEND VERBOSE

Example 3

Suppose you need to export a value that contains an embedded line feed character. A line feed character has the numeric value 10, which you can represent as the string '\x0a' in a SQL statement. You could execute the following statement, with HEXADECIMAL ON:

SELECT 'line1\x0aline2'; OUTPUT TO file.txt HEXADECIMAL ON

You get a file with one line in it, containing the following text:

line10x0aline2

If you execute the same statement with HEXADEMICAL OFF, you get the following:

line1\x0aline2

Finally, if you set HEXADECIMAL to ASIS, you get a file with two lines:

line1 line2

Using ASIS generates two lines because the embedded line feed character has been exported without being converted to a two-digit hex representation, and without a prefix.

Usage

The OUTPUT statement copies the information retrieved by the current query to a file.

You can specify the output format with the optional FORMAT clause. If no FORMAT clause is specified, the Interactive SQL OUTPUT_FORMAT option setting is used.

The current query is the SELECT or LOAD TABLE statement that generated the information that appears on the Results tab in the Results pane. The OUTPUT statement reports an error if there is no current query.

NoteOUTPUT is especially useful in making the results of a query or report available to another application, but it is not recommended for bulk operations. For high-volume data movement, use the ASCII and BINARY data extraction functionality with the SELECT statement. The extraction functionality provides much better performance for large-scale data movement, and creates an output file you can use for loads.

APPEND This optional keyword is used to append the results of the query to the end of an existing output file without overwriting the previous contents of the file. If the APPEND clause is not used, the OUTPUT statement overwrites the contents of the output file by default. The APPEND keyword is valid if the output format is ASCII, FIXED, or SQL.

VERBOSE When the optional VERBOSE keyword is included, error messages about the query, the SQL statement used to select the data, and the data itself are written to the output file. If VERBOSE is omitted (the default), only the data is written to the file. The VERBOSE keyword is valid if the output format is ASCII, FIXED, or SQL.

FORMAT Allowable output formats are:

ESCAPE CHARACTER The default escape character for characters stored as hexadecimal codes and symbols is a backslash (\), so \x0A is the linefeed character, for example.

This default can be changed using the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, you would enter:

... ESCAPE CHARACTER '!'

DELIMITED BY The DELIMITED BY clause is for the ASCII output format only. The delimiter string is placed between columns (default comma).

QUOTE The QUOTE clause is for the ASCII output format only. The quote string is placed around string values. The default is a single quote character. If ALL is specified in the QUOTE clause, the quote string is placed around all values, not just around strings.

COLUMN WIDTHS The COLUMN WIDTHS clause is used to specify the column widths for the FIXED format output.

HEXADECIMAL The HEXADECIMAL clause specifies how binary data is to be unloaded for the ASCII format only. When set to ON, binary data is unloaded in the format 0xabcd. When set to OFF, binary data is escaped when unloaded (\xab\xcd). When set to ASIS, values are written as is, that is, without any escaping—even if the value contains control characters. ASIS is useful for text that contains formatting characters such as tabs or carriage returns.

ENCODING The encoding argument lets you specify the encoding that is used to write the file. The ENCODING clause can be used only with the ASCII format.

If encoding is not specified, Interactive SQL determines the code page that is used to write the file as follows, where code page values occurring earlier in the list take precedence over those occurring later:


Side effects

In Interactive SQL, the Results tab displays only the results of the current query. All previous query results are replaced with the current query results.

Standards

Permissions

None

See also

DEFAULT_ISQL_ENCODING option [DBISQL]

OUTPUT_FORMAT option [ISQL]

SELECT statement