With static SQL, host variables used in embedded SQL statements are known at application compile time. With dynamic SQL, the embedded SQL statements and consequently the host variables are not known until application run time. Therefore, for dynamic SQL applications, you must preprocess the list of host variables that are used in your application.
You can use the DESCRIBE statement to obtain host variable information for any SELECT statement that has been prepared (using PREPARE), and store that information into the SQL descriptor area (SQLDA).
When the DESCRIBE statement gets executed in your application, the database manager defines your host variables in an SQLDA. Once the host variables are defined in the SQLDA, you can use the FETCH statement to assign values to the host variables, using a cursor.
- Declaring the SQLDA structure in a dynamically executed SQL program
An SQLDA contains a
variable number of occurrences of SQLVAR entries, each of which contains
a set of fields that describe one column in a row of data. There are
two types of SQLVAR entries: base SQLVAR entries and secondary SQLVAR
entries. - Preparing a dynamically executed SQL statement using the minimum SQLDA structure
Use the information provided here as an example of how
to allocate the minimum SQLDA structure for a statement. - Allocating an SQLDA structure with sufficient SQLVAR entries for dynamically executed SQL statements
After you determine
the number of columns in the result table, you must allocate storage
for a second full-size SQLDA. The first SQLDA is used for input parameters
and the second full-size SQLDA is used for output parameters. - Describing a SELECT statement in a dynamically executed SQL program
After you allocate sufficient space for the second SQLDA
(in this example, calledfulsqlda), you must code
the application to describe the SELECT statement. - Acquiring storage to hold a row
Before the application can fetch a row of the result table
using an SQLDA structure, the application must first allocate storage
for the row. - Processing the cursor in a dynamically executed SQL program
After you allocate the
SQLDA structure, you can open the cursor associated with the SELECT
statement and fetch rows. - Allocating an SQLDA structure for a dynamically executed SQL program
Allocate an SQLDA structure for your application so that
you can use it to pass data to and from your application. - Transferring data in a dynamically executed SQL program using an SQLDA structure
You have greater flexibility
when you transfer data using an SQLDA instead of using lists of host
variables. For example, you can use an SQLDA to transfer data that
has no native host language equivalent, such as DECIMAL data in the
C language. - Processing interactive SQL statements in dynamically executed SQL programs
You can write an application
using dynamic SQL to process arbitrary SQL statements. For example,
if an application accepts SQL statements from a user, the application
must be able to issue the statements without any prior knowledge of
the statements. - Determination of statement type in dynamically executed SQL programs
When an SQL statement
is prepared, you can determine information concerning the type of
statement by examining the SQLDA structure. This information is placed
in the SQLDA structure either at statement preparation time with the
INTO clause, or by issuing a DESCRIBE statement against a previously
prepared statement. - Processing variable-list SELECT statements in dynamically executed SQL programs
A varying-list SELECT
statement is one in which the number and types of columns that are
to be returned are not known at precompilation time. - Saving SQL requests from end users
If the users of your application can issue SQL requests
from the application, you might want to save these requests.