OUTPUT statement

User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements
OUTPUT statement

Function

To output the current query results to a file.

Syntax

     OUTPUT TO filename
          ... [ FORMAT output_format ]
          ... [ ESCAPE CHARACTER character ]
          ... [ DELIMITED BY string ]
          ... [ QUOTE string [ ALL ] ]
          ... [ COLUMN WIDTHS (integer,...) ]

Usage

ISQL.

Permissions

None.

Side effects

The current query results that are displayed in the ISQL data window are repositioned to the top.

Description

The OUTPUT statement copies the information retrieved by the current query to filename. The output format can be specified with the optional format clause. If no format clause is specified, the OUTPUT_FORMAT option setting is used (see "SET OPTION statement").

The current query is the SELECT or INPUT statement which generated the information displayed in the ISQL data window. The OUTPUT statement will report an error if there is no current query.

The default escape character for characters stored as hexadecimal codes and symbols is a backslash (\), so that \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

     ... ESCAPE CHARACTER '!'

The DELIMITED BY and QUOTE clauses are for the ASCII output format only. The delimiter string will be placed between columns (default comma) and the quote string will be placed around string values (default '---single quote). If ALL is specified in the QUOTE clause, then the quote string will be placed around all values, not just strings.

The COLUMN WIDTH clause is used to specify the column widths for the FIXED format output.

Allowable output formats are:

ASCII The output is an ASCII 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, then all values (not just strings) will be quoted.

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. This is the default output format.

DBASEII The output is a dBASE II format file with the column definitions at the top of the file. Note that a maximum of 32 columns can be output. Also, note that columns longer than 255 characters will be truncated in the file.

DBASEIII The output is a dBASE III format file with the column definitions at the top of the file. Note that a maximum of 128 columns can be output. Also, note that columns longer than 255 characters will be truncated in the file.

DIF The output is a file in the standard Data Interchange Format.

FIXED The output is fixed format with each column having a fixed width. The width for each column can be specified using the COLUMN WIDTH clause. If this 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. No column headings are output in this format.

FOXPRO The output is a FoxPro format file (the FoxPro memo field is different than the dBASE memo field) with the column definitions at the top of the file. Note that a maximum of 128 columns can be output. Also, note that columns longer than 255 characters will be truncated in the file.

LOTUS The output is a Lotus WKS format worksheet. Column names will be put as the first row in the worksheet. Note that there are certain restrictions on the maximum size of Lotus WKS format worksheets that other software (such as Lotus 1-2-3) can load. There is no limit to the size of file ISQL can produce.

SQL The output is an ISQL INPUT statement required to recreate the information in the table.

TEXT The output is a TEXT format file which prints the results in columns with the column names at the top and vertical lines separating the columns. This format is similar to that used to display data in the ISQL data window.

WATFILE The output is a WATFILE format file with the column definitions at the top of the file.

Example

Place the contents of the employee table in a file, in ASCII format.

     SELECT *
     FROM employee ;
     OUTPUT TO employee.txt
     FORMAT ASCII