Differences between Dynamic and Non-Dynamic Preprocessing

• Authorization checking. A non-dynamic command is executed if the owner of the program module has the proper authority at run time. A dynamic command is executed if the program executor has the proper authority at run time.

• Section creation. Any section created for a non-dynamic command becomes part of a module permanently stored in a DBEnvironment by the Pascal preprocessor. The module remains in that system catalog until you execute the DROP MODULE command or invoke the preprocessor with the -d option. Any section created for a dynamic command is temporary. The section is created at run time, temporarily stored, then deleted at the end of the transaction in which it was created.

Permanently Stored vs. Temporary Sections

In some instances, you could code the same SQL statement as either dynamic or non-dynamic, depending on whether you wanted to store permanent sections. A program that has permanently stored sections associated with it can be executed only against DBEnvironments containing those sections. Figure 10-1 illustrates how you create and use such programs. Note that the sections can be permanently stored either by the preprocessor or by using the ISQL INSTALL command.

Figure 10-1 Creation and Use of a Program that has a Stored Module

Programs that contain only SQL commands that do not have permanently stored sections can be executed against any DBEnvironment without the prerequisite of storing a module in the DBEnvironment. Figure 10-2 illustrates how you create and use programs in this category. Note that the program must still be preprocessed in order to create compilable files and generate ALLBASE/SQL external procedure calls.

Figure 10-2 Creation and Use of a Program that has No Stored Module

Examples of Non-Dynamic and Dynamic SQL Statements

The following example shows an embedded SQL statement that is coded so as to generate a stored section before run time:

   EXEC SQL UPDATE STATISTICS FOR TABLE PurchDB.Parts;

When you run the preprocessor on a source file containing this statement, a permanent section will be stored in the appropriate DBEnvironment.

The following example shows an SQL statement that is coded so as to generate a temporary section at run time:

   DynamicCommand := 'UPDATE STATISTICS FOR TABLE PurchDB.Parts;';

   EXEC SQL PREPARE MyCommand FROM :DynamicCommand;

   EXEC SQL EXECUTE MyCommand;

In this case, the SQL statement is stored in a host variable which is passed to ALLBASE/SQL in the PREPARE statement at run time. A temporary section is then created and executed, and the section is not stored in the DBEnvironment.

Why Use Dynamic Preprocessing?

In some cases, it may not be desirable to preprocess an SQL command before run time: