Application Programming

Preprocessor

The ALLBASE/SQL preprocessor performs the following tasks:

A module contains a group of sections. A section consists of ALLBASE/SQL instructions for executing an SQL statement at run time. ALLBASE/SQL ensures that any objects referenced in the section exist and that current authorization criteria are satisfied. The optimal data access path is determined at preprocessing time rather than at run time which enhances runtime performance.

When an application program becomes obsolete, you can use the DROP MODULE statement to delete its module from the DBEnvironment and thus ensure the program can no longer operate on the databases in the DBEnvironment. For example:

   DROP MODULE MyProgram

ALLBASE/SQL has the following statements that create modules when the information for an SQL statement cannot be completely defined in advance. These dynamic preprocessing statements are used in both programmatic and interactive environments:

   PREPARE
   EXECUTE
   EXECUTE IMMEDIATE

In addition to the above statements, ALLBASE/SQL includes the following statements which cannot be used interactively:

   BEGIN DECLARE SECTION       CLOSE CURSOR         DECLARE CURSOR
   DELETE WHERE CURRENT        DESCRIBE             END DECLARE SECTION
   FETCH                       INCLUDE              OPEN
   REFETCH                     SQLEXPLAIN           UPDATE WHERE CURRENT
   WHENEVER

Preprocessed programs receive messages from ALLBASE/SQL through the SQL Communication Area, called the SQLCA. Information is sent to ALLBASE/SQL through the SQL Description Area, called the SQLDA. These structures and the above statements are explained in detail along with examples in the ALLBASE/SQL application programming guides.

Authorization

ALLBASE/SQL authorization governs who can preprocess and execute a program that accesses a DBEnvironment as described here:

DBEnvironment Changes

Certain DBEnvironment changes can affect preprocessed programs. For example, one of the tables used by the program can be dropped from a database, or the authorities held by the module's owner can change. When you run a preprocessed program, ALLBASE/SQL automatically determines whether changes such as these have occurred. If any have, ALLBASE/SQL attempts to revalidate the affected sections. The only SQL statements that are executed at run time are those that operate on existing objects and those which the module's owner is authorized to execute.

Some changes do not affect successful execution of the program, but others can. If, for example, the owner of the program had SELECT and UPDATE authority for a table updated by the program and the UPDATE authority is later revoked, the program is no longer able to update that table. But if SELECT authority is revoked instead, the UPDATE statements for the table can still execute successfully.

Host Variables

Data is passed back and forth between a program and ALLBASE/SQL in host variables. SQL statements use both input and output host variables. Input host variables are used to transfer data into ALLBASE/SQL from the application. Output host variables move information from ALLBASE/SQL into the application.

An indicator variable is a special type of host variable. In the SELECT, FETCH, UPDATE, UPDATE WHERE CURRENT, and INSERT statements, the indicator variable is an input host variable whose value depends on whether an associated host variable contains a null value. If the indicator variable contains a negative number, then the associated host variable is null. If it contains a zero or positive number, the value in the host variable is not null.

In the SELECT and FETCH statements the indicator variable can be an output host variable and indicate that a value in the associated host variable is null or a column value is truncated. Host variable names are prefixed with a colon (:) when embedded in an SQL statement.

   :PartNumber
   :PartName
   :PartNameInd

When host variables are used in an application outside of an embedded SQL statement, the host variable name is not prefixed by a colon.

Multiple-Row Manipulations

Programmatic SELECTs and INSERTs can operate only on a row at a time unless you use a cursor or the BULK option of the SELECT, INSERT, or FETCH statement.

A cursor is a pointer that you advance one row at a time. The BULK option is used to manipulate multiple rows with a single execution of the SELECT, INSERT, or FETCH statements. When you do bulk manipulations, input and output host variables must be arrays.