Maybe there is also some confusion because there are various different routines for accessing web services, some are UDFs and some are UDTFs, the former being scalar functions which return a single return value, which is either CLOB or a BLOB and the latter being table functions (their names suffixed "VERBOSE"), which return a table with a single row. The main difference between the two is that the scalar functions only return the response body and so are only really good for stuff where you're not interested in the HTTP response headers. The table functions return both the response header and the response message. In either case, as has been stated, the response body is a LOB of some kind and neither have anything, per se, to do with arrays. They are documented here
https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/bb5007a3-d52b-44e4-a2bc-4e56b454ff0f/page/5ac75e56-a02f-425d-91a5-ab0719e65416/attachment/a24f17a3-093c-40b5-ad7b-943bbaaf9eb6/media/accessing_web_services_using_ibm_db2_for_i_udfs_and_udtfs.pdf
As for the original request, I've knocked up this example based on the Google API, which can be found here
https://github.com/fathert/json-rpg-example/blob/master/google-geocode-example.sql. I haven't tested it with a valid response because my API key won't work without a billing account being set-up (at least I think that's why) so it might not be 100% correct as is, however, it does get and parse the error returned. I also realise it doesn't fulfill the "free API" requirement either, but hopefully it will give you a good starting point should you find a different API to use.
If you just wish to insert the data into another table you can do it with this one SQL statement by replacing the "create table" with the appropriate "insert" statement, you don't need to mess around with cursors (which can often be a code smell anyway).
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of John Yeung <gallium.arsenide@xxxxxxxxx>
Sent: 26 February 2020 20:02
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Get latitude/longitude from address for free
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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7Cd4236f4c0e424ee86d7f08d7baee8607%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637183405916923800&sdata=8eCL4O2vMDdntsUAq5kWoWxG6Ai%2FB7pLL8SWJltoogQ%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7Cd4236f4c0e424ee86d7f08d7baee8607%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637183405916933811&sdata=WIfwA2LYus%2BpmQquXyaKERsbZZPTUDYfm9WuYAMia0s%3D&reserved=0.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7Cd4236f4c0e424ee86d7f08d7baee8607%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637183405916933811&sdata=0XLeLb%2Bw%2BVPHeqoh4VVFSzwsDiHCi3ygeUtyeWy9%2FvQ%3D&reserved=0
midrange.com
