HP 3000 Manuals

ALLBASE/SQL Pascal Application Programming Guide

ALLBASE/SQL Program Execution 

When an ALLBASE/SQL program is first created, it can only be executed by
the module OWNER or a DBA. In addition, it can only operate on the
DBEnvironment used at preprocessing time if a module was generated.  If
no module was generated because the SQL commands embedded in the program
are only commands for which no sections are created, the program can be
run against any DBEnvironment.

The program created in the previous example can be executed as follows by
Pgmr1.AcctDB:

     :RUN SOMEPROG.GroupP.AcctDB 

To make the program executable by other users in other DBEnvironments,
you must perform the following steps:

   *   Load the executable program file onto the machine where the
       production DBEnvironment resides.

   *   Install any related module in the production DBEnvironment.

   *   Ensure necessary module owner authorities exist.

   *   Grant required authorities to program users.

Installing the Program Module 

When the preprocessor stores a module in a DBEnvironment, it also creates
a file containing a copy of the module, which can be installed into
another DBEnvironment.  You use the INSTALL command in ISQL to install
the module in another DBEnvironment.  In this example, the module is
installed in the SomeDBE environment which is in the same group and
account as the PartsDBE environment:

     isql=> CONNECT TO 'SomeDBE.GroupDB.AcctDB'; 
     isql=> INSTALL SOMEMOD.GroupP.AcctDB; 

     Name of module in this file:  Pgmr1@AcctDB.SOMEMOD
     Number of sections installed:  6
     COMMIT WORK to save to DBEnvironment.

     isql=> COMMIT WORK; 

ISQL copies the module from the installable module file named
SOMEMOD.GroupP.AcctDB into a DBEnvironment named SomeDBE.GroupDB.AcctDB.
During installation, ALLBASE/SQL marks each section in the module valid
or invalid, depending on the current objects and authorities in
SomeDBE.GroupDB.AcctDB.

To use the INSTALL command, you need to be able to start a DBE session in
the DBEnvironment that is to contain the new module.  If you are
replacing a module with a new one of the same name, make sure no other
users are accessing the module.  To avoid problems, install modules while
connected to the DBEnvironment in single-user mode. 

Granting Required Owner Authorization 

At run time, embedded SQL commands are executed only if the original
module owner has the authority to execute them.  Therefore, you need to
grant required authorities to the module owner in the production
DBEnvironment.

If module Pgmr1@AcctDB.SomeMod contains a SELECT command for
table PurchDB.Parts, the following grant would ensure valid owner
authorization:

     isql=> GRANT SELECT ON PurchDB.Parts to Pgmr1@AcctDB; 

If Pgmr1@AcctDB had DBA authority, he could have assigned ownership of
the module to another owner at preprocessing time by using the OWNER
parameter:

     :RUN PSQLPAS.PUB.SYS;INFO='SomeDBE.GroupDB.AcctDB & 
     (MODULE(SOMEMOD) OWNER (PURCHDB))' 

In this case, ownership belongs to a class, PurchDB. Only an individual
with DBA authority can maintain this program, and runtime authorization
would be established as follows:

     isql=> GRANT SELECT ON PurchDB.Parts TO PurchDB; 

Granting Program User Authorization 

In order to execute an ALLBASE/SQL application program you must be able
to start any DBE session initiated in the program.  You must also have
one of the following authorities in the DBEnvironment accessed by the
program:

     RUN
     module OWNER
     DBA

A DBA must grant the authority to start a DBE session.  In most cases,
application programs start a DBE session with the CONNECT command, so
CONNECT authorization is sufficient:

     isql=> CONNECT TO 'SomeDBE.GroupDB.AcctDB'; 
     isql=> GRANT CONNECT TO SomeUser@SomeAcct; 
     isql=> COMMIT WORK; 

If you have module OWNER or DBA authority, you can grant RUN authority:

     isql=> CONNECT TO 'SomeDBE.GroupDB.AcctDB'; 
     isql=> GRANT RUN ON Pgmr1@AcctDB.SomeMod TO SomeUser@SomeAcct; 
     isql=> COMMIT WORK; 

Now SomeUser@SomeAcct can run program SomeProg.GroupP.AcctDB:

     :HELLO SomeUser.SomeAcct 
     .
     .
     .
     :RUN SomeProg.GroupP.AcctDB 

If the program executes subprograms that contain SQL commands, you must
also GRANT RUN authority to each subprogram module.

Running the Program 

At run time, two file equations may be required--one for the ALLBASE/SQL
message catalog and one for the DBEnvironment to be accessed by the
program.

If the program contains the SQLEXPLAIN command, the ALLBASE/SQL message
catalog must be available at run time.  SQLEXPLAIN obtains warning and
error messages from SQLCTxxx.PUB.SYS, where xxx is the numeric indicator
for the current native language.  If SQLCTxxx.PUB.SYS is installed in a
different group or account on your system, you must use a file equation
to specify its location.

If the program contains a CONNECT or START DBE command that uses a back
referenced DBEnvironmentName, submit a FILE command to identify the
DBEnvironment to be accessed by the program at run time:

     EXEC SQL CONNECT TO '*DBE';

         This command initiates a DBE session in the DBEnvironment 
         identified at run time as follows: 

     :FILE DBE=SomeDBE.SomeGrp.SomeAcct 

Once the ALLBASE/SQL message catalog and appropriate DBEnvironment are
identified, the program can be run:

     :RUN SomeProg.GroupP.AcctDB 

You must specify the name of an executable program file as SomeProg.  Do
not specify a module name in the RUN command.

At run time, an ALLBASE/SQL program interacts with the DBEnvironment as
illustrated in Figure 1-4.


          Figure 1-4.  Runtime Events 

All the Pascal constructs inserted by the preprocessor and the stored
sections automatically handle database operations, including providing
the application program with status information after each SQL command is
executed.  SQL commands that have a stored section are executed if the
section is valid at run time or can be validated by ALLBASE/SQL at run
time.

SQL commands that are not known until run time can also be processed by
an application program.  These SQL commands, known as dynamic commands,
are entered by the user at run time rather than embedded in the source
code at programming time.  ALLBASE/SQL converts these commands into
executable ALLBASE/SQL instructions at run time rather than at
preprocessing time.  Sections and other instructions created for dynamic
data manipulation commands are deleted at the end of the transaction.

MPE/iX 5.0 Documentation