SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-33
Using DEFINE Names With Programs
•
After a program that uses DEFINE names is SQL compiled and registered as a
valid program in a catalog, the program is valid only for the table and views
identified by the DEFINEs at compile time. If a different set of DEFINEs is used at
run time, the program is automatically recompiled with the new DEFINEs if
automatic recompilation is enabled.
If you want the program to be valid for a different table or view, you must SQL
compile the program with the new DEFINEs to revalidate the program with the
objects identified by the new DEFINEs.
•
You must know which DEFINEs are required in the run time environment: only
those DEFINEs that refer to a catalog or SQL object are required at run time.
These DEFINEs are optional at SQL compile time. Other DEFINEs used in
programming, such as =COPYLIB (referring to COPY libraries of the source code),
are already resolved by the precompilers and are not part of the run time DEFINE
set.
•
The EXPLAIN DEFINES option of the SQL compiler lists the DEFINE set used to
compile the program. You can access this listing in an OBEY command-file format
to use before executing the programs.
•
Using DEFINEs simplifies mobility issues for application programs. Using
DEFINEs, however, can create problems if the wrong DEFINE is active at run time.
If the incorrect DEFINE is in effect and identifies a table or view that exists, the
program could write to the wrong table. This error could happen most easily on a
system shared by groups using similar databases when security is not planned
carefully.
Examples
To demonstrate the use of DEFINEs with programs, several examples follow.
The first example uses a DEFINE in an INVOKE statement of a COBOL program. The
logical name =PARTS identifies a table. The DEFINE for =PARTS must be in effect
only during the preprocessing step of the COBOL compilation.
EXEC SQL
INVOKE =PARTS AS PARTS-REC LEVEL (01,04)
END-EXEC.
This example uses the logical name =PARTS in an INSERT statement. The DEFINE
for =PARTS must be in effect to identify the table when this statement is SQL compiled
and executed.
EXEC SQL
INSERT INTO =PARTS
VALUES (:PARTNUM OF PARTS,
:PARTDESC OF PARTS,
:PRICE OF PARTS,
:QTY-AVAILABLE OF PARTS )
END-EXEC.