ASA SQL Reference
SQL Statements
Use this statement to output the current query results to a file.
OUTPUT TO filename
[ APPEND ]
[ VERBOSE ]
[ FORMAT output-format ]
[ ESCAPE CHARACTER character ]
[ DELIMITED BY string ]
[ QUOTE string [ ALL ] ]
[ COLUMN WIDTHS (integer, ...) ]
[ HEXADECIMAL { ON | OFF | ASIS } ]
output-format :
ASCII | DBASEII | DBASEIII | EXCEL
| FIXED | FOXPRO | HTML | LOTUS | SQL | XML
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 ASCII, FIXED, or SQL.
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. If VERBOSE is omitted (the default) only the data is written to the file. The VERBOSE keyword is valid if the output format is ASCII, FIXED, or SQL.
FORMAT clause 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) are 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. Column names are truncated to 11 characters, and each row of data in each column is truncated to 255 characters.
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. Column names are truncated to 11 characters, and each row of data in each column is truncated to 255 characters.
EXCEL The output is an Excel 2.1 worksheet. The first row of the worksheet contains column labels (or names if there are no labels defined). Subsequent worksheet rows contain the actual table data.
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 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. Column names are truncated to 11 characters. Column names are truncated to 11 characters, and each row of data in each column is truncated to 255 characters.
HTML The output is in the Hyper Text Markup Language format.
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 an Interactive SQL INPUT statement yrequired to recreate the information in the table.
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. The INPUT statement does not accept XML as a file format.
ESCAPE CHARACTER clause 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 '!'
DELIMITED BY clause The DELIMITED BY clause is for the ASCII output format only. The delimiter string is placed between columns (default comma).
QUOTE clause The QUOTE clause is for the ASCII 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.
COLUMN WIDTHS clause The COLUMN WIDTHS clause is used to specify the column widths for the FIXED format output.
HEXADECIMAL clause The HEXADECIMAL clause specifies how binary data is to be unloaded for the ASCII format only. When set to ON, binary data is unloaded in the format 0xabcd. When set to OFF, binary data is escaped when unloaded (\xab\xcd). When set to ASIS, values are written as is, that is, without any escaping—even if the value contains control characters. ASIS is useful for text that contains formatting characters such as tabs or carriage returns.
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 Interactive SQL OUTPUT_FORMAT option setting is used (see OUTPUT_FORMAT option [ISQL]).
The current query is the SELECT or INPUT statement which generated the information that appears on the Results tab in the Results pane. The OUTPUT statement will report an error if there is no current query.
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 [ISQL].
None.
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.
INPUT statement [Interactive SQL]
xp_write_file system procedure
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not applicable.
Place the contents of the employee table in a file in ASCII format:
SELECT * FROM employee; OUTPUT TO employee.txt FORMAT ASCII
Place the contents of the employee table at the end of an existing file, and include any messages about the query in this file as well:
SELECT * FROM employee; OUTPUT TO employee.txt APPEND VERBOSE
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
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 HEXADECIMAL set to ON,
SELECT 'line1\x0aline2'; OUTPUT TO file.txt HEXADECIMAL ON
you get a file with one line in it containing the following text:
line10x0aline2
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 hex representation, and without being prefixed by anything.