A First Look At SQL Descriptors
September 15, 2015 Paul Tuohy
|
SQL descriptors allow for incredible flexibility when it comes to constructing dynamic SQL statements and/or processing the results of a dynamic SQL statement. But, since this is an introduction, let’s look at how they can be used in constructing dynamic SQL statements and leave their use in processing results for a later article. Assume we want to embed the following SQL statement in an RPG program:
select workDept, empno, firstname, lastname from employee
where workDept = ? and
hireDate >= ? and
birthDate >= ?
order by workDept, empno
The difficulty we have is that comparison values may or may 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. This difficulty can be tackled in one of three ways: 1. Use static SQL. 2. Construct a dynamic statement that includes the comparison values. 3. Construct a dynamic statement that uses parameter markers and an SQL descriptor. The following sample programs perform the same multi-row fetch for the required select statement. The difference in the programs is how the statement is constructed and executed. Using Static SQL The following piece of code shows a program using static SQL to perform the select. The solution is to always compare against the three columns regardless of whether or not a comparison value is provided. The test against the department code is for an equal comparison. Since it is not possible to have a generic “equal to any value”, a BETWEEN comparison is used instead of an equal comparison. Two work fields (p_fromDept and p_fromDept) are used to set the required range: Which will be lowest possible value to highest possible value if a department code is not requested; and from the requested department to the requested department if a department code is provided. It is a little simpler with the two dates since the comparison is for greater than or equal to. Just default the comparisons values to lowest possible date and use the requested date, if required.
d getRows s 10i 0 inz(%elem(data))
d gotRows s 10i 0
d data ds qualified dim(500)
d workDept 3a
d empno 6a
d firstName 12a varying
d lastName 15a varying
d getDept s 3a
d getHired s d
d getBirth s d
d setParm ds inz
d p_fromDept 3a inz(*loval)
d p_toDept 3a inz(*hiVal)
d p_hired d
d p_birth d
/free
if (getDept <> *blanks);
p_fromDept = getDept;
p_toDept = getDept;
endIf;
if (getHired <> d'0001-01-01');
p_hired = getHired;
endIf;
if (getBirth <> d'0001-01-01');
p_birth = getBirth;
endIf;
exec SQL
declare C1 scroll cursor for
select workDept, empno, firstname, lastname
from employee
where workdept between
:p_fromDept and :p_toDept and
hiredate >= :getHired and
birthdate >= :getBirth
order by workDept, empno ;
exec SQL
open C1 ;
exec SQL
fetch first from C1 for :getRows rows into :data;
gotRows = SQLErrd(3);
exec SQL
close C1;
*inLR = *on;
/end-Free
Although at first glance this may seem like an elegant solution, it isn’t. We are asking the query engine to perform unnecessary work:
Once upon a time, we might have been able to make an argument that using static SQL would be faster than using dynamic SQL, but that is no longer the case. So, on to the second solution. Construct a Dynamic Statement This next piece of code shows a program that constructs a dynamic SQL statement. The logic of the program constructs the required “where” clause, including the comparison values as part of the statement that is prepared.
d getRows s 10i 0 inz(%elem(data))
d gotRows s 10i 0
d data ds qualified dim(500)
d workDept 3a
d empno 6a
d firstName 12a varying
d lastName 15a varying
d getDept s 3a
d getHired s d
d getBirth s d
d myStatement s 2000a varying
d myWhere s 100a varying
d pad_And s 5a varying
d QUOTE c ''''
d ADD_AND c ' and '
d ADD_WHERE c ' where '
/free
if (getDept <> *blanks);
myWhere = 'workDept = ' + QUOTE + getDept + QUOTE + ' ';
pad_And = ADD_AND;
endIf;
if (getHired <> d'0001-01-01');
myWhere += pad_And + 'hiredate >= ' + QUOTE +
%char(getHired) + QUOTE + ' ';
pad_And = ADD_AND;
endIf;
if (getBirth <> d'0001-01-01');
myWhere += pad_And + 'birthdate >= ' + QUOTE +
%char(getBirth) + QUOTE + ' ';
pad_And = ADD_AND;
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
open C1 ;
exec SQL
fetch first from C1 for :getRows rows into :data;
gotRows = SQLErrd(3);
exec SQL
close C1;
endIf;
*inLR = *on;
/end-Free
While this is manageable, it is not the easiest code to write and debug. (I have lost count of the number of times I have left out a QUOTE). Although not in this case, there is also the potential for injection attacks if long generic host variables are used for the replacement values. Construct a Dynamic Statement Using an SQL Descriptor The preference with dynamic SQL statements is to use parameter markers whenever possible. In this case, the difficulty is that there may be one, two, three, or no parameter markers, and there is no consistency as to which marker is for which parameter. Add to this the difficulty that the number of host variables that would be provided on the OPEN statement would need to correspond to the number of markers in the statement. An SQL descriptor helps us overcome these problems. SQL descriptors once required the inclusion of a special data structure (the SQLDA) and a knowledge of the fields within the structure. But V5R4 introduced a much easier way of handling SQL descriptors. The basic principle of using an SQL descriptor with parameter markers is:
The important point to note is that the comparisons (if they are to be included) are added in sequence from left to right in the statement and that the corresponding values are added to the descriptor. The next piece of code shows a program that constructs a dynamic SQL statement using parameter markers and uses an SQL descriptor to provide the required parameter values. Please refer to the following callouts for details when looking at this next piece of code. A. Although a literal can be used for the descriptor name, it allows more flexibility if a host variable is used to provide the name. If we decide to change the name at a later stage, we only have to change it in one place. B. Named constants are used to provide the components of the WHERE clause. Each component has the required parameter marker–no need for those QUOTES. C. We ALLOCATE the descriptor and specify the maximum number of parameters–three in this case. D. If a comparison is to be made against the department code we add the comparison to the where clause and add the parameter value to the descriptor. E. The SET DESCRIPTOR statement sets a value for the required parameter number. This is the sequence of the marker in the constructed statement. The TYPE indicates the data type (1 is character). LENGTH is the length of the data being provided. Data is the value to be used in the comparison. F. Repeat the process for the Hire Date and Date of Birth. G. TYPE is 9 for a date/time/timestamp and DATE_TIME_INTERVAL_CODE of 1 indicates that the value provided is a date. H. SET DESCRIPTOR with COUNT is used to indicate how many parameters were added to the descriptor. I. When opening the cursor the “populated” SQL descriptor is used to provide the required parameter values. J. Deallocate the descriptor when we are finished with it.
d getRows s 10i 0 inz(%elem(data))
d gotRows s 10i 0
d data ds qualified dim(500)
d workDept 3a
d empno 6a
d firstName 12a varying
d lastName 15a varying
d getDept s 3a
d getHired s d
d getBirth s d
d myStatement s 2000a varying
d myWhere s 100a varying
d pad_And s 5a varying
d parmNo s 10i 0
(A) d descName s 20a varying inz('TEST_DESC')
d ADD_AND c ' and '
d ADD_WHERE c ' where '
(B) d ADD_DEPT c ' workDept = ? '
d ADD_HIRE c ' hiredate >= ? '
d ADD_BIRTH c ' birthdate >= ? '
/free
exec SQL
(C) allocate descriptor local :descName with max 3;
if (getDept <> *blanks);
(D) myWhere = ADD_DEPT;
pad_And = ADD_AND;
parmNo += 1;
exec SQL
set descriptor :descName
(E) value :parmNo type = 1,
length = 3,
data = :getDept;
endIf;
if (getHired <> d'0001-01-01');
(F) myWhere += pad_And + ADD_HIRE;
pad_And = ADD_AND;
parmNo += 1;
exec SQL
set descriptor :descName
(G) value :parmNo type = 9,
datetime_interval_code = 1,
data = :getHired;
endIf;
if (getBirth <> d'0001-01-01');
(F) myWhere += pad_And + ADD_BIRTH;
pad_And = ADD_AND;
parmNo += 1;
exec SQL
set descriptor :descName
(G) value :parmNo type = 9,
datetime_interval_code = 1,
data = :getBirth;
endIf;
if (parmNo > 0);
myWhere = ADD_WHERE + myWhere;
endIf;
myStatement = 'select workDept, empno, firstname, lastname ' +
' from employee ' +
myWhere +
' order by workDept, empno';
exec SQL
(H) set descriptor :descName count = :parmNo;
exec SQL
prepare D1 from :myStatement;
if SQLCode = 0;
exec SQL
declare C1 scroll cursor for D1;
exec SQL
(I) open C1 using SQL descriptor :descName;
exec SQL
fetch first from C1 for :getRows rows into :data;
gotRows = SQLErrd(3);
exec SQL
close C1;
endIf;
exec SQL
(J) deallocate descriptor local :descName;
*inLR = *on;
/end-Free
Although still somewhat cumbersome (as dynamic SQL always is), using an SQL descriptor allows us to code the use of a parameter and the provision of the value in one place. Some Other Considerations An SQL descriptor can be allocated as LOCAL, meaning it is scoped to the program, or GLOBAL, meaning it is scoped to the SQL session and may be used in other programs. Refer to the description of SET DESCRIPTOR in the SQL reference manual for a full list of the data type codes available for TYPE. This article showed how an SQL descriptor may be used to set variable parameters in a dynamic SQL statement. This is just one of the many uses of SQL descriptors: for example, they can also be used to retrieve information from a dynamic statement where the select clause is variable. But that is another article! Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.
|
