ALLBASE/SQL Program Execution

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:

   isql=> CONNECT TO 'somedbe';

   isql=> INSTALL mysource.sqlm;



   Name of module in this file:  PGMR1.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 mysource.sqlm into a DBEnvironment named somedbe. During installation, ALLBASE/SQL marks each section in the module valid or invalid, depending on the current objects and authorities in somedbe. To use the INSTALL command, you need the authority 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.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;

If pgmr1 had DBA authority, he could have assigned ownership of the module to another owner at preprocessing time by using the -o option:

   $ psqlpas somedbe -o PurchDB -m somemod -i mysource.sql -p mysource.p -d

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';

   isql=> GRANT CONNECT TO somelogin;

   isql=> COMMIT WORK;

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

   isql=> CONNECT TO 'somedbe';

   isql=> GRANT RUN ON someprog TO somelogin;

   isql=> COMMIT WORK;

Now somelogin can run program someprog.r:

   login: someuserid

         .

         .

         .

   $ someprog.r

Running 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 this catalog. The default message catalog is /usr/lib/nls/n-computer/hpsqlcat. For native language users, the catalog is /usr/lib/nls/$LANG/hpsqlcat, where $LANG is the current language. If this catalog is not available, ALLBASE/SQL issues a warning and uses the default catalog instead.

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

   $ someprog.r

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

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.