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