Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power


#Servers
#Programminglanguages
 View Only

  • 1.  Returning JSON Object from an SQL Stored Procedure

    Posted 04/29/22 04:37 PM
    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


  • 2.  RE: Returning JSON Object from an SQL Stored Procedure

    Posted 05/02/22 11:39 AM
    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
    ------------------------------



  • 3.  RE: Returning JSON Object from an SQL Stored Procedure

    Posted 05/02/22 04:04 PM
    I'll try that.  Thanks!

    ------------------------------
    Amy Vozza
    ------------------------------

    Original Message


  • 4.  RE: Returning JSON Object from an SQL Stored Procedure

    Posted 05/02/22 06:00 PM
    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


  • 5.  RE: Returning JSON Object from an SQL Stored Procedure

    Posted 05/02/22 11:41 PM
    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


  • 6.  RE: Returning JSON Object from an SQL Stored Procedure

    Posted 05/04/22 02:18 PM
    Thanks for the help.  I got this to work returning the result sets.

    ------------------------------
    Amy Vozza
    ------------------------------

    Original Message


Related Content