Re: SQL UDF to call RPG without service program

Still one of the best sources to learn about UDFs and UDTFs ...

-> https://www.scottklement.com/udtf/

Just dive into Scotts presentations and try it. Parameter style SQL oder DB2SQL is perfect - also when you want to create your own table functions.

What I do often is - I create a function in a service program - e.g.

	mySpecialFunction

Now if I want to expose it to SQL, and I need some SQL-ish overhead (like checking for NULLs), I simply create a wrapper in the same service program:

	mySpecialFunction_sql

Then I expose this function with a CREATE FUNCTION in SQL.

HTH
Daniel


> Am 03.03.2026 um 15:45 schrieb Charles Wilt <[email protected]>:
> 
> 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.
> 
-- 
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.