Re: SQL UDF to call RPG without service program

No, CREATE FUNCTION...

Here's an quick (AI generated) example...

CREATE OR REPLACE FUNCTION MYLIB.GET_TOTAL_PGM (
    qty INT,
    unit_price DECIMAL(9, 2)
) RETURNS DECIMAL(11, 2)LANGUAGE RPGLE
PARAMETER STYLE SQLDETERMINISTICNO EXTERNAL ACTIONEXTERNAL NAME
'MYLIB/TOTALPGM' -- Points to *PGM object
PROGRAM TYPE MAIN

RPG code:

**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);

dcl-pi *n;
  -- 1. Input Parameters (Matched to SQL types)
  inQty      int(10) const;
  inPrice    packed(9: 2) const;
  -- 2. Result Parameter (This is where you "return" the value)
  outTotal   packed(11: 2);
  -- 3. Input Indicators (2-byte integers)
  indQty     int(5) const;
  indPrice   int(5) const;
  -- 4. Result Indicator
  indTotal   int(5);
  -- 5. Housekeeping (Mandatory for Style SQL)
  sqlState   char(5);
  fName      varchar(139) const;
  specName   varchar(128) const;
  diagMsg    varchar(70);
end-pi;

-- Logic
sqlState = '00000';
indTotal = 0;

if (indQty < 0 or indPrice < 0);
  indTotal = -1;
else;
  outTotal = inQty * inPrice;
endif;

return;




On Tue, Mar 3, 2026 at 7:55 AM Vern Hamberg via RPG400-L <
[email protected]> wrote:

> Hi Charles
>
> In this technique, are you using CREATE PROCEDURE? I had posted that one
> could simply call a program, but it is PARAMETER STYLE GENERAL and all
> parameters are INOUT - does that line up with your knowledge?
>
> *Regards*
>
> *Vern Hamberg*
>
> IBM Champion 2025 <cid:[email protected]> CAAC
> (COMMON Americas Advisory Council) IBM Influencer 2023
>
>
> On 3/3/2026 8:45 AM, Charles Wilt wrote:
> > 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
> >>>>> athttps://archive.midrange.com/rpg400-l.
> >>>>>
> >>>>> Please [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
> >>>> athttps://archive.midrange.com/rpg400-l.
> >>>>
> >>>> Please [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
> >>> athttps://archive.midrange.com/rpg400-l.
> >>>
> >>> Please [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
> >> athttps://archive.midrange.com/rpg400-l.
> >>
> >> Please [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.