COBOL by Example: SQL (Embedded)

COBOL 2002

Embedding SQL statements in COBOL with EXEC SQL blocks, accessing relational databases like DB2, using host variables with colon prefix for data binding, checking SQLCODE for error handling, and integrating database queries into business logic.

Code

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SQL-DEMO.
       
       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL INCLUDE SQLCA END-EXEC.
           
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
           01  EMP-ID      PIC 9(5).
           01  EMP-NAME    PIC X(20).
           EXEC SQL END DECLARE SECTION END-EXEC.
       
       PROCEDURE DIVISION.
           MOVE 1001 TO EMP-ID.
           
           EXEC SQL
               SELECT NAME INTO :EMP-NAME
               FROM EMPLOYEES
               WHERE ID = :EMP-ID
           END-EXEC.
           
           IF SQLCODE = 0
               DISPLAY "Employee: " EMP-NAME
           ELSE
               DISPLAY "Error: " SQLCODE
           END-IF.
           STOP RUN.

Explanation

Enterprise COBOL is frequently used with relational databases like DB2. This is achieved using Embedded SQL. SQL statements are placed directly in the COBOL source code, sandwiched between EXEC SQL and END-EXEC delimiters. This allows COBOL to leverage the power of SQL for complex data queries.

A precompiler translates these SQL blocks into standard COBOL calls to the database interface module. Host variables (COBOL variables used in SQL) are prefixed with a colon (e.g., :EMP-NAME) to distinguish them from SQL column names. This binding allows data to flow seamlessly between the application and the database.

Error handling in embedded SQL is managed via the SQLCA (SQL Communication Area), specifically the SQLCODE variable. A value of 0 indicates success, 100 usually means "Row Not Found", and negative values indicate errors. Checking SQLCODE after every execution is mandatory for robust database applications.

Code Breakdown

6

EXEC SQL INCLUDE SQLCA END-EXEC. Includes the SQL Communication Area, which contains variables like SQLCODE for error checking.

8

EXEC SQL BEGIN DECLARE SECTION. Marks the start of host variable definitions. This helps the precompiler identify variables used in SQL.

17

SELECT NAME INTO :EMP-NAME. Fetches data from the database and stores it directly into the COBOL variable EMP-NAME.

19

WHERE ID = :EMP-ID. Uses the value of the COBOL variable EMP-ID to filter the query.

22

IF SQLCODE = 0. Checks if the query was successful. 0 means success, 100 means not found, negative numbers indicate errors.

Samplebadu

COBOL by Example: SQL (Embedded)

Code

Explanation

Code Breakdown