SQL Output Options

Posted by: Bob Cozzi

Rogue Programmer

Cozzi Productions, Inc.

www.cozTools.com/qryfile

Chicagoland

SQL Output Options
has no ratings.

Published: 02 Jun 2014
Revised: 16 Dec 2015 - 3054 days ago
Last viewed on: 26 Apr 2024 (5155 views)

Main URL:

SQL Output Options Published by: Bob Cozzi on 02 Jun 2014 view comments(7)

The standard IBM i SQL can output to *DISPLAY, *PRINTER, and *OUTFILE.

Our new SQL Query File tool can output to these as well as *PDF and *TEXT files.

I'm wondering what other output features would be helpful to end-user shops?

Currently we have the following output formats in plan:

OUTPUT(*CSV) - Write the result set to the IFS as a CSV file.
OUTPUT(*TEXT) - Writh the result set to the IFS as plain ASCII text file.
OUTPUT(*XML) - Write the result set to the IFS as Standard XML.
OUTPUT(*JSON) - Write the result set to the IFS as a text file with JSON content.
OUTPUT(*HMTL) - Write the result set to the IFS as an HTML text file.
OUTPUT(*EXCEL) - Write the result set to the IFS as an Excel-compatible "Symbolic Link" (SLK) file.

These are just in our plans now, in order of importance to us. Are there other formats that are as important as these? Are these important?

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: Ringer

Premium member *

Comment on: SQL Output Options
Posted: 9 years 10 months 24 days 7 hours 13 minutes ago

How about *TAB delmited? Parsers can get confused when strings contain quotes but if the CSV is tab delimited, it's a piece of cake to parse, since users cannot enter tabs in any screens (web or 5250).

Chris Ringer

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL Output Options
Posted: 9 years 10 months 24 days 6 hours 57 minutes ago
Edited: Tue, 03 Jun, 2014 at 09:21:44 (3615 days ago)

Chris. The existing coztools' CPYTOCSV allows TABS, but would you want it to be simply a text file (like a text version of a Report) but instead of the blanks between columns, there would be a TAB character, without any quotes or commas?

Posted by: Ringer

Premium member *

Comment on: SQL Output Options
Posted: 9 years 10 months 24 days 5 hours 7 minutes ago

Correct. Tab delmited instead of comma delimited.

The way CPYTOIMPOF does it with STRDLM(*NONE) STRESCCHR(*NONE) FLDDLM(*TAB).

"Bob""s","Data" would instead just look like Bob"s<tab>Data

Chris

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL Output Options
Posted: 9 years 10 months 24 days 3 hours 50 minutes ago

Okay. Got it. Thanks!

Bye the way, the Cozzi appTools has a csvparse() function that allows you to retrieve each "field" of a CSV record either by column name or by relative column number. I use it to import CSV data all the time.

Posted by: tdaly

Premium member *

Comment on: SQL Output Options
Posted: 9 years 10 months 23 days 20 hours 59 minutes ago
Edited: Tue, 03 Jun, 2014 at 18:41:42 (3615 days ago)

Along with *CSV I'd add *DELIMITED

I've encountered two kinds of delimited files:

1) Delimited with quoted strings
User specified delimiter, anything can be the delimiter: vertical bar, equal sign, tab, whitespace, null, tilde, caret, some other user defined character or hex

Same idea but more flexible than CSV

2) Delimited with Escaped Delimiter
User specified delimiter (for example, backslash)
Doubling the delimiter escapes the delimiter

sometimes files from *nix systems can be like this

So maybe *DELIMITED would be *QOUTEDELIM and *ESCDELIM, or something like that.

It would also be useful to specify a Record Delimiter: null, cr, lf, some other user defined character or hex byte(s)

Posted by: clbirk

Premium member *

Comment on: SQL Output Options
Posted: 9 years 10 months 22 days 1 minutes ago

how about it putting out a new table OUTPUT(*TABLE)?

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL Output Options
Posted: 9 years 10 months 21 days 23 hours 25 minutes ago

We already do that, clbirk.