... or you register the RPG program as stored procedure and then write a SQL UDF (without any C implementation). Within the function you call the Stored Procedure and return the output parameter value.
It does not matter on whether you write a RPG function that calls the program and returns the output parameter and then register the function as UDF or whether you register the program as stored procedure and create an UDF that calls the stored procedure.
As for including C-Code: SQL is converted into C-code with embedded SQL and then compiled (with the C-Compiler). So when calling a stored procedure the C-Code for calling the RPG program is generated under the covers.
BTW it is possible to directly call a (RPG) program in an SQL environment. But the passed parameters must exactly match. Because SQL is translated is translated into C for character parameters only pointers are passed.
That's why it is far better to register the (RPG) programs as stored procedures.
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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Friday, 23 January 2026 06:30
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: UDF return *PGM parm?
Hi Justin,
there is another solution to tackle that problem - but this involves creating a small snippet of C code to call your RPG program.
You don't have to create a whole C program - only a small part which does the program call. This small part is then simply included into the SQL sourced function between declaring a variable for the return value and returning that variable from the SQL function.
This way, the SQL "compiler" will generate C source code for a small *SRVPGM which is the backend for the SQL sourced UDF. And when SQL passes that C source code to the ILE-C compiler to compile it, the small C-snippet will be included.
We have to do it that way, because SQL has not idea of doing a native call to a *PGM object - but ILE-C can do that of course.
It sounds harder than it is - I will try to create a small example later and send the sources to the list if you like.
Regards,
Daniel
Am 22.01.2026 um 20:39 schrieb Justin Taylor <jtaylor.0ab@xxxxxxxxx>:
That tracks. The *PGM "should" be a *SRVPGM, but anyway...
Thanks
On Thu, Jan 22, 2026 at 12:17 PM Niels Liisberg
<nli@xxxxxxxxxxxxxxxxx>
wrote:
You need two SQL modules ( if you will make it the clean way) :
1) Create a stored procedure that wraps the RPG *PGM - your can use
the basic parameter style. That is basically just a parameter
template for SQL to know how it calls your program - no real object is created here.
2) Create a scalar function that calls the procedure and and returns
the output parameter from the stored procedure.
We do it all the time, so we can "call an RPG program" within a
select from statement.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
