Re: SQL UDF to call RPG without service program

You can use a MAINLINE RPG program as an external scalar SQL UDF.
You simply need to use PARAMETER STYLE SQL. (note the 3rd line below,
emphasis mine)

All applicable parameters are passed. The parameters are defined to be in
the following order:
n parameters for the input parameters that are specified for the function.
****A parameter for the result of the function.****
n parameters for indicator variables for the input parameters.
A parameter for the indicator variable for the result.
A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates
the success or failure of the function. The SQLSTATE returned can either be:
the SQLSTATE from the last SQL statement executed in the external program,
an SQLSTATE that is assigned by the external program.
The user may set the SQLSTATE to any valid value in the external program to
return an error or warning from the function.


A VARCHAR(517) input parameter for the fully qualified function name.
A VARCHAR(128) input parameter for the specific name.
A VARCHAR(1000) output parameter for the message text.
When control is returned to the invoking program, the message text can be
found in the 6th token of the SQLERRMC field of the SQLCA. Only a portion
of the message text is available. For information on the layout of the
message data in the SQLERRMC, see the replacement data descriptions for
message SQL0443 in message file QSQLMSG. The complete message text can be
retrieved using the GET DIAGNOSTICS statement. For more information, see
GET DIAGNOSTICS.


Zero to three optional parameters:
A structure (consisting of an INTEGER followed by a CHAR(n)) input and
output parameter for the scratchpad, if SCRATCHPAD was specified on the
CREATE FUNCTION statement.
An INTEGER input parameter for the call type, if FINAL CALL was specified
on the CREATE FUNCTION statement.
A structure for the dbinfo structure, if DBINFO was specified on the CREATE
FUNCTION statement.


Yes, the interface is more complex, but you gain a lot of control.
Personally, I've gotten to the point of always using PARAMETER STYLE SQL.

Of course, assuming an existing program called from other places, you could
add the additional parms as *OMIT and modify the code to see if the
additional parms where passed or not; thus allowing the program to
determine if it was called from RPG or SQL.  When called from SQL, you can
do the extra work to the additional parms,

Charles

On Mon, Mar 2, 2026 at 4:43 PM Reeve <[email protected]> wrote:

> Rob, "regular" RPG programs (with a mainline, I guess) can't return a
> value.  A function implicitly returns a single value.  Service programs
> (ctl-opt *NOMAIN) can return a value.
>
> Two ideas:
> 1) Read up on parameter passing and overloading.  If the attributes of the
> caller's parameter list don't fit with the called parameter list, you get a
> Not Found; I think a returned value is not considered in signature
> matching.
> 2) Set up a dummy service program named GETUPCHARGEFUNC and see if your
> code works with it.  If so, your SQL is good.
>
> --reeve
>
> On Mon, Mar 2, 2026 at 12:28 PM Robert Rogerson <[email protected]>
> 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
> > > > at https://archive.midrange.com/rpg400-l.
> > > >
> > > > Please contact [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.
> > >
> > >
> > --
> > 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.
> >
> >
> --
> 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.
>
>
-- 
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.