Re: SQL UDF to call RPG without service program

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I'm also confused as to what you're trying to do...
An RPGLE Program defined as an SQL Procedure:

   1. Is used as an easy way to invoke RPG programs from outside the box
   via JDBC/OLEDB/ADO.NET
   2. You'd normally store the SQL statement to create such a proc in a
   source member/stmf an run using RUNSQLSTMT or possibly ACS Run SQL Scripts.
   3. You only need to run it once (well unless you need to re-create it
   because of changes)
   4. It doesn't make sense to call it from RPG via EXEC SQL, just call it
   via prototype.

An RPGLE Program ( ideally Service Program Procedure) defined as an SQL
Function is similar
Points 2 and 3 still apply.

Points 1 and 4 apply also mostly apply.  The exception is that it's
perfectly acceptable to call a UDF inside a SQL Statement from outside the
box or from RPG.

so something like
exec sql
  select sku,  Appstrproc.f_SkuGetCurrentRetail(sku) as retailPrice  into
:results
  from ItemMaster
  where sku = :selectedSku;

(this is just an very simple example, in reality, you want to avoid 1 row
at a time)

If you haven't read it, I recommend the IBM Redbook, External Procedures,
Triggers, and User-Defined Functions on IBM DB2 for i
https://www.redbooks.ibm.com/abstracts/sg246503.html?Open

If you want assistance creating the function, show us the actual RPG
Program name and its interface.

HTH,
Charles

On Mon, Mar 2, 2026 at 12:20 PM 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
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.