Chapter 8 Embedded SQL

1
Chapter 8Embedded SQL
2
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

3
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

4
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

5
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

6
DATA DIVISION

• Tables to be processed are declared in
  WORKING-STORAGE
• Use the DECLARE TABLE command

7
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.

8
Code for Using Library

• EXEC SQL
• INCLUDE DECSALES_REP
• END-EXEC.

9
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.

10
SQLCA

• SQL communication area provides feedback to the
  program
• Example
• EXEC SQL
• INCLUDE SQLCA
• END-EXEC.

11
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
• FROM SALES_REP
• WHERE SLSREP_NUMBER 03
• Make provisions for exceptional conditions

12
Example 1

• Obtain the last name of sales rep number 03 and
  place it in W-LAST

13
Retrieve a Single Row and Column

• In SQL
• 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.

14
Example 2

• Obtain all information about the sales rep whose
  number is stored in the host variable
  W-SLSREP-NUMBER

15
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.

16
Program to Display Sales Rep Information
17
Program to Display Sales Rep Information (version
2)
18
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

19
Retrieve a Single Row from a Join

• EXEC SQL
• SELECT CUSTOMER.LAST, CUSTOMER.FIRST,
  CUSTOMER.STREET, CUSTOMER.CITY, CUSTOMER.STATE,
  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.

20
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.

21
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.

22
Program to Add Sales Reps
23
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

24
Change A Single Row In A Table

• EXEC SQL
• UPDATE SALES_REP
• SET LAST W-LAST
• WHERE SLSREP_NUMBER WSLSREP-NUMBER
• END-EXEC.

25
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

26
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.

27
Example 7

• Delete the sales rep whose number currently is
  stored in W-SLSREP-NUMBER from the SALES_REP table

28
Delete a Single Row From a Table

• EXEC SQL
• DELETE
• FROM SALES_REP
• WHERE SLSREP_NUMBER W-SLSREP-NUMBER
• END-EXEC.

29
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

30
DELETE Multiple Rows From a Table

• EXEC SQL
• DELETE
• FROM ORDER_LINE
• WHERE ORDER_NUMBER W-ORDER-NUMBER
• END-EXEC.

31
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)

32
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.

33
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

34
Using a Cursor

• EXEC SQL
• DECLARE CUSTGROUP CUROSR FOR
• SELECT CUSTOMER_NUMER, LAST, FIRST
• FROM CUSTOMER
• WHERE SLSREP_NUMBER W-SLSREP-NUMBER
• END-EXEC.

35
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

36
Opening a Cursor

• EXEC SQL
• OPEN CUSTGROUP
• END-EXEC.

37
Before OPEN
38
After OPEN, But Before First FETCH
39
Fetching Rows from a Cursor

• EXEC SQL
• FETCH CUSTGROUP
• INTO W-CUSTOMER-NUMBER, W-LAST, W-FIRST
• END-EXEC.

40
After First FETCH
41
After Second FETCH
42
After Third FETCH
43
After Attempting a Fourth FETCH
44
Closing a Cursor

• EXEC SQL
• CLOSE CUSTGROUP
• END-EXEC.

45
After CLOSE
46
Program to Display Customers of a Given Sales Rep
47
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

48
More Complex Cursors

• EXEC SQL
• DECLARE ORDGROUP CURSOR FOR
• SELECT ORDERS.ORDER_NUMBER, ORDERS.ORDER_DATE,
  CUSTOMER.CUSTOMER_NUMBER, CUSTOMER.LAST, 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_NUM
  BER
• AND CUSTOMER.SLSREP_NUMBER SALES_REP.SLSREP_NUMB
  ER
• ORDER BY CUSTOMER.CUSTOMER_NUMBER
• END-EXEC.

49
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)

50
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

51
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.

52
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

53
Error Handling

• Two types of error conditions
• Unusual but normal conditions
• Abnormal and unexpected conditions or fatal
  errors.

54
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

55
Abnormal and Unexpected Errors

• Value in SQLCODE is a negative number
• Appropriate action is to print a final message
  that indicates the problem and terminate the
  program
• Use the WHENEVER statement to handle these errors
  in a global way

56
WHENEVER Statement

• EXEC SQL
• WHENEVER SQLERROR GOTO ERROR-PROCESSING-ROUTINE
• END-EXEC.
• EXEC SQL
• WHENEVER SQLWARNING CONTINUE
• END-EXEC.
• EXEC SQL
• WHENEVER NOT FOUND CONTINUE
• END-EXEC.

57
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)

58
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
• The DATA DIVISION 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

59
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

60
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

61
Summary

• To see if an error has occurred, examine the
  value in SQLCODE
• Rather than checking SQLCODE in every place in
  the program where errors could occur, use the
  WHENEVER clause