JSON APIs

[gwilburn]

I realize this is a broad-spectrum question, but here goes.

We have the need to communicate with REST APIs (Bill.com) that use JSON but do not support XML... I've done quite a bit with HTTPAPI and XML, but have no experience with JSON (other than downloading and experimenting with Scott Klements YAJL service programs). I'm relativley comfortable with XML parsing in RPGLE and SQLRPGLE.

I'm soliciting opinions/experiences on how best to approach this type of project. I realize there are options to convert JSON to XML and XML back to JSON, and options to "convert" DB2 files to/from JSON.

Thanks,
Greg

[JonBoy]

If you are familia with Scott's YAJL port why not use that to parse the JSON? It can create and parse JSON very easily.

This article discusses JSON consumption with YAJL http://ibmsystemsmag.com/ibmi/develo...ata-with-yajl/ and this one creating it http://ibmsystemsmag.com/ibmi/develo...st-steps-json/.

Scott also has a presentation on his web site that covers both uses.

[B.Hauser]

SQL provides functions (JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, JSON_OBJECTAGG - for Generating) and a table function (JSON_TABLE - for parsing), which can be used in embedded SQL.
All JSON functions are available for Release 7.2 and 7.3 (latest Technology Refresh).
But since you are asking in an RPG forum SQL may be no option.

Birgitta

[gwilburn]

Birgitta - Thank you. SQL is an option.. however I'm on 7.1 right now. My SQL experience is limited to creating views, small stored procedures, and SQL embedded in RPG programs.

JonBoy - thanks for the links. I will check those out. I think I already have Scott's presentation.

Choices and options are good, I guess

[Scott Klement]

FWIW, I would use YAJL. The SQL JSON routines are interesting, but I prefer the YAJL syntax, and find it performs better, too. I do know others who prefer the SQL routine syntax, so it might be a matter of preference.

[gwilburn]

FWIW, I would use YAJL. The SQL JSON routines are interesting, but I prefer the YAJL syntax, and find it performs better, too. I do know others who prefer the SQL routine syntax, so it might be a matter of preference.

Thanks for chiming in... I was leaning that way. Turns out I may not get do this project. This company (Bill.com) charges a substantial yearly fee for using their API's.

[jtaylor___]

FWIW, I would use YAJL. The SQL JSON routines are interesting, but I prefer the YAJL syntax, and find it performs better, too. I do know others who prefer the SQL routine syntax, so it might be a matter of preference.

I've been using YAJL for a couple years now. We upgraded to 7.3 in September, so I tried the new SQL JSON routines. I decided to stick with YAJL for now. It just seems to give more control.

[mjhaston]

Just joining in and playing around with SQL JSON routines as well. I did a pretty simple SQL json_object statement just to test. I think it's a little sluggish, but it worked well. It's nice that it knows my data type and handles it. Is it possible to then send this data back to the browser using YAJL? Otherwise, I would just stick with YAJL at this time.

[Lorenz Wessa]

When possible, for JSON I would recommend using the db2 functionality, link with all the examples you will need:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/sqlp/rbafyjsonpublishing.htm

HOWEVER for many this won't be an option. The functionality is very new (release in 2017,2018 on seperate dates) so anyone who did an update before that or in between might be in my situation. You have access to JSON_TABLE,but not all the other functions.. Or you do have the functions but the applicability is limited.. Customisation is difficult and the JSON generates the way IBM sees fit. This goes at massive perfomance cost if you need to do operations on your JSON object to format it for REST api's, other programs such as php which want to use an object orientated approach to JSON, functionality that JSON_OBJECT simply can't provide. I have a solution, it's not 100% finished but it works and I have generated a load of configuration and other json files with this.
I call a PHP program over CLI using zendsrv and have it create a JSON from a DDS/DB2 table, it works generically all you need is an sql statement or a table, scheme name to turn your sql result or db2 table into JSON. (Also works over socket)

If anyone is interested I could post a full comprehensive guide someday on how to do this. Once you've got this function to call a PHP script from RPG you can include PHP into any RPG program!! If you're ever stuck on something or there's something that just can't be done simply with native RPG functions, you can always opt to simply use a PHP script (this can often save you incredible ammounts of time, aside from that it's lightning fast. Calling the a php script, connecting to db2, reading an sql from a file somewhere on the IFS, then creating a JSON file from the resultset of the sql statement even if it's a huge ammount of records goes faster than the blink of an eye! In my experience IFS functions from rpg are rather slow so is encoding large data, so I love this approach as it is performant and time-saving.

[jtaylor___]

When possible, for JSON I would recommend using the db2 functionality ...

What's your reasoning?

[Lorenz Wessa]

What's your reasoning?

My reasoning is that it's native functionality and fairly new (released in the past 2 years). It's performant and generaly easy to use. YAJL was made arround 2014, before native functionality was a thing, it served it's purpose filling a requirement that is now natively availble. If YAJL is significantly better, I'd love to hear why.

Kind regards

[Scott Klement]

Lorenz, I don't appreciate you putting down my tools. I spend a lot of time and effort on YAJL, making it easier to use, adding new functionality, providing help to people. This is all done at no charge on my own personal time.

It's somehow worse that IBM's support in DB2 because I got it to market sooner than IBM did?!

Use whichever you prefer, but please don't put down something because you chose an alternative.

[mjhaston]

When possible, for JSON I would recommend using the db2 functionality, link with all the examples you will need:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/sqlp/rbafyjsonpublishing.htm

HOWEVER for many this won't be an option. The functionality is very new (release in 2017,2018 on seperate dates) so anyone who did an update before that or in between might be in my situation. You have access to JSON_TABLE,but not all the other functions.. Or you do have the functions but the applicability is limited.. Customisation is difficult and the JSON generates the way IBM sees fit. This goes at massive perfomance cost if you need to do operations on your JSON object to format it for REST api's, other programs such as php which want to use an object orientated approach to JSON, functionality that JSON_OBJECT simply can't provide. I have a solution, it's not 100% finished but it works and I have generated a load of configuration and other json files with this.
I call a PHP program over CLI using zendsrv and have it create a JSON from a DDS/DB2 table, it works generically all you need is an sql statement or a table, scheme name to turn your sql result or db2 table into JSON. (Also works over socket)

If anyone is interested I could post a full comprehensive guide someday on how to do this. Once you've got this function to call a PHP script from RPG you can include PHP into any RPG program!! If you're ever stuck on something or there's something that just can't be done simply with native RPG functions, you can always opt to simply use a PHP script (this can often save you incredible ammounts of time, aside from that it's lightning fast. Calling the a php script, connecting to db2, reading an sql from a file somewhere on the IFS, then creating a JSON file from the resultset of the sql statement even if it's a huge ammount of records goes faster than the blink of an eye! In my experience IFS functions from rpg are rather slow so is encoding large data, so I love this approach as it is performant and time-saving.

That's a lot of moving parts! Using HTTPAPI + YAJL, I just made an API call. Received the JSON response. Parsed the JSON into my RPG program variables in about 3 lines of code and all in RPG.

[Lorenz Wessa]

Lorenz, I don't appreciate you putting down my tools. I spend a lot of time and effort on YAJL, making it easier to use, adding new functionality, providing help to people. This is all done at no charge on my own personal time.

It's somehow worse that IBM's support in DB2 because I got it to market sooner than IBM did?!

Use whichever you prefer, but please don't put down something because you chose an alternative.

Dear Mr. Klement, perhaps you misunderstood what I was saying I wasn't trying "to put down" your work. I actually have a lot of respect for all your publishments. PhoneGap was presented at our work and looks so very promising. Aside from that I have read quite some articles about you and your work. I look up to you in many ways, and I was just stating that JSON functions are now natively available, I was also asking if YAIJL is better and if so, why. I'm more trying to learn and brighten my horizons than criticising anyone's work. Aside from all of this I'm just a junior developer in a field surrounded by (mostly) older developers with a lot more experience, making it hard enough to voice myself as it is.

When I gave my recommendation, I was simply recommending the native functionality because when I was asked to write programs where JSON was involved I searched the internet, came accros db2's JSON_OBJECT and YAJL. When JSON_OBJECT wasn't an option for me I opted to use PHP, as it was something I found very easy to implement. Try to look at my recommendation through a young junior's eyes, I'm just sharing what I found easiest to use. It'll be a very long time before I'll comfortably say that "I know RPG" as everyday I open up an older program I'm still baffled and struggling, navigating old legacy code.

I hope you don't take any offence towards you or your work, again I do have a lot of respect for you, thank you for all your great work and posts and I'm sorry if I made it seem like I put down your work.

Kind regards.

[jtaylor___]

My reasoning is that it's native functionality and fairly new (released in the past 2 years). It's performant and generaly easy to use. YAJL was made arround 2014, before native functionality was a thing, it served it's purpose filling a requirement that is now natively availble. If YAJL is significantly better, I'd love to hear why.

Kind regards

I concur that, all else being equal, native is better than third party.

I don't see that "new" is a benefit on its own. "New" comes with risk, and there must be sufficient benefits to offset that risk.

For me the scales favor YAJL for RPG.

Thanks