Select Statements
SELECT statements can only be executed dynamically by using a separate PREPARE statement and the dynamic cursor logic. The statements DECLARE CURSOR, OPEN, FETCH, and CLOSE must be used.
There are two ways to execute a SELECT statement dynamically. The method depends on the characteristics of the SELECT statements for processing:
If the derived column list of the SELECT statement has a constant format, that is, the number of elements in the resultant table and their data types remain constant, the fixed derived column list method can be used.
If the derived column list varies, the varying derived column list method must be used. In the latter case, an SQL descriptor area (SQLDA) is required.
Note:
It is not possible to execute dynamically a single-row SELECT.
Fixed Derived Column List Method
Dynamic SELECT statements with a fixed derived column list produce resultant tables which have a fixed layout, that is, the number of columns is the same and the data type of each column is fixed and known at the time the application program is precompiled.
The fixed derived column list method assumes that the dynamically created SELECT statements have a fixed derived column list, so that a normal FETCH statement can be used to retrieve the rows of the resultant table. This FETCH statement requires that the columns of the resultant table are each assigned to specific hardcoded host variables. As these host variables have to be known at precompilation time, the layout of the derived column list must be determined at the same time. All other clauses of the SELECT statement, the FROM clause, the WHERE clause, etc., can vary dynamically every time the statement is prepared. This means that the fixed derived column list method can be used in those cases where the result and format of the query is known, but the search criteria can vary to such a degree that the rest of the query needs to be constructed dynamically at runtime.
The fixed derived column list method consists of a number of steps:
PREPARE
The entire SELECT statement must be constructed in a host variable which is passed to CONNX Embedded SQL as a parameter of a PREPARE statement. The application needs to ensure that the resulting format of the query cannot vary dynamically. Example:
EXEC SQL
PREPARE statement_id FROM :dyn_sql_statement;
Where dyn_sql_statement is a character string containing the dynamic SQL statement.
As a CONNX Embedded SQL extension, a host variable can be used to identify a statement. If so, CONNX Embedded SQL returns a unique value in this variable which must be initialized with blanks upon return from the PREPARE statement. This value is then used for all subsequent references to the prepared statement. Example:
EXEC SQL
PREPARE :statement_id FROM :dyn_sql_statement;
DECLARE
The prepared statement must then be associated with a cursor. This can either be achieved explicitly, by means of a dynamic DECLARE CURSOR statement, or implicitly by an OPEN statement. The dynamic DECLARE CURSOR statement is similar to the static DECLARE, but instead of specifying the SELECT statement, it specifies the statement identifier as defined in the PREPARE statement, thus associating the prepared SELECT statement with the cursor. Such a DECLARE statement may also be executed prior to the associated PREPARE statement or may be omitted altogether, if the associated OPEN statement specifies the SQL statement identifier instead. Example:
EXEC SQL
DECLARE ABC CURSOR FOR statement_id;
Alternatively, a CONNX Embedded SQL extension allows a host variable to be used to identify the cursor. This host variable must be initialized with a suitable value by the application program before use. Example:
EXEC SQL
DECLARE :cursor_name CURSOR FOR STATEMENT_ID;
If a host variable was used to express the statement identifier in the original PREPARE statement, then a host variable containing the same assigned value must be used here in order to identify the statement. If used at all, the DECLARE statement must be executed after the PREPARE statement.
EXEC SQL
DECLARE ABC CURSOR FOR :statement_id;
The difference between the dynamic DECLARE CURSOR statement differs and its normal static counterpart is that the prepared statement is associated to the particular cursor. The order of execution is important in a dynamic SQL application. Once the PREPARE and then the DECLARE CURSOR statements have been successfully executed, other cursor associated statements can be executed in the normal way, except that the cursor may need to be expressed as a host variable. The normal OPEN, FETCH, CLOSE logic is still applicable.
OPEN
The cursor associated with the dynamic SELECT statement is opened by means of an OPEN statement. Note that the cursor name may be expressed as a host variable. Example:
EXEC SQL
OPEN ABC;
If the SELECT statement contains host variable markers, the parameters can be submitted by the USING clause or the USING DESCRIPTOR clause. For more information, see Using Host Variable Markers later in this section. Example:
EXEC SQL
OPEN ABC USING :hv1, :hv2;
You can also use an SQL descriptor area:
EXEC SQL
OPEN ABC USING DESCRIPTOR :input_sql_da;
In addition, an SQL statement identifier can be specified in case the DECLARE CURSOR statement has been omitted.
EXEC SQL
OPEN ABC CURSOR FOR :statement_id;
FETCH
As the format of the derived column list of the dynamic SELECT statement is constant, the FETCH statement can be identical to the static case. For each one of the columns in the resultant table, a host variable needs to be specified which is of a compatible data type.
Although the format of the derived column list does not vary dynamically, it is still not visible to the CONNX Embedded SQL. Therefore, the compiler cannot check the validity of the FETCH statement and in particular its target buffer list. Naturally, at run time, such checks are performed.
An attempt to fetch a derived column of type binary, using a dynamically prepared SELECT statement and a FETCH statement which is identical to the static counterpart, will always result in an error condition. This is because, upon precompiling the FETCH statement, the fact that a character host variable is going to be used for the retrieval of a derived column of type binary is not foreseeable. If a derived column of type binary is to be retrieved using a dynamically prepared select statement, even if has a fixed derived column list, then a FETCH statement which uses a descriptor area must be used. Example:
EXEC SQL
FETCH ABC INTO :hv1, :hv3;
CLOSE
The closing of the cursor is identical to the static case. By executing the CLOSE statement, all resources reserved by the cursor are released. Example:
EXEC SQL
CLOSE ABC;
Likewise, once closed, the cursor may simply be re-opened again.
Summary
A program which issues dynamic fixed derived column list SELECT statements must include the following steps:
1. Construct the dynamic SELECT statement. The statement is constructed as a character string in a similar fashion to NON-SELECT dynamic statements. However, the derived column list must remain fixed and its format must have been determined at compile time.
2. PREPARE the dynamic SQL statement.
3. Optionally, DECLARE a cursor for the prepared statement using a dynamic DECLARE CURSOR statement.
4. OPEN the cursor in a similar way to a normal static cursor.
5. Variable input values, as specified by a host variable marker "?" may have to be provided by using an USING clause appended to the OPEN statement and specifying an SQLDA.
6. FETCH from the cursor as required until all rows have been processed.
7. CLOSE the cursor.
Varying Derived Column List Method
Dynamic SELECT statements with a varying derived column list are SELECT statements which produce resultant tables which have differing formats, that is, the format of the resultant table is specified dynamically and may vary from instance to instance.
This method is more complicated than the one of using a fixed derived column list but is only required if indeed the format of the possible resultant tables can vary. Otherwise the fixed derived column list method may be used. In order to be able to use the varying list method, the application program must be able to acquire dynamic storage and be able to manipulate pointers or addresses. This obviously limits the use of this method to those host languages which provide these facilities, or specially written subroutines are needed.
The application program needs to get information about the layout of the resultant table for a varying derived column list statement as target buffers must be dynamically provided. CONNX Embedded SQL provides special functions to aid the application program in this task. This information is passed to the program using an SQL descriptor area or an SQLDA.
PREPARE
The SELECT statement must be constructed in a host variable which is passed on to CONNX Embedded SQL as a parameter to a PREPARE statement. Example:
EXEC SQL
PREPARE statement_id FROM :dyn_sql_statement;
Alternatively, a CONNX Embedded SQL extension allows a host variable to be used to identify the statement. If so, CONNX Embedded SQL returns a unique value in this variable which must have been initialized with blanks upon return from the PREPARE statement. This value will be used for all subsequent references to the prepared statement. Example:
EXEC SQL
PREPARE :statement_id FROM :dyn_sql_statement;
DECLARE
The prepared statement must then be associated with a cursor. This can either be achieved explicitly by means of a dynamic DECLARE CURSOR statement or implicitly by an OPEN statement. The dynamic DECLARE CURSOR statement is similar to the static DECLARE, but instead of specifying the SELECT statement, it specifies the statement identifier as defined in the PREPARE statement, thus associating the prepared SELECT statement with the cursor. Such a DECLARE statement may also be executed prior to the associated PREPARE statement or may be omitted altogether, if the associated OPEN statement specifies the SQL statement identifier instead. Example:
EXEC SQL
DECLARE ABC CURSOR FOR statement_id;
Alternatively, a CONNX Embedded SQL extension allows a host variable to be used to identify the cursor. This host variable must be initialized with a suitable value by the application program before use.
EXEC SQL
DECLARE :cursor_name CURSOR FOR statement_id;
If in the original PREPARE statement, a host variable was used to express the statement identifier, then a host variable containing the same assigned value must be used here in order to identify the statement. If used at all, the DECLARE statement must be executed after the PREPARE statement. Example:
EXEC SQL
DECLARE ABC CURSOR FOR :statement_id;
DESCRIBE
A description of the resulting format of the query may now be retrieved from CONNX Embedded SQL. This is done using an SQLDA and a DESCRIBE statement. The functionality of the DESCRIBE statement can also be achieved by using an INTO clause in the PREPARE statement. Example:
EXEC SQL
DESCRIBE STATEMENT_ID INTO :output_sqlda;
After successful execution of the DESCRIBE statement, the SQLDA contains detailed information concerning the resulting format of the SELECT statement. The total number of columns and the particular type of each column will be supplied. The application program must act on this information by supplying dynamically an appropriate target buffer for each of the columns. The address of each target buffer must be written into the SQLDA. In addition, an associated indicator value may have to be assigned.
OPEN
The cursor associated with the dynamic SELECT statement is opened by means of an OPEN statement. The cursor name may be expressed as a host variable. Example:
EXEC SQL
OPEN ABC;
If the SELECT statement contained host variable markers, the parameters can be submitted by the USING clause or the USING DESCRIPTOR clause. For more information, see Using Host Variable Markers later in this section. Example:
EXEC SQL
OPEN ABC USING :hv1, :hv2;
You can also use an SQL input descriptor area:
EXEC SQL
OPEN ABC USING DESCRIPTOR :input_sqlda;
In addition, an SQL statement identifier can be specified in case the DECLARE CURSOR statement has been omitted.
EXEC SQL
OPEN ABC CURSOR FOR :statement_id
USING DESCRIPTOR :input_sqlda;
FETCH
The FETCH statement must be executed in conjunction with the SQLDA that has been constructed for this particular dynamic SELECT statement. The resulting values are copied into the locations specified in the corresponding column description in the SQLDA. Note that CONNX Embedded SQL can only assume that such locations are of sufficient size to accommodate the returned data. It is the responsibility of the application program to provide such locations. Using a DESCRIBE statement greatly simplifies this task.
EXEC SQL
FETCH ABC USING DESCRIPTOR :output_sqlda;
CLOSE
The CLOSE statement causes all resources reserved by the cursor to be released.
EXEC SQL
CLOSE ABC;
Once closed, the cursor may be re-opened again within the current transaction.
Summary
A program which issues dynamic varying derived column list SELECT statements must include the following steps:
1. Construct the dynamic SELECT statement. The statement is constructed as a character string in a similar fashion to NON-SELECT dynamic statements.
2. PREPARE the dynamic SQL statement.
3. Allocate and build an appropriate SQLDA. This may be done using a DESCRIBE statement. Assign appropriate target buffers.
4. Optionally, DECLARE a cursor for the prepared statement using a dynamic DECLARE CURSOR statement.
5. OPEN the cursor in a similar way to a normal static cursor.
6. Variable input values, as specified by a host variable marker "?", may have to be provided by supplying a USING clause appended to the OPEN statement specifying an SQLDA.
7. FETCH from the cursor as required until all rows have been processed. The output SQLDA must be specified in order to receive retrieved data.
8. CLOSE the cursor.