SQL Functions You Didn’t Know You Had, Part 2
November 12, 2014 Ted Holt
|
In SQL Functions You Didn’t Know You Had, Part 1, I showed you how to make an SQL function that runs a subprocedure in a service program. What I showed you is fine up to a point, but it’s not the entire story. The technique that I shared with you breaks down when null values enter the picture. First, the subprocedures in the ADDR service program cannot accept null arguments into parameters. Second, those subprocedures cannot return a null value to the SQL query. These behaviors are specified in two clauses of the CREATE FUNCTION statement:
And that’s why we didn’t have to talk about nulls in Part 1. RETURNS NULL ON NULL INPUT is usually all the support for nulls that I need. But that may not always be the case. Assume, for example, a price quotation subprocedure. Given a customer account number, item number and quantity, the subprocedure returns a price. If any of those values is not provided, the routine cannot return a price and must therefore return a null. RETURNS NULL ON NULL INPUT handles that case. But what if the values are all there, and one of the values is invalid? In that case, I might want the function to return a null value. (An alternative would be to have the function raise an error, which would cause the query to cancel.) To return to the subject at hand, I’ve borrowed a subprocedure from Aaron Bartell. Aaron’s IFS_file2var subprocedure copies the contents of a stream file into a variable. This function is somewhat like the GET_CLOB_FROM_FILE function that IBM added to DB2 for i as part of 7.1. You can get Aaron’s code here. I tweaked Aaron’s code a bit, not because there was anything wrong with it, but because I needed it to behave slightly differently for this illustration. Aaron’s IFS_file2Var subprocedure does all the work, but it doesn’t handle nulls. I added a wrapper subprocedure, LoadStmf, in order to add null support. Here’s the revised module source, from which I built my service program.
H nomain
//----------------------
// @Author: Aaron Bartell
// @Descr: Read an IFS file into an RPG string variable
// @Butchered by: Ted Holt
//----------------------
/copy prototypes,stmf
P IFS_file2Var b export
D IFS_file2Var pi 65535a varying
D pFile 256a value
D pSuccess n
D open pr 10i 0 extproc('open')
D filename * value options(*string)
D openflags 10i 0 value
D mode 10u 0 value options(*nopass)
D codepage 10u 0 value options(*nopass)
D creatcnvid 10u 0 value options(*nopass)
D read pr 10i 0 extproc('read')
D handle 10i 0 value
D buffer * value
D bytes 10u 0 value
D close pr 10i 0 extproc('close')
D handle 10i 0 value
D data s 65535a
D bytesRead s 10i 0
D handle s 10i 0
D rc s 10i 0
D O_RDONLY c 1
D O_TEXTDATA c 16777216
handle = open(%trim(pFile): O_RDONLY+O_TEXTDATA);
pSuccess = (handle > 0);
if pSuccess;
bytesRead = read(handle: %addr(data): %size(data));
pSuccess = (bytesRead >= 0);
rc = close(handle);
endif;
if pSuccess;
return %trimr(data);
endif;
P e
P LoadStmf b export
D pi
D inFile 256a varying
D ouValue 4096a varying
D inFileNull 5i 0
D ouNull 5i 0
D SqlState 5a
D FuncName 517a varying
D SpecificName 128a varying
D MsgText 70a varying
D success s n
D NullValue c const(-1)
D NonNullValue c const(0)
monitor;
ouValue = IFS_file2Var(inFile: success);
on-error;
success = *off;
endmon;
if success;
ouNull = NonNullValue;
else;
ouNull = NullValue;
endif;
P e
Here’s the prototype, in case you’re interested.
D IFS_file2Var pr 65535a varying
D pFile 256a value
D pSuccess n
D LoadStmf pr
D inFile 256a varying
D ouValue 4096a varying
D inFileNull 5i 0
D ouNull 5i 0
D SqlState 5a
D FuncName 517a varying
D SpecificName 128a varying
D MsgText 70a varying
From this source code, I created service program STMF. CRTRPGMOD MODULE(THOLT/STMF) SRCFILE(THOLT/QRPGLESRC) SRCMBR(STMF) CRTSRVPGM SRVPGM(STMF) MODULE(STMF) EXPORT(*ALL) Then I created an SQL function to run the LoadStmf subprocedure.
create or replace function LoadStmf
(inStmf varchar(256))
returns varchar(4096)
language rpgle
parameter style sql
not deterministic
no sql
returns null on null input
no external action
not fenced
no final call
allow parallel
no scratchpad
external name 'THOLT/STMF(LOADSTMF)'
To make the magic happen, I used PARAMETER STYLE SQL in the CREATE FUNCTION statement. (I have written about this before.) When using the SQL style to pass parameters, you specify the input parameter(s), the output value, a null indicator for each input parameter, and a null indicator for the output value. In the prototype, InFile is the only input parameter, ouValue is the return value, inFileNull is the null indicator for inFile, and ouNull is the null indicator for the output value. D LoadStmf pr D inFile 256a varying D ouValue 4096a varying D inFileNull 5i 0 D ouNull 5i 0 D SqlState 5a D FuncName 517a varying D SpecificName 128a varying D MsgText 70a varying A null indicator is a two-byte integer that indicates whether a parameter is null or not. A value of negative one means the parameter is null. A value of zero means the parameter is not null. If you want to know if a parameter is null, check the null value parameter that corresponds to it. In this example, I don’t want my subprocedure to handle a null stream file name, so I never check the value of inFileNull. I let RETURNS NULL ON NULL INPUT handle the case of a null file name. However, I do want the function to return a null if the specified stream file does not exist. In that case, I set ouNull to -1. Now I can do this:
select loadstmf('wisdom.txt') from qsys2.qsqptabl
And get this: LOADSTMF ==================================================== Time flies like an arrow. Fruit flies like a banana. Let me finish with one word of caution. Since the query engine has to run a subprocedure every time the function is called, performance may suffer. My RPG-based functions usually work well for me, but recently I put a function call in a join, and that did not work well at all. RELATED STORIES SQL Functions You Didn’t Know You Had, Part 1
|
