RE: JSON un-named arrays -- RPG400-L

Exactly, you have to run your SQL-Statement under commitment control!
LOB-Locators (are kind of pointers and must be freed) ... and the get freed with COMMIT or ROLLBACK!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Tuesday, 18 July 2023 15:06
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: JSON un-named arrays

Vern,

That's what I tried... I get an SQL Error: SQL State 42926, Sql0443 LOB and XML locators are not allowed with commit(*none).

I can't find how to overcome that.

Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of VERNON HAMBERG Owner via RPG400-L
Sent: Monday, July 17, 2023 6:17 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>; rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: RE: JSON un-named arrays

Hi Greg

As for how to do this with stream file, if you mean how do you process a JSON file in the IFS, there is an SQL function that can pull the JSON file in to be processed - it's used where Daniel has "insert your json here" - here's an example - JSON_TABLE(Get_CLOB_From_File(:xmlFilePath),

HTH
Vern

On Mon, 17 Jul, 2023 at 4:05 PM, Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

To: rpg programming on ibm i

Thanks Daniel!

With this API, the Data portion is only dynamic based on my call to the REST web service (I passed the value in the "fields" as a parameter to the web service).

I'm willing to try something new (and I like Run SQL Scripts)...

Just curious how I would use this with a stream file on the IFS? I like to save my latest response on the IFS.

Thx,
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Daniel Gross
Sent: Monday, July 17, 2023 4:19 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>>
Subject: Re: JSON un-named arrays

Hi Greg,

I don't know, if it helps, but you can walk the whole JSON document with SQL without problems.

select *
from json_table(
'{...insert.your.json.here...}',
'lax $'
columns (
records integer path '$.records',
fields varchar(1000) path '$.fields',
nested path 'lax $.data[*]'
columns (
trano integer path '$[0]',
ref varchar(50) path '$[1]',
duedate decimal(8, 0) path '$[2]',
name varchar(50) path '$[3]',
amount decimal(10, 2) path '$[4]'
)
)
) as json_doc;

As you can see, the named parts are referenced by ".name", whereas the "unnamed" JSON elements have no named reference, but only the "[index]". The nested path reference is the JSON equivalent of an table JOIN.

Of course - if the "data" part of the JSON is dynamic, and you have to detect the columns be the "fields" element, it can get a but complicated.

But even then, you can first split the "fields" list into its elements, an then construct a dynamic SQL to extract the "data" parts.

So from my POV - SQL is often the easier solution - especially because you can test it with Run SQL Scripts until everything is "perfect".

HTH and kind regards
Daniel

Am 17.07.2023<http://17.07.2023> um 17:31 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>:

Typically, I use DATA-INTO and the YAJLINTO parser... But I'm not sure if that's possible with the JSON below. The array "data" contains an un-named array of values.

Any suggestions on how handle reading this?
Do I need to use the YAJL "tree-based" method?

{
"records": 4,
"fields": "trano,ref,duedate,name,amount",
"data": [
[
1,
"40061433",
20230719,
"Mudpie",
187.2<http://187.2>
],
[
2,
"40061349",
20230719,
"kiddywampus",
156.21<http://156.21>
],
[
3,
"40062677",
20230721,
"Chase",
70.8<http://70.8>
],
[
4,
"40061703",
20230723,
"Stamford Toys",
110.7<http://110.7>
]
]
}
[Logo]<https://www.totalbizfulfillment.com/><https://www.totalbizfulfillment.com/>> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@totalbizfulfillment.c
om><mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@totalbizfu
lfillment.com>>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><http:/
/www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>>

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.