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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Stephen
Piland
Sent: Donnerstag, 16. Januar 2020 16:46
To: RPG programming on IBM i <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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta
Hauser
Sent: Wednesday, January 8, 2020 9:53 AM
To: 'RPG programming on IBM i' <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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Stephen
Piland
Sent: Mittwoch, 8. Januar 2020 16:06
To: RPG programming on IBM i <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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta
Hauser
Sent: Tuesday, January 7, 2020 11:23 PM
To: 'RPG programming on IBM i' <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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Stephen
Piland
Sent: Mittwoch, 8. Januar 2020 01:46
To: 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: 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
--
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
--
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
midrange.com
