Re: SQL UDF to call RPG without service program

Ah, mea culpa!

So a question, as I've not done this kind of thing - if one were to use 
the default practice of simply calling a program in some SQL context, 
it's fine if you don't want to know anything back, that the program is 
basically a procedure that only takes some action, and all parameters 
are really input. But if you wanted to have a result come back, 
something in the SQL context would have to be able to use some 
parameter's value.

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:[email protected]> CAAC 
(COMMON Americas Advisory Council) IBM Influencer 2023


On 3/3/2026 9:28 AM, Charles Wilt wrote:
> 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 messageemail:[email protected]
>>>>>>> To subscribe, unsubscribe, or change list options,
>>>>>>> visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
>>>>>>> oremail:[email protected]
>>>>>>> Before posting, please take a moment to review the archives
>>>>>>> athttps://archive.midrange.com/rpg400-l.
>>>>>>>
>>>>>>> [email protected] for any subscription
>>>>> related
>>>>>>> questions.
>>>>>>>
>>>>>>>
>>>>>> --
>>>>>> This is the RPG programming on IBM i (RPG400-L) mailing list
>>>>>> To post a messageemail:[email protected]
>>>>>> To subscribe, unsubscribe, or change list options,
>>>>>> visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
>>>>>> oremail:[email protected]
>>>>>> Before posting, please take a moment to review the archives
>>>>>> athttps://archive.midrange.com/rpg400-l.
>>>>>>
>>>>>> [email protected] for any subscription
>>>> related
>>>>>> questions.
>>>>>>
>>>>>>
>>>>> --
>>>>> This is the RPG programming on IBM i (RPG400-L) mailing list
>>>>> To post a messageemail:[email protected]
>>>>> To subscribe, unsubscribe, or change list options,
>>>>> visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
>>>>> oremail:[email protected]
>>>>> Before posting, please take a moment to review the archives
>>>>> athttps://archive.midrange.com/rpg400-l.
>>>>>
>>>>> [email protected] for any subscription
>> related
>>>>> questions.
>>>>>
>>>>>
>>>> --
>>>> This is the RPG programming on IBM i (RPG400-L) mailing list
>>>> To post a messageemail:[email protected]
>>>> To subscribe, unsubscribe, or change list options,
>>>> visit:https://lists.midrange.com/mailman/listinfo/rpg400-l
>>>> oremail:[email protected]
>>>> Before posting, please take a moment to review the archives
>>>> athttps://archive.midrange.com/rpg400-l.
>>>>
>>>> [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.