Reference Manual
   CHAPTER 9. SQL Statements     

OUTPUT statement [ISQL]

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

Permissions 

None.

Side effects 

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

See also 

SELECT statement

INPUT statement

Description 

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 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 Interactive SQL 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 \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, 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, 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.

If you are exporting Java methods that have string return values, you must use the HEXADECIMAL OFF clause.

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. No column headings are output in this format.

If the COLUMN WIDTH 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 defaults to 32 Kb.

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 Interactive SQL can produce.

SQL     The output is a Interactive SQL 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 Interactive SQL data window.

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

Exporting Java data 

When exporting Java data, you may wish to export objects as binary, or you may want to export them as strings using the toString() method. You can control which way Java data is exported using the DESCRIBE_JAVA_FORMAT Interactive SQL option.

For example, consider the following script:

CREATE VARIABLE JavaString java.lang.String;
SET JavaString = NEW java.lang.String( 'TestVar' );
SELECT JavaString FROM dummy;

If you set describe_java_format to Varchar:

• The following command gives the hexadecimal representation of TestVar in the output file.

  OUTPUT TO filename

• The following command gives a text representation of TestVar in the output file (possibly escaped).

  OUTPUT TO filename HEXADECIMAL OFF

If you set describe_java_format to binary:

• The following command gives the hexadecimal representation of JavaString in the output file.

  OUTPUT TO filename

• The following command gives the actual JavaString object in the output file (with escape sequences).

  OUTPUT TO filename HEXADECIMAL OFF

     For more information, see DESCRIBE_JAVA_FORMAT option.

Standards and compatibility 

• SQL/92     Vendor extension

• Sybase     Not applicable

Examples 

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

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

• Output the contents of the toString() method of the JProd column to file:

  SELECT JProd>>toString() 
  FROM jdba.product; 
  OUTPUT TO d:\temp\temp.txt 
  FORMAT ASCII HEXADECIMAL OFF