Chapter 3 Embedding SQL Commands

Table of Contents

In every ALLBASE/SQL COBOL program, you embed SQL commands in the DATA DIVISION and the PROCEDURE DIVISION in order to:

1: Declare the SQL Communications Area (SQLCA)
2: Declare host variables
3: Start a DBE session by connecting to the DBEnvironment
4 , 5: Define transactions
6: Terminate the DBE session
7: Implicitly check the status of SQL command execution
8: Define or manipulate data in the DBEnvironment
9: Explicitly check the status of SQL command execution
10: Obtain error and warning messages from the ALLBASE/SQL message catalog

The program listing shown in Figure 3-1 “Sample Program COBEX2 ” illustrates where in a program you can embed SQL commands to accomplish the activities listed above.

This chapter is a high-level road map to the logical and physical aspects of embedding SQL commands in a program. It addresses the reasons for embedding commands to perform the above activities. It also gives general rules for how and where to embed SQL commands for these activities. First however, it shows a program containing commands for the basic SQL functions listed above. Then it describes the general rules that apply when you embed any SQL command, referring to the numbered statements in the program.

Figure 3-1 Sample Program COBEX2



   * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

   * Program COBEX2:                                             *

   * This program illustrates the use of SQL's SELECT command to *

   * retrieve one row at a time.                                 *

   * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



    IDENTIFICATION DIVISION.



    PROGRAM-ID.             COBEX2.

    AUTHOR.                 HP TRAINING

    INSTALLATION.           HP.

    DATE-WRITTEN.           17 OCT 1987.

    DATE-COMPILED.          17 OCT 1987.



    ENVIRONMENT DIVISION.

    CONFIGURATION SECTION.

    SOURCE-COMPUTER.        HP-9000.

    OBJECT-COMPUTER.        HP-9000.



    INPUT-OUTPUT SECTION.



    FILE-CONTROL.

    SELECT TERM ASSIGN TO ":CO:".



    DATA DIVISION.



    FILE SECTION.

    FD TERM.

    01  PROMPT                  PIC X(34).



    WORKING-STORAGE SECTION.



    EXEC SQL INCLUDE SQLCA END-EXEC.                                      1 



   * * * * * *   BEGIN HOST VARIABLE DECLARATIONS  * * * * * * *

    EXEC SQL BEGIN DECLARE SECTION END-EXEC.                              2 

    01  PARTNUMBER              PIC X(16).

    01  PARTNAME                PIC X(30).

    01  SALESPRICE              PIC S9(8)V99 COMP-3.

    01  SALESPRICEIND           SQLIND.

    01  SQLMESSAGE              PIC X(132).

    EXEC SQL END DECLARE SECTION END-EXEC.

   * * * * * *   END OF HOST VARIABLE DECLARATIONS * * * * * * *





    77  DONE-FLAG              PIC X(01)  VALUE 'N'.

        88  NOT-DONE           VALUE 'N'.

        88  DONE               VALUE 'Y'.



    77  ABORT-FLAG             PIC X(01)  VALUE 'N'.

        88  NOT-STOP           VALUE 'N'.

        88  ABORT              VALUE 'Y'.



    01  DEADLOCK               PIC S9(9) COMP VALUE -14024.



    01  RESPONSE.

        05  RESPONSE-PREFIX    PIC X(01) VALUE SPACE.

        05  RESPONSE-SUFFIX    PIC X(15) VALUE SPACES.



    01  DOLLARS                 PIC $$$,$$$,$$$.99.



    PROCEDURE DIVISION.



    A100-MAIN.



        ACCEPT RESPONSE.



        DISPLAY "Program to SELECT specified rows from "

                "the Parts Table - COBEX2".

        DISPLAY " ".

        DISPLAY "Event List:".

        DISPLAY "  Connect to PartsDBE".

        DISPLAY "  Begin Work".

        DISPLAY "  SELECT specified Part Number from the "

                "Parts Table until user enters '/' ".

        DISPLAY "  Commit Work".

        DISPLAY "  Disconnect from PartsDBE".

        DISPLAY " ".



        OPEN OUTPUT TERM.



        PERFORM A200-CONNECT-DBENVIRONMENT  THRU  A200-EXIT.



        PERFORM B100-SELECT-DATA THRU B100-EXIT

                UNTIL DONE.



        PERFORM A500-TERMINATE-PROGRAM THRU  A500-EXIT.



    A100-EXIT.

        EXIT.



    A200-CONNECT-DBENVIRONMENT.



        EXEC SQL

             WHENEVER SQLERROR

             GO TO S300-SERIOUS-ERROR

        END-EXEC.



        DISPLAY "Connect to ../sampledb/PartsDBE".



        EXEC SQL CONNECT TO '../sampledb/PartsDBE' END-EXEC.             3 



    A200-EXIT.

        EXIT.



    A300-BEGIN-TRANSACTION.



        DISPLAY "Begin Work".

        EXEC SQL

             BEGIN WORK                                                   4 

        END-EXEC.



    A300-EXIT.

        EXIT.



    A400-END-TRANSACTION.



        DISPLAY "Commit Work".

        EXEC SQL

             COMMIT WORK                                                  5 

        END-EXEC.



    A400-EXIT.

        EXIT.



    A500-TERMINATE-PROGRAM.



        EXEC SQL

             RELEASE                                                      6 

        END-EXEC.



        STOP RUN.



    A500-EXIT.

        EXIT.



    B100-SELECT-DATA.



        MOVE SPACES TO RESPONSE.

        MOVE "Enter Part Number within Parts Table or '/' to STOP> "

             TO PROMPT.

        DISPLAY " ".

        WRITE PROMPT.

        ACCEPT RESPONSE.



        IF  RESPONSE-PREFIX = "/"

            MOVE "Y" TO DONE-FLAG

            GO TO B100-EXIT

        ELSE

            MOVE RESPONSE TO PARTNUMBER.



        EXEC SQL

             WHENEVER SQLERROR                                            7 

             GO TO S400-SQL-ERROR

        END-EXEC.



        EXEC SQL

             WHENEVER SQLWARNING

             GO TO S500-SQL-WARNING

        END-EXEC.



        EXEC SQL

             WHENEVER NOT FOUND

             GO TO S600-NOT-FOUND

        END-EXEC.



        DISPLAY "SELECT PartNumber, PartName and SalesPrice".



        PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT.



        EXEC SQL

             SELECT  PARTNUMBER, PARTNAME, SALESPRICE                     8 

               INTO :PARTNUMBER,

                    :PARTNAME,

                    :SALESPRICE :SALESPRICEIND

               FROM  PURCHDB.PARTS

              WHERE  PARTNUMBER = :PARTNUMBER

        END-EXEC.



        PERFORM A400-END-TRANSACTION THRU A400-EXIT.

        PERFORM B200-DISPLAY-ROW THRU B200-EXIT.



    B100-EXIT.

        EXIT.



    B200-DISPLAY-ROW.



        DISPLAY " ".

        DISPLAY "  Part Number:  " PARTNUMBER.

        DISPLAY "  Part Name:    " PARTNAME.



        IF  SALESPRICEIND < 0

            DISPLAY "  Sales Price is NULL"

        ELSE

            MOVE SALESPRICE  TO  DOLLARS

            DISPLAY "  Sales Price:  " DOLLARS.



    B200-EXIT.

        EXIT.





    S100-STATUS-CHECK.



        IF  SQLCODE  <  DEADLOCK                                         9 

            MOVE 'Y' TO ABORT-FLAG.



        PERFORM S200-SQL-EXPLAIN THRU S200-EXIT

                UNTIL SQLCODE = 0.



    S100-EXIT.

        EXIT.



    S200-SQL-EXPLAIN.



        EXEC SQL

             SQLEXPLAIN  :SQLMESSAGE                                     10 

        END-EXEC.



        DISPLAY SQLMESSAGE.



    S200-EXIT.

        EXIT.



    S300-SERIOUS-ERROR.



        PERFORM S100-STATUS-CHECK  THRU  S100-EXIT.

        PERFORM A500-TERMINATE-PROGRAM  THRU  A500-EXIT.



    S300-EXIT.

        EXIT.



    S400-SQL-ERROR.



        PERFORM S100-STATUS-CHECK THRU S100-EXIT.



        IF  ABORT-FLAG = 'Y'

            PERFORM A500-TERMINATE-PROGRAM

        ELSE

            PERFORM A400-END-TRANSACTION THRU A400-EXIT

            GO TO B100-EXIT.



    S400-EXIT.

        EXIT.



    S500-SQL-WARNING.



        DISPLAY "SQL WARNING has occurred.  The following row "

                "of data may not be valid:".



        PERFORM B200-DISPLAY-ROW THRU B200-EXIT.



        PERFORM A400-END-TRANSACTION  THRU A400-EXIT.



        GO TO B100-EXIT.



    S500-EXIT.

        EXIT.



    S600-NOT-FOUND.



        DISPLAY " ".

        DISPLAY "Part Number not found!".



        PERFORM A400-END-TRANSACTION THRU A400-EXIT.



        GO TO B100-EXIT.



    S600-EXIT.

        EXIT.

Chapter 3 Embedding SQL Commands

Technical documentation

» Table of Contents

» Index