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

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" <[email protected]> wrote on 03/07/2021 02:41:39 
PM:

> From: Kelly Cookson <KCookson-EAwJ3jYHSpVWk0Htik3J/[email protected]>
> To: "Web Enabling the IBM i (AS/400 and iSeries)" 
<[email protected]>
> Date: 03/07/2021 02:41 PM
> Subject: [EXTERNAL] [WEB400] How to eliminate escape characters from
> JSON in IWS REST API?
> Sent by: "WEB400" <[email protected]>
> 
> 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;
> 



-- 
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/web400
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/web400.