Guru: SUBSET on EXECUTE and OPEN
June 15, 2020 Paul Tuohy
The DB2 for i Enhancements in IBM i 7.3 Technology Refresh 8 / IBM i 7.4 Technology Refresh 2 just made dynamic SQL, embedded in RPG, a lot easier to use. It includes an enhancement entitled USING SUBSET on EXECUTE and OPEN with Extended indicators. This enhancement provides a simple and effective solution to a problem that I first highlighted in an article I wrote back in September of 2015. (See A First Look At SQL Descriptors.)
The “problem” we are dealing with is how to handle a variable number of host variables being used in a dynamic SQL statement. In the aforementioned article, I used the following SQL statement as an example:
select workDept, empno, firstname, lastname from employee
where workDept = ? and
hireDate >= ? and
birthDate >= ?
order by workDept, empno
The difficulty was that comparison values might or might not be provided for the department code, hire date and birth date. Therefore, the where clause (if it is to be included) might be against any combination of the three columns.
There were three possible coding solutions, each with its own caveats
- Use static SQL, which (most of the time) will result in the query engine performing unnecessary and inefficient work.
- Construct a WHERE clause that includes the comparison values, which results in convoluted, error-prone string handling with the (small) potential for an SQL injection attack.
- Use an SQL Descriptor, which (although better than the previous solutions) is not intuitive and requires a lot of attention to detail, especially for handling the data types of the host variables.
Code examples of these three solutions may be found in the A First Look At SQL Descriptors article. The new SUBSET option provides a much more eloquent solution.
SUBSET
The new SUBSET makes use of SQL indicators (an integer in RPG) associated with the host variable. You will have used one of these SQL indicators if you have ever dealt with null values for columns. Giving a value of -7 to an SQL indicator (for a host variable) is the equivalent of assigning an SQLIND_UNASSIGNED value to the variable.
This is an example of a program using dynamic SQL to generate the required SQL statement and using the new SUBSET option to assign the required values from host variables. The program defines a cursor for the generated statement and uses a multi row fetch to retrieve the rows. Please refer to the call outs in the code below.
dcl-s getRows int(10) inz(%elem(data));
dcl-s gotRows int(10);
dcl-ds data qualified dim(500);
workDept char(3);
empno char(6);
firstName varchar(12);
lastName varchar(15);
end-ds;
(A) dcl-s getDept char(3);
dcl-s getHired date;
dcl-s getBirth date;
(B) dcl-s getDept_ind int(5);
dcl-s getHired_ind int(5);
dcl-s getBirth_ind int(5);
(C) dcl-s myStatement varchar(2000);
dcl-s myWhere varchar(100);
dcl-s pad_And varchar(5);
dcl-C ADD_AND ' and ';
dcl-C ADD_WHERE ' where ';
(D) dcl-C ADD_DEPT ' workDept = ? ';
dcl-C ADD_HIRE ' hiredate >= ? ';
dcl-C ADD_BIRTH ' birthdate >= ? ';
if (getDept <> *blanks);
(E) myWhere = ADD_DEPT;
pad_And = ADD_AND;
getDept_ind = 0;
else;
(F) getDept_ind = -7;
endIf;
if (getHired <> d'0001-01-01');
(G) myWhere += pad_And + ADD_HIRE;
pad_And = ADD_AND;
getHired_ind = 0;
else;
getHired_ind = -7;
endIf;
if (getBirth <> d'0001-01-01');
(G) myWhere += pad_And + ADD_BIRTH;
pad_And = ADD_AND;
getBirth_ind = 0;
else;
getBirth_ind = -7;
endIf;
if (myWhere <> '');
myWhere = ADD_WHERE + myWhere;
endIf;
myStatement = 'select workDept, empno, firstname, lastname ' +
' from employee ' +
myWhere +
' order by workDept, empno';
exec SQL
prepare D1 from :myStatement;
if SQLCode = 0;
exec SQL
declare C1 scroll cursor for D1;
exec SQL
(H) open C1 using SUBSET :getDept :getDept_ind,
:getHired :getHired_ind,
:getBirth :getBirth_ind;
exec SQL
fetch first from C1 for :getRows rows into :data;
gotRows = SQLErrd(3);
exec SQL
close C1;
endIf;
*inLR = *on;
(A) Variables that may contain the required comparison values in the where clause. They would usually be passed as parameters.
(B) SQL indicators associated with the host variables that will be used in the OPEN statement.
(C) Work variables used in the construction of the dynamic SQL statement.
(D) Named constants are used to provide the components of the WHERE clause. Each component has the required parameter marker.
(E) If the statement is to include a comparison against the department code, then add the comparison to the WHERE clause and set the associated indicator to 0 (to indicate there is a value).
(F) If the statement will NOT include a comparison against the department code, then set the associated indicator to -7 (to indicate that the variable is to be ignored).
(G) Repeat the same process for the other two possible comparison values.
(H) When opening the cursor, you specify all of the host variables in the list and you precede that list with SUBSET. The host variables will have a value or they will be SQLIND_UNASSIGNED. Variables with SQLIND_UNASSIGNED are ignored for assignment. The important thing to note is the sequence of the host variable names must correspond to the sequence they assigned in the WHERE clause.
The new SUBSET feature make the proper construct (using parameter markers) of a dynamic SQL statement a lot easier and, therefore, makes dynamic SQL a lot easier to use.
Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.
Good article. Got a first use for it an stumbled already to a problem. When used as a method of filling a subfile and closing the cursor, even with the CLOSQLCSR(*ENDMOD), we are getting an SQL0501 error saying the cursor is still open or in use. Any idea of solving this.