Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

OPEN statement [ESQL] [SP] Next Page

OUTPUT statement [Interactive SQL]

Use this statement to output the current query results to a file.


OUTPUT TO file-name
[ FORMAT output-format ]
[ ESCAPE CHARACTER character ]
[ DELIMITED BY string ]
[ QUOTE string [ ALL ] ]
[ COLUMN WIDTHS (integer, ...) ]
[ ENCODING encoding ]

output-format :

encoding : string or identifier


APPEND clause    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 clause    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. Lines that do not contain data are prefixed by two hyphens. 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 clause    Allowable output formats are:

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

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


New line characters can be included as the combination \n, other characters can be included in data as hexadecimal ASCII codes, such as \x09 for the tab character. A sequence of two backslash characters ( \\ ) is interpreted as a single backslash. A backslash followed by any character other than n, x, X, or \ is interpreted as two separate characters. For example, \q inserts a backslash and the letter q.

ESCAPES clause    With ESCAPES turned on (the default), characters following the backslash character are recognized and interpreted as special characters by the database server. With ESCAPES turned off, the characters are written exactly as they appear in the source.

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

QUOTE clause    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 clause    The COLUMN WIDTHS clause is used to specify the column widths for the FIXED format output.

HEXADECIMAL clause    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 clause    The encoding argument allows you to specify the encoding that is used to write the file. The ENCODING clause can only be used with the ASCII format.

For more information on how to obtain the list of SQL Anywhere supported encodings, see Supported character sets.

With Interactive SQL, if encoding is not specified, the encoding that is used to write the file is determined as follows, where encoding values occurring earlier in the list take precedence over those occurring later in the list:

For more information about Interactive SQL and encodings, see default_isql_encoding option [Interactive SQL].


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

The output format can be specified with the optional FORMAT clause. If no FORMAT clause is specified, the Interactive SQL output_format option setting is used (see output_format option [Interactive SQL]).

The current query is the 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.

Because the INPUT statement is an Interactive SQL command, it cannot be used in any compound statement (such as IF) or in a stored procedure. See Statements allowed in procedures, triggers, events, and batches.



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.

See also
Standards and compatibility

Place the contents of the Employees table in a file in ASCII format:

FROM Employees;
OUTPUT TO Employees.txt

Place the contents of the Employees table at the end of an existing file, and include any messages about the query in this file as well:

FROM Employees;

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. If you execute the following statement, with HEXADECIMAL set to ON,

SELECT 'line1\x0aline2';

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


But if you execute the same statement with HEXADEMICAL set to OFF, you get the following:


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


You get two lines when you use ASIS because the embedded line feed character has been exported without being converted to a two digit hex representation, and without being prefixed by anything.