HP 3000 Manuals HP 3000 Manuals
Chapter 3 Embedding SQL Commands
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 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.
_____________________________________________________________________________
| |
| |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| * 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 * * * * * * * |
| |
| |
_____________________________________________________________________________
Figure 3-1. Sample Program COBEX2
___________________________________________________________________
| |
| 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. |
| |
| |
| |
| |
___________________________________________________________________
Figure 3-1. Sample Program COBEX2 (page 2 of 6)
_____________________________________________________________________________
| |
| 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 |
| |
| |
| |
_____________________________________________________________________________
Figure 3-1. Sample Program COBEX2 (page 3 of 6)
_____________________________________________________________________________
| |
| 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. |
_____________________________________________________________________________
Figure 3-1. Sample Program COBEX2 (page 4 of 6)
_____________________________________________________________________________
| |
| 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. |
| |
| |
_____________________________________________________________________________
Figure 3-1. Sample Program COBEX2 (page 5 of 6)
______________________________________________________________________
| |
| 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. |
| |
| |
| |
| |
______________________________________________________________________
Figure 3-1. Sample Program COBEX2 (page 6 of 6)