RE: sqlrpgle Get_CLOB_from_file(:url) arrays - getting mixed signals

This seems not to be a problem of GET_CLOB_FROM_FILE, but a problem of our JSON_TABLE Statemen.
What exactly do you want to get returned?
A table like this:
STATE CITY
Alaska Anchorage
Arkansas Stumptoe
Florida Jacksonville
Florida Tampa
Florida Orlando

If so, the SQL Statement must look as follows:
Select *

From JSON_TABLE('{"statecount":3,"states":[

{"state":"Alaska","citycount":1,"cities":["Anchorage","","",""]},
{"state":"Arkansas","citycount":1,"cities":["Stumptoe","","",""]},
{"state":"Florida","citycount":3,"cities":["Jacksonville","Tampa","Orlando",""]}
]}',
'$.states[*]'
Columns(State VarChar(30) path '$.state',
Nested '$.cities[*]'
Columns(City VarChar(30) path '$'))) x
Where City > '';

I added a where condition because several array elements are empty.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Booth Martin
Sent: Freitag, 29. November 2019 05:26
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sqlrpgle Get_CLOB_from_file(:url) arrays - getting mixed signals

{"statecount":3,"states":[
{"state":"Alaska","citycount":1,"cities":["Anchorage","","",""]},
{"state":"Arkansas","citycount":1,"cities":["Stumptoe","","",""]},
{"state":"Florida","citycount":3,"cities":["Jacksonville","Tampa","Orlando",""]}
]}

I have tried every permutation I can think of and the best I have done is to get the single entry of 3, which I believe is the "statecount"

I am using SQLRPGLE with commit(*CHG).

On 11/28/2019 2:18 AM, Birgitta Hauser wrote:

What do you want to do? Access the IFS file and directly consume it with the JSON_TABLE Table Function?

If so, you can specify the GET_CLOB_FROM_FILE Command directly in the JSON_TABLE Function, i.e. you declare a cursor and loop through the result as usual!
Something like this:
Declare Cursor X for
Select x.*
from JSON_TABLE(Get_CLOB_From_File('/home/YourDirectory/YourJSONFile.json'),
'$.....'
Columns(....)) as x;

... as an aside: GET_CLOB_FROM_FILE returns a LOB Locator ... and LOB Locators must be performed under commitment control!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Booth Martin
Sent: Donnerstag, 28. November 2019 07:18
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: sqlrpgle Get_CLOB_from_file(:url) arrays - getting mixed
signals

I have a simple .json file in the IFS:

* a counter for the number of states,
* and an array of states,
o and each state has a counter of the number of cities,
o and an array of cities.

4 fields in all.

Can that data be brought into an SQLRPGLE program using Get_CLOB_from_file()? I think I am seeing conflicting yes/no answers from people who know way more than I.

Either version 7.2 or version 7.3, if that is significant.


--
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@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
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@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com