Is there any example to show how to call stored procedures?

[Louis-7]

Tried many times to call stored procedures on IBM i.

I can send SQL statements to XMLSERVICE and it's working perfectly. Does anyone have a working example to show what format of HTTP request should I send to IBM i ? (ctl, ipc, xmlin...)

[jimoibm]

@Louis-7 You can call xmlservice like this:
IPC: '*NA' or IPC key path
CTL: '*here' for stateless or other flags in plugipc.h
XMLIN:
<?xml version=''1.0''?> <myscript> <pgm name=''XMLTEST'' lib=''JIMOXML'' error=''fast''> <parm io=''out''> <data type=''10A''></data> </parm> </pgm> </myscript>

[Louis-7]

Hey @jimoibm, so I suppose to create a PGM called XMLTEST and use it to call the stored procedure?

I tried this as well:
<?xml version="1.0"?> <xmlservice><myscript><sql><query stmt="main" error="fast"><![CDATA[call MYLIB.SPTEST()]]></query></sql></myscript></xmlservice>

I execute a query in my SP and open a cursor. Xmlservice always returns me error. The response looks like:

...
<error>
    <sqlcode>466</sqlcode>
    <sqlstate>0100C</sqlstate>
    <errnoxml>1500001</errnoxml>
    <xmlerrmsg>
        <![CDATA[SQL fail]]>
    </xmlerrmsg>
    <xmlhint>
        <![CDATA[0100C:466:1 result sets are available from procedure XXXX]]>
    </xmlhint>
</error>
<error>
    <sqlcode>466</sqlcode>
    <sqlstate>0100C</sqlstate>
    <errnoxml>1500001</errnoxml>
    <xmlerrmsg>
        <![CDATA[SQL fail]]>
    </xmlerrmsg>
    <xmlhint>
        <![CDATA[0100C:466:1 result sets are available from procedure XXXX]]>
    </xmlhint>
</error>
<jobinfo>
    <jobipc></jobipc>
    <jobipcskey>FFFFFFFF</jobipcskey>
    <jobname>QSQSRVR</jobname>
    <jobuser>QUSER</jobuser>
    <jobnbr>742573</jobnbr>
    <jobsts>*ACTIVE</jobsts>
    <curuser>XXXX</curuser>
    <ccsid>37</ccsid>
    <dftccsid>37</dftccsid>
    <paseccsid>0</paseccsid>
    <langid>ENU</langid>
    <cntryid>US</cntryid>
    <sbsname>QSYSWRK</sbsname>
    <sbslib>QSYS</sbslib>
    <curlib></curlib>
    <syslibl>QSYS QSYS2 QHLPSYS QUSRSYS</syslibl>
    <usrlibl>TECHLIB QGPL QTEMP</usrlibl>
    <jobcpffind>see log scan, not error list</jobcpffind>
</jobinfo>
...

I think your solution and mine are both working, but how can I get the return value from a SP? Can I get the return value in PGM?

[jimoibm]

@Louis-7 Yes and no. You need to create your own pgm but being called by xmlservice. Your web application talks with xmlservice in xml format. xmlservice calls your own pgm.

[Louis-7]

I see, but there is no way to get return value from SP?

[jimoibm]

@Louis-7 I am not sure I understand your question. xmlservice is just a broker that passes the dataIn and dataOut between your web app and your background program.. guess the return value you meant was output data? in and out data are both passed with address... they can be passed in/out...

[Louis-7]

Yes, output data. It should be returned in the response body. For example, I send a db query request to xmlservice, it should return the query result to me. I'd like to get the stored procedure executed result in xmlservice output data.

Maybe this question is not related to xmlservice itself but IBM i.

[jimoibm]

Can you show me more details on what you did?

[Louis-7]

Sure. This is my SP for the test, just do a simple query ->

CREATE PROCEDURE MYLIB.SPTEST (  )
	RESULT SETS 1
	LANGUAGE SQL
	SPECIFIC MYLIB.DBSPTEST

P1 : BEGIN
	-- Declare cursor
    DECLARE CURSOR1 CURSOR WITH RETURN TO CALLER 
    FOR
	SELECT * FROM A_TABLE;

	-- Cursor left open for client application
	OPEN CURSOR1;
END P1

Here is what I sent to xmlservice (with POST method) ->

ctl:*here *cdata(on)
db2:*LOCAL
pwd: my_password
uid: my_user
xmlin:<?xml version="1.0"?>↵<xmlservice><myscript><sql><query stmt="main" error="fast"><![CDATA[call MYLIB.SPTEST()]]></query></sql></myscript></xmlservice>

I expect this SP return the query result in xmlservice response body. But the actual result is it returns me the errors (I post the errors in the previous comment). The reason I said it may not the xmlservice problem is I call this SP on green screen (with 'strsql') and the result are same 1 result sets are available from procedure SPTEST in MYLIB. I'd like to know how can I get that query result?

[jimoibm]

OK.. You are not using xmlservice to call your program but SQL to DB2. Yes the result should be in the xml body returned. Maybe you can try to verify your SQL statements outside xmlservice or check the joblog that has been indicated in the error body. If you figured out any error pointing to xmlservice, please let me know.

[Louis-7]

I tried to call SP in Data Studio and I can get the query result. But on green screen - NO. I'm sure my SP can execute successfully. Yes, I guess I should check the joblog. Thanks.

[Louis-7]

@jimoibm , I found another way to call the stored procedure on IBM i. I can execute the command on the green screen but it can't be executed with xmlservice.

IBM i command:
RUNSQL SQL('call myctl.mysp(''parm1'',''parm2'')')

The xml I sent:

<?xml version="1.0"?>
<xmlservice><cmd exec='rexx'>RUNSQL SQL('call myctl.mysp(''parm1'',''parm2'')')</cmd></xmlservice>

Part of the return message.

 <joblog job='QSQSRVR' user='QUSER' nbr='869325'>
            <![CDATA[Cause . . . . . :   A delimiter is missing between two values or a delimiter
                                       that is not valid was found. Recovery  . . . :   Change the character that
                                       is not valid or if a delimiter is missing insert one. More information on
                                       delimiters can be found in the Information Center.
CPD0020    Diagnostic              30   05/09/19  23:57:22.852066  QCANPARS     QSYS        094F     QC2SYS      QSYS        *STMT
                                     From user . . . . . . . . . :   ZZLIU
                                     To module . . . . . . . . . :   QC2SYS
                                     To procedure  . . . . . . . :   system
                                     Statement . . . . . . . . . :   13
                                     Message . . . . :   Character 'p' not valid following string '''        '.
                                     Cause . . . . . :   A delimiter is missing between two values or a delimiter
                                       that is not valid was found. Recovery  . . . :   Change the character that
                                       is not valid or if a delimiter is missing insert one. More information on
                                       delimiters can be found in the Information Center.
CPD0020    Diagnostic              30   05/09/19  23:57:22.852074  QCANPARS     QSYS        094F     QC2SYS      QSYS        *STMT
                                     From user . . . . . . . . . :   ZZLIU
                                     To module . . . . . . . . . :   QC2SYS
                                     To procedure  . . . . . . . :   system
                                     Statement . . . . . . . . . :   13
                                     Message . . . . :   Character ''' not valid following string ')         '.
                                     Cause . . . . . :   A delimiter is missing between two values or a delimiter
                                       that is not valid was found. Recovery  . . . :   Change the character that
                                       is not valid or if a delimiter is missing insert one. More information on
                                       delimiters can be found in the Information Center.
CPF0001    Escape                  30   05/09/19  23:57:22.852245  QCADRV2      QSYS        053F     QCMDEXC     QSYS        012F
                                     From user . . . . . . . . . :   ZZLIU
                                     Message . . . . :   Error found on *N command.
                                     Cause . . . . . :   The system detected errors in the command. Recovery  . . .
                                       :   See the previously listed messages in the job log. Correct the errors
                                       and then try the command again.  *N instead of a command name means that the
                                       name had not been determined before the error was found.]]>
        </joblog>

I'm very confused with these error messages, shouldn't I wrap the parameters with the single quotation marks?

[richardschoen]

Looks like you are running SQL using a REXX command ? What are you expecting to come back from that ? I don't believe you will get any results back.

[richardschoen]

Instead of calling a SP you should be able to call your program directly via SQL using the same call format and if it returns a resultset instead of parms you can consume your results just like an SQL query. Not perfect but I just had to do something similar with the Mono .Net data access.

[Louis-7]

@richardschoen , I'd like to execute the stored procedure whether it returns the result or not. What I'm facing is I haven't found a way to execute my SP successfully on IBM i.

I tried to call SP via SQL (my second reply in this issue #35 (comment)).

As far as I know, you can't call SP directly by using SQL on IBM i. If it's possible could you share some example to show the xml you send to IBM i?

[richardschoen]

This works for me on an RPG program that is NOT registered as a stored procedure that returns a resultset. Found out recently you don't need to register a SPROC with DB2 if it returns a resultset which my sample RPG does. The program call takes in 2 parameters. I think you may have to pass parms in text format, but that's a small price to pay to have the equivalent of a straight up program call instead of a stored procedure without registering with the database. Feel just like calling a SPROC though.

I am using XMLSERVICE via HTTP calls from .Net.

db2=*LOCAL&uid=USER01&pwd=PASS01&ipc=/tmp/xmlservicei&ctl=*sbmjob *cdata&xmlin=<?xml version='1.0'?>
<?xml-stylesheet type='text/xsl' href='/DemoXslt.xsl'?>
<script>
<sql>
<options options='noauto' autocommit='off'/>
</sql>
<sql>
<connect conn='myconn' options='noauto'/>
</sql>
<sql>
<prepare conn='myconn'><![CDATA[call monoi.mnhello01 ('aaaaaaaaaa','bbbbbbbbbb')]]></prepare>
</sql>
<sql>
<execute/>
</sql>
<sql>
<describe desc='col'/>
</sql>
<sql>
<fetch block='all' desc='on'/>
</sql>
<sql>
<free/>
</sql>
</script>&xmlout=500000

[richardschoen]

Sample Minimal SQLRPGLE

      *
      * This program is a template for performing work and then returning
      * parms in a single record resultset with multiple field values.
      * This would also work to return multiple result records as well.
      *
      * Create a single element DS for returning info from the call
     D RtnResults      DS                  Dim(1) Qualified
     D   RtnCode                      1A
     D   RtnParm1                   100A   Varying
     D   RtnParm2                   100A   Varying
      *
     D RowCount        S              4S 0
      *
     D ErrorNull       ds                  qualified
     D    BytesProv                  10i 0 inz(0)
     D    BytesAvail                 10i 0 inz(0)
      *
     D INPARM1         S             10A
     D INPARM2         S             10A
      *
      * Parameter list
      *
     C     *Entry        Plist
     C                   Parm                    INPARM1
     C                   Parm                    INPARM2

      /Free

           // Do some work here
           Monitor;

              // Set row count because we are returning 1 row
              RowCount=1;
              // Simply return our inbound parms.
              // Normally return useful data.
              RtnResults(1).RtnCode = '0';
              // Set return parms and trim trailing whitespace
              RtnResults(1).RtnParm1 = %trimr(INPARM1);
              RtnResults(1).RtnParm2 = %trimr(INPARM2);
           ON-ERROR;
              // Misc error occurred
              RtnResults(1).RtnCode = '3';
           ENDMON;

           // Pass the result set back to caller
           EXEC SQL SET RESULT SETS ARRAY :RtnResults FOR :RowCount ROWS;

           // Exit the program
           *Inlr = *ON;
           return;

      /End-Free 

[Louis-7]

Thank you @richardschoen , this is very helpful. I don't know it can call an RPG program in a tag before, let me have a try.

[Louis-7]

Finally, I call the SP with "RUNSQL" command, the reason I got the error before is the IBM i user I used to send xmlservice do not have enough authority to update the data in DB. 😅

Call SP with PGM and then call PGM with xmlservice is my backup plan.