Re: Calling result set returning service program procedure from SQL

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.