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