Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
www.cozTools.com/qryfile
Chicagoland
CSV, HTML or JSON?
has no ratings.
Published: 09 Jun 2015
Revised: 17 Jun 2015 - 3234 days ago
Last viewed on: 24 Apr 2024 (3542 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.

CSV, HTML or JSON? Published by: Bob Cozzi on 09 Jun 2015 view comments(6)

I've completed work on CSV, Sylk (Excel), HTML and other file conversion formats.

I am now beginning my task of creating JSON directly from an SQL statement over DB2 for i.

IBM has announced JSON support, however their API does NOT create JSON from db2 files, it only stores, searches and returns JSON content stored in a BLOB within a DB2 file. Good for certain things, but not what I'm looking for.

I need to be able to create JSON directly from a delivered SQL statement, such as:

SELECT cstnbr,cstname,addr1,addr2,city,state,zipcode FROM mlib/customers order by zipcode

Once the SQL statement produces its resultset, I would deliver the records in JSON format to the caller or write them to STDOUT (the web).

QUESTION: Am I off track here? Don't we need db2-to-JSON?

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

COMMENTS

(Sign in to Post a Comment)
Posted by: starbuck5250
Premium member *
Comment on: CSV, HTML or JSON?
Posted: 8 years 10 months 15 days 20 hours 33 minutes ago

In general when a trading partner wants JSON, it's pretty specific, and not a JSON dump of all the columns of an entire file.  On the other hand, shope doing some sort of batch process where they load a temporary file to send to a trading partner might need exactly this.

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: CSV, HTML or JSON?
Posted: 8 years 10 months 15 days 18 hours 30 minutes ago

Hi Buck, I see what you mean. but can you clarify what restrictions you see in using SQL to produce the JSON content? I don't see any limitations regarding "entrire file" or similar.

Posted by: starbuck5250
Premium member *
Comment on: CSV, HTML or JSON?
Posted: 8 years 10 months 8 days 2 hours 11 minutes ago

Sorry for the delay - I was on holiday.  I'm not sure 'restrictions' is the word I'd use... it's more like 'how many times would I need to use a tool that does this?'  Imagine a trading partner who wants individual transactions in JSON.  The process flow looks something like this:

  1. RPG program creates transaction from 5250 input.
  2. RPG program uses HTTP to send transaction to partner.
  3. RPG program reads HTTP to get response from partner.
  4. RPG program finalises transaction, logs response, etc.

If SQL-JSON were used to format the transaction, what would that look like?

 

  1. RPG program creates transaction from 5250 input.
  2. RPG program invoked SQL-JSON, creating temporary output file to hold JSON.
  3. RPG program reads temporary file, uses HTTP to send transaction to partner.
  4. RPG program reads HTTP to get response from partner.
  5. RPG program finalises transaction, logs response, etc.

It's an extra step, an extra work file.  It does save the programmer from having to roll his own JSON formatter, so there's the plus side of the tradeoff.

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: CSV, HTML or JSON?
Posted: 8 years 10 months 8 days 1 hours 36 minutes ago

If you use SQL Query File, you can certainly streamline that, but I see what you mean.

If I were to use RPG to send the HTTP stuff, then it might flow like this:

  1. Create transactions
  2. Open the HTTP connection to the trading partner
  3. Read the database as JSON format content using SQL Query File
  4. Send it to the tradiing partner
  5. Repeat until complete.

We have interfaces that allow you to do a FETCH as JSON. You provide the return buffer, we do the heavy lifting.

Posted by: starbuck5250
Premium member *
Comment on: CSV, HTML or JSON?
Posted: 8 years 10 months 7 days 20 hours 40 minutes ago

Interesting!  So SQL Query File returns an SQL cursor that an RPG program can FETCH?

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: CSV, HTML or JSON?
Posted: 8 years 10 months 7 days 18 hours 2 minutes ago
Edited: Wed, 17 Jun, 2015 at 18:04:32 (3234 days ago)

Somewhat. We hide cursor, but yes, you can open, exec and fetch until EOF the data of a resultset.

Data can be retrieved in several formats, regular (raw) data, like a normal SQL Fetch or RPG I/O where the data is in its native form, packed, char, zoned, etc. Or you can return everything as char (plain text), or you can call one of the other interfaces:

  • CSV
  • HTML
  • JSON