OUTPUT statement [Interactive SQL]

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

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

encoding : string or identifier

OUTPUT  
USING connection-string
INTO destination-table-name
[ CREATE TABLE { ON | OFF } ]

connection-string :  
{ DSN = odbc-data-source
| DRIVER = odbc-driver-name [; connection-parameter = value [; ... ] ] }

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 TEXT, FIXED, or SQL.
BYTE ORDER MARK clause Use this clause to specify whether to include a byte order mark (BOM) at the start of a Unicode file. By default, this option is ON, which directs Interactive SQL to write a byte order mark (BOM) at the beginning of the file. If BYTE ORDER MARK is OFF, DBISQL does not write a BOM.
The BYTE ORDER MARK clause is relevant only when writing TEXT formatted files. Attempts to use the BYTE ORDER MARK clause with FORMAT clauses other than TEXT returns an error.

The BYTE ORDER MARK clause is used only when reading or writing files encoded with UTF-8 or UTF-16 (and their variants). Attempts to use the BYTE ORDER MARK clause with any other encoding returns an error.
COLUMN WIDTHS clause The COLUMN WIDTHS clause is used to specify the column widths for the FIXED format output.
CREATE TABLE clause Use the CREATE TABLE clause to specify whether to create the destination table if it does not exist. The default is ON.
DELIMITED BY clause The DELIMITED BY clause is for the TEXT output format only. The delimiter string is placed between columns. The default is comma.
ENCODING clause The ENCODING clause allows you to specify the encoding that is used to write the file. The ENCODING clause can only be used with the TEXT format.
The ENCODING clause is useful when you have data that cannot be represented in the operating system character set. In this case, if you do not use the ENCODING clause, characters that cannot be represented in the default encoding are lost in the output (that is, a lossy conversion occurs).

If the input file was created using the OUTPUT statement and an encoding was specified, then the same ENCODING clause should be specified on the INPUT statement. See INPUT statement [Interactive SQL].

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

When running Interactive, the encoding that is used to export the data is determined in the following order:
- The encoding specified by the ENCODING clause (if this clause is specified)
- The encoding specified with the default_isql_encoding option (if this option is set). See default_isql_encoding option [Interactive SQL].
- The default encoding for the platform you are running on. On English Windows computers, the default encoding is 1252.
For more information about Interactive SQL and encodings, see default_isql_encoding option [Interactive SQL].
ESCAPE CHARACTER clause The default escape character for characters stored as hexadecimal codes and symbols is a backslash (\). For example, \x0A is the linefeed character.
This can be changed using the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, specify:
... ESCAPE CHARACTER '!'
The new line character can be specified as '\n'. Other characters can be specified using 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 is interpreted as 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 data.
FORMAT clause The FORMAT clause allows you to specify the file format for the output. Allowable output formats are:
- TEXT The 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 ALL is specified in the QUOTE clause, all values (not just strings) are quoted. TEXT is the default output type
  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 want to output to TEXT but do not want to include quotes or newlines in your output, turn off quotes and escapes as follows: QUOTE '' ESCAPES OFF.
- FIXED The 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 the COLUMN WIDTHS clause is omitted, 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 default to 32 KB.
- HTML The output is in the Hyper Text Markup Language format.
- SQL The output is an Interactive SQL INPUT statement (required to recreate the information in the table) in a .sql file.
- XML The 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.

HEXADECIMAL clause The HEXADECIMAL clause specifies how binary values are output for the TEXT format. Allowable values are:

ON When set to ON, binary values are written with an Ox prefix followed by a series of hexadecimal pairs; for example, 0xabcd.

OFF When set to OFF, unprintable character values are prefixed with the escape character, such as a backslash, followed by an x, and then followed by the hexadecimal pair for the byte. Printable characters are output as-is.

For example, the following command outputs a file which contains 'one\x0Atwo\x0Athree':

SELECT 'one\ntwo\nthree'
OUTPUT TO 'test.txt' HEXADECIMAL OFF;

ASIS When set to ASIS, values are written as is, without any escaping, even if the values contain control characters. ASIS is useful for text that contains formatting characters such as tabs or carriage returns.

QUOTE clause The QUOTE clause is for the TEXT output format only. The quote string is placed around string values. The default is a single quote ('). If ALL is specified in the QUOTE clause, the quote string is placed around all values, not just around strings. To suppress quoting, specify empty single quotes. For example, QUOTE ''.
USING clause The USING clause exports data to an ODBC data source. You can either specify the ODBC data source name with the DSN option, or the ODBC driver name and connection parameters with the DRIVER option. Connection-parameter is an optional list of database-specific connection parameters.
Odbc-data-source is the name of a user or ODBC data source name. For example, odbc-data-source for the SQL Anywhere sample database is SQL Anywhere 12 Demo.

Odbc-driver-name is the ODBC driver name. For a SQL Anywhere database, the odbc-driver-name is SQL Anywhere; for an UltraLite database, odbc-driver-name is UltraLite 12.
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 TEXT, FIXED, or SQL.
WITH COLUMN NAMES clause The WITH COLUMN NAMES clause inserts the column names in the first line of the text file. The WITH COLUMN NAMES clause is for TEXT format only.

The OUTPUT statement outputs data to a file or database. The OUTPUT statement is used directly after a statement that retrieves the data to be output.

To export multiple result sets, use syntax 1 and set the isql_show_multiple_result_sets option to On. Interactive SQL creates a file for each result set. The files are named filename-x, where x is a counter starting at 1. For example, specifying OUTPUT TO a file named data.txt results in files named data-1.txt, data-2.txt, and so on. See Returning multiple result sets from procedures.

You cannot use syntax 2 to export multiple result sets.

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

Because the OUTPUT 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.

When exporting columns containing BINARY or LONG BINARY data to a Microsoft Excel workbook, you must convert the data to a string or number. In addition, when data is exported to a Microsoft Excel workbook, the data is read-only unless the ReadOnly parameter is set to zero or turned off when the DSN option is selected.

None.

In Interactive SQL, the Results tab displays the results of the current query.

SQL/2008 Vendor extension.

Place the contents of the Employees table in a text file:

SELECT *
FROM Employees;
OUTPUT TO 'Employees.txt'
FORMAT TEXT;

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

SELECT *
FROM Employees;
OUTPUT TO 'Employees.txt' 
APPEND VERBOSE;

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. For example, execute the following statement, with HEXADECIMAL set to ON:

SELECT CAST ('line1\x0aline2' AS VARBINARY);
OUTPUT TO 'file.txt' HEXADECIMAL ON;

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

0x6c696e65310a6c696e6532

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

'line1\x0Aline2'

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

'line1
line2'

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

The following example outputs the data from the Customers table to a new table, Customers2:

SELECT * FROM Customers;
OUTPUT USING 'dsn=SQL Anywhere 12 Demo'
INTO "Customers2";

The following example copies the Customers table from the sample database to a database called mydatabase.db, using the DRIVER option.

SELECT * FROM Customers;
OUTPUT USING "DRIVER=SQL Anywhere 12;uid=dba;pwd=sql;dbf=c:\test\mydatabase.db"
INTO "Customers";

The following example copies the Customers table from the SQL Anywhere sample database into a table called Customers in a fictitious UltraLite database, myULDatabase.db, using the DRIVER option.

SELECT * FROM Customers;
OUTPUT USING "DRIVER=UltraLite 12;dbf=c:\test\myULDatabase.udb"
INTO "Customers";

The following example copies the Customers table into a fictitious MySQL database called mydatabase, using the DRIVER option.

SELECT * FROM Customers;
OUTPUT USING "DRIVER=MySQL ODBC 5.1 Driver;DATABASE=mydatabase;SERVER=mySQLHost;UID=me;PWD=secret"
INTO "Customers";

OUTPUT statement [Interactive SQL]

Syntax 1 - Output to a file

Syntax 2 - Output to an ODBC data source

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Examples