5. Working with Embedded SQL : Dynamic Programming : Execute a Dynamic Select Statement : Prepare and Describe Select Statements
 
Share this page                  
Prepare and Describe Select Statements
If the program has no advance knowledge of the resulting columns, the first step in executing a dynamic SELECT statement is to prepare and describe the statement. Preparing the statement encodes and saves the statement and assigns it a name. For information about the syntax and use of PREPARE, see Prepare and Execute Statements (see page Prepare and Execute Statements) in this chapter.
The DESCRIBE statement returns descriptive information about a prepared statement into a program descriptor, that is, an SQLDA structure. This statement is primarily used to return information about the result columns of a SELECT statement to the program; however, it is also possible to describe other statements. (When a non-select statement is described, the only information returned to the program is that the statement was not a SELECT statement.)
The syntax of the DESCRIBE statement is:
EXEC SQL DESCRIBE statement_name INTO|USING descriptor_name;
When a SELECT statement is described, information about each result column is returned to an sqlvar element. (For information about sqlvar elements, see Structure of the SQLDA (see page Structure of the SQLDA).) This is a one-to-one correspondence: the information in one sqlvar element corresponds to one result column. Therefore, before issuing the DESCRIBE statement, the program must allocate sufficient sqlvar elements and set the SQLDA sqln field to the number of allocated sqlvars. The program must set sqln before the DESCRIBE statement is issued.
After issuing the DESCRIBE statement, the program must check the value of sqld, which contains the number of sqlvar elements actually used to describe the statement. If sqld is zero, the prepared statement was not a SELECT statement. If sqld is greater than sqln, the SQLDA does not have enough sqlvar elements: more storage must be allocated and the statement must be re-described.
The following example shows a typical DESCRIBE statement and the surrounding host program code. The program assumes that 20 sqlvar elements are sufficient:
sqlda.sqln = 20;
exec sql describe s1 into sqlda;
if (sqlda.sqld = 0) then

     statement is not a select statement;

else if (sqlda.sqld > sqlda.sqln) then

     save sqld;
     free current sqlda;
     allocate new sqlda using sqld as the size;
     sqlda.sqln = sqld;
     exec sql describe s1 into sqlda;

end if;