OUTPUT statement [Interactive SQL]

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

Syntax 1 - Output to a file

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 ]

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

encoding : string or identifier

Syntax 2 - Output to an ODBC data source

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 [; ... ] ] }

Parameters

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.
For more information about how to obtain the list of SQL Anywhere supported encodings, see Supported character sets.

With Interactive SQL, if the Encoding clause is not specified, the encoding that is used to write the file is determined in the following order:
- the encoding specified with the default_isql_encoding option (if this option is set)
- the default encoding of the operating system character set on the computer that Interactive SQL is running on
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 an encoding is specified in the OUTPUT statement, then the same ENCODING clause should be specified if the data is inserted using the INPUT statement.

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.
- 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.
  
  Note
  
  The INPUT statement does not accept XML as a file format.
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, binary values are written one byte at a time. Each byte is prefixed with the escape character, such as a backslash, followed by x and then the hexadecimal pair for the byte; for example \xab\xcd.
- 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.
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 demo database is SQL Anywhere 11 Demo.

Odbc-driver-name is the ODBC driver name. For a SQL Anywhere 11 database, the odbc-driver-name is SQL Anywhere 11; for an UltraLite database, odbc-driver-name is UltraLite 11.
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.

Remarks

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. If the previous statement generates more than one result set, an error is returned.

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]).

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.

Permissions

None.

Side effects

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

Standards and compatibility

SQL/2003 Vendor extension.

Examples

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 HEXADEMICAL 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 11 Demo'
INTO "Customers2";

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

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

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

SELECT * FROM Customers;
OUTPUT USING "DRIVER=Ultralite 11;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";

Discuss this page in DocCommentXchange.
Send feedback about this page using email.