OUTPUT

Seznam témat:

popis funkce

příklad

Popis funkce

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  } ]

output-format :

ASCII  | DBASE  | DBASEII  | DBASEIII  | EXCEL

| FIXED  | FOXPRO  | HTML  | LOTUS  | SQL  | TEXT

Parameters

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 WIDTH clause    The COLUMN WIDTH 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 )

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

·        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.

·        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 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.

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 '!'

Description

The OUTPUT statement copies the information retrieved by the current query to a file.

If you include the optional APPEND keyword, you can append the results of the query to the end of an existing file. If you include the optional VERBOSE keyword, you can append any messages about the query to the end of the file. Both the APPEND and VERBOSE keywords are only valid if the output format is ASCII, Fixed, or SQL.

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

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.

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 :

OUTPUT TO filename

OUTPUT TO filename HEXADECIMAL OFF

If you set DESCRIBE_JAVA_FORMAT to binary :

OUTPUT TO filename

OUTPUT TO filename HEXADECIMAL OFF

Příklad

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

Související témata