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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.