× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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;

Thanks,

Kelly Cookson
Senior Software Engineer II
Dot Foods, Inc.
1-217-773-4486 ext. 12676
www.dotfoods.com<http://www.dotfoods.com>


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2023 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.