Embedded SQL allows COBOL programs to interact with databases directly by embedding SQL statements within the COBOL code. This integration provides a powerful way to perform database operations such as querying, updating, and managing data within a COBOL application.
Key Concepts
- Embedded SQL: SQL statements are written directly within the COBOL program.
- Host Variables: COBOL variables used to pass data between the COBOL program and the SQL statements.
- EXEC SQL: The keyword used to start an embedded SQL statement.
- END-EXEC: The keyword used to end an embedded SQL statement.
Basic Structure
An embedded SQL statement in COBOL typically follows this structure:
Example
Here is a simple example of an embedded SQL statement in a COBOL program:
IDENTIFICATION DIVISION.
PROGRAM-ID. EmbeddedSQLExample.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMP-ID PIC 9(5).
01 WS-EMP-NAME PIC X(20).
PROCEDURE DIVISION.
EXEC SQL
SELECT EMP_NAME
INTO :WS-EMP-NAME
FROM EMPLOYEE
WHERE EMP_ID = :WS-EMP-ID
END-EXEC.
DISPLAY 'Employee Name: ' WS-EMP-NAME.
STOP RUN.Explanation
- Host Variables:
WS-EMP-IDandWS-EMP-NAMEare COBOL variables used in the SQL statement. - EXEC SQL ... END-EXEC: The SQL statement is embedded between these keywords.
- SELECT Statement: Retrieves the employee name from the
EMPLOYEEtable where theEMP_IDmatchesWS-EMP-ID.
Practical Example
Let's create a more detailed example that includes connecting to a database, querying data, and handling the results.
Example: Fetching Employee Details
IDENTIFICATION DIVISION.
PROGRAM-ID. FetchEmployeeDetails.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMP-ID PIC 9(5) VALUE 12345.
01 WS-EMP-NAME PIC X(20).
01 WS-EMP-SALARY PIC 9(7)V99.
PROCEDURE DIVISION.
EXEC SQL
CONNECT TO 'DBNAME' USER 'username' USING 'password'
END-EXEC.
EXEC SQL
SELECT EMP_NAME, EMP_SALARY
INTO :WS-EMP-NAME, :WS-EMP-SALARY
FROM EMPLOYEE
WHERE EMP_ID = :WS-EMP-ID
END-EXEC.
DISPLAY 'Employee Name: ' WS-EMP-NAME.
DISPLAY 'Employee Salary: ' WS-EMP-SALARY.
EXEC SQL
COMMIT
END-EXEC.
EXEC SQL
DISCONNECT CURRENT
END-EXEC.
STOP RUN.Explanation
- CONNECT Statement: Connects to the database using the provided credentials.
- SELECT Statement: Retrieves the employee name and salary from the
EMPLOYEEtable. - COMMIT Statement: Commits the transaction.
- DISCONNECT Statement: Disconnects from the database.
Practical Exercises
Exercise 1: Retrieve Department Name
Task: Write a COBOL program that retrieves the department name for a given department ID.
Solution:
IDENTIFICATION DIVISION.
PROGRAM-ID. FetchDepartmentName.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-DEPT-ID PIC 9(3) VALUE 101.
01 WS-DEPT-NAME PIC X(30).
PROCEDURE DIVISION.
EXEC SQL
CONNECT TO 'DBNAME' USER 'username' USING 'password'
END-EXEC.
EXEC SQL
SELECT DEPT_NAME
INTO :WS-DEPT-NAME
FROM DEPARTMENT
WHERE DEPT_ID = :WS-DEPT-ID
END-EXEC.
DISPLAY 'Department Name: ' WS-DEPT-NAME.
EXEC SQL
COMMIT
END-EXEC.
EXEC SQL
DISCONNECT CURRENT
END-EXEC.
STOP RUN.Exercise 2: Update Employee Salary
Task: Write a COBOL program that updates the salary of an employee based on their employee ID.
Solution:
IDENTIFICATION DIVISION.
PROGRAM-ID. UpdateEmployeeSalary.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMP-ID PIC 9(5) VALUE 12345.
01 WS-NEW-SALARY PIC 9(7)V99 VALUE 75000.00.
PROCEDURE DIVISION.
EXEC SQL
CONNECT TO 'DBNAME' USER 'username' USING 'password'
END-EXEC.
EXEC SQL
UPDATE EMPLOYEE
SET EMP_SALARY = :WS-NEW-SALARY
WHERE EMP_ID = :WS-EMP-ID
END-EXEC.
DISPLAY 'Employee salary updated successfully.'
EXEC SQL
COMMIT
END-EXEC.
EXEC SQL
DISCONNECT CURRENT
END-EXEC.
STOP RUN.Common Mistakes and Tips
- Unmatched Host Variables: Ensure that the host variables in the SQL statement match the COBOL variables in terms of data type and length.
- SQL Errors: Always check for SQL errors after executing an SQL statement. Use SQLCODE and SQLSTATE for error handling.
- Proper Disconnection: Always disconnect from the database to free up resources.
Conclusion
Embedded SQL in COBOL provides a seamless way to integrate database operations within COBOL programs. By understanding the basic structure and practicing with examples, you can effectively manage and manipulate data in your COBOL applications. In the next section, we will delve into handling cursors in COBOL, which is essential for processing multiple rows of data retrieved from a database.