Re: Problem calling ILE Stored Procedure from SQL Function

On 01-Jul-2014 14:10 -0500, Steve Stevens wrote:

I am having trouble calling an ILE stored procedure that is part of
a service program from an SQL Function.

What was described in the message is an External Routine [an ILE procedure in a Service Program (SRVPGM)] that is defined to the SQL as a User Defined Function (UDF); defined using the CREATE FUNCTION (External Scalar). Mention of Stored Procedure just confuses the issue; the SQL uses the term Stored Procedure to refer to what is the effect of the CREATE PROCEDURE statement.

Short story is that the results are inconsistent. Sometimes it will
correctly determine whether the customer is a store, sometimes not,
and sometimes it gives a decimal data error. The decimal data error
always occurs where the parameter is moved to the key field for the
CHAIN.

The code snippet showed "eval mlcst# = PrmCustNo", so the key value was apparently assigned the value from the argument; i.e. to be clear, was not any of the various MOVE statements.?

The ILE stored procedure works every time when called from RPG.

Presumably that is meant to imply that when the ILE RPG procedure is invoked via a non-SQL RPG invocation, then the ILE RPG procedure functions as expected.?

<<SNIP>>

d AmIAStore pi 1a
d PrmCustNo 9s 0 value
d* Field definitions . . .

<<SNIP>>

The snippet of included procedure, snipped above, conspicuously did not include a RETURN statement.?

Copy member for prototypes:

d/if defined(AmIAStore)
d AmIAStore pr 1a
d CustNo 9s 0 value
d/endif

SQL Create Function:

CREATE FUNCTION spslib/AMIASTOR_F
(Customer Numeric(9,0))
RETURNS char(1)
language rpgle
deterministic
no sql
no external action
external name 'PMBASO/VALID(AMIASTORE)'
parameter style general
program type sub

Note: The choice of /deterministic/ for dynamically retrieved row data is potentially problematic. Be sure to understand fully, the implications of the [chosen] scoping of the cached result as effected by the database [rather than ensuring the UDF is invoked every time and any caching is instead the domain of the external routine].

Invocation:

select mlcst#
, amiastor_f(cast(mlcst# as Numeric(9,0)))
from lmllst01

This should return the customer number and a flag indicating whether
it is a store (Y or N).

The SQL supports only pass-by-reference parameters. The ILE RPG prototype\interface should remove the VALUE keyword.