Forums >> Programming >> Proof of Concept (POC)

Jump to:




bvstone

Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Posted:

Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

When it comes to contributions to the IBM i community, it's hard to find any one more involved than Scott Klement.  He is (or should be) a household name in most IBM i shops.

One of his more recent offerings to the IBM i and RPG world is a port of the YAJL JSON Parser.

Because I've been doing a lot of projects that have been consuming JSON, and I had already written a JSON parser because there was not any available at the time, I hadn't had the need for YAJL (but I did test it when it came out and yes, it's much faster than my parser... I'm guessing because it's written in C instead of RPG).

But now I have a couple of projects where we are going to be required to send JSON data to a consumer.  So, because YAJL was out there and I know Mr. Klement's work is second to none, I decided to give it a try.  And to my surprise it was much easier than expected.  I'm sure that being comfortable with JSON already played a part, but even so, it was quite easy to figure out the ins and outs of YAJL starting with the examples provided at Mr. Klement's site.

I started with a simple program similar to the example provided named JSONWRITE.  But, I decided instead to build the JSON from my customer master file (yes, we store all of our customer data on an IBM i using custom software we wrote!).  That proved easy and created a simple JSON file in the IFS.

The program ended up looking like the following:

     H DFTACTGRP(*NO) BNDDIR('YAJL')
      ****************************************************************
      * Imports
      ****************************************************************
      /include yajl_h
      /COPY QCOPYSRC,P.SQL
      ****************************************************************
      * Global Definitions
      ****************************************************************
     D CSTMSTDS      E DS                  EXTNAME(CSTMSTPF)
      ****************************************************************
      * Work Variables
      ****************************************************************
     D errMsg          s            500a   varying
      ****************************************************************
      /free

       yajl_genOpen(*ON);   // use *ON for easier to read JSON
                            //    *OFF for more compact JSON

       yajl_beginObj(); //main JSON object

       exsr $Header;

       yajl_endObj(); // Main JSON Object

       // Save JSON To Stream File
       yajl_saveBuf('/tmp/makejson1.json': errMsg);

       if (errMsg <> '');
          // handle error
       endif;

       yajl_genClose();

       *inlr = *on;

       //***************************************************************
       //* Add JSON Row
       //***************************************************************
       begsr $Header;

         exec SQL declare C1 cursor for
             select cmemail, cmlname, cmfname, cmfcomp, cmfadd1,
                    cmfadd2, cmfcity, cmfstate, cmfzip, cmfcnt
               from CSTMSTPF;

         exec SQL open C1;
         exec SQL fetch next from C1 into
             :cmemail, :cmlname, :cmfname, :cmfcomp, :cmfadd1,
             :cmfadd2, :cmfcity, :cmfstate, :cmfzip, :cmfcnt;

         yajl_beginArray('customerList');

         dow (xSQLState2 = Success_On_Sql);
           yajl_beginObj();  //Customer Object
           yajl_addChar('email':%trimr(cmemail));
           yajl_addChar('lname':%trimr(cmlname));
           yajl_addChar('fname':%trimr(cmfname));
           yajl_addChar('comp':%trimr(cmfcomp));
           yajl_addChar('add1':%trimr(cmfadd1));
           yajl_addChar('add2':%trimr(cmfadd2));
           yajl_addChar('city':%trim(cmfcity));
           yajl_addChar('state':%trimr(cmfstate));
           yajl_addChar('zip':%trimr(cmfzip));
           yajl_addChar('country':%trimr(cmfcnt));
           yajl_endObj();  //Customer Object

           exec SQL fetch next from C1 into
               :cmemail, :cmlname, :cmfname, :cmfcomp, :cmfadd1,
               :cmfadd2, :cmfcity, :cmfstate, :cmfzip, :cmfcnt;
         enddo;

         exec SQL close C1;

         yajl_endArray(); //customerList

       endsr;

The resulting file looked like the following (actual data is replaced for customer privacy and shortened):

{
    "customerList": [
        {
            "email": "customer1@email.com",
            "lname": "LastName",
            "fname": "FirstName",
            "comp": "Company, Inc.",
            "add1": "Address1",
            "add2": "Address2",
            "city": "Eugene",
            "state": "OR",
            "zip": "97401",
            "country": "USA",
         },
         {
            "email": "customer2@email.com",
            "lname": "LastName",
            "fname": "FirstName",
            "comp": "Company, Inc.",
            "add1": "Address1",
            "add2": "Address2",
            "city": "Fargo",
            "state": "ND",
            "zip": "58201",
            "country": "USA",
         },
        ....
    ]
}

Next, I wanted to make it a little more complex. 

I have two detail files with customer information.  One that shows the products they've purchased and another that holds the machine, partition and license key information.  A "one to many" relationship.  So I then modified the program.  The end result is as follows:

     H DFTACTGRP(*NO) BNDDIR('YAJL')
      ****************************************************************
      * Imports
      ****************************************************************
      /include yajl_h
      /COPY QCOPYSRC,P.SQL
      ****************************************************************
      * Global Definitions
      ****************************************************************
     D CSTMSTDS      E DS                  EXTNAME(CSTMSTPF)
     D CSTDTLDS      E DS                  EXTNAME(CSTDTLPF)
     D CSTKEYDS      E DS                  EXTNAME(CSTKEYPF)
      ****************************************************************
      * Work Variables
      ****************************************************************
     D errMsg          s            500a   varying
      ****************************************************************
      /free

       yajl_genOpen(*ON);   // use *ON for easier to read JSON
                            //    *OFF for more compact JSON

       yajl_beginObj(); //main JSON object

       exsr $Header;

       yajl_endObj(); // Main JSON Object

       // Save JSON To Stream File
       yajl_saveBuf('/tmp/makejson2.json': errMsg);

       if (errMsg <> '');
          // handle error
       endif;

       yajl_genClose();

       *inlr = *on;

       //***************************************************************
       //* Add JSON Row
       //***************************************************************
       begsr $Header;

         exec SQL declare C1 cursor for
             select cmemail, cmlname, cmfname, cmfcomp, cmfadd1,
                    cmfadd2, cmfcity, cmfstate, cmfzip, cmfcnt
               from CSTMSTPF;

         exec SQL open C1;
         exec SQL fetch next from C1 into
             :cmemail, :cmlname, :cmfname, :cmfcomp, :cmfadd1,
             :cmfadd2, :cmfcity, :cmfstate, :cmfzip, :cmfcnt;

         yajl_beginArray('customerList');

         dow (xSQLState2 = Success_On_Sql);
           yajl_beginObj();  //Customer Object
           yajl_addChar('email':%trimr(cmemail));
           yajl_addChar('lname':%trimr(cmlname));
           yajl_addChar('fname':%trimr(cmfname));
           yajl_addChar('comp':%trimr(cmfcomp));
           yajl_addChar('add1':%trimr(cmfadd1));
           yajl_addChar('add2':%trimr(cmfadd2));
           yajl_addChar('city':%trim(cmfcity));
           yajl_addChar('state':%trimr(cmfstate));
           yajl_addChar('zip':%trimr(cmfzip));
           yajl_addChar('country':%trimr(cmfcnt));

           exsr $Detail;
           exsr $Keys;

           yajl_endObj();  //Customer Object

           exec SQL fetch next from C1 into
               :cmemail, :cmlname, :cmfname, :cmfcomp, :cmfadd1,
               :cmfadd2, :cmfcity, :cmfstate, :cmfzip, :cmfcnt;
         enddo;

         exec SQL close C1;

         yajl_endArray(); //customerList

       endsr;

       //***************************************************************
       //* Customer Detail
       //***************************************************************
       begsr $Detail;

         exec SQL declare C2 cursor for
             select cdsoft, cdinvd, cdcreq
               from CSTDTLPF
             where cdemail = :cmemail;

         exec SQL open C2;
         exec SQL fetch next from C2 into
             :cdsoft, :cdinvd, :cdcreq;

         yajl_beginArray('detail');

         dow (xSQLState2 = Success_On_Sql);
           yajl_beginObj();  //Customer Detail Object
           yajl_addChar('software':%trimr(cdsoft));
           yajl_addChar('invoice_date':%trimr(%char(%date(cdinvd):*USA/)));
           yajl_addNum('qty':%char(cdcreq));
           yajl_endObj();  //Customer Detail Object

           exec SQL fetch next from C2 into
               :cdsoft, :cdinvd, :cdcreq;
         enddo;

         yajl_endArray(); //detail

         exec SQL close C2;

       endsr;
       //***************************************************************
       //* Customer Keys
       //***************************************************************
       begsr $Keys;

         exec SQL declare C3 cursor for
             select cksoft, cklser, ckser, ckmodel, ckkey, ckkver, ckact
               from CSTKEYPF
             where ckemail = :cmemail;

         exec SQL open C3;
         exec SQL fetch next from C3 into
             :cksoft, :cklser, :ckser, :ckmodel, :ckkey, :ckkver, :ckact;

         yajl_beginArray('keys');

         dow (xSQLState2 = Success_On_Sql);
           yajl_beginObj();  //Customer Key Object
           yajl_addChar('software':%trimr(cksoft));
           yajl_addChar('l_ser':%trimr(cklser));
           yajl_addChar('ser':%trimr(ckser));
           yajl_addChar('model':%trimr(ckmodel));
           yajl_addChar('key':%trimr(ckkey));
           yajl_addChar('ver':%trimr(ckkver));
           yajl_addBool('active':(ckact = 'Y'));
           yajl_endObj();  //Customer Key Object

           exec SQL fetch next from C3 into
               :cksoft, :cklser, :ckser, :ckmodel, :ckkey, :ckkver, :ckact;
         enddo;

         yajl_endArray(); //keys

         exec SQL close C3;

       endsr;

As you can see, there are 2 extra subroutines created, $Detail and $Keys.  They are called before we close the JSON object for each customer.  Each builds the detail or license key information for the specific customer we're currently processing.

One thing I found right away is it's best to comment the beginning and ending object functions (for objects and arrays) so you know what you're opening or closing.  If things get really complex it can be very easy to lose your place as to what you're dealing with! 

A sample of the data is as follows (again, the names, addresses, serial numbers and license keys have been altered for privacy reasons):

{
    "customerList": [
		{
            "email": "gary.anderson@company1.com",
            "lname": "Anderson",
            "fname": "Gary",
            "comp": "Company 1, Inc.",
            "add1": "123 Main Street",
            "add2": "",
            "city": "Nashville",
            "state": "TN",
            "zip": "37203",
            "country": "USA",
            "detail": [
                {
                    "software": "SPLTOOL",
                    "invoice_date": "04/12/2007",
                    "qty": 1
                },
                {
                    "software": "SPLTOOL",
                    "invoice_date": "11/28/2011",
                    "qty": 1
                },
                {
                    "software": "SPLTOOL",
                    "invoice_date": "08/04/2015",
                    "qty": 1
                }
            ],
            "keys": [
                {
                    "software": "SPLTOOL",
                    "l_ser": "060xxxx1",
                    "ser": "060xxxx",
                    "model": "E4B",
                    "key": "7HBYJB991",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "103xxxx1",
                    "ser": "103xxxx",
                    "model": "520",
                    "key": "72F777JI52",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "21Fxxxx1",
                    "ser": "21xxxx",
                    "model": "41A",
                    "key": "JIJ0B50II4",
                    "ver": "v3",
                    "active": true
                }
            ]
        },
        {
            "email": "geirpeterson@company2.no",
            "lname": "Peterson",
            "fname": "Geir",
            "comp": "Company 2",
            "add1": "Postboks 65545",
            "add2": "",
            "city": "",
            "state": "Oslo",
            "zip": "0102",
            "country": "Norway",
            "detail": [
                {
                    "software": "SPLTOOL",
                    "invoice_date": "10/20/2004",
                    "qty": 1
                },
                {
                    "software": "OBJTOOL",
                    "invoice_date": "05/11/2006",
                    "qty": 1
                },
                {
                    "software": "SPLTOOL",
                    "invoice_date": "01/04/2011",
                    "qty": 2
                },
                {
                    "software": "MAILTOOL",
                    "invoice_date": "09/27/2013",
                    "qty": 2
                },
                {
                    "software": "MAILTOOLP",
                    "invoice_date": "09/27/2013",
                    "qty": 2
                },
                {
                    "software": "MAILTOOL",
                    "invoice_date": "08/14/2015",
                    "qty": 3
                },
                {
                    "software": "MAILTOOLP",
                    "invoice_date": "08/14/2015",
                    "qty": 3
                },
                {
                    "software": "SPLTOOL",
                    "invoice_date": "08/14/2015",
                    "qty": 3
                }
            ],
            "keys": [
                {
                    "software": "SPLTOOL",
                    "l_ser": "",
                    "ser": "44Gxxxx",
                    "model": "820",
                    "key": "G07BH665I2",
                    "ver": "v2",
                    "active": false
                },
                {
                    "software": "OBJTOOL",
                    "l_ser": "",
                    "ser": "65xxxx",
                    "model": "825",
                    "key": "444II2F9I7",
                    "ver": "v1",
                    "active": false
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "",
                    "ser": "65xxxx",
                    "model": "825",
                    "key": "B033Y0I962",
                    "ver": "v2",
                    "active": false
                },
                {
                    "software": "MAILTOOL",
                    "l_ser": "06Bxxxx",
                    "ser": "06BB614",
                    "model": "E4A",
                    "key": "Q72FB675IY",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "MAILTOOLP",
                    "l_ser": "06Bxxxx",
                    "ser": "06BB614",
                    "model": "E4A",
                    "key": "YUIH5F275JK",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "06Bxxxx",
                    "ser": "06BB614",
                    "model": "E4A",
                    "key": "755567YT5",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "MAILTOOL",
                    "l_ser": "06Bxxxx",
                    "ser": "06B9B74",
                    "model": "E4A",
                    "key": "TY5H7255I6",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "MAILTOOLP",
                    "l_ser": "06Bxxxx",
                    "ser": "06B9B74",
                    "model": "E4A",
                    "key": "668H5H5H9I2",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "06Bxxxx",
                    "ser": "06B9B74",
                    "model": "E4A",
                    "key": "H6F5H2H654",
                    "ver": "v3",
                    "active": false
                },
                {
                    "software": "MAILTOOL",
                    "l_ser": "21Exxxx1",
                    "ser": "21Exxxx",
                    "model": "41A",
                    "key": "FJ57220755",
                    "ver": "v3",
                    "active": true
                },
                {
                    "software": "MAILTOOLP",
                    "l_ser": "21Exxxx1",
                    "ser": "21Exxxx",
                    "model": "41A",
                    "key": "56HH2550TY2",
                    "ver": "v3",
                    "active": true
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "21Exxxx1",
                    "ser": "21Exxxx",
                    "model": "41A",
                    "key": "7RE0BH2TR5",
                    "ver": "v3",
                    "active": true
                },
                {
                    "software": "MAILTOOL",
                    "l_ser": "21Exxxx1",
                    "ser": "21Exxxx",
                    "model": "41A",
                    "key": "FTRGFF75T2",
                    "ver": "v3",
                    "active": true
                },
                {
                    "software": "MAILTOOLP",
                    "l_ser": "21Exxxx1",
                    "ser": "21Exxxx",
                    "model": "41A",
                    "key": "5597GF9H5R4",
                    "ver": "v3",
                    "active": true
                },
                {
                    "software": "SPLTOOL",
                    "l_ser": "21Exxxx1",
                    "ser": "21Exxxx",
                    "model": "41A",
                    "key": "TIGFHIH5R2",
                    "ver": "v3",
                    "active": true
                }
            ]
        }...
    ]
}		

In this example I wanted to make sure I tried the JSON character, numeric and boolean functions and they all worked very nicely.  

I also tested the escaping of data and that appeared to work as well.

So, for your next JSON project, you shouldn't be worried at all!  With the YAJL library ported to the IBM i by Scott Klement 99% of the work is already done!

Related Articles:

 


Last edited 07/12/2019 at 10:51:16



Latest Posts:

Update for Google WorkSpace Accounts (2024): Google Dropping Support for Update for Google WorkSpace Accounts (2024): Google Dropping Support for "Less Secure Apps" May 30th, 2022. What Does This Mean for Your IBM i Email?
Posted by January 19, 2024
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sales By State Report in QuickBooks Online Sales By State Report in QuickBooks Online
Posted by January 13, 2024
QuickBooks >> QuickBooks Online
How to Whitelist GreenTools for G Suite (G4G) For Your Organization How to Whitelist GreenTools for G Suite (G4G) For Your Organization
Posted by November 5, 2023
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific Discussion
QuickBooks Online Releases QuickBooks Online Releases "New Invoices!"... and It's Terrible!
Posted by October 8, 2023
QuickBooks >> QuickBooks Online
Admin/4i - What is it? Admin/4i - What is it?
Posted by September 30, 2023
Vendor Corner >> MSD Information Technology
BVSTools Releases Send Job Log to BVSTools (SNDLOG2BVS) Command BVSTools Releases Send Job Log to BVSTools (SNDLOG2BVS) Command
Posted by August 28, 2023
BVSTools >> BVSTools Announcements
MAILTOOL Now Allows Email Redirection for Development and Testing MAILTOOL Now Allows Email Redirection for Development and Testing
Posted by May 27, 2023
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
GreenTools for Microsoft Apps (G4MS) Now Supports Footers When Sending Email GreenTools for Microsoft Apps (G4MS) Now Supports Footers When Sending Email
Posted by March 29, 2023
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
QuickBooks Online - Subtotals and Discounts Frustration QuickBooks Online - Subtotals and Discounts Frustration
Posted by March 16, 2023
QuickBooks >> QuickBooks Online
Making the Switch From QuickBooks Desktop to QuickBooks Online - No Picnic Making the Switch From QuickBooks Desktop to QuickBooks Online - No Picnic
Posted by March 16, 2023
QuickBooks >> QuickBooks Online
BVSTools Software Verified on V7R5 and Power10 BVSTools Software Verified on V7R5 and Power10
Posted by December 9, 2022
BVSTools >> BVSTools Announcements
Microsoft Office 365 Servers and Random Errors Issue Microsoft Office 365 Servers and Random Errors Issue
Posted by November 14, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sending/Resending Emails Using a MIME File with MAILTOOL Sending/Resending Emails Using a MIME File with MAILTOOL
Posted by November 8, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sending an HTML Email on Your IBM i Using MAILTOOL Sending an HTML Email on Your IBM i Using MAILTOOL
Posted by November 1, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Transferring License Keys from One System to Another Transferring License Keys from One System to Another
Posted by October 31, 2022
BVSTools >> BVSTools Software Discussion
mcouch

RE: Building JSON using RPG with Scott Klement\'s Port of YAJL (Yet Another JSON Library)

Posted:

RE: Building JSON using RPG with Scott Klement\'s Port of YAJL (Yet Another JSON Library)

Thank you for your Article Parsing JSON using RPG with Scott Klement's Port of YAJL - Part 3.  It had the exact answer that I needed for my project.  The vendor that I am getting a file from puts an unnamed array inside a file and sends that JSON file.  Using your vehicleList = docNode example, solved my problem of the missing array name.

 

Thank you, thank you.

 

 


Last edited 08/05/2016 at 09:07:42



bvstone

RE: RE: Building JSON using RPG with Scott Klement\\'s Port of YAJL (Yet Another JSON Library)

Posted:

RE: RE: Building JSON using RPG with Scott Klement\\'s Port of YAJL (Yet Another JSON Library)

I'm glad it helped!  I've been doing a lot of JSON both building and parsing and hope to have more examples soon.


Last edited 08/05/2016 at 09:10:24



tim.hurring@finzsoft.com

RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Posted:

RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Another excellent article. I've been successfully using YAJL to create and parse JSON documents. I've now got a requirement to generate a single JSON across multiple external programs i.e. PGM A generates the first part of the JSON, then calls PGM B to generate the remainder of the JSON. How do I pass the JSON object between programs? I assume this can be done using pointers? Thanks in advance.


Last edited 05/12/2022 at 23:24:51



bvstone

RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Posted:

RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

If you're building the json in a stream file just pass the path to the file.


Last edited 05/13/2022 at 07:45:15



tim.hurring@finzsoft.com

RE: RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Posted:

RE: RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Thanks for your prompt reply. I'm not currently using a stream file, I'm building the JSON object in memory, then writing it to a CLOB in the database once it's complete. I assumed that the overhead of multiple read/writes to the IFS would have a negative performance impact. Is there a pointer based solution?


Last edited 05/13/2022 at 16:06:47



bvstone

RE: RE: RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Posted:

RE: RE: RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Well, if you're using YAJL, you have a pointer to the main node that you could pass.  Just have to be careful with pointers.

I actually always use stream files.  The I/O isn't that big of a hurt, and if you're debugging you can see it much easier.

If you asked your question on code400.com forum, Scott Klement will most likely answer the question better, or offer some other pointers (no pun intended.. ha)


Last edited 05/13/2022 at 16:11:00



tim.hurring@finzsoft.com

RE: RE: RE: RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Posted:

RE: RE: RE: RE: Building JSON using RPG with Scott Klement's Port of YAJL (Yet Another JSON Library)

Thank you again for the reply. I'll do both - try the stream file approach, and post the pointer question to the code400 forum.


Last edited 05/13/2022 at 18:48:20




Reply




© Copyright 1983-2020 BVSTools
GreenBoard(v3) Powered by the eRPG SDK, MAILTOOL Plus!, GreenTools for Google Apps, jQuery, jQuery UI, BlockUI, CKEditor and running on the IBM i (AKA AS/400, iSeries, System i).