Dynamically Invoke SQL UDFs From RPG Code
September 7, 2011 Hey, Mike
|
I’m wondering if it is possible to use dynamic SQL (prepare/execute or execute immediate) to call a User Defined [scalar] Function (UDF). I understand we can use the SET statement in our code but we would like to “soft” code some RPG business logic and we are looking at having the RPG code dynamically call certain UDFs based on certain conditions. So it would be nice to create dynamic SQL statements to call certain UDFs. Thanks! –Brian Hi, Brian: Today is your lucky day because there is a way to do this. Starting with V5R1, the VALUES INTO statement can be dynamically prepared by an RPG program. VALUES INTO is just like SET except that it can “set” multiple values in a single statement, and the output variables are placed on the right/bottom instead of the left/top. In the following sample statement, three SQL registers are simultaneously fetched into corresponding host variables:
EXEC SQL VALUES (CLIENT WRKSTNNAME, CLIENT_PROGRAMID, CURRENT TIMESTAMP)
INTO :WORKSTATION, :PROGRAM, :TIME;
Incidentally, the i7.1 DB2 SQL Reference manual is the first to declare that SET can be dynamically prepared. However, in my test the dynamic SET statement didn’t work; not sure if it’s an O/S bug or I did something wrong. Now for an RPG embedded SQL example! Please excuse the quick and dirty code (including the use of the DSPLY op-code). To start, a VALUES INTO statement is placed in a SQL string. I’ve used the built-in POWER function for a simple illustration; but you would substitute your own user-defined function name(s) as needed. SQL='VALUES (POWER(3,CAST(? AS INT))) INTO ?'; In this case a host variable (called Exponent) will be used as the exponent input parameter for the POWER function. A second host variable (Result) will be assigned the result. Also, notice that sometimes DB2 might need a hint as to the input parameter’s data type and so CAST is used to supply this information. Once the SQL string is built, the PREPARE statement is used to create an executable version of the statement. The prepared statement is identified by a name the developer assigns; in this case FunctionEval. Once a statement is prepared, it can be run by name using the EXECUTE statement. (The host variables are substituted in the statement’s parameter markers from left to right; top to bottom.) DSQL S 512 Varying DExponent S 10I 0 INZ(4) DResult S 8F /Free SQL='VALUES (POWER(3,CAST(? AS INT))) INTO ?'; Exec SQL Prepare FunctionEval From :SQL; If SqlCode<>*Zero; Dsply 'Prep Problem' '' SqlCode; EndIf; Exec SQL Execute FunctionEval Using :Exponent,:Result; If SqlCode<>*Zero; Dsply SqlCode '' *InLR; Else; Dsply Result '' *InLR; EndIf; *InLR=*On; Return; /End-Free The exponent value passed to the function from the “Exponent” host variable is “4”; therefore SQL will evaluate three to the fourth power and the result “81” is placed in the “Result” host variable. If you’re going to do much with dynamically prepared statements, I’d recommend becoming familiar with the SQL Descriptor area and Descriptor statements in the DB2 for i SQL Reference. SET DESCRIPTOR, for example, can be used to set the input parameter’s initial value and data type attributes so that a CAST would not necessarily be required to give DB2 a data type “hint.” Keep in mind that dynamic prepartion of SQL is relatively slow. Once a statement has been prepared, if it needs to be evaluated several times, you should continue to issue EXECUTE … USING and substitute new variable values with each execution. Finally, it appears that VALUES INTO can only be prepared in a high level language program like RPG, C or COBOL. My attempts to dynamically prepare a VALUES INTO statement in VBA and .NET environments were unsuccessful. –Mike Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.
|
