Comparing Static and Dynamic Applications

1. At coding time, by embedding only dynamic statements (as defined in the previous section), you are assured of a dynamic application. Such an application can be preprocessed in either full preprocessing mode or static conversion mode. With static conversion mode, a DBEnvironment name need not be specified on the command line.

2. At preprocessing time, by choosing static conversion mode, your resulting application is dynamic. If your source code contains static statements, they are converted to dynamic ones. Any dynamic statements are unchanged and remain dynamic.

Coding an Application that can be Either Static or Dynamic

Suppose you are coding a user interface to the PurchDB.Parts database. You know at coding time that you want the user to have the option of selecting information from each table in the database environment. You also know the exact format of each select statement. In addition, you want the user to be able to enter an ad hoc query, one that meets their particular needs at run time, for any of the tables in the database. The displayed menu might look something like the following:

                   Menu to Select Parts Information

                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



1. PARTS          3. ORDERS           5. VENDORS           7. REPORTS

2. INVENTORY      4. ORDER ITEMS      6. SUPPLY PRICE      8. AD HOC QUERY

You could code seven static SELECT statements and a dynamic select statement (for the ad hoc query). Depending on your requirements, you could preprocess this application in full preprocessing mode or static conversion mode. Full preprocessing could enhance performance when users most often choose one of the static SELECT statements. Static conversion processing would result in a portable application, ideal for use in a distributed database environment.

Converting a Static Application to a Dynamic Application

There are two ways of converting a static application to a dynamic application. One method is to change the source code to contain the required dynamic SQL statements. (The ALLBASE/SQL application programming guides provide complete information about coding with dynamic SQL statements.) The second method is to preprocess the application using static conversion mode. The following paragraphs discuss use of the second method with an application originally designed to use full preprocessing mode.

By choosing the DYNAMIC option at preprocessing time, you avoid having to make major changes to your existing source code. You also maintain the flexibility of preprocessing the source code to be either static or dynamic simply by changing the preprocessor command line options.

Unlike other preprocessing modes in which any DECLARE CURSOR statements are commented out, static preprocessing mode converts DECLARE CURSOR statements to executable code. Therefore, if your existing code contains such a statement in a non-executable portion of the code, you must move the statement to an executable portion of the application. In the C language, for example, you would declare the cursor within curly brackets following any data declaration statements within a given block. For Pascal, position the statement within a BEGIN/END block.

Another consideration is that host variables used in static statements (except for BULK FETCH and BULK SELECT statements) must adhere to the restrictions for dynamic parameters. See the chapter in this document titled "Using Parameter Substitution in Dynamic Statements" for further details.

Enhancing Performance

By default, ALLBASE/SQL does authorization checks on a dynamic query each time it is executed. By setting the authorize once per session flag to ON, you insure that authorization checks on dynamic queries are performed only the first time the query is executed during a given user session.

You can set this flag by means of the SQLUtil ALTDBE command described in the ALLBASE/SQL Database Administration Guide.