Re: SQL UDF to call RPG without service program
Eric Wesson <[email protected]> Tue, 3 Mar 2026 15:33:09 +0000
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <DM6PR05MB678094D72A1F4E0FD56AC628C37FA@DM6PR05MB6780.namprd05.prod.outlook.com> |
Thank you! ________________________________ From: RPG400-L <[email protected]> on behalf of Daniel Gross <[email protected]> Sent: Tuesday, March 3, 2026 8:54 AM To: [email protected] <[email protected]> Subject: Re: SQL UDF to call RPG without service program Still one of the best sources to learn about UDFs and UDTFs ... -> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.scottklement.com%2Fudtf%2F&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669463998%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=UrZoTeZnkxcn8teRGdA1Quv%2FgXEMWqgphgvfHER7Kss%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669491360%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=XrD9TZRdN%2FxjyMsDCMJUFze9iAPSEimXQQhw1k%2FW3cw%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> >>>>> or email: [email protected] >>>>> Before posting, please take a moment to review the archives >>>>> at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669513298%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=5g9qWeeUEjyb6lySlU4CrDmk4XSevN3FG9AZJUq8bQs%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669534978%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=zAbrfMs2bleK5oI6k0qfl9Ojn01mcsgEdvIoIkbg6%2F8%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> >>>> or email: [email protected] >>>> Before posting, please take a moment to review the archives >>>> at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669557371%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Nq0bqwXxfi1c1SycdYf8gpkWqCj%2Bl9yFFdp5xJOYw%2FM%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669577689%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=P95ny%2BCD%2BIj%2BObutTwrFCsCGCIS0yPEyNxhCs92hgZo%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> >>> or email: [email protected] >>> Before posting, please take a moment to review the archives >>> at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669600431%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=FJChGrzfSHGh00PX6mCJLpezdaEdsWxEwkLtcIarTeo%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669625160%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=QTBFRPkjbx%2Bn7KAT1fAvA1%2BrrctrR3EDs5j01IqM6ag%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> >> or email: [email protected] >> Before posting, please take a moment to review the archives >> at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669646592%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=IyiMkd5LCmw3Gc890ImtT8jrrsuVzbC3U%2Fxmw2FlU9k%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669666686%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=8vEV%2Bc3mqBOQIPSbPv6QcXpuyryeNgCQcEvpkWPxsYQ%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> > or email: [email protected] > Before posting, please take a moment to review the archives > at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669809495%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=eMgOrcIvyoYUBd%2FcFYSHGM3VaM2VgCIeoEkRhKeNoog%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669832162%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=UX58FXogW4KfdXS3cFsnhwmpj19gS2ZKoXCOcF7HpQE%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> or email: [email protected] Before posting, please take a moment to review the archives at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7C6c4f31ca5a3e4c24a95908de7934c278%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639081464669851783%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=%2F2Yz9X18fkKrdApgu1%2Fsd9EKMtqThQ4%2BFTPctxPOEao%3D&reserved=0<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.