HP 3000 Manuals HP 3000 Manuals
Application Programming
To use SQL statements in an application program, you embed the statements
in source code, then use the ALLBASE/SQL preprocessor that supports the
source language.
Preprocessor
The ALLBASE/SQL preprocessor performs the following tasks:
* Checks the syntax of SQL statements embedded in an application
program.
* Translates embedded SQL statements into compilable C, FORTRAN,
COBOL, or Pascal constructs that call ALLBASE/SQL external
procedures at run time.
* Stores a module in the DBEnvironment.
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:
* To preprocess a program, you need DBA or CONNECT authority and the
authorities needed to execute all activities against the database
that are executed by the program. The module stored for the
program is owned by the logon name of the individual who invokes
the preprocessor. A DBA, however, can associate the module with a
different owner at preprocessing time. Other users can assign a
group name as the module owner if they belong to the group.
* To run a program, you need either RUN authority or OWNER authority
for the stored module. You also need the authority to start the
DBE session as it is started in the program.
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.