Calling SQL Functions From RPG, A Service Program

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:

• Use the UPPER function instead of defining two translation strings and using %XLATE.
• Use DAYNAME function instead of calculating the day of the week and using an array to provide the day name.
• Using the REGEXP_ functions as opposed to calling the regular expression APIs.
• Using the ENCRYPT/DECRYPT functions instead of calling APIs.
• Using the SOUNDEX function instead of . . . well, there is no 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.

/INCLUDE SQLSCALAR/SQLSCALAR,PSQLSCALE

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;  

Parameters

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.

Extra Subprocedures

There are a few other extra subprocedures to be aware of.

• There are two versions of the LAST_DAY function. rSQL_last_Day() returns a date and rSQL_last_Day_Stamp() returns a timestamp.
• The subprocedure rSQL_set_Encryption_Password() can be used to set a default password (and optional hint), as opposed to passing an optional password (and optional hint) on calls to rSQL_encrypt_AES(), rSQL_encrypt_RC2(), rSQL_encrypt_TDES() and rSQL_decrypt_CHAR().
• The subprocedure rSQL_full_Text_Date() returns a full text description of a date as follows:

 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.