RE: Stored Procedure Test -- MIDRANGE-L

... or just set the library list correctly directly before you run the
stored procedure

CL: CHGLIBL ...;
Or
CL: ADDLIBLE...;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Samstag, 6. November 2021 00:00
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Stored Procedure Test

you can configure the library list to use in the JDBC connection of ACS Run
SQL Scripts...

"Edit"-->JDC Configurations...

Charles

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

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

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