Embedded SQL
IBM Training Groningen © Copyright IBM Corp. 2015 1
Embedded SQL in COBOL
Objectives
Embed SQL commands in COBOL
statements
Retrieve single rows using embedded SQL
Update a table using embedded INSERT,
UPDATE, and DELETE commands
Use cursors to retrieve multiple rows in
embedded SQL
Update a database using cursors
Learn how to handle errors in programs
containing embedded SQL commands
IBM Training Groningen © Copyright IBM Corp. 2015 2
Embedded SQL in COBOL
Introduction
Use a procedural language to use
embedded SQL commands to
accomplish tasks beyond the
capabilities of SQL
Use EXEC SQL and END-SQL in
COBOL programming to distinguish
embedded SQL commands from
standard COBOL statements
3
IBM Training Groningen © Copyright IBM Corp. 2015 3
Embedded SQL in COBOL
Introduction
DATA DIVISION (WORKING-
STORAGE SECTION) contains
statements declaring the tables that will
be used in processing the database
Communications area includes items
that allow SQL to communicate various
aspects of processing to the program
IBM Training Groningen © Copyright IBM Corp. 2015 4
Embedded SQL in COBOL
Introduction
SQLCODE contains a code indicating
the fate of the executed statement
Normal = zero
Not normal = value in SQLCODE that
indicates problem
PROCEDURE DIVISION contain new
statements that will be SQL statements
with slight variations
IBM Training Groningen © Copyright IBM Corp. 2015 5
Embedded SQL in COBOL
DATA DIVISION
Tables to be processed are declared in
WORKING-STORAGE
Use the DECLARE TABLE command
IBM Training Groningen © Copyright IBM Corp. 2015 6
Embedded SQL in COBOL
Create SALES_REP Table
EXEC SQL
DECLARE SALES_REP TABLE
(SLSREP_NUMBER DECIMAL (2),
LAST CHAR (10),
FIRST CHAR (8),
STREET CHAR (15),
CITY CHAR (15),
STATE CHAR (2),
ZIP_CODE CHAR (5),
TOTAL_COMMISSION DECIMAL (7,2),
COMMISSION_RATE DECIMAL (3,2) )
END-EXEC.
Note: This is the database representation of the data
IBM Training Groningen © Copyright IBM Corp. 2015 7
Embedded SQL in COBOL
Code for Using Library
EXEC SQL
INCLUDE DECSALES_REP
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 8
Embedded SQL in COBOL
COBOL Variables
01 W-SALES-REP.
03 W-SLSREP_NUMBER PIC S9(2) COMP-3.
03 W-LAST PIC X(10).
03 W-FIRST PIC X(8).
03 W-STREET PIC X(15).
03 W-CITY PIC X(15).
03 W-STATE PIC X(2).
03 W-ZIP-CODE PIC X(5).
03 W-TOTAL-COMMISSION PIC S9(5)V9(2) COMP-3.
03 W-COMMISSION-RATE PIC S9V9(2) COMP-3.
Note: This is where the database data will be stored for processing by COBOL
IBM Training Groningen © Copyright IBM Corp. 2015 9
Embedded SQL in COBOL
SQLCA
SQL communication area provides
feedback to the program
Example:
EXEC SQL
INCLUDE SQLCA
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 10
Embedded SQL in COBOL
PROCEDURE DIVISION
Use Host variables
Proceed the variable with a colon
Replace results of SQL queries in host
variables in the INTO CLAUSE
SELECT LAST
INTO :W-LAST this is a COBOL variable name
FROM SALES_REP
WHERE SLSREP_NUMBER = “03” This is DB variable
Make provisions for exceptional conditions
IBM Training Groningen © Copyright IBM Corp. 2015 11
Embedded SQL in COBOL
Example 1
Obtain the last name of sales rep
number 03 and place it in W-LAST
IBM Training Groningen © Copyright IBM Corp. 2015 12
Embedded SQL in COBOL
Retrieve a Single Row and
In SQL:
Column
SELECT LAST
FROM SALES_REP
WHERE SLSREP_NUMBER = “03”;
In COBOL:
EXEC SQL
SELECT LAST
INTO :W-LAST
FROM SALES_REP
WHERE SLSREP_NUMBER = “03”
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 13
Embedded SQL in COBOL
Example 2
Obtain all information about the sales
rep whose number is stored in the host
variable W-SLSREP-NUMBER
IBM Training Groningen © Copyright IBM Corp. 2015 14
Embedded SQL in COBOL
Retrieve a Single Row and All
Columns
EXEC SQL
SELECT LAST, FIRST, STREET, CITY, STATE, ZIP_CODE,
TOTAL_COMMISSION, COMMISSION RATE
INTO :W-LAST, :W-FIRST, :W-STREEET, :W-CITY,
:W-STATE, :W-ZIP-CODE, :W-TOTAL-COMMISSION,
:W-COMMISSION-RATE
FROM SALES_REP
WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 15
Embedded SQL in COBOL
Example 3
Obtain the last name, first name, and
address of the customer whose
customer number is stored in the host
variable, W-CUSTOMER-NUMBER, as
well as the number, last name, and first
name of the sales rep who represents
this customer
IBM Training Groningen © Copyright IBM Corp. 2015 16
Embedded SQL in COBOL
Retrieve a Single Row from a Join
EXEC SQL
SELECT [Link], [Link], [Link],
[Link], [Link], CUSTOMER.ZIP_CODE,
CUSTOMER.SLSREP_NUMBER, SALES_REP.LAST,
SALES_REP.FIRST
INTO :W-LAST OF :W-CUSTOMER,
:W-FIRST OF :W-CUSTOMER, :W-STREET OF :W-
CUSTOMER, :W-CITY OF :W-CUSTOMER,
:W-STATE OF :W-CUSTMOER, :W-ZIP-CODE OF
:W-CUSTOMER, :W-SLSREP-NUMBER OF
:W-CUSTOMER, :W-LAST OF :W-SLAES-REP,
:W-FIRST OF :W-SALES-REP
FROM SALES-REP, CUSTOMER
WHERE SALES-REP.SLSREP_NUMBER =
CUSTOMER.SLSREP_NUMBER
AND CUSTOMER.CUSTOMER_NUMBER =
:W-CUSTOMER-NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 17
Embedded SQL in COBOL
Example 4
Add a row to the SALES_REP table
The sales rep number, last name, first name
address, total commission, and credit limit
already have been placed in the variables W-
SLSREP-NUMBER, W-LAST, W-FIRST, W-
STREET, W-CITY, W-STATE, W-ZIP-CODE,
W-TOTAL-COMMISION, and W-
COMMISSION-RATE, respectively.
IBM Training Groningen © Copyright IBM Corp. 2015 18
Embedded SQL in COBOL
Insert a Row Into a Table
EXEC SQL
INSERT
INTO SALES_REP
VALUES (:W-SLSREP-NUMBER, :W-LAST, :W-FIRST,
:W-STREET, :W-CITY, :W-STATE, :W-ZIP-CODE,
:W-TOTAL-COMMISSION, :W-COMMISSION-RATE)
END-EXEC.
19
IBM Training Groningen © Copyright IBM Corp. 2015 19
Embedded SQL in COBOL
Example 5
Change the last name of the sales rep
whose number currently is stored in W-
SLSREP-NUMBER to the value
currently stored in W-LAST
IBM Training Groningen © Copyright IBM Corp. 2015 20
Embedded SQL in COBOL
Change A Single Row In A
Table
EXEC SQL
UPDATE SALES_REP
SET LAST = :W-LAST
WHERE SLSREP_NUMBER = :WSLSREP-NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 21
Embedded SQL in COBOL
Example 6
Add the amount stored in the host
variable INCREASE-IN-RATE to the
commission rate for all sales reps who
currently represent any customer having
a credit limit of $1,000
22
IBM Training Groningen © Copyright IBM Corp. 2015 22
Embedded SQL in COBOL
Change Multiple Rows in a
Table
EXEC SQL
UPDATE SALES_REP
SET COMMISSION_RATE = COMMISSION_RATE +
:INCREASE-IN-RATE
WHERE SLSREP_NUMBER IN
(SELECT SLSREP_NUMBER
FROM CUSTOMER
WHERE CREDIT_LIMIT = 1000)
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 23
Embedded SQL in COBOL
Example 7
Delete the sales rep whose number
currently is stored in W-SLSREP-
NUMBER from the SALES_REP table
IBM Training Groningen © Copyright IBM Corp. 2015 24
Embedded SQL in COBOL
Delete a Single Row From a
Table
EXEC SQL
DELETE
FROM SALES_REP
WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 25
Embedded SQL in COBOL
Example 8
Delete every order line for the order
whose order number currently is stored
in the host variable W-ORDER-
NUMBER from the ORDER_LINE table
IBM Training Groningen © Copyright IBM Corp. 2015 26
Embedded SQL in COBOL
DELETE Multiple Rows From
a Table
EXEC SQL
DELETE
FROM ORDER_LINE
WHERE ORDER_NUMBER = :W-ORDER-NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 27
Embedded SQL in COBOL
Multiple-Row SELECT
Example of SELECT statement producing
multiple rows
EXEC SQL
SELECT CUSTOMER_NUMBER, LAST, FIRST
INTO :W-CUSTOMER-NUMBER, :W-LAST, :W-FIRST
FROM CUSTOMER
WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
END-EXEC
Problem:
COBOL can process only one record at a time,
where this SQL command produces multiple rows
(records)
28
IBM Training Groningen © Copyright IBM Corp. 2015 28
Embedded SQL in COBOL
Cursors
Cursor
A pointer to a row in the collection of rows
retrieved by a SQL statement
Advances one row at time to provide sequential,
record-at-a-time access to the retrieved rows so
COBOL can process the rows
Using a cursor, COBOL can process the set of
retrieved rows as though they were records in a
sequential file.
IBM Training Groningen © Copyright IBM Corp. 2015 29
Embedded SQL in COBOL
Example 9
Retrieve the number, last name, and
first name of every customer
represented by the sales rep whose
number is stored in the host variable W-
SLSREP-NUMBER
IBM Training Groningen © Copyright IBM Corp. 2015 30
Embedded SQL in COBOL
Using a Cursor
EXEC SQL
DECLARE CUSTGROUP CUROSR FOR
SELECT CUSTOMER_NUMER, LAST, FIRST
FROM CUSTOMER
WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 31
Embedded SQL in COBOL
OPEN, FETCH,CLOSE
OPEN, FETCH, and CLOSE commands
are used in processing a cursor
Analogous to the OPEN, READ, and
CLOSE commands used in processing
a sequential file
IBM Training Groningen © Copyright IBM Corp. 2015 32
Embedded SQL in COBOL
Opening a Cursor
EXEC SQL
OPEN CUSTGROUP
END-EXEC.
This causes the Cursor’s SQL statement to be executed
IBM Training Groningen © Copyright IBM Corp. 2015 33
Embedded SQL in COBOL
Before OPEN
IBM Training Groningen © Copyright IBM Corp. 2015 34
Embedded SQL in COBOL
After OPEN, But Before First
FETCH
IBM Training Groningen © Copyright IBM Corp. 2015 35
Embedded SQL in COBOL
Fetching Rows from a Cursor
EXEC SQL
FETCH CUSTGROUP
INTO :W-CUSTOMER-NUMBER, :W-LAST, :W-FIRST
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 36
Embedded SQL in COBOL
After First FETCH
IBM Training Groningen © Copyright IBM Corp. 2015 37
Embedded SQL in COBOL
After Second FETCH
IBM Training Groningen © Copyright IBM Corp. 2015 38
Embedded SQL in COBOL
After Third FETCH
IBM Training Groningen © Copyright IBM Corp. 2015 39
Embedded SQL in COBOL
After Attempting a Fourth
FETCH
IBM Training Groningen © Copyright IBM Corp. 2015 40
Embedded SQL in COBOL
Closing a Cursor
EXEC SQL
CLOSE CUSTGROUP
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 41
Embedded SQL in COBOL
Example 10
For every order that contains an order line for
the part whose part number is stored in W-
PART-NUMBER, retrieve the order number,
order date, last name, and first name of the
customer who placed the order, and the
number, last name, and first name of the
sales rep who represent the customer
Sort the results by customer number
IBM Training Groningen © Copyright IBM Corp. 2015 42
Embedded SQL in COBOL
More Complex Cursors
EXEC SQL
DECLARE ORDGROUP CURSOR FOR
SELECT ORDERS.ORDER_NUMBER, ORDERS.ORDER_DATE,
CUSTOMER.CUSTOMER_NUMBER, [Link], FIRST,
SALES_REP.SLSREP_NUMBER, SALES_REP.LAST, FIRST
FROM ORDER_LINE, ORDERS, CUSTOMER, SALES_REP
WHERE ORDER_LINE.PART_NUMBER = :W-PART-NUMBER
AND ORDER_LINE.ORDER_NUMBER =
ORDERS.ORDER_NUMBER
AND ORDERS.CUSTOMER_NUMBER =
CUSTOMER.CUSTOMER_NUMBER
AND CUSTOMER.SLSREP_NUMBER =
SALES_REP.SLSREP_NUMBER
ORDER BY CUSTOMER.CUSTOMER_NUMBER
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 43
Embedded SQL in COBOL
Advantages of Cursors
The coding the in the program is greatly simplified
A special component of the database management
system called the optimizer determines the best way
to access the data
If the database structure changes in such a way that
the necessary information is still obtainable using a
different query, the only change required in the
program is the cursor definition in WORKING-
STORAGE (PRODECURE DIVISION code is not
affected)
IBM Training Groningen © Copyright IBM Corp. 2015 44
Embedded SQL in COBOL
Example 11
Add $100 to the credit limit for every customer
represented by the sales rep whose number currently
is stored in the host variable W-SLSREP-NUMBER,
whose balance is not over the credit limit, and whose
credit limit is $500 or less
Add $200 to the credit limit of every customer of this
sales rep whose balance is not over the credit limit
and whose credit limit is more than $500
Write the number, last name, and first name of every
customer of this sales rep whose balance is greater
than the credit limit
IBM Training Groningen © Copyright IBM Corp. 2015 45
Embedded SQL in COBOL
Updating Cursors
EXEC SQL
DECLARE CREDGROUP CURSOR FOR
SELECT CUSTOMER_NUMBER, LAST, FIRST,
BALANCE, CREDIT_LIMIT
FROM CUSTOMER
WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
FOR UPDATE OF CREDIT-LIMIT
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 46
Embedded SQL in COBOL
Code to FETCH a Row
EXEC SQL
FETCH CREDGROUP
INTO :W-CUSTOMER-NUMBER, :W-LAST, :W-FIRST, :W-BALANCE, :W-CREDIT-LIMIT
END-EXEC
IF SQLCODE = 100
MOVE “NO” TO ARE-THERE-MORE-CUSTOMERS
ELSE
PERFORM CUSTOMER-UPDATE.
CUSTOMER-UPDATE.
IF W-CREDIT-LIMIT > W-BLANCE
DISPLAY W-FIRST, W-LAST
ELSE IF W-CREDIT-LIMIT > 500
EXEC SQL
UPDATE CUSTOMER
SET CREDIT-LIMIT = CREDIT_LIMIT + 200
WHERE CURRENT OF CREDGROUP
END-EXEC
ELSE
EXEC SQL
UPDATE CUSTOMER
SET CREDIT_LIMIT = CREDIT_LIMIT + 100
WHERE CURRENT OF CREDGROUP
END-EXEC.
IBM Training Groningen © Copyright IBM Corp. 2015 47
Embedded SQL in COBOL
Error Handling
Two types of error conditions
Unusual but normal conditions
Abnormal and unexpected conditions or
fatal errors.
IBM Training Groningen © Copyright IBM Corp. 2015 48
Embedded SQL in COBOL
Unusual Conditions Errors
The value in SQLCODE will be a positive
number
Appropriate action is to print an error
message and continue processing
Appropriate action for END OF DATA
(SQLCODE = 100) is termination of some
loop and continuation with the rest of the
program
No error message is required
IBM Training Groningen © Copyright IBM Corp. 2015 49
Embedded SQL in COBOL
Error Codes
SQLERROR = abnormal or fatal
condition (SQLCODE < 0)
SQLWARNING = unusual but normal
conditions (SQLCODE > 0)
NOT FOUND = special warning END
OF DATA (SQLCODE =100)
IBM Training Groningen © Copyright IBM Corp. 2015 50
Embedded SQL in COBOL
Summary
To embed SQL commands in a COBOL program,
precede the SQL command with EXEC SQL, and follow
the command with END-EXEC
Statements to define the tables to be accessed must
appear in the DATA DIVISION / WORKING-STORAGE
The DATA DIVISION / WORKING-STORAGE must
contain the INCLUDE SQLCA statement, which allows
access to the SQL communication area
You can use host language variables (variables that are
not columns within a table) in embedded SQL
commands by preceding the variable name with a colon
IBM Training Groningen © Copyright IBM Corp. 2015 51
Embedded SQL in COBOL
Summary
You can use SELECT statements as embedded SQL
commands in COBOL programs only when a single row is
retrieved
To place the results of a SELECT statement into host
language variables, use the INTO clause in the SELECT
command
You can use INSERT, UPDATE, and DELETE statements
in COBOL programs, even when they affect more than
one row
If a SELECT statement is to retrieve more than one row, it
must be used to define a cursor that will be used to select
one row at a time
IBM Training Groningen © Copyright IBM Corp. 2015 52
Embedded SQL in COBOL
Summary
To activate a cursor, use the OPEN command to
execute the query in the cursor definition
To select the next row in COBOL, use the FETCH
command
To deactivate a cursor, use the CLOSE command.
The rows initially retrieved will not longer be available
to COBOL
DATA in the tables on which a cursor is based can be
updated by including the WHERE CURRENT OF
cursor name clause in the update statement
This clause updates only the current (most recently fetched)
row
IBM Training Groningen © Copyright IBM Corp. 2015 53
Embedded SQL in COBOL
Summary
To see if an error has occurred, examine the value in
SQLCODE
USE EVALUATE
IBM Training Groningen © Copyright IBM Corp. 2015 54
Embedded SQL in COBOL
Thank You
IBM Training Groningen © Copyright IBM Corp. 2015 55