ALLBASE/SQL COBOL Programs

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

• Identify COBOL variables referenced in SQL commands, known as host variables.

• set up a special variable known as the SQL Communications Area (SQLCA) in the main program, for communicating the status of executed SQL commands to your program.

• Generate error-handling code for SQL commands.

• Identify cursor declarations.

Program Structure

The following skeleton program illustrates the relationship between COBOL statements and embedded SQL commands in an application program. SQL commands may appear in a program at locations highlighted.

   IDENTIFICATION DIVISION.

   PROGRAM-ID.    ProgramName.

   COBOL Statements

   .

   .

   .

   DATA DIVISION.

   FILE SECTION.

   {{Host Variable Declarations}}

   COBOL Statements

   .

   .

   .

   WORKING-STORAGE SECTION.

   {{SQLCA Declaration}}

   {{Host Variable Declarations}}

   COBOL Statements

   .

   .

   .

   LINKAGE SECTION.

   {{SQLCA Declaration}}

   {{Host Variable Declarations}}

   COBOL Statements

   .

   .

   .

   LINKAGE SECTION.

   {{SQLCA Declaration}}

   {{Host Variable Declarations}}

   COBOL Statements

   .

   .

   .

   PROCEDURE DIVISION.

   .

   .

   .

   COBOL Paragraphs, some containing {{SQL Commands}}

   .

   .

   .

   STOP RUN.

To delimit SQL commands for the preprocessor, you put the prefix EXEC SQL and the suffix END-EXEC around each SQL command:

EXEC SQL BEGIN WORK END-EXEC.

Most SQL commands appear within the Procedure Division 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 DBEnvironment is PartsDBE which is in the group and account GROUPDB.ACCTDB.

   RUN PSQLCOB.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.

   PROCEDURE DIVISION.

   .

   .

   .

   EXEC SQL CONNECT TO 'PARTSDBE.GROUPDB.ACCTDB' END-EXEC.

   .

   .

   .

   EXEC SQL RELEASE END-EXEC.

   STOP RUN.

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 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, 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 authority to start the DBE session in the program:

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 for it. However, any dynamic command is executed only if the individual running the program has the authority to execute the command at run time. (Chapter 10 contains information about dynamic commands.)

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. The group and account name at creation time are part of the DBECon file name. 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.

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 PSQLCOB.PUB.SYS;INFO = 'SOMEDBE.SQL.DBSUPPORT'