Is there a documentation for the dbisqlc OUTPUT statement?

[VolkerBarth]

Yes, I still do use dbisqlc with SA 11.0.1 and SA 12, and no, I'm not in the SA dev team:)

It's a fast tool, for example to export result sets to text files, but several times, I have to hamper with the supported format options, as they don't fit the ones documented for DBISQL in V11/V12.

Just in that moment I tried to use the verbose option with a text output, and it gives a "syntax error".

So, is there a documentation for the current dbisqlc OUTPUT statement?

Or do I have to check back in the v7/v8 docs?

[VolkerBarth]

...or are you gently leading me to move to DBISQL (which is becoming better and better, as I have to confess...)?

[Breck_Carter]

You cannot hide forever, Luke. Give yourself to the Dark Side. http://www.imdb.com/title/tt0086190/quotes?qt0447238

[VolkerBarth]

Have I already suggested the "Joker" badge?

[johnsmirnios]

You could also consider using UNLOAD SELECT ... INTO CLIENT FILE if UNLOAD supports the format you are looking for.

[VolkerBarth]

@John: Well, if doing exports on a regular base, I generally use STPs with UNLOAD SELECT - it's just the ad-hoc query execution and export where OUTPUT seems very handy (sometimes in preparation of a regular export facility). And I do know (and accept) that dbisqlc is deprecated, it was just the exact documentation I had missed.

[VolkerBarth]

@Breck: Wait - in the end, doesn't that B/C++ wing throw a bomb in the garbage collector at the heart of Death JVM II? (Sorry, haven't found a quote yet.)

[johnsmirnios · Accepted solution]

Here is the v5 doc for the OUTPUT statement. Some output formats may no longer be supported though:

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

[VolkerBarth · Accepted solution]

So you say, dbisqlc's feature set is frozen (with some exceptions) on v5? That's really old. (FWIW, I still have to use v5.5, and so I have the v5 docs available...)

[johnsmirnios · Accepted solution]

Almost. It was documented in v6 (with some additions relevant to Java columns) but the v7 doc I found seemed to be for the Java-based dbisql. Once the new dbisql was shipped, we didn't continue to document dbisqlc and, apart for a few minor features and fixes, it is has not changed. It is not under active development. Looking at the grammar for dbisqlc, there is no VERBOSE keyword. It does support HEXADECIMAL [ON|OFF] which wasn't in v5 but I don't see any other syntax added to OUTPUT since then.

[VolkerBarth · Accepted solution]

Thanks for the further clarification - good to know:)