HP 3000 Manuals HP 3000 Manuals
ALLBASE/SQL FORTRAN Programs
To write a FORTRAN application that accesses an ALLBASE/SQL database, SQL
commands are be embedded in the FORTRAN source to:
* Start or terminate a DBEnvironment session, either in single-user
mode or multiuser 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.
Special SQL commands known as preprocessor directives may also be
embedded in the FORTRAN source. The FORTRAN preprocessor uses these
directives to:
* Identify FORTRAN variables referenced in SQL commands, known as
host variables.
* Set up a common block, known as the SQL Communications Area
(SQLCA), for communicating the status of executed SQL commands to
your program.
* Optionally automate program flow based on SQLCA information.
* Identify cursor declarations.
Program Structure
The following skeleton program illustrates the relationship between
FORTRAN statements and embedded SQL commands in an application program.
SQL commands may appear in a program at locations indicated by boldface
notations. The SQLCA Common Block Declaration may appear either before
or after the FORTRAN type declaration section. The SQLCA Common Block
Declaration must appear, however, before the host variable declaration
section which must be the last of the type declarations in the program
unit. Refer to Chapter 3 for further clarification of program structure.
PROGRAM ProgramName
FORTRAN Statements
.
.
.
SQLCA Declaration
FORTRAN Type Declarations
Host Variable Declarations
.
.
.
FORTRAN Statements, some containing SQL Commands
.
.
.
END
SUBROUTINE SubroutineName
FORTRAN Statements
.
.
.
SQLCA Declaration
FORTRAN Type Declarations
Host Variable Declarations
.
.
.
FORTRAN Statements, some containing SQL Commands
.
.
RETURN
END
To delimit SQL commands for the preprocessor, each SQL command is
prefixed by EXEC SQL:
EXEC SQL BEGIN WORK
SQL commands may appear in the main program or any subprogram unit 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 runtime. In this example,
the environment is SomeDBE which is in the group and account
SomeGroup.SomeAcct.
: RUN PSQLFOR.PUB.SYS; INFO = "SomeDBE.SomeGroup.SomeAcct"
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 command terminates that session.
SUBPROGRAM Unit
.
.
.
EXEC SQL CONNECT TO 'SomeDBE.SomeGroup.SomeAcct'
.
.
.
EXEC SQL RELEASE
RETURN
END
At runtime, the program starts a DBE session in
SomeDBE.SomeGroup.SomeAcct, 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 9 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. 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 a file is preprocessed, it must be compiled so that no linking is
performed before the next source file is preprocessed. 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 do so
in sequence. 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 file, ALLBASE/SQL stores a module for that program
file 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 file.
To run a program accessing an ALLBASE/SQL DBEnvironment, you need the
following authorities:
* If the program uses a CONNECT command to start a DBE session, you
need both CONNECT authority and either 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 runtime, any SQL command in the program, except for the command used
to start the DBE session, is executed only if the OWNER of the module has
the authorization to execute the command at runtime. However, any
dynamic command is executed only if the individual running the program
has the authority to execute the command at run time. A dynamic command
is an SQL command entered by the user at runtime.
Maintaining an ALLBASE/SQL program includes such activities as modifying
a program in production use and keeping runtime authorization 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 is created with the following command:
START DBE 'PartsDBE' NEW
and the user is currently in the SQL group of the DBSUPPORT account, the
file name PARTSDBE.SQL.DBSUPPORT is stored in the DBECon file. If the
user subsequently creates 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 with the name ORDERSFS.SQL.DBSUPPORT. If however, the user creates a
DBEFile with the command:
CREATE DBEFILE ORDERS WITH PAGES=50, NAME='DBSUPPORT'
the name stored in the DBECon file is ignored while creating this file.
The user then needs to fully qualify this file name each time the file is
referenced. Remember, a file, group, or account name can contain a
maximum of 8 bytes. Fully qualified file names, enclosed in quotes, are
restricted to a maximum length of 36 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 PSQLFOR.PUB.SYS;INFO = 'SOMEDBE.SQL.DBSUPPORT'