noxDB comparison (2018)

[worksofliam]

I have recently been able to help bring the noxDB project to GitHub, integrate a basic makefile and also create documentation for the APIs. noxDB is an XML and JSON parser and generator. It has some other SQL functionality too to generate data sets from an SQL statement, which saves a lot of the dirty work for you.

This is an opinion based article. You may feel differently about things that I write about.

In this post I am going to be comparing YAJL and noxDB. YAJL (Yet Another JSON Library) was ported to IBM i by Scott Klement, which he then added a wrapper API to it to make it easier to use in RPG applications (YAJLR4). YAJLR4 also handles the EBCDIC to UTF8 conversion, which is nice. noxDB was written from the ground up by System and Method and now is available as open-source on GitHub.

In this post we're going to cover these topics on both solutions:

• Installation
• Ease of APIs
• Extra
• Ending

Installation

YAJL installation is kind of outdated in my opinion. There are two ways to install: either restoring a save file or building from source. Now, I am more than happy to build from source, but most people won't do that. Most people would rather just extract a save file, which makes sense. The real shame is that most of the process is manual and has not been automated.

ftp your-ibmi-system
(sign in)
quote rcmd CRTSAVF QGPL/YAJLLIB
binary
put YAJLLIB.SAVF QGPL/YAJLLIB
quote rcmd CRTLIB YAJL
quote rcmd RSTOBJ OBJ(*ALL) SAVLIB(QTEMP) DEV(*SAVF) RSTLIB(YAJL) SAVF(QGPL/YAJLLIB)

noxDB is a two step process. noxDB requires you to build from source, but this step has been totally automated for you. To install noxDB, you need to use the pase environment (over SSH for example) and with a couple of seconds you can have the project built. No need to download save files, upload them or restore them.

git clone [email protected]:sitemule/noxDB.git
cd noxDB
gmake

Ease of APIs

The original version of YAJL has the concept of a 'handle', which would allow work on multiple different JSON stores at one time. It seems that YAJLR4 does not have this concept and therefore everything must be done in a global space (and one stream at a time). The nice thing about YAJL is that the developer does not need to worry about pointers in any way, which makes their life nice and ease. Personally though, the loss here is not being able to handle two data streams at once.

Dcl-Ds PRODUCT ExtName('PRODUCT') Qualified End-Ds;
Dcl-S JSONPtr Pointer;  
Dcl-S Length Uns(10);

yajl_genOpen(*Off);  
yajl_beginArray();  

EXEC SQL  
  DECLARE Prod_Cur CURSOR FOR  
    SELECT * FROM PRODUCT  
    WHERE MANUID = 'SAMSUNG';  

EXEC SQL OPEN Prod_Cur;  

If (SQLSTATE = '00000');  
  EXEC SQL FETCH Prod_Cur INTO :PRODUCT;  

    Dow (SQLSTATE = '00000');  
      yajl_beginObj();  
      yajl_addNum('key': %Char(PRODUCT.ProdKey) );  
      yajl_addChar('id': %TrimR(PRODUCT.ProdID) );  
      yajl_addChar('desc': %TrimR(PRODUCT.DESC) );  
      yajl_addNum('price': %Char(PRODUCT.Price) );  
      yajl_endObj();  

      EXEC SQL FETCH Prod_Cur INTO :PRODUCT;  
    Enddo;  

  Endif;  

EXEC SQL CLOSE Prod_Cur;  

yajl_endArray();  
yajl_getBuf(JSONPtr:Length);  
yajl_genClose();

Note that when we want to create a JSON object, we start with yajl_beginObj, end with yajl_endObj and anything in between goes into that object. I am not a huge fan of the yajl_addNum API because the developer still has to convert that number into a string. yajl_addBool is nice because you can actually pass *YES or *NO as you would expect.

noxDB is nice in that you don't need to do anything in a specific order because everything is a pointer effectively. If you create an array, you get a pointer to it. If you create an object, you get a pointer. Want to create an array of object, use the API to add the pointer to the object to the array.

Dcl-S ArrayPtr  Pointer;
Dcl-S ObjectPtr Pointer;
Dcl-S Result    Char(4098);

ArrayPtr = json_NewArray();

EXEC SQL
  DECLARE Prod_Cur CURSOR FOR
  SELECT * FROM PRODUCT
  WHERE MANUID = 'SAMSUNG';

EXEC SQL OPEN Prod_Cur;

If (SQLSTATE = '00000');
  EXEC SQL FETCH Prod_Cur INTO :PRODUCT;

  Dow (SQLSTATE = '00000');
    ObjectPtr = json_newObject();
    json_SetNum(ObjectPtr:'key': PRODUCT.ProdKey );
    json_setStr(ObjectPtr:'id': %TrimR(PRODUCT.ProdID) );
    json_setStr(ObjectPtr:'desc': %TrimR(PRODUCT.DESC) );
    json_SetNum(ObjectPtr:'price': PRODUCT.Price );
    json_ArrayPush(ArrayPtr:ObjectPtr:JSON_MOVE_UNLINK);

    EXEC SQL FETCH Prod_Cur INTO :PRODUCT;
  Enddo;

Endif;

EXEC SQL CLOSE Prod_Cur;

Result = json_AsJsonText(ArrayPtr);
json_NodeDelete(ArrayPtr);

Another nice thing, as I mentioned before is that the json_SetNum API does not require a character and accept a normal numeric value. Also, as a side node, if you wanted to (for some reason) generate XML from this document store, you could use json_AsXmlText(ArrayPtr).

Extra

So now you've seen that you can use noxDB or YAJL to generate JSON (or XML), but there is still one fantastic feature of noxDB which has not been shown yet. noxDB ships with some excellent SQL functionality, which would remove a lot of the ground work of an RPG application that generates a JSON document from an SQL query.

Dcl-S ArrayPtr Pointer;  
Dcl-S sql Varchar(256);  
Dcl-S Result Char(4098);  

//------------------------------------------------------------- *

// return one object with one row
sql = (  
  'select * ' +  
  'from product ' +  
  'where MANUID = ''SAMSUNG'''  
);  

// The key is setup in the json template:  
ArrayPtr = json_sqlResultSet(sql);  

Result = json_AsJsonText(ArrayPtr);

// Cleanup: delete the object and disconnect  
json_NodeDelete(ArrayPtr);  
json_sqlDisconnect();

End

noxDB has documentation available on GitHub. You can read more about creating & reading JSON (and XML) documents there. Hope this was a good insight to using a new tool.