HP 3000 Manuals HP 3000 Manuals
ALLBASE/SQL Pascal Programs
To write a Pascal application that uses an ALLBASE/SQL database, you
embed SQL commands in the Pascal source wherever you want the program to
do the following tasks:
* Start or terminate a DBEnvironment session, either in single user
mode or multi-user mode.
* Start or terminate a transaction.
* Retrieve rows from or change data in tables in a database.
* Create or drop objects, such as indexes or views.
You also embed special SQL commands known as preprocessor directives.
The Pascal preprocessor uses these directives to do the following tasks:
* Identify Pascal variables referenced in SQL commands, known as
host variables.
* Set up a data structure known as the SQL Communications Area
(SQLCA) in the main program, for communicating the status of
executed SQL commands to your program.
* Optionally automate program flow based on SQLCA information.
* Generate error handling code.
* Set up a special variable known as the SQL Description Area
(SQLDA) in the main program or subprogram, for handling
dynamically preprocessed SELECT commands.
* Identify cursor declarations.
Program Structure
The following skeleton program illustrates the relationship between
Pascal constructs and embedded SQL commands in an application program.
SQL commands may appear in a program at locations indicated by shading.
(* PROGRAM HEADING *)
program ProgramName (input, output);
:
(* PROGRAM DECLARATION PART *)
var
SQLCA Declaration
SQLDA Declaration
Host Variable Declarations
:
(* PROGRAM STATEMENT PART *)
begin
:
Pascal statements and SQL Commands
:
end.
The global area of a subprogram cannot contain host variable
declarations. Only Level 1 procedures in a subprogram can contain host
variable declarations, but you can use the host variables in procedures
at other levels.
To delimit SQL commands for the preprocessor, you begin each command with
EXEC SQL and end each command with a semicolon.
EXEC SQL BEGIN WORK;
Most SQL commands appear within Pascal procedures where you establish
DBEnvironment access and manipulate data in a database.
DBEnvironment Access
You must always specify a DBEnvironment at preprocessing time. The
preprocessor needs to access the DBEnvironment you specify in the INFO
string. It does so in order to store a module containing permanent
sections used by your application program at run time. In this example,
the environment is PartsDBE which is in the group and account
GroupDB.AcctDB.
:RUN PSQLPAS.PUB.SYS; INFO = 'PartsDBE.GroupDB.AcctDB'
Your application program needs to access the DBEnvironment to perform its
work. The CONNECT command starts a DBEnvironment session for a specific
environment. The RELEASE statement terminates that session.
begin
.
.
.
EXEC SQL CONNECT TO 'PartsDBE.GroupDB.AcctDB';
.
.
.
EXEC SQL RELEASE;
end.
At run time, the program starts a DBE session in PartsDBE.GroupDB.AcctDB,
where a module for the program has been stored.
A program can accept a DBEnvironment name from the program user and
dynamically preprocess the SQL command that starts a DBEnvironment
session. Refer to Chapter 10 for more information on dynamically
connecting to a database and refer to Chapter 4 for more information on
using a host variable to connect to a database.
No matter how you access a DBEnvironment (dynamic or stored sections),
you must always specify a DBEnvironment name when you preprocess.
In some cases an ALLBASE/SQL program is used with one or more
DBEnvironments in addition to the DBEnvironment accessed at preprocessing
time. In these cases, you use ISQL to install the installable module
created by the preprocessor into each additional DBEnvironment accessed
by your program. You can also preprocess the same application repeatedly
with different DBEnvironments. See Chapter 2 for information on the
installable module.
An alternative method of accessing more than one DBEnvironment from the
same program would be to separate the program into separate compilable
files. Each source file would access a DBEnvironment. In each file you
start and terminate a DBE session for the DBEnvironment accessed. You
then preprocess and compile each file separately. When you invoke the
preprocessor, you identify the DBEnvironment accessed by the source file
being preprocessed. After each separate source file is preprocessed, it
must be compiled without linking. When all source files have been
preprocessed and compiled, you link them to create the executable
program.
Note that a program which accesses more than one DBEnvironment must
access them one after another. Such program design may adversely affect
performance and requires special consideration.
To preprocess or to use an already preprocessed ALLBASE/SQL application
program, you must satisfy the authorization requirements for each
DBEnvironment accessed.
Authorization
ALLBASE/SQL authorization governs who can preprocess, execute, and
maintain a program that accesses an ALLBASE/SQL DBEnvironment.
To preprocess a program for the first time, you need CONNECT or DBA
authority in the DBEnvironment your program accesses. When you
preprocess a program, ALLBASE/SQL stores a module for that program in the
DBEnvironment's system catalog and identifies your User@Account as the
owner of that module. Subsequently, if you have OWNER or DBA authority,
you can re-preprocess the program.
To run a program accessing an ALLBASE/SQL DBEnvironment, you need the
following authorities to start the DBE session in the program:
* If the program uses a CONNECT command to start a DBE session, you
need CONNECT authority and RUN or module OWNER authority to run
the program.
* If the program uses a START DBE command to start the DBE session,
you need DBA authority to run the program.
At run time, any SQL command in the program is executed only if the OWNER
of the module has the authorization to execute the command at run time,
and the individual running the program has RUN authority to the program.
However, any dynamic command is executed only if the userid of the user
running the program has the authority to execute the entered command at
run time. A dynamic command is an SQL command entered by the user at run
time.
Maintaining an ALLBASE/SQL program includes such activities as modifying
a program in production use and keeping runtime authorizations current as
program users change. For these activities, you need OWNER authority for
the module or DBA authority. More on this topic appears later in this
chapter under "Maintaining ALLBASE/SQL Programs."
File Referencing
When you create a DBEnvironment, a Database Environment Configuration
(DBECon) file is created. The file name of this DBECon file is stored in
the DBECon file itself. In all subsequent references to files, you may
use either a fully qualified file name or a file name relative to that of
the DBECon file.
For example, if a DBEnvironment was created with the following command:
START DBE 'PARTSDBE' NEW
and the user was currently in the SQL group of the DBSUPPORT account, the
file name PARTSDBE.SQL.DBSUPPORT would be stored in the DBECon file. If
the user were subsequently to create a DBEFile with the command:
CREATE DBEFILE ORDERS WITH PAGES=50, NAME='ORDERSFS'
the ORDERSFS file is created in the same group and account as the DBECon
file and would be ORDERSFS.SQL.DBSUPPORT. If however, the user were to
create a DBEFile with the command:
CREATE DBEFILE ORDERS WITH PAGES=50, NAME='ORDERSFS.SHIPPING.DBSUPPORT'
the name stored in the DBECon file would be ignored while creating this
file. The user would need to fully qualify this file name each time the
file is referenced. Fully qualified file names, enclosed in quotes, are
restricted to a maximum length of 36 bytes. The maximum length of
unquoted file names is 8 bytes. DBEnvironment names are restricted to a
maximum length of 128 bytes.
In addition, if the DBEnvironment you want the preprocessor to access
resides in a group and account other than your current group and account,
you will have to qualify the name of the DBEnvironment.
For example, if the DBEnvironment you want the preprocessor to access
resides in the SQL group of account DBSUPPORT, you would invoke the
preprocessor as follows:
$ RUN PSQLPAS.PUB.SYS;INFO = 'SOMEDBE.SQL.DBSUPPORT'