RE: SQL UDF to call RPG without service program

1. I'd write an RPG Function that calls the RPG program and then returns the
output value as return value.
2. I'd register the RPG function as a SQL external function. In this way it
can easily be called from within the SELECT List of an SELECT-Statement.


Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Eric
Wesson
Sent: Monday, 2 March 2026 20:20
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL UDF to call RPG without service program

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 at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.