Calling SQL Functions From RPG, A Service Program
November 15, 2016 Paul Tuohy
Note: The code accompanying this article is available for download here.
In this article I would like to introduce a service program that provides RPG subprocedure “wrappers” for using SQL scalar functions. This means you can use an SQL scalar function as if it were an RPG built-in function.
Using an SQL scalar function can be a lot easier than using the RPG alternative:
The SQLSCALAR service program contains subprocedure wrappers for the following SQL scalar functions:
ABS, ACOS, ANTILOG, ASCII, ASIN, ATAN, ATAN2, ATANH, BIT_LENGTH, CEIL, CHR, COALESCE, COS, COSH, COT, DATABASE, DAYNAME, AYOFWEEK, DAYOFWEEK_ISO, DAYOFYEAR, DECRYPT_CHAR, DEGREES, DIFFERENCE, ENCRYPT_AES, ENCRYPT_RC2, ENCRYPT_TDES, EXP, FLOOR, GETHINT, HEX, IFNULL, JULIAN_DAY, LAND, LAST_DAY, LCASE, LEFT, LN, LNOT, LOG10, LOR, LOWER, LPAD, MAX, MIDNIGHT_SECONDS, MIN, MONTHNAME, NEXT_DAY, PI, QUARTER, RADIANS, RAND, REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REPLACE, RIGHT, ROUND_TIMESTAMP, RPAD, SIN, SINH, SOUNDEX, TAN, TANH, TRUNC_TIMESTAMP, TRUNCATE, UCASE, UPPER, VALUE, WEEK, WEEK_ISO and XOR.
Subprocedures are not provided for SQL scalar functions that have a direct RPG equivalent (e.g. SUBSTR) or that only make sense when used with multiple rows (e.g. the XML functions).
The library (SQLSCALAR), containing the service program (SQLSCALAR) and a test program (TESTSCALE), can be downloaded at http://www.systemideveloper.com/downloads.html.
Using SQL Scalar Functions In RPG
Using an SQL scalar function in embedded SQL is very straightforward. There are two ways it can be done.
1. Use VALUES INTO.
exec SQL values upper(:textOut) into :textOut;
2. Use SET.
exec SQL set :textOut = upper(:textOut);
I prefer to use VALUES INTO simply because SET can be used in other ways (e.g., using SET OPTIONS to set the SQL run time environment.)
Wrapping A Scalar Function In A Subprocedure
Like any BIF in RPG, our wrapper subprocedures need to return a single value. This is a wrapper subprocedure for the UPPER SQL scalar function:
dcl-Proc rSQL_upper export; dcl-Pi *n varchar(32000); textIn varchar(32000) const; end-Pi; dcl-S textOut varchar(32000); textOut = %trimR(textIn); exec SQL values upper(:textOut) into :textOut; setCodes(); return %trimR(textOut); end-Proc;
The subprocedure accepts a VARCHAR filed of up to 32000 characters in length and returns a VARCHAR field of up to 32000 characters in length. The use of the CONST keyword on the input parameter means that a character expression or field may also be passed as a parameter (as opposed to only VARCHAR).
Using The Subprocedures
To make use of the subprocedures, you need to use the /INCLUDE compiler directive to copy the source member PSQLSCALE1 in source physical file member SQLSCALAR/SQLSCALAR into your programs.
You will also need to specify the SQLSCALAR binding directory in the control spec or on the BNDDIR parameter when you create a program. Alternatively, just add an entry for the SQLSCALAR service program to a binding directory you are already using.
PSQLSCALE1 contains the prototypes for the wrapper subprocedures. The subprocedures are named rSQL_ followed by the name of the SQL Scalar Function.
The Test Program
You can call the program TESTSCALE to see how a scalar function works. The program will prompt for the name of a function (just the SQL name; no rSQL_ prefix) and will then show an example of the input parameters and returned value for the subprocedure call. It will also show the values of SQLCODE and SQLSTATE–in case there was a problem!
This would be the result of calling TESTSCALE and specifying UPPER as the function:
DSPLY Select Function: upper DSPLY Parameter is this is lower DSPLY Result is THIS IS LOWER DSPLY SQL Code is 0 DSPLY SQL State is 00000
To see an example of calling one of the subprocedures, just browse the source of TESTSCALE for the name of the scalar function. This is the code used to call the UPPER function:
elseIf (function = 'UPPER'); dsply ('Parameter is ' + %trim(lowercase)); showIt = ('Result is ' + %trim(rSQL_upper(lowercase))); dsply showIt;
RPG is much more stringent about the definition of variables than SQL. In all cases, I had to choose an arbitrary maximum length for VARCHAR variables (which varies, depending on the function, but usually between 500 and 32000) and the length and decimal precision for numbers (usually 30:16). It should not be too much of a concern when these definitions relate to input parameters. Since all input parameters are defined with the CONST keyword, you can provide any character or numeric variable you wish. You need only be concerned if the definition is not large enough for the required parameter or returned value.
Some of the procedures have optional parameters. For example, the rSQL_coalesce() subprocedure can accept from two to 10 parameters.
For functions that deal specifically with null values (e.g., rSQL_coalesce()), an empty value is considered null.
Check for Errors
Two extra subprocedures (rSQL_SQLCode() and rSQL_SQLState()) can be called, after a call to one of the wrapper subprocedures, and will return the value of SQLCODE or SQLSTATE that was set by the underlying VALUES INTO statement.
There are a few other extra subprocedures to be aware of.
DSPLY Select Function: full_text_date DSPLY Parameter is 2016-09-07 DSPLY = Wednesday, September 7, 2016 DSPLY SQL Code is 0 DSPLY SQL State is 00000
Roll Your Own
I hope this service program gives you a starting point to building your own SQL scalar function utilities.
Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.