Birgitta has some procedures and functions that can help with that. I haven't had a chance to try them out yet, but it's high on my list of things to try out. https://github.com/BirgittaHauser/Write-to-IFS-with-SQL


On Thu, 2020-01-16 at 19:12 +0000, Stephen Piland wrote:

If I want to make sure this works in ACS SQL first, how do I save to IFS there? Since can't use host variables there..


Into Clob_file('/temp/sample.json') ?


-----Original Message-----

From: RPG400-L <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of Stephen Piland

Sent: Thursday, January 16, 2020 12:34 PM

To: RPG programming on IBM i <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


Awesome. Thanks. I'll give it a try.


-----Original Message-----

From: RPG400-L <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of Birgitta Hauser

Sent: Thursday, January 16, 2020 11:48 AM

To: 'RPG programming on IBM i' <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


Instead of Fetch first 10 Rows Only, you use LIMIT and OFSET in composition with a host variable.

But then you need to create multiple IFS files, with different names etc.


HostVar += 10;


with cte as (select * from dbtable Limit 10 Offset :HostVar)

select json_object('data' value json_arrayagg(json_object('id' value id, 'account' value cust_acct)))

into :OutFile

from SysIBM.SysDummy1;




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 <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of Charles Wilt

Sent: Donnerstag, 16. Januar 2020 18:09

To: RPG programming on IBM i <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: Re: JSON_OBJECT and Writing to IFS


cursor still isn't needed...


with cte as (select * from dbtable fetch first 10 rows only) select json_object('data' value json_arrayagg(json_object('id' value id, 'account' value cust_acct)))

from cte;


Charles




On Thu, Jan 16, 2020 at 10:04 AM Stephen Piland <

<mailto:Stephen@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>

Stephen@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

wrote:


Understood. However, the outside vendor that we would be creating

this for is requesting we limit the size of the each file created to

100 rows worth of data for example.


Maybe it isn't possible and I have to go a more 'manual' route.


-----Original Message-----

From: RPG400-L <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Birgitta Hauser

Sent: Thursday, January 16, 2020 10:59 AM

To: 'RPG programming on IBM i' <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


You do not need to define a cursor, because you'll build the complete

JSON document within a SQL Statement (which might be a little more complex).

So you will only get a single row! No need to limit the number of rows.


A SELECT ... INTO or VALUES ... INTO will do the job:


DCL-S Outfile SQLType(CLOB_File) CCSID(1208);


Clear OutFile;

Outfile_Name = '/temp/accounts_{timestamp}.json'

Outfile_NL = %len(%trimr(Outfile_Name))

Outfile_FO = SQFCRT;


Exec SQL Values(SELECTStatementThatCreatesTheJSONData) into :OutFile;

If SQLCODE < *Zeros;

//Handle Error;

Else;

//Success

End;


Just a tip. Build your SELECT Statement first within ACS - Run SQL Scripts.

If the JSON document is built correctly, copy the SELECT statement

into your program.



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 <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Stephen Piland

Sent: Donnerstag, 16. Januar 2020 16:46

To: RPG programming on IBM i <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


I was able to build a JSON document with multiple objects (rows from

DB) using json_arrayagg within a json_object and that worked fine.


I was wondering if anyone has ever tried to limit the number of rows

from DB table per IFS created in a SQL cursor like loop?


Pseudo code..

DCL-S OutFile SQLType(CLOB_FILE) CCSID(1208);


Exec sql Declare c1 cursor for json_object('data' value

json_arrayagg(json_object('id' value id, 'account' value cust_acct)))

from DB_Table offset 10 rows; Exec sql open c1;


Dow sqlcod = 0;

Outfile_Name = '/temp/accounts_{timestamp}.json'

Outfile_NL = %len(%trimr(Outfile_Name))

Outfile_FO = SQFCRT;

Exec sql fetch c1 for 10 rows into :Outfile; Enddo;


Exec sql close c1;


I know this isn't 100% 'correct' but hopefully someone gets a clue

what I'm trying to do.


Thanks!


-----Original Message-----

From: RPG400-L <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Birgitta Hauser

Sent: Wednesday, January 8, 2020 9:53 AM

To: 'RPG programming on IBM i' <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


Select ... INTO can only handle a single return value/row!


In this way you should build the complete JSON document within a

single SELECT statement and write the result into the IFS.

For complex queries use common table expressions (CTE).

In the first (few) CTEs generate the raw data that must be included in

the JSON document.

In the next CTEs build successive the JSON data, starting with the

lowest level and then merging the result of these CTEs in the next

CTEs ... until the complete JSON document is built.


... on the other side it is possible to append data to an IFS table.

You have to change the _FO value to SQLAPP (=Append). After having

changed the File Operation, new data is added to the end of the IFS file.


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 <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Stephen Piland

Sent: Mittwoch, 8. Januar 2020 16:06

To: RPG programming on IBM i <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


Great! Thanks! That did work. It only allows me to fetch the first

row to dump into the IFS file, which makes sense to me.


How could I include multiple JSON messages / 'rows'? Someone

approached us about doing ND JSON, which might be what this would be.

I'm not sure to be honest.


Any thoughts?


-----Original Message-----

From: RPG400-L <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Birgitta Hauser

Sent: Tuesday, January 7, 2020 11:23 PM

To: 'RPG programming on IBM i' <

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx



Subject: RE: JSON_OBJECT and Writing to IFS


Outfile must be a CLOB_FILE CCSID 1208.


DCL-S OutFle SQLType(CLOB_FILE) CCSID(1208);


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 <

<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>

rpg400-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Stephen Piland

Sent: Mittwoch, 8. Januar 2020 01:46

To:

<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>

rpg400-l@xxxxxxxxxxxxxxxxxx


Subject: JSON_OBJECT and Writing to IFS


Anyone have any luck writing the encoded JSON out to the IFS like we

have done with XML using sqltype(xml_clob_File) and Select-ing into

that variable? Do I need to convert / cast the JSON_OBJECT into a

CLOB in the SQL? Thanks!


Quick Test...

dcl-s Outfile sqltype(xml_clob_File) ; exec sql Set Option

COMMIT=*NONE,CLOSQLCSR=*ENDMOD,DATFMT=*ISO;


// Prep JSON File

Outfile_Name = %trim('/temp/test.json'); Outfile_NL =

%len(%trimr(Outfile_Name)); Outfile_FO = SQFCRT;


exec sql Select json_object ('upc' value fldupc, 'price' value price)

Into :Outfile From PRICING;




--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com



--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com


--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com



--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com


--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com



--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com


--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe,

or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related

questions.


Help support midrange.com by shopping at amazon.com with our affiliate

link:

<https://amazon.midrange.com>

https://amazon.midrange.com



--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related questions.


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

<https://amazon.midrange.com>

https://amazon.midrange.com



--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related questions.


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

<https://amazon.midrange.com>

https://amazon.midrange.com


--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email:

<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

RPG400-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,

visit:

<https://lists.midrange.com/mailman/listinfo/rpg400-l>

https://lists.midrange.com/mailman/listinfo/rpg400-l


or email:

<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

RPG400-L-request@xxxxxxxxxxxxxxxxxx


Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>

https://archive.midrange.com/rpg400-l

.


Please contact

<mailto:support@xxxxxxxxxxxx>

support@xxxxxxxxxxxx

for any subscription related questions.


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

<https://amazon.midrange.com>

https://amazon.midrange.com




[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com



CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].