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:

BVSTools is Now Running V7R4M0 BVSTools is Now Running V7R4M0
Posted by September 28, 2019
BVSTools >> BVSTools Announcements
GreenTools for G Suite (G4G) Now Includes Send Mail Functionality (G4GSMAIL Addon) GreenTools for G Suite (G4G) Now Includes Send Mail Functionality (G4GSMAIL Addon)
Posted by August 27, 2019
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
GreenTools For G Suite (G4G) v12.00 Released With Base OAuth 2.0 Functionality GreenTools For G Suite (G4G) v12.00 Released With Base OAuth 2.0 Functionality
Posted by July 28, 2019
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
BVSTools Small Price Increase in 2020 BVSTools Small Price Increase in 2020
Posted by July 26, 2019
BVSTools >> BVSTools Announcements
GreenTools for Vertex Cloud (VTXCLOUD) Now Available GreenTools for Vertex Cloud (VTXCLOUD) Now Available
Posted by July 22, 2019
BVSTools >> BVSTools Announcements >> GreenTools for Vertex Cloud (VTXCLOUD) Specific Announcements
GreenTools for Google Apps (G4G) - Drive Addon Successfully Verified by Google GreenTools for Google Apps (G4G) - Drive Addon Successfully Verified by Google
Posted by July 22, 2019
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
Why I Cancelled my DynDNS Service and How I Replaced It with an IBM i Application Why I Cancelled my DynDNS Service and How I Replaced It with an IBM i Application
Posted by July 17, 2019
IBM Power Systems >> (QGPL) IBM i
Green Tools for G Suite (G4G) Product Updates (Licensing, Functionality, Base Product) Green Tools for G Suite (G4G) Product Updates (Licensing, Functionality, Base Product)
Posted by July 13, 2019
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
Reading JSON Data from Standard Input With YAJL and RPG Reading JSON Data from Standard Input With YAJL and RPG
Posted by July 12, 2019
Programming >> Proof of Concept (POC)
MAILTOOL Updated to Allow Use of IBM Global Security Kit (GSKIT) for SSL/TLS Communications MAILTOOL Updated to Allow Use of IBM Global Security Kit (GSKIT) for SSL/TLS Communications
Posted by June 19, 2019
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
GETURI v10.00 Released Supporting IBM Global Security Kit (GSKIT) and Server Name Indication (SNI) GETURI v10.00 Released Supporting IBM Global Security Kit (GSKIT) and Server Name Indication (SNI)
Posted by June 11, 2019
BVSTools >> BVSTools Announcements >> Get URI (GETURI) Specific Announcements
BVSTools Now Offers Vertex Cloud Interface BVSTools Now Offers Vertex Cloud Interface
Posted by April 15, 2019
BVSTools >> BVSTools Announcements
Token Has an Invalid Signature Error for Office 365 Email Token Has an Invalid Signature Error for Office 365 Email
Posted by March 22, 2019
BVSTools >> BVSTools Software Discussion >> GreenTools for Microsoft Apps (G4MS) Specific Discussion
Resending Emails that have Errored Out with Updated Router or Authentication Information Resending Emails that have Errored Out with Updated Router or Authentication Information
Posted by March 1, 2019
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
BVSTools Offers Toolset to Work With HubSpot OAuth 2.0 APIs On Your IBM i BVSTools Offers Toolset to Work With HubSpot OAuth 2.0 APIs On Your IBM i
Posted by January 27, 2019
BVSTools >> BVSTools Announcements
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




Reply




Copyright 1983-2019 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).