Using SQL UDF with RPG

This short post comes from a discussion I was having with two other IBM i developers at my place of work. I had shown them a number of SQL User Defined Functions, UDF, I had created to make my SQL statements easier.

"That's fine," started one of the developers, "but how does that help me in RPG. Won't I have to duplicate the procedures so I can call them in RPG?"

"No", I replied, "you can call them using SQL".

At first they were skeptical. Having shown them how easy it was to do they were sold.

In the examples I showed them the UDF were calling RPG procedures. In my opinion there are just some things that RPG does better then SQL. Validating and converting dates from one format to another are a case in point.

The ERP my employer uses store the dates as seven packed numbers in CYMD format. When I create extract files for the users no-one wants to see the CYMD they want to see the date formatted for the USA. Having to do this everywhere I needed a date became a pain in the neck.

SELECT SUBSTR(DIGITS(ADDDTE),4,2) || '/' || SUBSTR(DIGITS(ADDDTE),6,2) || '/' || SUBSTR(DIGITS(ADDDTE + 19000000),2,4) FROM ORDHIST

The USA "date" is really character: MM/DD/YYYY

So I created my own UDF, CYMDTOUSA, to do the same thing.

SELECT MODLIB.CYMDTOUSA(ADDDTE) FROM ORDHIST

My two colleagues agreed that this was a better approach, but how could they do the same thing in a RPG program.

I quickly wrote this program to show them how it could be used without using a SQL Select.

01 **free 02 dcl-s Char10 char(10) ; 03 dcl-s Packed7 packed(7) ; 04 exec sql SET OPTION COMMIT=*NONE,CLOSQLCSR=*ENDMOD ; 05 Packed7 = 1180731 ; 06 exec sql SET :Char10 = MODLIB.CYMDTOUSA(:Packed7) ; 07 dsply ('Char10 = ' + Char10) ; 08 *inlr = *on ;

Line 1: This program is written in totally free RPG.

Lines 2 and 3: I have defined two variables. The first, line 2, is a 10 long character variable. The second, line 3, is a 7 long packed variable.

Line 4: I always include a SET OPTION in my SQL RPG programs to ensure that when the program is compiled the options will be the way I want them to be.

Line 5: Move a "date" into the packed variable.

Line 6: The SQL SET will execute the UDF, with the packed variable as the input parameter, and return a value into the character variable. Notice that the variable names all have to start with a colon ( : ).

Line 7: I have use the DSPLY operation code to show the value in Char10.

DSPLY Char10 = 07/31/2018

There was a "light bulb" moment as my colleagues realized that they could use the UDF in their RPG programs to convert the date whenever they needed to.

If you are interested in how I made this UDF I started by creating a procedure that is added to the source member I had my other UDF procedures in, UDFMODULE.

01 **free 02 ctl-opt nomain ; // Convert *CYMD date (7P0) to *USA (10A) 03 dcl-proc CYMDtoUSA export ; 04 dcl-pi *n char(10) ; 05 inCymd packed(7) const ; 06 end-pi ; 07 test(de) *cymd inCymd ; 08 if (%error) ; 09 return '00/00/0000' ; 10 else ; 11 return %char(%date(inCymd:*cymd):*usa/) ; 12 endif ; 13 end-proc ;

Line 2: This control option is used on when the source member contains procedures that will be called from elsewhere.

Line 3: The start of the procedure CYMDtoUSA. It needs the EXPORT keyword as it will be called and return values to procedures and program outside of this module.

lines 4 – 6: The procedure interface. One parameter is passed to the procedure inCYMD, line 5. The way I say what is returned in on the initial procedure interface line, line 4. I have CHAR(10) at the end of the line which means that a 10 character value is returned to whatever called this procedure.

Line 7: The passed date is validated using the TEST(DE) operation code.

Line 8 and 9: If there was an error, the date is not valid, zeroes are returned.

Lines 10 and 11: If the date is valid the CYMD number is converted to a date using the %DATE built in function, which is then converted to character using the %CHAR BiF.

Line 13: The procedure ends.

I have to create a service program that contains the module, that in turn contains all of the UDF procedures.

CRTSRVPGM SRVPGM(MODLIB/UDFSRVPGM) MODULE(MODLIB/UDFMODULE) EXPORT(*ALL) TEXT('UDF service program')

I put the SQL statements that create the UDF into one source member. I would add this new statement for the UDF.

-- CONVERT *CYMD (7P0) TO *USA (10A) 01 CREATE OR REPLACE FUNCTION MODLIB.CYMDTOUSA (INCYMD DECIMAL(7,0)) 02 RETURNS CHAR(10) 03 LANGUAGE RPGLE 04 DETERMINISTIC 05 NO SQL 06 EXTERNAL NAME 'MODLIB/UDFSRVPGM(CYMDTOUSA)' 07 PARAMETER STYLE GENERAL 08 PROGRAM TYPE SUB ;

Line 1: My function is called CYMDTOUSA, it is in MODLIB, and I need to define the parameter that is passed, inCymd.

Line 2: This function returned a 10 character value.

Line 3: This function is written in RPGLE.

Line 4: DETERMINISTIC means that if it called with the same incoming parameter more than once it somehow "remembers" the value to return.

Line 5: The code within the function does not use any SQL.

Line 6: This is where I define where the code for the function is to be found. This is given in the format: library/service program(procedure).

Line 7: This is the simplest way to pass parameters in and out of the function. Only the defined incoming parameter, line 1, and the outgoing parameter, line 2, are returned to the SQL statement.

Line 8: As the function uses a subprocedure I need to give the program type of SUB.

I run the SQL statements in the source member by using the Run SQL Statement command, RUNSQLSTM. This create all of my UDFs, and I am ready to use them within SQL statements, like Select, or to pass the returned value to RPG, using the Set.

 

This article was written for IBM i 7.3, and should work for earlier releases too.