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