Re: JSON_OBJECT and Writing to IFS

["Art Tostaine, Jr." <atostaine@xxxxxxxxx>]

I tried this  and am getting two errors:

  DCL-S  OutFile   SQLType(CLOB_FILE) ccSid(1208) ;
  exec sql set option commit=*none, naming=*sys;
  Outfile_Name = %trim('/tmp/mytest.json');
  Outfile_NL = %len(%trimr(Outfile_Name));
  Outfile_FO = SQFCRT;.

Message ID . . . . . . :   CPD4318       Severity . . . . . . . :   40

Message type . . . . . :   Diagnostic

Date sent  . . . . . . :   01/08/20      Time sent  . . . . . . :
11:34:11


Message . . . . :   Derived operands not valid for operator JSON_OBJECT.

  Reason code 12.

Cause . . . . . :   Either the derived operands are not compatible with
each
  other or the operands are not compatible with operator JSON_OBJECT.  The

  type of incompatibility is indicated by reason code 12.  The reason codes

  and their meanings follow:
  12 -- The CCSIDs (Coded Character Set Identifiers) of the operands cannot
be made compatible.

Even though my job CCSID and system CCSID is 37, I'm also getting this
 Message ID . . . . . . :   SQL0332       Severity . . . . . . . :   30

 Message type . . . . . :   Diagnostic

 Date sent  . . . . . . :   01/08/20      Time sent  . . . . . . :
11:34:11


 Message . . . . :   Character conversion between CCSID 65535 and CCSID
1208
   not valid.

 Cause . . . . . :   Character or graphic conversion has been attempted for

   data that is not compatible. There is no conversion defined between
CCSID
   65535 and CCSID 1208.



On Wed, Jan 8, 2020 at 10:50 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> 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