Re: Calling result set returning service program procedure from SQL

[Michael Ryan <michaelrtr@xxxxxxxxx>]

Thanks for the information Steve! I've got this working with stored
procedures, but it does make things interesting. Good point about the
environment that the SP sets up. Thanks!


On Mon, Apr 29, 2013 at 3:03 PM, Needles,Stephen J <
SNEEDLES@xxxxxxxxxxxxxxxx> wrote:

> Michael,
>
> You must call it as an SQL stored proc because you wish to consume an SQL
> cursor.  It is not simply "another wrapper".  The needed SQL Stored Proc
> defines the environment that it needs to execute the cursor development
> within.
>
> Q1 - you can call a service program procedure from embedded SQL so long as
> there is an SQL UDF or SQL Stored proc that encompasses it.
>
> Q2 - no...you cannot use an Associated Result Set without creating said
> result set from within an SQL call.
>
> Steve Needles
> St. Paul, MN 55102
> Tel: 651-310-4203
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Michael Ryan
> Sent: Friday, April 26, 2013 10:07 AM
> To: RPG programming on the IBM i / System i
> Subject: Calling result set returning service program procedure from SQL
>
> Ok...I want to call a procedure in a service program from embedded SQL.
> The reason I'm using embedded SQL is that I want the called procedure to
> return a result set that the caller will consume. I'm on 7.1, so I know I
> can produce a result set in the called by using this:
>
> Exec SQL
>   Set Result Sets For Return To Client
>      Array :ReturnDS For :WkRowcount Rows;
>
> And consume the result set in the caller with this:
>
> // Call Stored Procedure
> Exec SQL
>   Call Msp101R(:WkVnda, :WkMdl);
>
> // Associate result set and result set locator Exec SQL Associate Result
> Set Locator (:rResultDS)
>               with Procedure Msp101R(Char(5), Char(20));
>
> // Define a cursor associated with the result set // and Open the cursor
> Exec SQL Allocate Csr01 Cursor For Result Set :rResultDS;
>
> But I'd rather not use an SP if I don't have to - just another 'wrapper'
> that I'd rather not use.
>
> Question: Can I call a service program procedure from embedded SQL?
>
> Question: Can I use Associate Result Set without using a Stored Procedure?
>
> I want to the called procedure (that returns the result set) to be
> available to both RPG and PHP.
>
> Thanks!
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> http://archive.midrange.com/rpg400-l.
>
>
> ________________________________
> This communication, including attachments, is confidential, may be subject
> to legal privileges, and is intended for the sole use of the addressee. Any
> use, duplication, disclosure or dissemination of this communication, other
> than by the addressee, is prohibited. If you have received this
> communication in error, please notify the sender immediately and delete or
> destroy this communication and all copies.
>
> TRVDiscDefault::1201
>
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.