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" <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;