A Second Look At SQL Descriptors
July 19, 2016 Paul Tuohy
|
Note: The code accompanying this article is available for download here. My previous article, A First Look at SQL Descriptors, looked at how SQL descriptors can be used in constructing and processing dynamic SQL statements. This article examines how SQL descriptors can be used in processing the information returned through a dynamic SQL statement. As an example, we will write a program that dynamically constructs the select clause in an SQL select statement. Traditionally, this would cause us a problem in our RPG programs since we would not be able to code the INTO clause correctly. We have no idea which columns we would be selecting, so an SQL descriptor comes to the rescue. In reality, the SQL statement we would be constructing would be a lot more complicated than just a dynamic select clause but, for the sake of clarity, we will just concentrate on the select clause. Normally, the scenario would be where the select statement could be selecting from different tables or views and there could be different columns names on each table or view – a truly dynamic statement. Our sample program is going to select data from the employee table. Our final dynamic SQL statement will have the following format: select workDept, empno, firstnme, lastname from employee where workDept = ? The select clause can have any permutation and combination of the 14 columns empNo, firstName, midInit, lastName, workDept, phoneNo, hireDate, job, edLevel, sex, birthDate, salary, bonus, and comm. The sample program will prompt for a department code and a list of values to identify the columns that are to be retrieved: this list is simply a single character identifier for each column. The program then constructs a dynamic SQL statement, runs it and, for each row in the result set, retrieves the requested columns. The D Specs The following piece of code shows the global variables used in the program. Refer to the callouts below the code for more detail.
(A) d doVoodoo pr
d forDescriptor 20a varying
d forColIn 13a const
d forDept 3a const
d setColumns pr 1000a varying
d forColIn 13a const
d getRowData pr likeDS(b_getVars)
d forDescriptor 20a varying
d numColumns 10i 0 const
(B) d getDept s 3a
d getColumns s 13a
(C) d useDescriptor s 20a varying inz('TEST_DESC')
(D) d b_getVars ds template qualified
d empNo 6a
d firstName 12a varying
d midInit 1a
d lastName 15a varying
d workDept 3a
d phoneNo 4a
d hireDate d datFmt(*ISO)
d job 8a
d edLevel 10i 0
d sex 1a
d birthDate d datFmt(*ISO)
d salary 9p 2
d bonus 9p 2
d comm 9p 2
d ds
(E) d colList 140a inz('EMPNO FIRSTNME MIDINIT +
d LASTNAME WORKDEPT PHONENO +
d HIREDATE JOB EDLEVEL +
d SEX BIRTHDATE SALARY +
d BONUS COMM ')
(F) d colNames 10a overLay(colList) dim(14)
(G) d codeList 14a inz('123456789abcd')
(H) d codes 1a overlay(codeList) dim(14)
A. Prototypes for three subprocedures used in the program. B. Work fields for the selected department code and column selection list. C. The name of the SQL descriptor we will be using in the program. D. A template for all of the columns that can be retrieved from the select statement. In this example, it is simply every column in the employee table. It is very important that the definition of these fields match the definition of the columns in the table (more about this later). E. The list of column names that will be used in constructing the select clause. F. An array of column names. The array overlays the list of names defined at D. So, EMPNO is the value of element 1, FIRSTNME is the value of element 2, etc. G. The list of codes that can be entered to identify a column. 1 is EMNO, 2 is FIRSTNME, a is SEX, b is BIRTHDATE, etc. H. An array of column identification codes. The array overlays the list of codes defined at F. Do, 1 is the value of element 1, 2 is the value of element 2, etc. The Mainline The next piece of code shows the mainline of the sample program (again, refer to the callouts below).
/free
(A) exec SQL
allocate descriptor local :useDescriptor with max 14;
getDept = 'A00';
(B) dsply 'Select Department: ' ' ' getDept;
(C) doU (getColumns = 'x');
dsply 'Select Columns: ' ' ' getColumns;
(D) getColumns = %xlate('ABCDX': 'abcdx': getColumns);
if (getColumns <> 'x');
(E) doVoodoo(useDescriptor: getColumns: getDept);
endIf;
endDo;
(F) exec SQL
deallocate descriptor local :useDescriptor;
*inLR = *on;
/end-Free
A. We start by allocating the descriptor we will be using to when retrieving information. The maximum number of items the descriptor will be handling is 14, the number of columns in the table. B. Prompt for the department code to use when selecting rows. C. Keep looping until a value of ‘x’ is entered for the column selection. D. Prompt for the column selection and ensure that any of the required codes that might have been inadvertently entered in upper case are converted to lower case. The column selection is simply the list of required columns (as per codelist at (G) in Figure 1). E. Call the procedure to process the entered list. F. Since we are now finished with the descriptor, we can deallocate it. The setColumns() Procedure The setColumns() procedure, shown in the next clip of code, converts the column selection list (passed as a parameter) to a list of column names (returned value) to be used in a SELECT clause. The list of column names is created by looping through the selected codes and, for each code found in the forCol array, adds the corresponding element from the colNames array. A comma is added before every column name except the first.
p setColumns b
d pi 1000a varying
d forColIn 13a const
d columns s 1000a varying
d forCol s 13a varying
d numCols s 10i 0
d i s 10i 0
d pos s 10i 0
/free
forCol = %trim(forColIn);
numCols = %len(forCol);
for i = 1 to numCols;
pos = %lookup(%subst(forCol: i : 1): codes);
if (pos > 0);
if (columns <> '');
columns += ', ';
endIf;
columns += %trim(colNames(pos));
endIf;
endFor;
return columns;
/end-Free
p e
The doVoodoo() Procedure The doVoodoo() procedure, shown next, constructs, prepares, runs, and processes the required select statement (refer to the callouts):
p doVoodoo b
d pi
(A) d forDescriptor 20a varying
d forColIn 13a const
d forDept 3a const
d myStatement s 2000a varying
d numColumns s 10i 0
d i s 10i 0
(B) d getVars ds likeDS(b_getVars)
/free
(C) myStatement = 'select ' + setColumns(forColIn) +
' from employee ' +
' where workDept = ? ';
exec SQL
prepare D1 from :myStatement;
if SQLCode = 0;
(D) exec SQL
describe D1 using SQL descriptor local :forDescriptor;
(E) exec SQL
get descriptor :forDescriptor :numColumns = COUNT;
(F) exec SQL
declare C1 scroll cursor for D1;
exec SQL
open C1 using :forDept;
(G) exec SQL
fetch first from C1 into SQL descriptor :forDescriptor;
doW (SQLCode >= 0 and SQLCode <> 100);
(H) getVars = getRowData(forDescriptor: numColumns);
// Here - do the voodoo that you do so well
(I) dsply (getVars.empno + getVars.firstName +
%char(getVars.hireDate));
(G) exec SQL
fetch next from C1 into SQL descriptor :forDescriptor;
endDo;
exec SQL
close C1;
endIf;
/end-Free
p e
A. The procedure is passed the name of the SQL descriptor, the columns selection list and the department code to be used in the WHERE clause. B. The contents of the getVars data structure will be populated for each row retrieved. C. The SQL SELECT statement is constructed and prepared. A call is made to setColumns() to construct the contents of the select clause from the columns selection list. D. The SQL DESCRIBE statement is used to populate the descriptor with information about the prepared SELECT statement. E. The SQL GET DESCRIPTOR statement is used to determine how many columns will be returned on each FETCH. The COUNT variable identifies the number of items in the descriptor. F. We now have the standard process of declaring a cursor, opening the cursor, looping through a fetch of the rows in the result set and closing the cursor. But there is a difference with the fetch. G. When we fetch a row, we must fetch it into the descriptor. Remember, we have no idea how the select clause is constructed, so we cannot fetch directly into host variables or a host structure. H. Call the getRowData() procedure for each row fetched. getRowData() will determine which columns were fetched and assign them to the corresponding columns in the getVars data structure. I. Now do whatever needs to be done with the data from the row just fetched. In this example, the program is displaying the value of three of the columns, regardless of whether or those columns were retrieved. The getRowData() Procedure The getRowData() procedure, shown in the next piece of code, determines which columns were fetched and assign them to the corresponding columns in a getVars data structure, which is then returned to the caller (refer to the callouts):
p getRowData b
(A) d pi likeDS(b_getVars)
d forDescriptor 20a varying
d numColumns 10i 0 const
d colIs s 10a
d i s 10i 0
(B) d getVars ds likeDS(b_getVars) inz
/free
(C) for i = 1 to numColumns;
(D) exec SQL
get descriptor :forDescriptor
value :i :colIs = DB2_SYSTEM_COLUMN_NAME;
(E) if (colIs = colNames(1));
exec SQL
get descriptor :forDescriptor
value :i :getvars.empno = DATA;
elseIf (colIs = colNames(2));
exec SQL
get descriptor :forDescriptor
value :i :getvars.firstName = DATA;
elseIf (colIs = colNames(3));
exec SQL
get descriptor :forDescriptor
value :i :getvars.midInit = DATA;
elseIf (colIs = colNames(4));
exec SQL
get descriptor :forDescriptor
value :i :getvars.lastName = DATA;
elseIf (colIs = colNames(5));
exec SQL
get descriptor :forDescriptor
value :i :getvars.workDept = DATA;
elseIf (colIs = colNames(6));
exec SQL
get descriptor :forDescriptor
value :i :getvars.phoneNo = DATA;
elseIf (colIs = colNames(7));
exec SQL
get descriptor :forDescriptor
value :i :getvars.hireDate = DATA;
elseIf (colIs = colNames(8));
exec SQL
get descriptor :forDescriptor
value :i :getvars.job = DATA;
elseIf (colIs = colNames(9));
exec SQL
get descriptor :forDescriptor
value :i :getvars.edLevel = DATA;
elseIf (colIs = colNames(10));
exec SQL
get descriptor :forDescriptor
value :i :getvars.sex = DATA;
elseIf (colIs = colNames(11));
exec SQL
get descriptor :forDescriptor
value :i :getvars.birthDate = DATA;
elseIf (colIs = colNames(12));
exec SQL
get descriptor :forDescriptor
value :i :getvars.salary = DATA;
elseIf (colIs = colNames(13));
exec SQL
get descriptor :forDescriptor
value :i :getvars.bonus = DATA;
elseIf (colIs = colNames(14));
exec SQL
get descriptor :forDescriptor
value :i :getvars.comm = DATA;
endIf;
endFor;
return getVars;
/end-Free
p e
A. The procedure is passed the name of the SQL descriptor an the number of columns to process. A getVars data structure is returned. B. The getVars is defined and initialized within the procedure. It is very important that the contents of the data structure are initialized on each call since there is no guarantee that all columns will be populated by a fetch. C. The procedure processes each of the columns returned. D. Use the SQL GET DESCRIPTOR statement to get the name of the column being processed. “Value i” identifies the item in the descriptor to be processed and DB2_SYSTEM_COLUMN_NAME indicates that it is the system column name (the short 10 character name as opposed to the long SQL name) that is to be placed in the variable colIs. E. Based on the value of the variable colIs, use an SQL GET DESCRIPTOR statement to retrieve the column value into the relevant column in the getVars data structure. DATA indicates that it is the retrieved column data that is required. Now we see why it is important that the definition of the fields in the getVars data structure match the definition of the columns in the table since the assignment of the value is directly from an SQL statement. Underneath the covers, this means that the data is returned as a parameter from a called program, so we must make sure that the types match. More Detail On The Column Information It is possible to be more generic when retrieving column information. In this example, we are simply using the column name to determine the data type and length we are processing. But, as shown in the following piece of code, we could use an SQL GET DESCRIPTOR statement to get the data type.
exec SQL
get descriptor :forDescriptor
value :i :type = TYPE,
:length = LENGTH,
:dateTime = DATETIME_INTERVAL_CODE;
Refer to the description of GET DESCRIPTOR n the SQL reference manual for a full list of the information (it is a big list) that can be retrieved from a descriptor. Just A Taste Remember, the construct, and the decisions on how to construct, the select statement would normally be a lot more complex than in the example presented here – such as selecting from different tables or views where there could be different columns names on each table or view and different where conditions, etc. Hopefully, this article and the preceding article have given you an idea of how, even though the coding may be somewhat cumbersome, SQL DESCRIPTORS can offer you a lot of flexibility in your programs. 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. RELATED STORY A First Look at SQL Descriptors
|
