Re: Stored Procedure Test -- MIDRANGE-L

Figured it out.
First thing is STRSQL will not show the data set.
Second thing was the error that ACS-RSS was due to a library list issue.
Solution was to have the Stored Procedure call a CLLE that changes/fixes
the library list. Then calls the SQLRPGLE program. In
that program changed the EXEC SQL statement to look like this. Note the
'With Return to Client Array'
Exec SQL
SET RESULT SETS with return to client Array
:ResultsDS FOR :MaxRows ROWS;

Now when run in ACS-RSS it shows the data set. Without error.

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

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

--
KCrawford