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:
- Create an RPG Service Program with an exported procedure.
- Create the function so that SQL/400 can use the function.
- 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
Last Modified On:
No, open a new Support Case
