Re: SQL UDF to call RPG without service program

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.