You can use SQL User Defined Functions (UDFs) to call custom logic from SQL. The following example calls a sample RPG UDF named RJSUDFSHELL in library QGPL.
 
To use a UDF:
  1. Create an RPG Service Program with an exported procedure. 
  2. Create the function so that SQL/400 can use the function. 
  3. Start using the UDF in your SQL code. 

Sample UDF ILE/RPG Service Program

This function receives a string and concatenates a string value to it:
 
HNomain                                                              
Hcopyright('Copyright RJS Software Systems Inc. 1990-2004')           

DRJSUDFSHELL      Pr           255A   VARYING                        
D InString                     255A   VARYING                         

*--------------------------------------------------------------------
* Function: RJSUDFSHELL                                             
*     Desc: RJS Sample Shell String Based User Defined Function     
*--------------------------------------------------------------------

PRJSUDFSHELL      B                   Export                         
D                 Pi           255A   VARYING                        
D InString                     255A   VARYING                         

D OutString       S            255A   VARYING   

*                  ** Do a simple string concatenation              
C                   EVAL      OutString = InString + ' ' + 'TEST'     

*                  ** Return the concatenated string                
C                   Return    OutString                               

PRJSUDFSHELL      E                                                   
 

Compiler Parameters for the Service Program

The following examples assume that the library is QGPL:
  • To create the RPG module, use:

    CRTRPGMOD MODULE(QGPL/RJSUDFSAMP) SRCFILE(QGPL/RJSUDFSHELL) SRCMBR(*MODULE) 
  • To create the service program, use:

    CRTSRVPGM SRVPGM(QGPL/RJSUDFSAMP) EXPORT(*ALL) ACTGRP(*CALLER)        
 
You can also use SQL code to create the RJSUDFSHELL function:
CREATE FUNCTION RJSUDFSHELL(VARCHAR(255)) RETURNS VARCHAR(255) 
LANGUAGE RPGLE                                                 
EXTERNAL NAME 'QGPL/RJSUDFSAMP(RJSUDFSHELL)'                 
DETERMINISTIC                                                  
NO EXTERNAL ACTION                                             
PARAMETER STYLE GENERAL                                        
NO SQL                 
The following sample SQL statement uses the RJSUDFSHELL function:

SELECT RJSUDFSHELL('Test Line1'),CUSNUM FROM QIWS/QCUSTCDT

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016