Chapter 8 Embedded SQL

							     Chapter 8
   Embedded SQL



A Guide to SQL, Fifth Edition

                                1
            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
                                           2
         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
           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

                                            4
          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
                                         5
       DATA DIVISION
Tables to be processed are declared in
WORKING-STORAGE
Use the DECLARE TABLE command




                                         6
   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.
                                                7
      Code for Using Library
EXEC SQL
   INCLUDE DECSALES_REP
END-EXEC.




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


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




                                  10
  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

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




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

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




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




                                                        15
Program to Display Sales Rep
        Information




                           16
Program to Display Sales Rep
   Information (version 2)




                           17
            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

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


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




                                                     21
Program to Add Sales Reps




                            22
           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




                                         23
  Change A Single Row In A
           Table
EXEC SQL
   UPDATE SALES_REP
      SET LAST = :W-LAST
      WHERE SLSREP_NUMBER = :WSLSREP-NUMBER
END-EXEC.




                                              24
           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




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




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




                                    27
 Delete a Single Row From a
            Table
EXEC SQL
   DELETE
      FROM SALES_REP
      WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
END-EXEC.




                                               28
           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




                                         29
DELETE Multiple Rows From
        a Table
EXEC SQL
   DELETE
      FROM ORDER_LINE
      WHERE ORDER_NUMBER = :W-ORDER-NUMBER
END-EXEC.




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


                                                       32
           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




                                       33
            Using a Cursor
EXEC SQL
   DECLARE CUSTGROUP CUROSR FOR
      SELECT CUSTOMER_NUMER, LAST, FIRST
      FROM CUSTOMER
      WHERE SLSREP_NUMBER = :W-SLSREP-NUMBER
END-EXEC.




                                               34
  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




                                35
         Opening a Cursor
EXEC SQL
   OPEN CUSTGROUP
END-EXEC.




                            36
Before OPEN




              37
After OPEN, But Before First
         FETCH




                               38
 Fetching Rows from a Cursor
EXEC SQL
   FETCH CUSTGROUP
      INTO :W-CUSTOMER-NUMBER, :W-LAST, :W-FIRST
END-EXEC.




                                                   39
After First FETCH




                    40
After Second FETCH




                     41
After Third FETCH




                    42
After Attempting a Fourth
          FETCH




                            43
          Closing a Cursor
EXEC SQL
   CLOSE CUSTGROUP
END-EXEC.




                             44
After CLOSE




              45
Program to Display Customers of a
        Given Sales Rep




                                    46
             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


                                              47
      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_NUMBER
      AND CUSTOMER.SLSREP_NUMBER =
         SALES_REP.SLSREP_NUMBER
      ORDER BY CUSTOMER.CUSTOMER_NUMBER
END-EXEC.
                                                       48
    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)

                                                        49
              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

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




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


                                                                                      52
         Error Handling
Two types of error conditions
 Unusual but normal conditions
 Abnormal and unexpected conditions or
  fatal errors.




                                          53
 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

                                                54
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

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




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


                                  57
                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
                                                      58
                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
                                                   59
                    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
                                                               60
                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




                                                    61