On Wed, Feb 26, 2020 at 1:01 PM Rob Berendt <rob@xxxxxxxxx> wrote:
Assume it only returns a single row.
Look at the full example at the URL I had given you using that same json_table function.
I don't agree with that answer. At least, I don't think it's clear
enough or helpful enough.
Let's look at Jeff's question again:
is :
"Select * from json_table(systools.httpgetclob(:URL, ..."
designed for reading a table via cursor for ultimately putting into an
array?
Jeff, because you put "ultimately" in there, my short answer to you is: yes.
But I think I know where Rob is coming from. You should understand
that the retrieval is kind of done in two phases.
The first phase is calling SYSTOOLS.HTTPGETCLOB. That is the thing
that is actually calling the Web service, and in that phase, you are
getting back *exactly one* CLOB. Think of it as a long string. And
that string contains some arbitrary JSON data. I believe that when Rob
refers to one record, he means you just get back this one CLOB.
Now, the JSON data in that CLOB could be anything. Depending on the
Web service and the parameters you passed to it, it could be wrapping
up zero, one, or multiple "records" within the JSON. So then the next
phase is the JSON_TABLE function parsing the JSON into *those*
records. And yes, the example in Rob's URL uses a cursor to loop over
those records.
These phases might be clearer if you were using Python or Node.js (or
similar languages). Because they have ways of talking with Web
services directly, and ways of parsing JSON directly, you don't have
to shoehorn everything into SQL. In those other languages, you could
have pseudocode much more like
long_string = http_request(my_url)
data_objects = parse_json(long_string)
for object in data_objects:
do_thing(object)
Well, I guess technically you don't HAVE to shoehorn everything into
SQL with RPG either. You could use things like Scott Klement's HTTPAPI
and YAJL instead, and then it would be a lot more like the pseudocode
I mentioned (just more verbose than with Python, Node.js, etc.).
John Y.
midrange.com
