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:part1.FN2sgG0C.xBk0XvlB@centurylink.net> 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<rfritchman@xxxxxxxxx> wrote:

External name is FUNC00001A: shouldn't you be calling that object?

On Mon, Mar 2, 2026 at 11:20 AM Eric Wesson<fjwesson@xxxxxxxxxxx> 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:RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email:RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
or email:RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.