HP 3000 Manuals

ALLBASE/SQL FORTRAN Application Programming Guide

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 FORTRAN preprocessor.  The module remains in the 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 9-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 9-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 9-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 9-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.

MPE/iX 5.0 Documentation