Normally an JSON object nested or not is a single string, so you can handle it as a (VarChar if shorter than 32K or CLOB - Character Large Object if longer than 32 k up to 2 GB) output parameter.
If you need to return multiple independent JSON objects, you may return them as a result set.
When creating your stored procedure you have to specify RESULT SET 1.
A result set can be returned by a stored procedure just by defining a Cursor and Opening it (and not closing it before ending the stored procedure)
------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------
Original Message:
Sent: Mon May 02, 2022 05:59 PM
From: Amy Vozza
Subject: Returning JSON Object from an SQL Stored Procedure
Will that work if I have multiple rows to return? Is there something else I'd need to do in that instance?
------------------------------
Amy Vozza
Original Message:
Sent: Mon May 02, 2022 04:04 PM
From: Amy Vozza
Subject: Returning JSON Object from an SQL Stored Procedure
I'll try that. Thanks!
------------------------------
Amy Vozza
Original Message:
Sent: Mon May 02, 2022 11:38 AM
From: Kent Milligan
Subject: Returning JSON Object from an SQL Stored Procedure
Can the JSON value just be returned as an Output parameter defined for the Stored Procedure?
CREATE PROCEDURE jsontest(OUT jsonout VARCHAR(128) CCSID 1208)
LANGUAGE SQL
BEGIN
VALUES (JSON_OBJECT('first' : 'John', 'last' : 'Doe')) INTO jsonout;
END;
------------------------------
Kent Milligan
Original Message:
Sent: Fri April 29, 2022 04:36 PM
From: Amy Vozza
Subject: Returning JSON Object from an SQL Stored Procedure
How do you code in an SQL stored procedure for the return of a JSON object? We are creating an SQL stored procedure to be called from IWS to return a data set in JSON. If we code in the stored procedure to create a JSON object, is there something similar to "WITH RETURN TO CLIENT FOR" to make that object available for return to the IWS caller or how does that work? Can someone point me to an example?
------------------------------
Amy Vozza
------------------------------
#SQL