Database Interface in COBOL

Introduction

  • In COBOL database interface, programs interact with DB2 database.
  • It includes various terms, such as embedded SQL, DB2 Application programming, host variables, SQLCA, SQL Queries, Cursors.

Embedded SQL

  • Embedded SQL statements are used in COBOL programs to execute standard SQL operations.
  • These statements are preprocessed by SQL processor before the application program is compiled.
  • COBOL is called as Host Language. COBOL-DB2 applications are those applications which consists of both COBOL and DB2.
  • It works like normal SQL statements with some small changes.

DB2 Application Programming

The following are the rules to be considered while coding a COBOL-DB2 program:
  • All the SQL statements should be delimited between EXEC SQL and END-EXEC.
  • The SQL statement should be coded in Area B.
  • All the tables are used in a program that should be declared in the Working-Storage Section using with the INCLUDE statement.
  • NCLUDE and DECLARE TABLE must appear in the Procedure Division.

Host Variables

  • Host variables are used for inserting or collecting a data from the table.
  • It is important to declare host variables for all values passed between the program and DB2.
  • Host variables are declared in the Working-Storage section.
  • It cannot be group items, but it may be grouped together in host structure.
  • Host variables cannot be redefined or renamed.

Syntax

Following syntax defines how to declare host variables and include a table in Working-Storage section:

DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE table-name
END-EXEC.

EXEC SQL BEGIN DECLARE SECTION
END-EXEC.

01 EMPLOYEE-REC.
05 EMPLOYEE-ID PIC 9(4).
05 EMPLOYEE-NAME PIC X(25).
05 EMPLOYEE-ADDRESS X(50).
EXEC SQL END DECLARE SECTION
END-EXEC.

SQLCA

  • SQLCA is known as a SQL Communication Area.
  • It acts as a communication area between COBOL and DB2 to validate the SQLs execution.
  • It is a communication area which DB2 passes the feedback of SQL execution to the program. It notifies the program, whether an execution was successful or not.
  • SQLCA is used to verify the return code of the specific execution once SQL execution is finished.
  • In SQLCA, there are a number of predefined variables like SQLCODE that contains the error code.
Following are the return code which verifies the SQLCODE:

i. If the SQLCODE is 0(ZERO) then successfully executed the query.
ii. If the SQLCODE is Positive then query executed successfully, but with a warning.
iii. If SQLCODE is Negative then displays error condition.

Syntax

Following syntax defines how to declare an SQLCA in the Working-Storage section:

DATA DIVISION.
WORKING-STORAGE SECTION.
      EXCE SQL
      INCLUDE SQLCA
      END-EXEC.

Cursors

  • Cursor is a data structure which holds all the results of the query.
  • It is defined in the procedure division or working-storage section.
  • It is used to handle multiple rows selection at a time.
  • Declare, Open, Close, Fetch are the operations associated with the cursor.

Declare cursor

  • DECLARE statement is the first statement which is non-executable statement.
  • The declaration can be done in the Working-Storage section or the Procedure Division.
Syntax:
EXEC SQL
        DECLARE EMPCUR CURSOR FOR
        SELECT EMPLOYEE-ID, EMPLOYEE-NAME, EMPLOYEE-ADDRESS FROM EMPLOYEE

WHERE EMPLOYEE-ID >: WS-EMPLOYEE-ID

END-EXEC.

Open

Open statement performs before using a cursor. It prepares the SELECT statement for execution.

Syntax:
EXEC SQL
     OPEN EMPCUR
END-EXEC.

Close

Close statement is necessary to close a cursor before ending a program. It releases all the memory occupied by the cursor.

Syntax:
EXEC SQL
      CLOSE EMPCUR
END-EXEC.

Fetch

Fetch statement recognizes the cursor & puts the value in the INTO clause and coded in a loop.

Syntax:
EXEC SQL
        FETCH EMPCUR
        INTO : WS-EMPLOYEE-ID, : WS-EMPLOYEE-NAME, WS-EMPLOYE-ADDRESS
END-EXEC.
▲