Re: JSON output in CGIPGM - Wrong type or CCSID?

[Pete Helgren <pete@xxxxxxxxxx>]

And, here is the code.  Pretty simple but I must be missing something:

**free
       ctl-opt dftactgrp(*no) option(*nodebugio:*nounref)
               bnddir('QDFT');
       dcl-pr   Wrtout extproc('QtmhWrStout');
         dtavar char(32365) const options(*varsize);
         dtavarlen int(10) const;
         errorcode  char(8000) options(*varsize);
       end-pr;

       dcl-c CRLF x'0d25';

       dcl-s  header   varchar(1000);
       dcl-s  json     varchar(2000) CCSID(1208);
       dcl-s  sqlstmt  varchar(2048) Inz;

       dcl-ds err;
          byteProv    int(10) inz(0);
          byteAvail   int(10);
       end-ds;

       dcl-s q char(1) inz('''');

       // form the header
       header = 'Status: 200 OK' + CRLF +
             'Content-type: application/json' + CRLF + CRLF;

       // Generate the JSON from SQL

       sqlstmt =  'values (json_array((select json_object 
('+q+'firstname'+q+
         ':empfname,'+q+'lastname'+q+':emplname,'+q+'address'+q+
          ':empaddress) from employee.employee) format json))';

        exec sql prepare s1 from  :sqlstmt;

        exec sql  declare c1 cursor for S1;

        exec sql  open c1;

        exec sql fetch c1 into :json;

      // dump the data out
      //
        Wrtout( header: %len(header) : err);
        Wrtout( json: %len(json) : err);

        exec sql close c1;

       *inlr = *on;

Nothing special in the HTTP server config.  If I comment out the second 
write to stdout and only output the header, no problem. It's just the 
output into the :json variable that it is unhappy about.

I did discover that the system default CCSID was set to 65535 and I have 
now changed it to 37.  Don't know if any services need to be restarted 
to make it take effect (underlying JVM job?)  Don't know.  Apache job 
has this:

Coded character set identifier  . . . . . . . . . :   37
Default coded character set identifier  . . . . . :   37

Pete Helgren
www.petesworkshop.com
GIAC Secure Software Programmer-Java
AWS Certified Cloud Practitioner
Microsoft Certified: Azure Fundamentals
Twitter - Sys_i_Geek  IBM_i_Geek

On 5/23/2021 8:20 AM, Pete Helgren wrote:
> Thanks Birgitta.  The result is tiny...less than a thousand 
> characters. I did switch the CCSID to 1208 but I am seeing the same 
> result so I am not exactly sure where the hangup is.  I have a similar 
> example using Scotts YAJL port that will output correctly so he must 
> be doing some magic to coerce the output to the correct format.  I'll 
> post up the code so you can see what I am doing.  It *seems* simple 
> and straightforward but I must be missing something....
>
> Thanks also for the info on the SQLType CLOB....I saw a bunch of 
> examples using that type but there was no explanation about where the 
> _Data and _Len variables were coming from.  You filled in that missing 
> info.
>
> Pete Helgren
> www.petesworkshop.com
> GIAC Secure Software Programmer-Java
> AWS Certified Cloud Practitioner
> Microsoft Certified: Azure Fundamentals
> Twitter - Sys_i_Geek  IBM_i_Geek
>
> On 5/22/2021 7:33 AM, Birgitta Hauser wrote:
> > Hi Pete,
> >
> > it really depends how big your data (or your variable) is.
> > If it is shorter then 32K you can write it into a VarChar-Variable 
> > CCSID 1208 (UTF-8)
> > If it is longer then 32K you have to write it into a CLOB-Variable 
> > CCSID 1208.
> > When using CLOB-Variables, be aware that the SQL precompiler converts 
> > the variable into a Data Structure with the Sub-Fields:
> > 1. Name of the Clob Variable with suffix _LEN (Definition UNS(10))
> > 2. Name of the CLOB Variable with the suffix _DATA (Definition 
> > CHAR(Length of your Variable).
> > With RPG you have to use the data structure subfields and with SQL 
> > you use the data structure name.
> >
> > Here an example:
> >         DCL-S GblJSONData     SQLType(CLOB: 1000000) CCSID(1208);
> >
> >         //More RPG Code
> >         //Generate and output the JSON into the CLOB-Variable
> >             exec sql
> >             With x as (....)
> >             Select JSON_Object(...)
> >                     into :GblJSONData
> >                From x;
> >
> >             If SQLCODE < *Zeros;
> >                //Handle SQL Error
> >             EndIf;
> >
> >             //Write the data to the Web
> >             WrtStdout(%Addr(GblJSONData_Data): GblJSONData_Len: 
> > DsApierr);
> >             On-Error;
> >             EndMon;
> >
> > 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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of 
> > Pete Helgren
> > Sent: Samstag, 22. Mai 2021 00:41
> > To: Midrange Systems Technical Discussion 
> > <midrange-l@xxxxxxxxxxxxxxxxxx>
> > Subject: JSON output in CGIPGM - Wrong type or CCSID?
> >
> > I have an sqlrpgle program that creates JSON output using JSON_ARRAY 
> > (among others) and outputs JSON (actually, SQL is pretty cool along 
> > these lines).  Running just the SQL statement interactively, I get 
> > just what I expect, nicely formatted JSON. When I run it within the 
> > RPG program, I have some issues.  It really boils down to 
> > understanding a couple of things:
> >
> > If the goal is to output the SQL into a variable and then dump that 
> > variable out using Standard Out (QtmhWrStout) what should the 
> > variable type be?  Originally I tried varchar but I got an error when 
> > it was output due to "reason code 12:   -- The CCSIDs (Coded 
> > Character Set
> > Identifiers) of the operands cannot be made compatible."   So either 
> > I chose the wrong data type or I need to set a CCSID.  I tripped 
> > across a post from a few years ago that using a DBCLOB or a CLOB type 
> > an CCSID of 1200.  I do get output now (no error) but it is just a 
> > string of '@@@@@@@@@@@@@@@@...'s that tells me a have CCSID issue.
> >
> > Tried a few other ideas, still get the wrong output.  So the question
> > is:  If the plan is to output through a CGI pgm using QtmhWrStout how 
> > should I define the variable and should it be assigned a CCSID?
> >
> > --
> > Pete Helgren
> > www.petesworkshop.com
> > GIAC Secure Software Programmer-Java
> > AWS Certified Cloud Practitioner
> > Microsoft Certified: Azure Fundamentals
> > Twitter - Sys_i_Geek  IBM_i_Geek
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> > mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives at 
> > https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription 
> > related questions.
> >
> > Help support midrange.com by shopping at amazon.com with our 
> > affiliate link: https://amazon.midrange.com