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 Title not available 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 describes the general rules that apply when you embed any SQL command.

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

       DATE-COMPILED.          17 JULY 1987.

       REMARKS.                SQL'S SELECT WITH WHENEVER COMMAND.





       ENVIRONMENT DIVISION.

       CONFIGURATION SECTION.

       SOURCE-COMPUTER.        HP-3000.

       OBJECT-COMPUTER.        HP-3000.

       SPECIAL-NAMES.          CONSOLE IS TERMINAL-INPUT.





       INPUT-OUTPUT SECTION.





       FILE-CONTROL.

           SELECT CRT ASSIGN TO "$STDLIST".





       DATA DIVISION.





       FILE SECTION.

       FD CRT.

       01  PROMPT                  PIC X(34).

      $PAGE

       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  FILLER             PIC X(15) VALUE SPACES.





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

      $PAGE

       PROCEDURE DIVISION.





       A100-MAIN.





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





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

           EXEC SQL CONNECT TO '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.

      $PAGE

       B100-SELECT-DATA.





           MOVE SPACES TO RESPONSE.

           MOVE "Enter Part Number or '/' to STOP> "

                TO PROMPT.

           WRITE PROMPT AFTER ADVANCING 1 LINE.

           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.





      $PAGE

       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