Dynamic SQL

The SQL statements described up to this point can be used to create an ESQL program which is compiled once and can then be executed as often as required. The form of every SQL statement is known at the time the program is compiled.

If, for instance, you wanted to perform a wide range of queries on a database, you could try to predict all possible queries in an ESQL program. Any given query could then be analyzed by the ESQL program and the appropriate SQL statement called. It is clear that an approach of this type would soon reach its limitations. In order to implement applications of this type, you need language constructs which allow you to specify SQL statements at runtime.

Applications of this type can be implemented using dynamically compiled SQL statements, i.e. statements which are only compiled at runtime. Special statements are available for compiling and executing dynamically compilable SQL statements. These are known as “statements for dynamic SQL” or simply “dynamic SQL”. In addition to the SQL statements for dynamic SQL on "SQL statements for dynamic SQL", they also include SQL statements for dynamic cursors, i.e. cursors defined only at runtime. In contrast to dynamic SQL, those statements which must be defined before compilation are referred to as static SQL.

The following overview illustrates the increasing levels of flexibility provided by the various dynamic SQL constructs:

SQL language resources	Number of parameters known at compilation time	Data types of the parameters known at compilation time	Value of the parameter
Static SQL	yes	yes	Can be passed in host variables
Dynamic SQL EXECUTE IMMEDIATE	no parameters	no parameters	no parameters
Dynamic SQL PREPARE EXECUTE with host variables	yes	yes	Placeholders as dummy input parameters
Dynamic SQL PREPARE EXECUTE with descriptor areas	no	no	Placeholders as dummy input parameters

Table 36: Increasing flexibility when using dynamic SQL

This gain in flexibility is offset by an increased effort when programming applications using dynamic SQL.

The most comprehensive mechanism for setting and reading input parameters for dynamically compiled SQL statements is the SQL descriptor area. An SQL descriptor area acts as an interface for input and output parameters between the application program and the database. SQL statements which are only entered at runtime can contain variable input parameters which are stored in the SQL descriptor area and which can be queried in this area. It is also possible to store the results of querying the database in the SQL descriptor area and to read information on the number, name, data type, values etc. of the derived columns and to take them into account in the ESQL program. It is thus possible, for example, to use descriptor areas to print edit the results of any queries to the database in ESQL programs.