ALLBASE/SQL Pascal Programs

• 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.

• 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. However, any level of a procedure in a subprogram can contain host variable declarations, and you can use the host variables in procedures at other levels.

The ALLBASE/SQL preprocessor does not allow the Pascal language MODULE keyword, or the associated IMPORT and EXPORT keywords.

	NOTE: 300/400 Series 300 and 400 systems do not allow subprograms.

To delimit SQL commands for the preprocessor, you begin each command with EXEC SQL and end each command with a semicolon, as follows:

   EXEC SQL BEGIN WORK;

SQL commands can appear within any Pascal procedure 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 order to store a module containing permanent sections used by your application program at run time. In this example, the DBEnvironment is PartsDBE which is in the sampledb directory.

   $ psqlpas ../sampledb/PartsDBE -i mysource.sql -p mysource.p -d

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.

   EXEC SQL CONNECT TO '../sampledb/PartsDBE';

   .

   .

   .

   EXEC SQL RELEASE;

At run time, the program starts a DBE session in the DBEnvironment PartsDBE, where one or more modules for the program have been stored.

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.

During preprocessing, the preprocessor needs to access the same DBEnvironment your program will access at run time. Therefore, when you invoke the preprocessor, the DBEnvironment your program accesses must exist and you must identify that DBEnvironment.

   $ psqlpas mydbe -i mysource.sql -p mysource.p -d

In addition, you define a DBEnvironment session for the same DBEnvironment with the following source code syntax:

   .

   .

   .

   EXEC SQL CONNECT TO 'mydbe';

   .

   .

   .

   EXEC SQL RELEASE;

At run time, the program starts a DBE session in mydbe, where a module for the program is stored.

In some cases an ALLBASE/SQL program is used with a DBEnvironment named differently from the DBEnvironment accessed at preprocessing time. In these cases, you can accept a DBEnvironment name into a host variable from the program user and execute the SQL command that starts a DBE session, using the host variable. No dynamic preprocessing is involved. You can also accept the whole CONNECT command and dynamically preprocess the command that starts the DBE session. Dynamic preprocessing is covered in the chapter, "Using Dynamic Operations."

File Referencing

When you create a DBEnvironment, a DataBase Environment Configuration (DBECon) file is created. The absolute pathname of this DBECon file is stored in the DBECon file itself. Once the DBECon file is created, the user, in all subsequent references to DBEnvironment files, may use either an absolute or a relative pathname. By default, all pathnames of files and directories are relative to the pathname stored in the DBECon file. Any file reference which does not begin with a slash (/) assumes a pathname relative to that of the DBECon file.

For example, a DBEnvironment is created with the following command:

   START DBE 'PartsDBE' NEW

If the user was currently in the directory /users/dbsupport/sql, the pathname /users/dbsupport/sql would be stored in the DBECon file. Suppose the user were subsequently to create a DBEFile with the command:

   CREATE DBEFILE Orders WITH PAGES=50, NAME='OrdersDF'

The actual pathname of the file OrdersDF would be relative to the pathname stored in the DBECon file and would be /users/dbsupport/sql/OrdersDF. Relative pathnames are restricted to a maximum length of 128 characters. If however, the user were to create a DBEFile with the command:

   CREATE DBEFILE Orders WITH PAGES=50, NAME='/users/shipping/OrdersDF'

the default pathname stored in the DBECon file would not be used while creating this file. The user would need to fully qualify this pathname each time this file is referenced. Absolute pathnames are restricted to a maximum length of 128 characters.

In addition, if the DBEnvironment you want the preprocessor to access resides in a directory other than your current working directory, you will have to either absolutely or relatively qualify the pathname of that DBEnvironment.

For example, if the DBEnvironment you want the preprocessor to access resides in a subdirectory of yours, you would invoke the preprocessor as follows:

   $ psqlpas sampledb/SomeDBE -i SourceFileName.sql -d

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 your DBEnvironment. When you preprocess a program, ALLBASE/SQL stores a module for that program in the DBEnvironment's system catalog and identifies your userid as the owner of that module. Subsequently, if you have OWNER or DBA authority, you can preprocess the program again.

To run a program accessing an ALLBASE/SQL DBEnvironment, you need the following authorities to start the DBE session in 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."