Re: SQL UDF to call RPG without service program

I'll try calling RPG directly from a function.

I'm really curious how you use unit tests. Are they set to run automatically when you push code to production? How do you get results from the unit tests?
________________________________
From: RPG400-L <[email protected]> on behalf of Reeve <[email protected]>
Sent: Monday, March 2, 2026 5:52 PM
To: RPG programming on IBM i <[email protected]>
Subject: Re: SQL UDF to call RPG without service program

The other option is to make change the current code into a service program
(and get all the performance benefits) and write a wrapper around it shoukl
you need an RPG interface.  Unless you're working with old code or have
some other constraint, I'd put your code into carefully-designed service
programs.  When I write a service program, I also write a CL wrapper
fronted by a command; this makes unit testing (and the inevitable
debugging) enormously easier.

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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923708891836%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=mZ%2BK4A6%2Fy0OBNaKkU6Knki6K87v01IdQCiSPKfxAr2Y%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923708921518%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=c%2BQG1g5ekjqmFxtF%2FTdSwdHoQ1TYWYjp%2BWOqcH7V480%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923708944676%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=M5%2FQ%2FZPiCgC833NtllQcjsc7w0S2DJaZPrR6oJlt7Vs%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923708970240%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=DM6RGyMXXyqzRRS2QcmUo1VrUe26wupHZLEflbahBeQ%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923708992284%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=rBrzlMcFsWGqa28uyxuvyayA7PE0cgVNFblbFdqhmkU%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923709013309%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=iYNP0TkwDy7ZAE1MI47YqfOAKVaJfvv5uW5hDKJc4jg%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923709034967%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=LI6RQCNISrEksBW0b%2FLbvopqEiF3sanlw799iZpmW4M%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%7C7c9fddeca5944e7fd1ab08de78b6cf27%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080923709056315%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=9RHNsRC4fVGmdlrDmxbp3KZkRDTDkNclXkmsCqPHpmE%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.