Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL Output Options
has no ratings.
Published: 02 Jun 2014
Revised: 16 Dec 2015 - 523 days ago
Last viewed on: 22 May 2017 (2156 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

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?

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: SQL Output Options
Posted: 2 years 11 months 20 days 2 hours 9 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
Comment on: SQL Output Options
Posted: 2 years 11 months 20 days 1 hours 54 minutes ago
Edited: Tue, 03 Jun, 2014 at 09:21:44 (1084 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: 2 years 11 months 20 days 3 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
Comment on: SQL Output Options
Posted: 2 years 11 months 19 days 22 hours 47 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: 2 years 11 months 19 days 15 hours 56 minutes ago
Edited: Tue, 03 Jun, 2014 at 18:41:42 (1084 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: 2 years 11 months 17 days 18 hours 58 minutes ago

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

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL Output Options
Posted: 2 years 11 months 17 days 18 hours 21 minutes ago

We already do that, clbirk.