Filter
Top Products
Chapter 7 Executing Dynamic SQL Statements
When Application Programs are Executed
This chapter describes the functions and uses of dynamic SQL.
This chapter consists of the following sections:
7.1 Overview of Dynamic SQL
7.2 Dynamically Modifying and Executing SQL Statements
7.3 Dynamically Modifying and Executing SQL Statement Conditions
7.4 Immediately Executing SQL Statements
7.5 Changing Database Names and Omitted Schema Names
7.1 Overview of Dynamic SQL
Dynamic SQL is used to generate and execute SQL statements when application programs are executed. For
example, suppose an application program to delete rows from a table is to be developed using delete row search
conditions entered from an external data set. In this case, SQL statements to delete rows cannot be specified since
the search condition to delete rows cannot be determined when the program is coded. SQL statements cannot be
completed until the search condition is read from the external data set when the application program is executed.
Embedded SQL statements cannot be used to write this kind of application program since SQL statements must be
completed at compilation because the embedded SQL statements are interpreted at compilation. Use dynamic SQL
for this kind of application program. With dynamic SQL, the SQL statements are interpreted and executed at
application program run-time.
This section explains the following elements required to execute dynamic SQL:
· Overview of Dynamic SQL
· Preparable statements
· Dynamic parameter specification
· USING clause
SQLDA structure and SQL descriptor area
If SQL statements are dynamically modified to manipulate data (i.e., if an SQL statement is entered outside an
application program), the SQL statements to be entered are unknown when an application program is created. When
data is fetched from a database, for example, the variable used to fetch the data must be declared in the application
program. However, if the SQL statement to be entered is not known, a variable cannot be declared. This situation is
the same as when data is entered in the database. Therefore, when modifying SQL statements dynamically, allocate a
temporary descriptor area for data manipulation. When this descriptor area is used, data can be manipulated simply
by declaring a proper variable.
The descriptor area is used as an SQLDA structure or an SQL descriptor area. The SQLDA structure can be used
only in C.
SQLDA structure
By dynamically allocating and using an SQLDA structure area in an application program, the user can directly
manipulate SQLDA structure information.
When the SQLDA structure is used, processing is simplified only if the dynamic parameter specification and select
column list information are set in an SQLDA structure member or are obtained from a member.
The SQLDA structure is positioned as shown in the figure below.
131