Re: Stored Procedure Test

[K Crawford <kscx3ksc@xxxxxxxxx>]

Charles, I agree on using ACS RSS instead of STRSQL.
But I have had a few cases where if my SQL statement fails in a big way it
will issue many many many messages on my QZDASSINT jobs.  Only way to stop
them is to disconnect the ACS RSS session.  So until I am sure it will work
I use STRSQL then jump to ACS RSS.

On Fri, Nov 5, 2021 at 1:30 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

> 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
> --
> 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