Re: Stored Procedures Calling Service Programs

Hi Michael,

SQL doesn't support passing parameters by value. My suggestion is to create a "wrapper" (a second subprocedure that calls the first after changing the parameters)

In fact, even if SQL supported parameters by value (which it doesn't) you couldn't call this subprocedure from SQL as a stored procedure because it has a return value. You'd have to use a UDF, or code a wrapper (like I'm suggesting)

So this is a brand new procedure added to your service program (if you're using signatures properly, this will have no impact on existing callers):

P IsComponent_SQL...
P B export
P IsComponent_SQL...
D PR
D icCONO 3A const
D icCPY 3A const
D icCOMP 11P 0 const
D icRECP 11P 0 const
D icSUB 1A const
D returnVal 1A
/free
returnVal = isComponent( icCONO
: icCPY
: icCOMP
: icRECP
: icSUB );

/end-free
P E

Since this procedure calls the original one, it should serve the exact same purpose. And since it accepts all of it's parameters by reference, it can be called from SQL

D icCONO 3A const
D icCPY 3A const
D icCOMP 11P 0 const
D icRECP 11P 0 const
D icSUB 1A const
D returnVal 1A

Create Procedure isComponent(
IN icCoNo char(3),
IN icCPY char(3),
IN icCOMP decimal(11, 0),
IN icRECP decimal(11, 0),
IN icSUB char(1),
OUT returnVal char(1)
)
language RPGLE
Not Deterministic
Reads SQL Data
external name 'YOURLIB/SRVPGM(ISCOMPONENT_SQL)'
parameter style GENERAL;

Hope that helps


On 7/14/2011 1:24 PM, Schutte, Michael D wrote:

I have this existing service program that has input parameters defined with the keyword of VALUE. I have wrapped a SQL function around one of the procedures in the service program. However, when I get into the service program from SQL, the parameters are all garbage. I figured it has to do with the procedure interface saying that the parms are passed by value rather than by reference. Has anyone else experienced this and/or have a solution? Changing the service program isn't an option at the moment because it would require a lot of changes to existing programs using the service program.


Example procedure in service program.

D IsComponent PR N
D icCONO 3A VALUE
D icCPY 3A VALUE
D icCOMP 11P 0 VALUE
D icRECP 11P 0 VALUE
D icSUB 1A VALUE

P IsComponent B EXPORT
D IsComponent PI N
D icCONO 3A VALUE
D icCPY 3A VALUE
D icCOMP 11P 0 VALUE
D icRECP 11P 0 VALUE
D icSUB 1A VALUE

// Code removed...
P E EXPORT



Thank you,
Schutte

Join BE Mail now and receive a grocery coupon for a Buy One, Get One Free Side Dish in your Inbox! You can also look forward to exclusive monthly offers from Bob Evans as well as insider news about our restaurants, grocery food products, recipes, chefs and local community activities. For more information, visit www.bobevans.com/bemail<http://www.bobevans.com/bemail>

________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.