Re: SQL UDF to call RPG without service program

Y'all

Robert's suggestion sounds feasible - you can use the SQL CALL with any 
IBM i program - all parameters are INOUT and parameter style is GENERAL 
- this means that the parameters need to match what is in the program - 
no extra parameters beyond that.

Procedures do not return anything, parameters can be modified in the 
called program instead.

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:[email protected]> CAAC 
(COMMON Americas Advisory Council) IBM Influencer 2023


On 3/2/2026 2:27 PM, Robert Rogerson wrote:
> Hi Reeve,
>
> I may be missing something but why not just call the RPG from the
> function?  I'm not sure of the purpose of the procedure and if it's needed.
>
> Here's an example of a function calling an RPG program.
>
> CREATE FUNCTION MYLIB.GETUPCHARGEFUNC (
>      WHSENBR NUMERIC(3, 0) ,
>      ITEMNBR NUMERIC(6, 0) )
>      RETURNS NUMERIC(7, 2)
>      LANGUAGE RPGLE
>      SPECIFIC MYLIB.GETUPCHARGEFUNC
>      NOT DETERMINISTIC
>      MODIFIES SQL DATA
>      CALLED ON NULL INPUT
>      NOT FENCED
>      EXTERNAL NAME 'MYLIB/GETUPCHRGF'
>      PARAMETER STYLE SQL ;
>
> To call
> SELECT
>         ivwhid AS "Warehouse",
>         ivitm# AS "Item Number",
>         GETUPCHARGEFUNC(ivwhid, ivitm#) AS "Up Charge"
>    FROM invmasp
>   WHERE ivwhid = 90
>     AND ivitm# = 5
>   ORDER BY 1, 2;
>
> HTH,
>
> Rob
>
> On Mon, Mar 2, 2026 at 2:43 PM Reeve<[email protected]> wrote:
>
>> External name is FUNC00001A: shouldn't you be calling that object?
>>
>> On Mon, Mar 2, 2026 at 11:20 AM Eric Wesson<[email protected]> wrote:
>>
>>> I am trying to create an SQL function that effectively calls an RPG
>>> program and returns the result. I've read you can do it by creating a
>>> service program and calling it but I'm trying to do it without the
>> service
>>> program.
>>>
>>> I have successfully created an sql stored procedure that calls the RPG
>>> program and I've created a function that calls the stored procedure.
>>>
>>> When i try to use the function, I get an error saying "FUN0001 not
>> found".
>>> I can successfully call the function directly using "CALL
>>> APPSTRPROC.FUNC0001(12345, 0)".
>>> I've beat my head against the wall on this one. Any ideas?
>>>
>>>
>>> Code that creates procedure and function
>>> // Stored procedure to call rpg
>>>           Exec sql
>>>             Create or replace Procedure Appstrproc.FUNC0001(
>>>                                           in SystemSku dec(15),
>>>                                           out Retail dec(9,2))
>>>             Language RPGLE
>>>             Called on null input
>>>             Not deterministic
>>>             No external action
>>>             Reads sql data
>>>             External name FUNC00001A
>>>             Parameter style general;
>>>
>>>           // Function to call stored procedure
>>>           Exec Sql
>>>             Create or Replace Function
>>> Appstrproc.f_SkuGetCurrentRetail(SystemSku dec(15))
>>>             Returns Decimal(9,2)
>>>             Language SQL
>>>             Return Appstrproc.FUNC0001(SystemSku, 0);
>>>
>>> Thanks,
>>> Eric
>>> --
>>> This is the RPG programming on IBM i (RPG400-L) mailing list
>>> To post a message email:[email protected]
>>> To subscribe, unsubscribe, or change list options,
>>> visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
>>> or email:[email protected]
>>> Before posting, please take a moment to review the archives
>>> athttps://archive.midrange.com/rpg400-l.
>>>
>>> Please [email protected] for any subscription related
>>> questions.
>>>
>>>
>> --
>> This is the RPG programming on IBM i (RPG400-L) mailing list
>> To post a message email:[email protected]
>> To subscribe, unsubscribe, or change list options,
>> visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email:[email protected]
>> Before posting, please take a moment to review the archives
>> athttps://archive.midrange.com/rpg400-l.
>>
>> Please [email protected] for any subscription related
>> questions.
>>
>>
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.