Re: json_table(httpgetclob, nested array -- WEB400

It seems better to me to use the platform rather than rely on a third party library, unless there's something that can't be done natively, but I think SQL has pretty much got everything covered these days.

________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of B Stone <bvstone@xxxxxxxxx>
Sent: 18 November 2019 17:38
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] json_table(httpgetclob, nested array

Booth,

have you even tried YAJL, or are you just trying to learn something new
with SQL here?

On Mon, Nov 18, 2019 at 10:25 AM Booth Martin <booth@xxxxxxxxxxxx> wrote:

At this point I am thinking it is my not understanding SQLRPGLE. The
code, used in an SQLRPGLE program, returns SQLCODE error (I have no idea
what this message means.):

Message ID . . . . . . : SQL0305
Date sent . . . . . . : 11/18/19 Time sent . . . . . . :
11:09:52

Message . . . . : Indicator variable required.

Cause . . . . . : A FETCH, embedded SELECT, CALL, GET DESCRIPTOR, or a
SET
or VALUES INTO statement has resulted in a null value, but an indicator
variable was not specified for host variable WSEAS. The relative
position
of the host variable in the INTO clause or parameter list is 3. If
the host
variable name is *N, a descriptor area was specified.
If this error occurs on a GET DESCRIPTOR statement, the null value is
being returned but the INDICATOR item was not specified on the GET
DESCRIPTOR statement.

On 11/18/2019 2:54 AM, Tim Fathers wrote:

Hi Booth,

This is the syntax you need:

select *

from json_table(
'{"wData_length":14,"SeasOceans":["Adriatic Sea","Arctic

Ocean","Atlantic Ocean","Bay of Bengal","Caribbean Sea"]}',

'lax $'
columns(
DATA_LEN varchar(20) path '$.wData_length',
nested path '$.SeasOceans[*]'
columns(
SEA varchar(20) path '$'
)
)
) as U

...which gives...

DATA_LEN SEA
-------- --------------
14 Adriatic Sea
14 Arctic Ocean
14 Atlantic Ocean
14 Bay of Bengal
14 Caribbean Sea

As others have said, if you want to process the data row-by-row, you'll

need to read it with a cursor but they are usually best avoided if you can,
so if you need this data in a file just wrap the whole thing in an insert.
Assuming the "wData_length" represents the length of the array then I think
it's superfluous anyway, as you will only get back one row per array
element so you don't need to care how many elements it's says there are..

Tim.

________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Booth

Martin <booth@xxxxxxxxxxxx>

Sent: 18 November 2019 01:32
To: Web Enabling the IBM i (AS/400 and iSeries) <

web400@xxxxxxxxxxxxxxxxxx>

Subject: Re: [WEB400] json_table(httpgetclob, nested array

exec sql select * into :wCount, :wSeas
from json_table(systools.httpgetclob(:wURL, ''), '$'
columns(
"wData_length" dec(3,0),
SeasOceans varchar(20) PATH 'lax $.SeasOceans[4]' ));

also works, in that it gives me the count and element[4].

For collecting an array, "nested" seems to be the correct word, probably
also needing ''lax $'. I suspect the issue involves either the SQLRPGLE
usage, or more likely, programmer error. I did try to loop. That gave
me element 1 repeatedly; I could not find a way to use a variable in
place of the $.SeasOcceans[4] and using an * instead of the 4 failed.

..On 11/17/2019 6:02 PM, Jon Paris wrote:

It is going to return a row for each element Booth so you'll probably

need to use a cursor.

Does it work without nested?

--
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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C5cca7ac7b88d4aa1b4b908d76c45c2ee%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096919181306050&sdata=vfgEcjDK%2BEJ5T0JjgQpG6TUwziEJwrnsQvQfr4S33cc%3D&reserved=0

or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at

https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C5cca7ac7b88d4aa1b4b908d76c45c2ee%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096919181306050&sdata=hzvq32VDOQ2%2BXGEQxOlqwWPebaJy8baEtm16RBJ%2FxaQ%3D&reserved=0
.

--
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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C5cca7ac7b88d4aa1b4b908d76c45c2ee%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096919181306050&sdata=vfgEcjDK%2BEJ5T0JjgQpG6TUwziEJwrnsQvQfr4S33cc%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C5cca7ac7b88d4aa1b4b908d76c45c2ee%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096919181306050&sdata=hzvq32VDOQ2%2BXGEQxOlqwWPebaJy8baEtm16RBJ%2FxaQ%3D&reserved=0.

--
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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C5cca7ac7b88d4aa1b4b908d76c45c2ee%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096919181306050&sdata=vfgEcjDK%2BEJ5T0JjgQpG6TUwziEJwrnsQvQfr4S33cc%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C5cca7ac7b88d4aa1b4b908d76c45c2ee%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096919181306050&sdata=hzvq32VDOQ2%2BXGEQxOlqwWPebaJy8baEtm16RBJ%2FxaQ%3D&reserved=0.