The processing of a dynamic SQL statement consists of the following steps:
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 Adabas SQL Gateway Embedded SQL for compilation. This is done 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 Adabas SQL Gateway 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 has been prepared, it may 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 dynamic SQL needs to be considered.
Once dynamic SQL has been identified as a viable possibility, the particular variation or degree of complexity of dynamic SQL must be decided upon. The following questions to be answered are:
Must the program contain dynamically constructed SELECT statements or not?
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, when deciding whether to use dynamic SQL, 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 the possibility exists 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
CLOSE
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.