DB2 UDB for iSeries SQL: Select-statement for allocating storage for SQLDA

Example: A SELECT statement for allocating storage for SQLDA

Suppose that your application needs to handle a dynamic SELECT statement that changes from one use to the next. This statement can be read from a display, passed in from another application, or built dynamically by your application.
In other words, you don't know exactly what this statement is going to be returning every time. Your application needs to handle the varying number of result columns with data types that are unknown ahead of time.
For example, the following statement needs to be processed:
   SELECT WORKDEPT, PHONENO 
     FROM CORPDATA.EMPLOYEE      WHERE LASTNAME = 'PARKER'
This SELECT statement has no INTO clause. Dynamic SELECT statements must not have an INTO clause, even if they return only one row.
The statement is assigned to a host variable. The host variable, in this case named DSTRING, is then processed by using the PREPARE statement as shown:
EXEC SQL PREPARE S1 FROM :DSTRING;
Next, determine the number of result columns and their data types. To do this, you need an SQLDA.
The first step in defining an SQLDA is to allocate storage for it. (Allocating storage is not necessary in REXX.) The techniques for acquiring storage are language-dependent. The SQLDA must be allocated on a 16-byte boundary. The SQLDA consists of a fixed-length header that is 16 bytes in length. The header is followed by a varying-length array section (SQLVAR), each element of which is 80 bytes in length.
The amount of storage that allocate depends on how many elements you want to have in the SQLVAR array. Each column you select must have a corresponding SQLVAR array element. Therefore, the number of columns listed in your SELECT statement determines how many SQLVAR array elements you should allocate. Because this SELECT statement is specified at run time, it is impossible to know exactly how many columns will be accessed. Consequently, estimate the number of columns. Suppose, in this example, that no more than 20 columns are ever expected to be accessed by a single SELECT statement. In this case, the SQLVAR array should have a dimension of 20, ensuring that each item in the select-list has a corresponding entry in SQLVAR. This makes the total SQLDA size 20 x 80, or 1600, plus 16 for a total of 1616 bytes
Having allocated what you estimated to be enough space for your SQLDA, set the SQLN field of the SQLDA equal to the number of SQLVAR array elements, in this case 20.
Having allocated storage and initialized the size, you can now issue a DESCRIBE statement.
EXEC SQL DESCRIBE S1 INTO :SQLDA;
When the DESCRIBE statement is run, SQL places values in the SQLDA that provide information about the select-list for your statement. The following tables show the contents of the SQLDA after the DESCRIBE is run. Only the entries that are meaningful in this context are shown.

Table 1. SQLDA header
Description Value

SQLAID 'SQLDA'
SQLDABC 1616
SQLN 20
SQLD 2

SQLDAID is an identifier field initialized by SQL when a DESCRIBE is run. SQLDABC is the byte count or size of the SQLDA. The SQLDA header is followed by 2 occurrences of the SQLVAR structure, one for each column in the result table of the SELECT statement being described:

Table 2. SQLVAR element 1
Description Value

SQLTYPE 453
SQLLEN 3
SQLDATA (3:4) 37
SQLNAME 8 WORKDEPT

Table 3. SQLVAR element 2
Description Value

SQLTYPE 453
SQLLEN 4
SQLDATA(3:4) 37
SQLNAME 7 PHONENO

Your program might need to alter the SQLN value if the SQLDA is not large enough to contain the described SQLVAR elements. For example, suppose that instead of the estimated maximum of 20 columns, the SELECT statement actually returns 27. SQL cannot describe this select-list because the SQLVAR needs more elements than the allocated space allows. Instead, SQL sets the SQLD to the actual number of columns specified by the SELECT statement and the remainder of the structure is ignored. Therefore, after a DESCRIBE, you should compare the SQLN value to the SQLD value. If the value of SQLD is greater than the value of SQLN, allocate a larger SQLDA based on the value in SQLD, as follows, and perform the DESCRIBE again:
EXEC SQL     DESCRIBE S1 INTO :SQLDA;
IF SQLN <= SQLD THEN DO;
 
/*Allocate a larger SQLDA using the value of SQLD.*/
/*Reset SQLN to the larger value.*/
 
EXEC SQL     DESCRIBE S1 INTO :SQLDA;
END;
If you use DESCRIBE on a non-SELECT statement, SQL sets SQLD to 0. Therefore, if your program is designed to process both SELECT and non SELECT statements, you can describe each statement after it is prepared to determine whether it is a SELECT statement. This example is designed to process only SELECT statements; the SQLD value is not checked.
Your program must now analyze the elements of SQLVAR returned from the successful DESCRIBE. The first item in the select-list is WORKDEPT. In the SQLTYPE field, the DESCRIBE returns a value for the data type of the expression and whether nulls are applicable or not.
In this example, SQL sets SQLTYPE to 453 in SQLVAR element 1. This specifies that WORKDEPT is a fixed-length character string result column and that nulls are permitted in the column.
SQL sets SQLLEN to the length of the column. Because the data type of WORKDEPT is CHAR, SQL sets SQLLEN equal to the length of the character column. For WORKDEPT, that length is 3. Therefore, when the SELECT statement is later run, a storage area large enough to hold a CHAR(3) string will be needed.
Because the data type of WORKDEPT is CHAR FOR SBCS DATA, the first 4 bytes of SQLDATA were set to the CCSID of the character column.
The last field in an SQLVAR element is a varying-length character string called SQLNAME. The first 2 bytes of SQLNAME contain the length of the character data. The character data itself is typically the name of a column used in the SELECT statement, in this case WORKDEPT. The exceptions to this are select-list items that are unnamed, such as functions (for example, SUM(SALARY)), expressions (for example, A+B-C), and constants. In these cases, SQLNAME is an empty string. SQLNAME can also contain a label rather than a name. One of the parameters associated with the PREPARE and DESCRIBE statements is the USING clause. You can specify it this way:
EXEC SQL     DESCRIBE S1 INTO:SQLDA         USING LABELS;
If you specify:

NAMES (or omit the USING parameter entirely)

Only column names are placed in the SQLNAME field.

SYSTEM NAMES

Only the system column names are placed in the SQLNAME field.

LABELS

Only labels associated with the columns listed in your SQL statement are entered here.

ANY

Labels are placed in the SQLNAME field for those columns that have labels; otherwise, the column names are entered.

BOTH

Names and labels are both placed in the field with their corresponding lengths. Remember to double the size of the SQLVAR array because you are including twice the number of elements.

ALL

Column names, labels, and system column names are placed in the field with their corresponding lengths. Remember to triple the size of the SQLVAR array

In this example, the second SQLVAR element contains the information for the second column used in the select: PHONENO. The 453 code in SQLTYPE specifies that PHONENO is a CHAR column. SQLLEN is set to 4.
Now set up to use the SQLDA to retrieve values when running the SELECT statement.
After analyzing the result of the DESCRIBE, you can allocate storage for variables that are to contain the result of the SELECT statement. For WORKDEPT, a character field of length 3 must be allocated; for PHONENO, a character field of length 4 must be allocated. Since both of these results can be the NULL value, an indicator variable must be allocated for each field as well.
After the storage is allocated, set SQLDATA and SQLIND to point to the allocated storage areas. For each element of the SQLVAR array, SQLDATA points to the place where the result value is to be put. SQLIND points to the place where the null indicator value is to be put. The following tables show what the structure looks like now. Only the entries that are meaningful in this context are shown:

Table 4. SQLDA header
Description Value

SQLAID 'SQLDA'
SQLDABC 1616
SQLN 20
SQLD 2

Table 5. SQLVAR element 1
Description Value

SQLTYPE 453
SQLLEN 3
SQLDATA Pointer to area for CHAR(3) result
SQLIND Pointer to 2 byte integer indicator for result column

Table 6. SQLVAR element 2
Description Value

SQLTYPE 453
SQLLEN 4
SQLDATA Pointer to area for CHAR(4) result
SQLIND Pointer to 2 byte integer indicator for result column

You are now ready to retrieve the SELECT statements results. Dynamically defined SELECT statements must not have an INTO statement. Therefore, all dynamically defined SELECT statements must use a cursor. Special forms of the DECLARE, OPEN, and FETCH are used for dynamically defined SELECT statements.
The DECLARE statement for the example statement is:
EXEC SQL DECLARE C1 CURSOR FOR S1;
As you can see, the only difference is that the name of the prepared SELECT statement (S1) is used instead of the SELECT statement itself. The actual retrieval of result rows is made as follows:
EXEC SQL     OPEN C1;
EXEC SQL     FETCH C1 USING DESCRIPTOR :SQLDA;
DO WHILE (SQLCODE = 0);
/*Process the results pointed to by SQLDATA*/
EXEC SQL     FETCH C1 USING DESCRIPTOR :SQLDA;
END;
EXEC SQL     CLOSE C1;
The cursor is opened. The result rows from the SELECT are then returned one at a time using a FETCH statement. On the FETCH statement, there is no list of output host variables. Instead, the FETCH statement tells SQL to return results into areas described by your SQLDA. The results are returned into the storage areas pointed to by the SQLDATA and SQLIND fields of the SQLVAR elements. After the FETCH statement has been processed, the SQLDATA pointer for WORKDEPT has its referenced value set to 'E11'. Its corresponding indicator value is 0 since a non-null value was returned. The SQLDATA pointer for PHONENO has its referenced value set to '4502'. Its corresponding indicator value is also 0 since a non-null value was returned.

Parent topic:
Processing SELECT statements and using a descriptor

Related reference

Varying-list SELECT statements
SQLDA format

Description	Value
SQLTYPE	453
SQLLEN	3
SQLDATA	Pointer to area for CHAR(3) result
SQLIND	Pointer to 2 byte integer indicator for result column