Calling SQL Functions Directly From a High Level Language Program

The Create SQL Function from the article SQL Cross Platform Interoperability: The Proper Function creates a CLE *srvpgm on the iSeries. Can this be accessed from an RPGLE program? If so, what would the prototype look like? I am not familiar with C and cannot get this to work, so far, on my own.

–Gene

Note: The code accompanying this article can be downloaded here.

This astute reader noticed that when creating an SQL-based function, DB2 generates an ILE C service program behind the scenes. This prompted the reader to inquire if there was a way to call the logic in the service program directly from an RPG program.

The short answer is yes. But first, let’s consider how a single call to an SQL function is meant to be used within a high level language program. This RPG example below shows an embedded SQL example using the SET statement to retrieve the value from the Proper function discussed in the article referenced above:

DInput   S  100  Varying Inz('CONVERT TO PROPER CASE')
DOutput  S  100  Varying
C/Exec SQL
C+ Set :Output=Proper(:Input)
C/End-Exec

As a reminder, the Proper function accepts a VarChar(100) parameter and returns VarChar(100). In the RPG code, a varying length variable called input is passed to the function and a varying length variable called output receives the function’s result.

If the function can return a NULL value, then an additional NULL indicator variable is required since RPG data types cannot represent a NULL. The NULL indicator variable will contain a -1 if the function returns NULL and a zero if the function’s result is NOT NULL. The NULL indicator variable is defined as the equivalent of the SQL SMALLINT data type. In RPG, this is a 5 byte integer (5I 0). (See section High-Level Languages, Embedded SQL, and NULLs from The NULL Nemesis for more information on handling NULLs in embedded SQL.)

Now back to the original question–how can SQL function logic be called directly? Since SQL generates a C ILE service program to store the Proper function’s logic, then there must be an entry point to the service program that we can call directly from any ILE program. If the function’s name is 10 characters or less and is unique, then the service program will retain the same name as the function name. If the function name is not unique or is over 10 characters then the system will generate a service program name consisting of the first five characters of the function name followed by a system assigned five-digit sequence number. For non-unique or long function names you will need to keep track of the service program name associated with each function.

The SQL sample function “Proper” is less than 10 characters and is unique so that a service program of the same name should be in the same schema (library) where the function was defined. (If you have not created the Proper function, download the code from here and create the function.) By doing a DSPSRVPGM SCHEMA/PROPER command and advancing to the “Procedure Exports” portion we can see that the entry point to the program is a procedure called PROPER_1 (case sensitive).

Now that we know what procedure we need to call, how do we find the procedure’s signature (i.e. parameter list)? There are several ways to do this, here are two:

1. When creating an SQL function the “under the covers” ILE C member is placed in source file QTEMP/QSQLSRC. Therefore, when creating a function from an interactive 5250 session a source member in QTEMP will be available for review.
2. When creating the function, include the DBGVIEW=*LIST option in the function’s SET OPTION statement. This option will preserve the source in the service program’s module. Then use the ILE debugger to examine the source code. For example: STRDBG SRVPGM(SCHEMA/PROPER)

Looking at the source code for the Proper function, we see the signature of the procedure that needs to be prototyped in RPG:

 void PROPER_1(
 char * SQLP_V1,
 char * SQLF_OUTPUT,
 short *SQLF_IND1,
 short *SQLF_IND2,
 char SQL_STATE[6],
 char SQL_FNAME[140],
 char SQL_FINST[129],
 char SQL_MSGTEXT[71])

Since I’m largely unfamiliar with C, how do I define equivalent data types between RPG and C? I found the cheat sheet to equating RPG and C data types in Section 5.1.1 of the Redbook entitled Who Knew You Could Do That with RPG IV? A Sorcerer’s Guide to System Access and More.

Here is the RPG prototype I came up with along with the variable declarations:

DProper           PR                  ExtProc('PROPER_1')
D parmInput                       *   Value              
D parmOutput                      *   Value              
D parmInput_N                   10I 0                    
D parmOutput_N                  10I 0                    
D parmSQLState                    *   Value              
D parmSQLFName                    *   Value              
D parmSQLFInst                    *   Value              
D parmMsgText                     *   Value              
DInput            S            100    Varying               
DOutput           S            100    Varying               
DptrOutput        S               *   Inz(%Addr(Output))    
DptrInput         S               *   Inz(%Addr(Input))     
DInput_N          S             10I 0                       
DOutput_N         S             10I 0                       
DSQLState1        S              6    Inz(x'00')            
DptrSQLState      S               *   Inz(%Addr(SQLState1)) 
DFName            S            140    Inz(x'00')            
DptrFName         S               *   Inz(%Addr(FName))     
DFInst            S            129    Inz(x'00')            
DptrFInst         S               *   Inz(%Addr(FInst))     
DSQLMsgText       S             71    Inz(x'00')            
DptrSQLMsgText    S               *   Inz(%Addr(SQLMSGTEXT))

Incidentally, you may have noticed that the ILE C program generated by the CREATE FUNCTION statement follows the rules for defining parameters lists when calling an external scalar user defined function. The parameter list rules for an external scalar function are shown below:

• Input variables
• Output variable
• Input variable null indicators
• Output variable null indicator
• SQL State
• SQL Function Name
• SQL Specific Name
• SQL Message Text

If you’re not familiar with building parameter lists for external functions, see the table in Scribble on SQL’s Scratchpad, although the variable sizes for the special DB2 variables are slightly different within the DB2 generated C program.

Here is the sample RPG code that invokes the function. Note that the ILE C service program created by SQL must be registered in a binding directory in order to compile the program in a single step using the CRTBNDRPG command.

Wow, that was an awful lot of work to call SQL function directly, but we’re not done. The SQL service program still has to be placed in a binding directory and technically the program (which is acting like DB2) should check the SQL State upon the function’s exit and pass in the function name and specific name. On the brighter side, as long as the function’s signature doesn’t change, the SQL function can be dropped and re-created without changing the service program’s signature. Recall, if the service program’s signature changes then all programs using the service program will need to be reviewed and re-created.

With all this work, what are the potential benefits? I can think of two:

• You want to use SQL logic in an RPG program but don’t have the SQL Development Kit installed and therefore can’t use embedded SQL to use the SET statement demonstrated at the start of the article.
• The function needs to be called several times and the absolute best performance is required.

The second reason isn’t very promising. I created a sample program that called the Proper function’s code directly along with a sample that used the SQL SET statement to access the logic. After performing repetitive calls 10,000 times, calling the service program directly only saved an average of 13 seconds (about a 6 percent performance improvement on our small System i). Not much improvement for the extra work although the results may be a little more dramatic for functions with complex parameter lists.

Due to the complexity of prototyping the calls and registering the service program in a binding directory, I recommend using embedded SQL wherever possible to invoke user defined functions.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.

RELATED STORIES

SQL Cross Platform Interoperability: The Proper Function

The NULL Nemesis

Scribble on SQL’s Scratchpad

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot