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

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