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:

GreenTools for Google Apps (G4G) v15.00 Now Offers Functions to Bypass Registration Command and BVSTools Landing Page GreenTools for Google Apps (G4G) v15.00 Now Offers Functions to Bypass Registration Command and BVSTools Landing Page
Posted by May 3, 2022
BVSTools >> BVSTools Announcements >> GreenTools for G Suite (Google Apps) (G4G) Specific Announcements
How Do I Switch From MAILTOOL Plus to GreenTools for Google or Microsoft Office 365? How Do I Switch From MAILTOOL Plus to GreenTools for Google or Microsoft Office 365?
Posted by April 18, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
PTFs Issued for SSL/TLS Issues PTFs Issued for SSL/TLS Issues
Posted by March 12, 2022
IBM Power Systems >> PTF Watch
Google Dropping Support for Google Dropping Support for "Less Secure Apps" May 30th, 2022. What Does This Mean for Your IBM i Email?
Posted by March 4, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Have You Installed a New Version of MAILTOOL and Now Things Are Acting Different?  Check the Command Defaults! Have You Installed a New Version of MAILTOOL and Now Things Are Acting Different? Check the Command Defaults!
Posted by February 28, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Using MAILTOOL Plus on V7R1, or Any OS Using TLS 1.1 or Older Using MAILTOOL Plus on V7R1, or Any OS Using TLS 1.1 or Older
Posted by January 27, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
BVSTools ILE Functions Being Updated to Remove Hashtag (#) from Function Names BVSTools ILE Functions Being Updated to Remove Hashtag (#) from Function Names
Posted by December 30, 2021
BVSTools >> BVSTools Announcements
GETURI v12.00 Released Removing Beginning # (Hashtag) From Function Names GETURI v12.00 Released Removing Beginning # (Hashtag) From Function Names
Posted by December 28, 2021
BVSTools >> BVSTools Announcements >> Get URI (GETURI) Specific Announcements
Is any of BVSTools Software Affected by the log4j exploit? Is any of BVSTools Software Affected by the log4j exploit?
Posted by December 20, 2021
BVSTools >> BVSTools Software Discussion
GreenTools for Microsoft Apps (G4MS) Updated to Allow Downloads, Deletes, and Sharing of Files GreenTools for Microsoft Apps (G4MS) Updated to Allow Downloads, Deletes, and Sharing of Files
Posted by December 17, 2021
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
SSL Handshake Errors with GETURI, MAILTOOL and GreenTools Products SSL Handshake Errors with GETURI, MAILTOOL and GreenTools Products
Posted by October 18, 2021
BVSTools >> BVSTools Software Discussion
MAILTOOL Updated to Retry Sending when GSK SSL Handshake Error 415 (GSK_ERROR_BAD_PEER) is Encountered MAILTOOL Updated to Retry Sending when GSK SSL Handshake Error 415 (GSK_ERROR_BAD_PEER) is Encountered
Posted by August 19, 2021
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
MAILTOOL Updated to Allow List-Unsubscribe and User Defined Headers MAILTOOL Updated to Allow List-Unsubscribe and User Defined Headers
Posted by August 13, 2021
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
AWS signing process in as400 AWS signing process in as400
Posted by August 13, 2021
Programming >> Web Programming
2022 License Price Increase and Consulting Update for Non-Software Specific Assistance 2022 License Price Increase and Consulting Update for Non-Software Specific Assistance
Posted by August 9, 2021
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



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).