Re: How to eliminate escape characters from JSON in IWS REST API?

["Nadir Amra" <amra@xxxxxxxxxx>]

Hi Kelly. 

Since you will be returning the results and because you have one output 
parameter, then when you deploy you must specify 

SQL result type:  Media resource

which will indicate to IWS to just leave the output alone. 

Did you do that when you deployed?



"WEB400" <web400-bounces@xxxxxxxxxxxxxxxxxx> wrote on 03/07/2021 02:41:39 
PM:

> From: Kelly Cookson <KCookson@xxxxxxxxxxxx>
> To: "Web Enabling the IBM i (AS/400 and iSeries)" 
<web400@xxxxxxxxxxxxxxxxxx>
> Date: 03/07/2021 02:41 PM
> Subject: [EXTERNAL] [WEB400] How to eliminate escape characters from
> JSON in IWS REST API?
> Sent by: "WEB400" <web400-bounces@xxxxxxxxxxxxxxxxxx>
>
> How do I get rid of escape characters in JSON output when calling a 
> stored procedure from an IWS REST API?
>
> 1. The problem is not caused by the stored procedure. When I call 
> the stored procedure from ACS Run SQL Scripts, the JSON contains no 
> escape characters.
>
>   call myLib.myStrdProc('GETDATES',' ',?);
>
> The JSON from Run SQL Scripts looks like this:
>
>   Output Parameter #3 (JSON_OUT_PARM) = {"MYFILE":[{"Begin 
Date":20200101,
>   "Close Date":20200126},{"Begin Date":20210101,"Close Date":20210131}]}
>
> 2. The problem occurs when I call the stored procedure from An IWS 
> REST API procedure. The JSON contains escape characters.
>
> The SQL call in the REST API procedure is the same:
>
>   call myLib.myStrdProc ('GETDATES',' ',?)
>
> The JSON looks like this:
>
>   "JSON_OUT_PARM": "{\"MYFILE\":[{\"Begin Date\":20200101,\"Close 
> Date\":20200126},
>   {\"Begin Date\":20210101,\"Close Date\":20210131}]}"
>
> The relevant code snippets from the stored procedure are:
>
> -- PROCEDURE DEFINITION
> CREATE OR REPLACE PROCEDURE MYSTRDPROC
>     (IN ACTION_PARM CHAR(30),
>      IN JSON_IN_PARM CLOB(1M) DEFAULT NULL,
>      INOUT JSON_OUT_PARM CLOB(1M) DEFAULT NULL)
>     RESULT SETS 1
>     LANGUAGE SQL
>     SET OPTION DBGVIEW = *SOURCE
>
>     WHEN ACTION_PARM = 'GETDATES' THEN
>         SELECT JSON_OBJECT('MYFILE':
>             JSON_ARRAYAGG(
>             JSON_OBJECT(
>             'Begin Date' : MYBEGDT,
>             'Close Date' : MYCLSDT
>             RETURNING CLOB(1M))))
>         INTO JSON_OUT_PARM
>         FROM MYLIB.MYFILE
>         WHERE (MYYEAR=2020 OR MYYEAR=2021)
>         AND MYPRD=1;