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 08/31/2018 at 11:38:36



Latest Posts:

Create QRCODE in DDS Create QRCODE in DDS
Posted by September 21, 2018
Programming >> RPG Programming
Base64 Encoding a File with RPG Base64 Encoding a File with RPG
Posted by September 6, 2018
Programming >> RPG Programming
Building JSON with RPG and YAJL and Writing to Standard Output Building JSON with RPG and YAJL and Writing to Standard Output
Posted by August 31, 2018
Programming >> Proof of Concept (POC)
How to Delete Files or Empty Trash From Your Google Drive with your IBM i and RPG/ILE How to Delete Files or Empty Trash From Your Google Drive with your IBM i and RPG/ILE
Posted by July 24, 2018
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific Discussion
GreenTools for G Suite (G4G) Updated to Include Delete and Empty Trash Function GreenTools for G Suite (G4G) Updated to Include Delete and Empty Trash Function
Posted by July 24, 2018
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
What to Do If Your License Keys Don't Work What to Do If Your License Keys Don't Work
Posted by July 18, 2018
BVSTools >> BVSTools Software Discussion
MAILTOOL Updated to Allow Failed Message on Invalid Recipient MAILTOOL Updated to Allow Failed Message on Invalid Recipient
Posted by May 20, 2018
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
Non HTTPS Callbacks Removed from GreenTools for G Suite (G4G) Non HTTPS Callbacks Removed from GreenTools for G Suite (G4G)
Posted by April 15, 2018
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
IBM i Related Survey Available IBM i Related Survey Available
Posted by April 7, 2018
IBM Power Systems >> (QGPL) IBM i
BVSTools Releases Braintree Webhook Open Source Application - Node.js BVSTools Releases Braintree Webhook Open Source Application - Node.js
Posted by April 5, 2018
Programming >> Open Source
BVSTools Now Offering Web Services (BETA) BVSTools Now Offering Web Services (BETA)
Posted by April 3, 2018
BVSTools >> BVSTools Announcements
Creating a Reverse SSL Proxy Using RPG on the IBM i - Part 2 Creating a Reverse SSL Proxy Using RPG on the IBM i - Part 2
Posted by March 29, 2018
Programming >> Web Programming
Still on V7R1 or Earlier?  Here's Why You Should Upgrade NOW! Still on V7R1 or Earlier? Here's Why You Should Upgrade NOW!
Posted by February 21, 2018
IBM Power Systems >> (QGPL) IBM i
Converting a MMDDYY date format to YYMMDD for Sorting Using SQL Converting a MMDDYY date format to YYMMDD for Sorting Using SQL
Posted by February 16, 2018
Programming >> RPG Programming
Moving All Files from a Google Drive Folder to the Trash Using GreenTools for Google Apps (G4G) Moving All Files from a Google Drive Folder to the Trash Using GreenTools for Google Apps (G4G)
Posted by February 3, 2018
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific 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




Reply




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