OUTPUT Statement [Interactive SQL]

Quick Links:

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> ]
   [ WITH COLUMN NAMES  ]

output-format
   TEXT | FIXED | HTML | SQL | XML

Parameters

(back to top)

FORMAT the output format. If no FORMAT clause is specified, the Interactive SQL OUTPUT_FORMAT database option setting is used.

TEXT output is a TEXT format file with one row per line in the file. All values are separated by commas, and strings are enclosed in apostrophes (single quotes). The delimiter and quote strings can be changed using the DELIMITED BY and QUOTE clauses. If the ALL clause is specified in the QUOTE clause, all values (not just strings) are quoted. TEXT is the default output format.
Three other special sequences are also used. The two characters \n represent a newline character, \\ represents a single \, and the sequence \xDD represents the character with hexadecimal code DD.

If you are exporting Java methods that have string return values, you must use the HEXADECIMAL OFF clause.

FIXED output is fixed format with each column having a fixed width. The width for each column can be specified using the COLUMN WIDTHS clause. No column headings are output in this format.
If you omit the COLUMN WIDTHS clause, the width for each column is computed from the data type for the column, and is large enough to hold any value of that data type. The exception is that LONG VARCHAR and LONG BINARY data defaults to 32KB.

HTML output is in the Hyper Text Markup Language format.

SQL output is an Interactive SQL INPUT statement required to re-create the information in the table.
Note SAP IQ does not support the INPUT statement. Change this statement to a valid LOAD TABLE (or INSERT) statement to use it to load data back in.

XML output is an XML file encoded in UTF-8 and containing an embedded DTD. Binary values are encoded in CDATA blocks with the binary data rendered as 2-hex-digit strings. The LOAD TABLE statement does not accept XML as a file format.

APPEND appends the results of the query to the end of an existing output file without overwriting the previous contents of the file. By default, if you do not use APPEND clause, the OUTPUT statement overwrites the contents of the output file.
The APPEND clause is valid if the output format is TEXT, FIXED, or SQL.

VERBOSE error messages about the query, the SQL statement used to select the data, and the data itself are written to the output file. By default, if you omit the VERBOSE clause, only the data is written to the file. The VERBOSE clause is valid if the output format is TEXT, FIXED, or SQL.

ESCAPE CHARACTER
the default escape character for characters stored as hexadecimal codes and symbols is a backslash (\), so \x0A is the line feed character, for example.
To change this default, use the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, enter:
```
... ESCAPE CHARACTER '!'
```

DELIMITED BY for the TEXT output format only. The delimiter string, by default a comma, is placed between columns.

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

COLUMN WIDTHS specifies column widths for the FIXED format output.

HEXADECIMAL specifies how binary data is to be unloaded for the TEXT 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 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
specifies the encoding that is used to write the file. You can use the ENCODING clause only with the TEXT format. Can be a string or identifier.
If you do not specify the ENCODING clause, 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:
- The code page specified with the DEFAULT_ISQL_ENCODING option (if this option is set)
- The default code page for the computer Interactive SQL is running on

Examples

(back to top)

Example 1
places the contents of the Employees table in a text file:
```
SELECT * FROM Employees; 
OUTPUT TO employees.txt FORMAT TEXT
```
Example 2
places the contents of the Employees table at the end of an existing file, and includes any messages about the query in this file as well:
```
SELECT * FROM Employees; 
OUTPUT TO employees.txt APPEND VERBOSE
```
Example 3
exports 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.
Execute this statement with HEXADECIMAL ON:
```
SELECT 'line1\x0aline2'; OUTPUT TO file.txt HEXADECIMAL ON
```
The result is a file with one line in it, containing this text:
```
line10x0aline2
```
Execute the same statement with HEXADECIMAL OFF:
```
line1\x0aline2
```
If you set HEXADECIMAL to ASIS, the result is 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

(back to top)

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.

Note

OUTPUT is especially useful in making the results of a query or report available to another application, but 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.

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

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Database products—Not applicable.

Permissions

(back to top)

None