output

Description

Imports data into a database table from an external file or from the keyboard.

Syntax

Syntax 1:

output to file_name
     [ 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 } ]
     [ quoate string [ all ] ]
     [ verbose ]
     [ with column names]
output_format :
     text 
     | fixed
     | html
     | sql 
     | xm
encoding : string | 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 – appends 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
byte order mark – specifies 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.
create table – specifies whether to create the destination table if it does not exist. The default is on.
delimited by – specifies a string to be used as the delimiter between column values in text input format. The default delimiter is a comma.
encoding – specifies the encoding that is used to read the file. The encoding clause can only be used with the text format.

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

When running Interactive SQL, the encoding used to export data is determined in the following order:
- The encoding specified by the encoding clause (if this clause is specified).
- The encoding specified by the default_isql_encoding option (if this option is set).
- If the file has a byte-order mark, the appropriate Unicode encoding is used.
- The default encoding for the computer you are running on. On English Windows computers, the default encoding is 1252.
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.
escape character – default escape character for hexadecimal codes is a backslash (\). For example, \x0A is the linefeed character.

The escape character can be changed using the escape character clause. For example, to use the exclamation mark as the escape character, specify:
```
... escape character ‘!’
```
You can specify the newline character 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 – when enabled (the default), characters following the escape character are interpreted as special characters by the database server. With escapes disabled, the characters are read exactly as they appear in the source.
format – specifies the format of the input file.

If you do not specify the FORMAT clause, the format specified by the output_format option is used. If you specify the FORMAT clause, the setting of the output_format option is ignored. The default output format is text. Allowable output formats are.

The supported 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 recognized: \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 – output is in the Hyper Text Markup Language format.
- sql – output is an Interactive SQL INPUT statement (required to recreate the information in the table) in a .sql file.
- 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.
hexidecimal – specifies how binary data is to be unloaded for the text format only. Values allowed are:
- on – binary values are written with an Ox prefix followed by a series of hexadecimal pairs; for example, 0xabcd.
- 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 – 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 – for the text 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. To suppress quoting, specify empty single quotes. For example, quote ' '
using – 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 name of the ODBC driver.
verbose – 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 – inserts the column names in the first line of the text file. The with column names clause is for text format only.

Examples

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

select *
from employee
go
output to employee.txt
format ASCII

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
go
output to employee.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. If you execute the following statement, with hexidecimal set to on,

select ‘line1 n x0aline2’
go
output to file.txt hexidecimal on

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

line10x0aline2

But if you execute the same statement with hexidecimal set to off, you get the following:

line1 n x0aline2

Finally, if you set hexidecimal 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 hexidecimal representation, and without being prefixed by anything.

Usage

The output statement copies the information retrieved by the current query 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.
You cannot use syntax 2 to export multiple result sets.
You can specify the output format with the format clause. The default format is text. If you do not specify a format clause, the Interactive SQL output_format option setting is used.
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.
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.

Permissions

Any user can run this command.

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.