Re: Stored Procedure Test

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I could have sworn STRSQL would show the results set from a stored
procedure call; although output parameters were another story.

In any case, use ACS Run SQL scripts instead of STRSQL.

Much better option.

Charles

On Fri, Nov 5, 2021 at 12:26 PM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

> I have a stored procedure I am trying to test.  I am using STRSQL.
> I have done a few of these before but normally a SQL.  No issues testing.
> I issue this command.
> call myLibrary.myStoredProc('AB', 123456, 1234)
> I get this message.
> 1 result sets are available from procedure MYSTOREDPROC in MYLIBRARY.
> How do I see the result set?
>
> My create command is this:
> CREATE OR REPLACE PROCEDURE myLibrary.myStoredProc (
>         IN    Parm1  CHAR(2)
>        ,IN    Parm2  numeric(7,0)
>        ,IN    Parm3  numeric(4,0)
>       )
> DYNAMIC RESULT SETS 1
> LANGUAGE RPGLE
> READS SQL DATA
> EXTERNAL NAME myLibrary.mySQLRPGLE
> PARAMETER STYLE GENERAL
> ;
>
> In a nut shell this is my SQLRPGLE
>        // Results Datastructure to sent to Stored Procedure data set
>        dcl-ds ResultsDS dim(8) qualified;
>          RIn    zoned(7: 2);
>          ROut   zoned(7: 2);
>          REavl  zoned(7: 2);
>          RYear1 zoned(4);
>        End-ds;
>
>        ...Code here to do stuff...
>
>        // Populate the Results Datastructure
>        for Cntr = 1 to MaxRows;
>          ResultsDS(Cntr).RIn = Rollin(Cntr);
>          ResultsDS(Cntr).Rout = Rollou(Cntr);
>          ResultsDS(Cntr).REavl = eavl(Cntr);
>          ResultsDS(Cntr).RYear1 = year1;
>        EndFor;
>
>        Exec SQL SET RESULT SETS Array :ResultsDS FOR :MaxRows ROWS;
> --
> KCrawford
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com