Database Interface in COBOL
- 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 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 ProgrammingThe 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 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.
Following syntax defines how to declare host variables and include a table in Working-Storage section:
EXEC SQL BEGIN DECLARE SECTION
05 EMPLOYEE-ID PIC 9(4).
05 EMPLOYEE-NAME PIC X(25).
05 EMPLOYEE-ADDRESS X(50).
EXEC SQL END DECLARE SECTION
Following are the return code which verifies the SQLCODE:
- 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.
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.
Following syntax defines how to declare an SQLCA in the Working-Storage section:
- 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 statement is the first statement which is non-executable statement.
- The declaration can be done in the Working-Storage section or the Procedure Division.
DECLARE EMPCUR CURSOR FOR
SELECT EMPLOYEE-ID, EMPLOYEE-NAME, EMPLOYEE-ADDRESS FROM EMPLOYEE
WHERE EMPLOYEE-ID >: WS-EMPLOYEE-ID
Open statement performs before using a cursor. It prepares the SELECT statement for execution.
Close statement is necessary to close a cursor before ending a program. It releases all the memory occupied by the cursor.
Fetch statement recognizes the cursor & puts the value in the INTO clause and coded in a loop.
INTO : WS-EMPLOYEE-ID, : WS-EMPLOYEE-NAME, WS-EMPLOYE-ADDRESS