HP 3000 Manuals HP 3000 Manuals
Differences between Dynamic and Non-Dynamic Preprocessing
The authorization checking and section creation activities for
non-dynamic and dynamic ALLBASE/SQL commands differ in the following
ways:
* 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 DROP 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.
Click here to view figure.
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.
Click here to view figure.
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:
* You may need to code an application that permits ad hoc queries
requiring that SQL commands be entered by the user at run time.
(ISQL is an example of an ad hoc query facility in which the
command the user will submit is completely unknown at programming
time.)
* You may need more specialized applications requiring SQL commands
that are defined partly at programming time and partly by the user
at run time. An application may, for example, perform UPDATE
STATISTICS operations on tables the user specifies at run time.
* You may wish to run an application on different DBEnvironments at
different times without the need to permanently store sections in
those DBEnvironments.
* You may wish to code only one dynamic command (a CONNECT, for
instance) and then preprocess or install the same application in
several different DBEnvironments.