Booth, I'm afraid I'm at a loss then as to what it is you're trying to ask or what the problem is with the solutions you've been provided. You seem to me to keep questioning whether using JSON_TABLE with an array works, yet I've given you two examples demonstrating that it does work, and how to use it. The whole point of JSON_TABLE is to deal with arrays! My second example even used nested arrays. Have you even tried the example code? If you are still labouring under the impression that JSON_TABLE doesn't work then I think you must have a misconception about what it is supposed to do, the only way to read an array of JSON data with multiple elements is by iterating over the result set returned by JSON_TABLE, and if you wish to read row-by-row in RPG then you need a cursor - there is no other way!
Your assertion that even a complex request might be done in 20 lines of code, compared to my example with it's 185 is disingenuous I feel (and plain wrong). Even the examples from Birgitta make it clear that this is not true. The example I wrote for you was written with two objectives in mind a) to be clear and easy to follow, b) to be complete enough to be used as a template for writing something more than a proof on concept. It wasn't written with a view to minimising the number of lines of code (which is a dubious goal in any case). Even with point b) in mind, I omitted most of the complexities of exception handing and used "gethttpclob" instead of the more complex "verbose" version, which you really should use if you are writing robust code.
I've taken a look at your example Name a Day function
http://www.martinvt.com/Web/Name-A-Day/name-a-day.html in order to try to work out what your issue in understanding might be and, while I don't wish to make this a critique of your code, I do have a one or two observations. Firstly, there's absolutely no error checking at all, so if the SQL statements fail, they do so silently. Second, you are completely ignoring the request headers that are supposed to be sent with the HTTP request and, while you might get away with this in an extremely simple example, for most proper webservice APIs calls you are going to have to provide something in the headers (I included code to build the headers in my example for this reason). Thirdly, you do need even need to use JSON_TABLE in your example because you are not reading an array of data from the returned JSON object, which is flat, you can instead use the much simpler JSON_QUERY function. So your code would become:
exec sql set :S1DAYNAME = json_query(SYSTOOLS.HTTPGETCLOB(:dataIn,''), '$.dayOut');
....saving you at least one whole line of code, if not two 🙂.
My point is, it's easy to knock up an example against a very simple API, with no error handling, minimal structure and and corner-cutting things like the headers, but to write any kind of robust code against a "proper" webservice API is going to require a bit more effort. However, as my day job is currently writing SOAP webservice calls (using SQL/RPG) to various different shipping agents, I can assure you that far and away the biggest effort is spent trying to understand complex third-party APIs, that are often poorly documented, rather than writing the code which, even done robustly, is fairly trivial by comparison.
Cheers,
Tim.
________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Booth Martin <booth@xxxxxxxxxxxx>
Sent: 20 November 2019 00:00
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] json_table(httpgetclob, nested array
I need to be careful how I answer. First, I appreciate the responses
and the discussion; I really do. I try the various suggestions and
these lead to other questions; all in all, a useful and interesting
side trip.
But I want to come back to the thread's subject line "
json_table(httpgetclob, nested array". "_json_table(httpgetclob_ ...)
is, if I am reading things right, less than 20 lines of code for even
complex requests compared with Tim's example of a simple request which
is about 185 lines. If json_table(httpgetclob, nested array does in
fact work, then teaching average RPG programmers to consume .json data
becomes a useful goal for the platform.
In other words, IWS and httpgetclob lets us ordinary programmers quickly
learn to publish and/or consume .json data.
ps: Birgitta has a very nice slideshow on the process. It may be a bit
dated by now though?
birgitta <
https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocplayer.net%2F53054226-Consuming-json-data-with-sql.html&data=02%7C01%7C%7C7b64932a792241f1b27508d76d445891%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637098012613043421&sdata=enXfdQuxaVADlVJKbXaP9MiPxYlBtZ1bAV6kjK2Jmig%3D&reserved=0>
On 11/18/2019 4:49 PM, Jon Paris wrote:
As I and others have pointed out Booth you need to process the data with a cursor! It is returning a result set - not an array.
Look at the earlier example that was posted - it included all the code you needed - custom and all.
--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C7b64932a792241f1b27508d76d445891%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637098012613043421&sdata=801eEM25rNDI417DDd4oJE0xjR%2BISyRkwaEz8zzYDfg%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C7b64932a792241f1b27508d76d445891%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637098012613043421&sdata=ulhijfttRJHdPC5G%2F6uGVPx%2BbDe5l8Fj6ECGV2i3MCs%3D&reserved=0.
midrange.com
