General Aspects

A string containing the SQL statement is created. The application program has complete control over the contents of the string and, therefore, the SQL statement is dynamic in nature.

After the SQL statement has been constructed, it must be passed to CONNX Embedded SQL for compilation. This is done by either using a PREPARE statement, or an EXECUTE IMMEDIATE statement. The compiled form of the dynamic SQL statement is called the prepared statement.

If the statement was processed using an EXECUTE IMMEDIATE statement, it is not only compiled, but also executed at the same time. The prepared form of the statement is not retained.

If the statement was processed using a PREPARE statement, the prepared statement can be executed using an EXECUTE statement, or by using cursor processing, as many times as required.

It may be that additional information about the prepared statement is required before it can be executed, for example, for statements with an unknown derived column list. This information can be retrieved from CONNX Embedded SQL through an SQL descriptor area (SQLDA) using a DESCRIBE statement. An SQLDA can also be used to resolve host variable markers. Such information must be obtained prior to execution.

After the dynamic SQL statement is prepared, it can be executed more than once, by using the same statement identifier. A prepared statement remains available until the completion of the current session. A prepared statement can be deallocated using a DEALLOCATE PREPARE statement.

The choice between static and dynamic SQL is a choice of flexibility versus complexity and performance. It is easier to code static SQL statements than to construct SQL statements dynamically. In most cases, it will be possible to use static SQL, but there are some applications where the use of dynamic SQL is unavoidable. If the number of static SQL statements that would be required for a certain application exceeds a manageable amount, dynamic SQL may be the solution.

In principle, the question to be answered is "Is it possible to define all necessary SQL statements in my application and will this be a manageable and feasible amount of coding?". If the answer is no, then you should consider dynamic SQL.

Once dynamic SQL has been identified as a viable possibility, the particular variation or degree of complexity of dynamic SQL must be decided upon. This time, the questions are:

If SELECT statements are dynamically constructed, does the derived column list vary dynamically?

Are host variable markers to be used, and if so, does the number and type of host variable markers vary dynamically per prepared statement?

Another point of consideration is the issue of performance. Compiling SQL statements at runtime has an influence on the overall performance of the execution of that SQL statement. The compilation of an SQL statement also includes the access of information stored in the catalog, like table and column descriptions. These descriptions are buffered, but there is a possibility that additional database requests need to be issued.

The consequence of the fact that a dynamic SQL statement is compiled at runtime is also that the statement is compiled with more current information concerning the existence of indices and other optimization information.

Using dynamic SQL also means that syntactical and semantical errors are only detected during runtime. This means that the PREPARE and EXECUTE IMMEDIATE statements may return an SQLCODE indicating a syntactical or semantical error.

The following SQL statements cannot be used as dynamic SQL statements, that is, they cannot be prepared or executed:

BEGIN DECLARE

DEALLOCATE PREPARE

DECLARE

DESCRIBE

DISCONNECT

END DECLARE

EXECUTE

EXECUTE IMMEDIATE

FETCH

INCLUDE

OPEN

PREPARE

WHENEVER

Dynamic SQL may require the use of addresses and pointers within the application program. It may also require dynamically obtained memory.