HP 3000 Manuals HP 3000 Manuals
Approaches to Status Checking
This section presents examples of how to use implicit and explicit status
checking and to notify program users of the results of status checking.
Implicit status checking is useful when control to handle warnings and
errors can be passed to one predefined point in the program.
Explicit status checking is useful when you want to test for specific
SQLCA values before passing control to one of several locations in your
program.
Error and warning conditions detected by either type of status checking
can be conveyed to the program user in various ways:
* SQLEXPLAIN can be used one or more times after an SQL command is
processed to retrieve warning and error messages from the
ALLBASE/SQL message catalog. (The ALLBASE/SQL message catalog
contains messages for every negative SQLCODE and for every
condition that sets SQLWARN0.)
* Your own messages can be displayed when a certain condition
occurs.
* You can choose not to display a message; for example, if a
condition exists that is irrelevant to the program user or when an
error is handled internally by the program.
Implicit Status Checking Techniques
The WHENEVER command has two components: a condition and an action. The
command format is:
EXEC SQL WHENEVER Condition Action END-EXEC.
There are three possible WHENEVER conditions:
* SQLERROR
If WHENEVER SQLERROR is in effect, ALLBASE/SQL checks for a
negative SQLCODE after processing any SQL command except:
BEGIN DECLARE SECTION
DECLARE
END DECLARE SECTION
INCLUDE
SQLEXPLAIN
WHENEVER
* SQLWARNING
If WHENEVER SQLWARNING is in effect, ALLBASE/SQL checks for a W in
SQLWARN0 after processing any SQL command except:
BEGIN DECLARE SECTION
DECLARE
END DECLARE SECTION
INCLUDE
SQLEXPLAIN
WHENEVER
* NOT FOUND
If WHENEVER NOT FOUND is in effect, ALLBASE/SQL checks for the
value 100 in SQLCODE after processing a SELECT or FETCH command.
A WHENEVER command for each of these conditions can be in effect at the
same time.
There are three possible WHENEVER actions:
* STOP
If WHENEVER Condition STOP is in effect, ALLBASE/SQL rolls back
the current transaction and terminates the DBE session and the
program when the Condition exists.
* CONTINUE
If WHENEVER Condition CONTINUE is in effect, program execution
continues when the Condition exists. Any earlier WHENEVER command
for the same condition is cancelled.
* GOTO LineLabel.
If WHENEVER Condition GOTO LineLabel is in effect, the code
routine located at that alpha-numeric line label is executed when
the Condition exists. The line label must appear in the paragraph
where the GOTO is executed.
GOTO and GO TO forms of this action have exactly the same effect.
Any action may be specified for any condition.
The WHENEVER command causes the COBOL preprocessor to generate
status-checking and status-handling code for each SQL command that comes
after it physically in the program until another WHENEVER command for the
same condition is found. In the following program sequence, for example,
the WHENEVER command in Procedure1 is in effect for SQLCommand1, but not
for SQLCommand2, even though SQLCommand1 is executed first at run time:
PERFORM PARAGRAPH1.
PERFORM PARAGRAPH2.
PARAGRAPH2.
EXEC SQL SQLCommand2 END-EXEC.
PARAGRAPH1.
EXEC SQL WHENEVER SQLERROR GO TO ERROR-HANDLER END-EXEC.
EXEC SQL SQLCommand1 END-EXEC.
The code that the preprocessor generates depends on the condition and
action in a WHENEVER command. In the previous example, the preprocessor
inserts a test for a negative SQLCODE and a sentence that invokes
ERROR-HANDLER:
**** Start SQL Preprocessor ****
* EXEC SQL
* WHENEVER SQLERROR
* GO TO S300-SERIOUS-ERROR
* END-EXEC
**** Start Inserted Statements ****
CONTINUE
**** End SQL Preprocessor ****
**** Start SQL Preprocessor ****
* EXEC SQL SQLCommand1 END-EXEC
**** Start Inserted Statements ****
Statements for executing SQLCommand1 appear here
IF SQLCODE IS NEGATIVE
GO TO S300-SERIOUS-ERROR
END-IF
**** End SQL Preprocessor ****
As the previous example illustrates, you can pass control to an
exception-handling paragraph with a WHENEVER command, but you use a GO TO
statement rather than a PERFORM statement. Therefore after the
exception-handling paragraph is executed, control cannot automatically
return to the paragraph which invoked it. You must use another GO TO or
a PERFORM statement to explicitly pass control to a specific point in
your program:
ERROR-HANDLER.
IF SQLCODE < -14024
THEN PERFORM TERMINATE-PROGRAM
ELSE
PERFORM SQLEXPLAIN UNTIL SQLCODE = 0
GO TO LineLabel.
This exception-handling routine explicitly checks the first SQLCODE
returned. The program terminates, or it continues from LineLabel after
all warning and error messages are displayed. Note that a GO TO
statement was required in this paragraph in order to allow the program to
continue. Using a GO TO statement may be impractical when you want
execution to continue from different places in the program, depending on
the part of the program that provoked the error. This situation is
discussed under "Explicit Status Checking" later in the chapter.
Implicitly Invoking Status-Checking Procedures.
The program illustrated in Figure 5-1 contains five WHENEVER commands:
* The WHENEVER command numbered 1 handles errors associated with the
following commands:
CONNECT
BEGIN WORK
COMMIT WORK
* The WHENEVER commands numbered 2 through 4 handle warnings and
errors associated with the SELECT command.
The paragraph named S300-SERIOUS-ERROR is executed when an error occurs
during the processing of session-related and transaction-related
commands. The program terminates after displaying all available error
messages. If a warning condition occurs during the execution of these
commands, the warning condition is ignored, because the WHENEVER
SQLWARNING CONTINUE command is in effect by default.
The paragraph named S100-SQL-ERROR is executed when an error occurs
during the processing of the SELECT command.
S100-SQL-ERROR explicitly examines SQLCODE to determine whether a
deadlock or shared memory problem occurred (SQLCODE = -14024 or -4008) or
whether the error was serious enough to warrant terminating the program
(SQLCODE < -14024):
* If a deadlock or shared memory problem occurred, the program
attempts to execute the SELECT command as many as three times
before notifying the user of the situation.
* If SQLCODE contains a value less than -14024, the program
terminates after all available warnings and error messages from
the ALLBASE/SQL message catalog have been displayed.
In the case of any other errors, the program displays all available
messages, then passes control to B110-EXIT.
The paragraph named S500-SQL-WARNING is executed when only a warning
condition results during execution of the SELECT command. This paragraph
displays a message and the row of data retrieved.
The NOT FOUND condition that may be associated with the SELECT command is
handled by paragraph S600-NOT-FOUND. This paragraph displays the message
Part Number not found!, then passes control to B110-EXIT. SQLEXPLAIN does
not provide a message for the NOT FOUND condition, so the program must
provide one.
Code the Preprocessor Generates.
The NOT FOUND condition generates code only for data manipulation
commands. Had this program contained other data manipulation commands,
NOT FOUND code would have been generated for each data manipulation
command that occurred sequentially after the WHENEVER NOT FOUND command
in the source code. Note also that none of the WHENEVER commands caused
exception-handling code to be generated for SQLEXPLAIN.
______________________________________________________________________
| |
| |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *|
| *Program COBEX5: * |
| *This program is the same as program COBEX2, except this * |
| *program handles deadlocks differently. * |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *|
| |
| IDENTIFICATION DIVISION. |
| |
| PROGRAM-ID. COBEX5. |
| AUTHOR. HP TRAINING |
| INSTALLATION. HP. |
| DATE-WRITTEN. 23 JULY 1987. |
| DATE-COMPILED. 23 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. |
| |
| * * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * * |
| EXEC SQL BEGIN DECLARE SECTION END-EXEC. |
| 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 5-1. Implicitly Invoking Status-Checking Paragraphs
_______________________________________________________________
| |
| 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". |
| |
| 77 SQL-COMMAND-DONE-FLAG PIC X(01) VALUE "N". |
| 88 NOT-SQL-CMD-DONE VALUE "N". |
| 88 SQL-COMMAND-DONE VALUE "Y". |
| |
| 01 NOMEMORY PIC S9(9) COMP VALUE -4008. |
| 01 DEADLOCK PIC S9(9) COMP VALUE -14024. |
| |
| 01 TRY-COUNTER PIC S9(4) COMP VALUE 0. |
| 01 TRY-LIMIT PIC S9(4) COMP VALUE 3. |
| |
| 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 - COBEX5". |
| 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. |
_______________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Paragraphs (page 2 of 7)
_______________________________________________________________________
| |
| PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. |
| |
| A100-EXIT. |
| EXIT. |
| |
| A200-CONNECT-DBENVIRONMENT. |
| |
| EXEC SQL |
| WHENEVER SQLERROR |
| GO TO S300-SERIOUS-ERROR 1|
| END-EXEC. |
| |
| DISPLAY "Connect to PartsDBE". |
| EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. |
| |
| A200-EXIT. |
| EXIT. |
| |
| A300-BEGIN-TRANSACTION. |
| |
| DISPLAY "Begin Work". |
| EXEC SQL |
| BEGIN WORK |
| END-EXEC. |
| |
| A300-EXIT. |
| EXIT. |
| |
| A400-END-TRANSACTION. |
| |
| DISPLAY "Commit Work". |
| EXEC SQL |
| COMMIT WORK |
| END-EXEC. |
| |
| A400-EXIT. |
| EXIT. |
| |
| A500-TERMINATE-PROGRAM. |
| |
| EXEC SQL |
| RELEASE |
| END-EXEC. |
| |
| STOP RUN. |
| A500-EXIT. |
| EXIT. |
_______________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Paragraphs (page 3 of 7)
________________________________________________________________________
| |
| $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 |
| GO TO S100-SQL-ERROR 2|
| END-EXEC. |
| |
| EXEC SQL |
| WHENEVER SQLWARNING |
| GO TO S500-SQL-WARNING 3|
| END-EXEC. |
| |
| EXEC SQL |
| WHENEVER NOT FOUND |
| GO TO S600-NOT-FOUND 4|
| END-EXEC. |
| |
| MOVE "N" TO SQL-COMMAND-DONE-FLAG. |
| |
| MOVE 0 TO TRY-COUNTER. |
| |
| PERFORM B110-SQL-SELECT THRU B110-EXIT |
| UNTIL SQL-COMMAND-DONE. |
| |
| B100-EXIT. |
| EXIT. |
| |
| |
| B110-SQL-SELECT. |
| |
| ADD 1 TO TRY-COUNTER. |
| |
| DISPLAY "SELECT PartNumber, PartName and SalesPrice". |
________________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Paragraphs (page 4 of 7)
__________________________________________________________
| |
| PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT.|
| |
| EXEC SQL |
| SELECT PARTNUMBER, PARTNAME, SALESPRICE |
| INTO :PARTNUMBER, |
| :PARTNAME, |
| :SALESPRICE :SALESPRICEIND |
| FROM PURCHDB.PARTS |
| WHERE PARTNUMBER = :PARTNUMBER |
| END-EXEC. |
| |
| IF SQL-COMMAND-DONE |
| GO TO B110-EXIT. |
| |
| PERFORM A400-END-TRANSACTION THRU A400-EXIT |
| PERFORM B200-DISPLAY-ROW THRU B200-EXIT. |
| |
| MOVE "Y" TO SQL-COMMAND-DONE-FLAG. |
| |
| B110-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-SQL-ERROR. |
| |
| IF SQLCODE < DEADLOCK |
| PERFORM S200-SQL-EXPLAIN THRU S200-EXIT |
| UNTIL SQLCODE = 0 |
| PERFORM A500-TERMINATE-PROGRAM. |
| |
| IF SQLCODE = DEADLOCK |
| OR SQLCODE = NOMEMORY |
| |
__________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Paragraphs (page 5 of 7)
_____________________________________________________________________
| |
| IF TRY-COUNTER = TRY-LIMIT |
| MOVE "Y" TO SQL-COMMAND-DONE-FLAG |
| DISPLAY "Deadlock occurred, or not enough shared "|
| "memory. You may want to try again." |
| GO TO B110-EXIT |
| ELSE |
| GO TO B110-EXIT |
| ELSE |
| PERFORM S200-SQL-EXPLAIN THRU S200-EXIT |
| PERFORM A500-TERMINATE-PROGRAM. |
| |
| S100-EXIT. |
| EXIT. |
| |
| S200-SQL-EXPLAIN. |
| |
| EXEC SQL |
| SQLEXPLAIN :SQLMESSAGE |
| END-EXEC. |
| |
| DISPLAY SQLMESSAGE. |
| |
| S200-EXIT. |
| EXIT. |
| |
| S300-SERIOUS-ERROR. |
| |
| PERFORM S200-SQL-EXPLAIN THRU S200-EXIT. |
| PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. |
| |
| S300-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. |
| |
| IF SQLWARN6 NOT = "W" |
| PERFORM A400-END-TRANSACTION THRU A400-EXIT. |
| |
| MOVE "Y" TO SQL-COMMAND-DONE-FLAG |
| GO TO B110-EXIT. |
| |
| S500-EXIT. |
| EXIT. |
_____________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Paragraphs (page 6 of 7)
_______________________________________________________
| |
| S600-NOT-FOUND. |
| |
| DISPLAY " ". |
| DISPLAY "Part Number not found!". |
| |
| PERFORM A400-END-TRANSACTION THRU A400-EXIT.|
| |
| MOVE "Y" TO SQL-COMMAND-DONE-FLAG |
| GO TO B110-EXIT. |
| |
| S600-EXIT. |
| EXIT. |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_______________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Paragraphs (page 7 of 7)
Explicit Status Checking Techniques
With explicit status checking, you invoke a paragraph after explicitly
checking SQLCA values rather than using the WHENEVER command. The
program in Figure 5-1 has already illustrated several uses of explicit
status checking to:
* Isolate errors so critical that they caused ALLBASE/SQL to roll
back the current transaction.
* Control the number of times SQLEXPLAIN is executed.
* Detect when more than one row qualifies for the SELECT operation.
The example in Figure 5-1 illustrates how implicit routines can sometimes
reduce the amount of status checking code. As the number of SQL
operations in a program increases, however, the likelihood of needing to
return to different locations in the program after execution of such a
routine increases.
The example shown in Figure 5-2 contains four data manipulation
operations: INSERT, UPDATE, DELETE, and SELECT. Each of these operations
is executed from its own paragraph.
As in the program in Figure 5-1, one paragraph is used for status
checking: S100-SQL-ERROR. Unlike the program in Figure 5-1, however,
this paragraph is invoked after explicit tests of SQLCODEs are made
immediately following each data manipulation operation.
Because the status-checking paragraph is invoked with a PERFORM command,
it does not need to contain GO TO statements to return control to the
point in the program where it was invoked.
_____________________________________________________________________________
| |
| 01 OK PIC S9(9) COMP VALUE 0. |
| 01 NOTFOUND PIC S9(9) COMP VALUE 100. |
| 01 DEADLOCK PIC S9(9) COMP VALUE -14024. |
| 01 MULTIPLE-ROWS PIC S9(9) COMP VALUE -1002. |
| 01 NOMEMORY PIC S9(9) COMP VALUE -4008. |
| . |
| . |
| PERFORM DM THRU DM-EXIT UNTIL DONE. |
| . |
| . |
| DM. |
| This paragraph prompts for a number that indicates whether |
| the user wants to SELECT, UPDATE, DELETE, or INSERT rows, |
| then invokes a paragraph that accomplishes the selected |
| activity. The DONE flag is set when the user enters a slash. |
| DM-EXIT. |
| . |
| . |
| . |
| INSERT-DATA. |
| Sentences that accept data from the user appear here. |
| EXEC SQL INSERT |
| INTO PURCHDB.PARTS (PARTNUMBER, |
| PARTNAME, |
| SALESPRICE) |
| VALUES (:PARTNUMBER, |
| :PARTNAME, |
| :SALESPRICE) |
| END-EXEC. |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-ERROR THRU S100-EXIT. |
| . |
| . |
| . |
| UPDATE-DATA. |
| This paragraph verifies that the row(s) to be changed exist, then |
| invokes paragraph DISPLAY-UPDATE to accept new data from the user.|
| EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE |
| INTO :PARTNUMBER, |
| :PARTNAME, |
| :SALESPRICE |
| FROM PURCHDB.PARTS |
| WHERE PARTNUMBER = :PARTNUMBER |
| END-EXEC. |
| IF SQLCODE = OK |
| PERFORM DISPLAY-UPDATE. |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-ERROR THRU S100-EXIT. |
_____________________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Paragraphs
___________________________________________________________________
| |
| DISPLAY-UPDATE. |
| Sentences that prompt the user for new data appear here.|
| EXEC SQL UPDATE PURCHDB.PARTS |
| SET PARTNAME = :PARTNAME, |
| SALESPRICE = :SALESPRICE, |
| WHERE PARTNUMBER = :PARTNUMBER |
| END-EXEC. |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-ERROR THRU S100-EXIT. |
| . |
| . |
| . |
| |
| |
| |
| DELETE-DATA. |
| This paragraph verifies that the row(s) to be deleted |
| exist, then invokes paragraph DISPLAY-DELETE to delete |
| the row(s). |
| EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE |
| INTO :PARTNUMBER, |
| :PARTNAME, |
| :SALESPRICE |
| FROM PURCHDB.PARTS |
| WHERE PARTNUMBER = :PARTNUMBER |
| END-EXEC. |
| IF SQLCODE = OK |
| PERFORM DISPLAY-DELETE. |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-ERROR THRU S100-EXIT. |
| . |
| . |
| . |
| |
| |
| |
| DISPLAY-DELETE. |
| |
| Sentences that verify that the deletion should |
| actually occur appear here. |
| |
| EXEC SQL DELETE FROM PURCHDB.PARTS |
| WHERE PARTNUMBER = :PARTNUMBER |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-ERROR THRU S100-EXIT. |
| |
___________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Paragraphs (page 2 of 3)
__________________________________________________________________
| |
| . |
| SELECT-DATA. |
| Sentences that prompt for a partnumber appear here. |
| EXEC SQL SELECT PARTNUMBER, PARTNAME, SALESPRICE |
| INTO :PARTNUMBER, |
| :PARTNAME, |
| :SALESPRICE |
| FROM PURCHDB.PARTS |
| WHERE PARTNUMBER = :PARTNUMBER |
| END-EXEC. |
| IF SQLCODE = OK |
| PERFORM DISPLAY-ROW. |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-ERROR THRU S100-EXIT. |
| . |
| S100-SQL-ERROR. |
| |
| IF SQLCODE = NOT-FOUND |
| DISPLAY "Part Number not found!" |
| PERFORM A400-END-TRANSACTION THRU A400-EXIT |
| GO TO S100-EXIT. |
| |
| IF SQLCODE = MULTIPLE-ROWS |
| DISPLAY "WARNING: More than one row qualifies!" |
| GO TO S100-EXIT. |
| |
| IF SQLCODE = DEADLOCK |
| DISPLAY "Someone else is using that part number. "|
| "Please try again." |
| GO TO S100-EXIT. |
| |
| IF SQLCODE = NOMEMORY |
| DISPLAY "TEMPORARY PROBLEM! Please try again." |
| GO TO S100-EXIT. |
| |
| IF SQLCODE < DEADLOCK |
| PERFORM S200-SQL-EXPLAIN THRU S200-EXIT |
| UNTIL SQLCODE = 0 |
| PERFORM A500-TERMINATE-PROGRAM |
| GO TO S100-EXIT. |
| |
| IF SQLWARN0 = "W" |
| PERFORM S300-SQL-WARNING THRU S300-EXIT. |
| |
| S100-EXIT. |
| EXIT. |
| |
| |
__________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Paragraphs (page 3 of 3)
Handling Deadlock and Shared Memory Problems.
A deadlock exists when two transactions need data that the other
transaction already has locked. When a deadlock occurs, ALLBASE/SQL
rolls back the transaction with the larger priority number. If two
deadlocked transactions have the same priority, ALLBASE/SQL rolls back
the newer transaction.
An SQLCODE of -14024 indicates that a deadlock has occurred:
Deadlock detected. (DBERR 14024)
An SQLCODE of -4008 indicates that ALLBASE/SQL does not have access to
the amount of shared memory required to execute a command:
ALLBASE/SQL shared memory allocation failed in DBCORE. (DBERR 4008)
One way of handling deadlocks and shared memory problems is shown in the
previous example, Figure 5-2. Another method would be to use a counter
to reapply the transaction a specified number of times before notifying
the user of the situation.
Determining Number of Rows Processed.
SQLERRD(3) is useful in the following ways:
* To determine how many rows were processed in one of the following
operations, when the operation could be executed without error:
SELECT
INSERT
UPDATE
DELETE
Cursor operations:
FETCH
UPDATE WHERE CURRENT
DELETE WHERE CURRENT
The SQLERRD(3) value can be used in these cases only when SQLCODE
does not contain a negative number. When SQLCODE is 0, SQLERRD(3)
is always equal to 1 for SELECT, FETCH, UPDATE WHERE CURRENT, and
DELETE WHERE CURRENT operations. SQLERRD(3) may be greater than 1
if more than one row qualifies for an INSERT, UPDATE, or DELETE
operation. When SQLCODE is 100, SQLERRD(3) is 0.
* To determine how many rows were processed in one of the BULK
operations:
BULK SELECT
BULK FETCH
BULK INSERT
In this case, you also need to test SQLCODE to determine whether
the operation executed without error. If SQLCODE is negative,
SQLERRD(3) contains the number of rows that could be successfully
retrieved or inserted before an error occurred. If SQLCODE is 0,
SQLERRD(3) contains the total number of rows that ALLBASE/SQL put
into or took from the host variable array. If, in a BULK SELECT
operation, more rows qualify than the array can accommodate,
SQLCODE will be 0.
Examples follow.
INSERT, UPDATE, and DELETE Operations.
The example in Figure 5-2 could be modified to display the number of rows
inserted, updated, or deleted by using SQLERRD(3). In the case of the
update operation, for example, the actual number of rows updated could be
displayed after the UPDATE command is executed:
WORKING-STORAGE SECTION.
.
.
.
01 OK PIC S9(9) COMP VALUE 0.
01 NUMBER-OF-ROWS PIC X(4).
.
.
.
PROCEDURE DIVISION.
.
.
.
DISPLAY-UPDATE.
Sentences that prompt user for new data appear here.
EXEC SQL UPDATE PURCHDB.PARTS
SET PARTNAME = :PARTNAME,
SALESPRICE = :SALESPRICE,
WHERE PARTNUMBER = :PARTNUMBER
END-EXEC.
IF SQLCODE = OK
MOVE SQLERRD(3) TO NUMBER-OF-ROWS
DISPLAY "The number of rows updated was: " NUMBER-OF-ROWS;
ELSE
DISPLAY "No rows could be updated!"
PERFORM S100-SQL-ERROR.
If the UPDATE command is successfully executed, SQLCODE is OK (defined as
zero in the WORKING-STORAGE SECTION) and SQLERRD(3) contains the number
of rows updated. If the UPDATE command cannot be successfully executed,
SQLCODE contains a negative number and SQLERRD(3) contains a 0.
BULK Operations.
When using the BULK SELECT, BULK FETCH, or BULK INSERT commands, you can
use the SQLERRD(3) value in several ways:
* If the command executes without error, to determine the number of
rows retrieved into an output host variable array or inserted from
an input host variable array.
* If the command causes an error condition, to determine the number
of rows that could be successfully put into or taken out of the
host variable array before the error occurred.
In the code identified as 1 in Figure 5-3, the value in SQLERRD(3) is
displayed when only some of the qualifying rows could be retrieved before
an error occurred.
In the code identified as 2, the value in SQLERRD(3) is compared with the
maximum array size to determine whether more rows might have qualified
than the program could display. You could also use a cursor and execute
the FETCH command until SQLCODE=100.
In the code identified as 3, the value in SQLERRD(3) is used to control
the number of times procedure DISPLAY-ROW is executed.
__________________________________________________________________________
| |
| WORKING-STORAGE SECTION. |
| EXEC SQL INCLUDE SQLCA END-EXEC. |
| EXEC SQL BEGIN DECLARE SECTION END-EXEC. |
| 01 PARTSTABLE. |
| 05 TABLE-ELEMENT OCCURS 25 TIMES. |
| 10 PARTNUMBER PIC X(16). |
| 10 PARTNAME PIC X(30). |
| 10 SALESPRICE PIC S9(8)V99 COMP-3. |
| 01 ERRORMESSAGE PIC X(132). |
| EXEC SQL END DECLARE SECTION END-EXEC. |
| 01 OK PIC S9(9) COMP VALUE 0. |
| 01 NOTFOUND PIC S9(9) COMP VALUE 100. |
| 01 MAXIMUMROWS PIC S9(9) COMP VALUE 25. |
| 01 I PIC S9(9) COMP. |
| 01 NUMBER-OF-ROWS PIC X(4). |
| 01 DOLLARS PIC $$$,$$$,$$$.99. |
| . |
| . |
| . |
| PROCEDURE DIVISION. |
| . |
| . |
| . |
| BULK-SELECT. |
| EXEC SQL BULK SELECT PARTNUMBER, |
| PARTNAME, |
| SALESPRICE |
| INTO :PARTSTABLE |
| FROM PURCHDB.PARTS |
| END-EXEC. |
| |
| IF SQLCODE = OK; |
| PERFORM DISPLAY-TABLE; |
| ELSE |
| IF SQLCODE = NOTFOUND; |
| DISPLAY " "; |
| DISPLAY "No rows qualify for this operation!"; |
| ELSE |
| MOVE SQLERRD(3) TO NUMBER-OF-ROWS; |
| DISPLAY "Only " NUMBER-OF-ROWS "rows were retrieved " 1|
| "before an error occurred!" |
| PERFORM DISPLAY-TABLE; |
| PERFORM DISPLAY-ERROR. |
| |
| |
__________________________________________________________________________
Figure 5-3. Determining Number of Rows Processed After a BULK SELECT
_____________________________________________________________________________
| |
| |
| . |
| . |
| . |
| DISPLAY-TABLE. |
| IF SQLERRD(3) = MAXIMUMROWS; 2 |
| DISPLAY " "; |
| DISPLAY "WARNING: There may be additional rows that qualify!"|
| The column headings are displayed here. |
| PERFORM DISPLAY-ROW VARYING I FROM 1 BY 1 3 |
| UNTIL I > SQLERRD(3). |
| DISPLAY " ". |
| |
| DISPLAY-ROW. |
| MOVE SALESPRICE(I) TO DOLLARS. |
| DISPLAY PARTNUMBER(I), "|", |
| PARTNAME(I), "|", |
| SALESPRICE(I), "|", |
| DOLLARS. |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 5-3. Determining Number of Rows Processed After a BULK SELECT (page 2 of 2)
Detecting End of Scan.
Previous examples in this chapter have illustrated how an SQLCODE of 100
can be detected and handled for data manipulation commands that do not
use a cursor. When a cursor is being used, this SQLCODE value is used to
determine when all rows in an active set have been fetched:
.
.
.
EXEC SQL OPEN CURSOR1 END-EXEC.
.
.
.
PERFORM FETCH-ROW THRU FETCH-ROW-EXIT
UNTIL DONE-FETCH.
.
.
.
FETCH-ROW.
EXEC SQL FETCH CURSOR1
INTO :PARTNUMBER,
:PARTNAME,
:SALESPRICE
END-EXEC.
IF SQLCODE = OK;
PERFORM DISPLAY-ROW
ELSE
IF SQLCODE = NOTFOUND
MOVE "X" TO DONE-FETCH-FLAG;
DISPLAY " ";
DISPLAY "Row not found or no more rows!";
GO TO FETCH-ROW-EXIT;
ELSE
PERFORM DISPLAY-ERROR.
In this example, the active set is defined when the OPEN command is
executed. The cursor is then positioned before the first row of the
active set. When the FETCH command is executed, the first row in the
active set is placed into the program's host variables, then displayed.
The FETCH command retrieves one row at a time into the host variables
until the last row in the active set has been retrieved; the next attempt
to FETCH after the last row from the active set has been fetched sets
SQLCODE to NOTFOUND (defined as 100 in WORKING-STORAGE). If no rows
qualify for the active set, SQLCODE is NOTFOUND the first time paragraph
FETCH-ROW is executed.
Determining When More Than One Row Qualifies.
If more than one row qualifies for a non-BULK SELECT or FETCH operation,
ALLBASE/SQL sets SQLCODE to -10002. In the following example, when
SQLCODE is MULTIPLEROWS (defined as -10002 in WORKING-STORAGE), a
status-checking paragraph is not invoked; instead a warning message is
displayed:
UPDATE-DATA.
This paragraph verifies that the row(s) to be changed
exist, then invokes paragraph DISPLAY-UPDATE to accept
new data from the user.
EXEC SQL SELECT ORDERNUMBER, ITEMNUMBER, ORDERQTY
INTO :ORDERNUMBER,
:ITEMNUMBER,
:ORDERQTY
FROM PURCHDB.ORDERITEMS
WHERE ORDERNUMBER = :ORDERNUMBER
END-EXEC.
IF SQLCODE = OK
PERFORM DISPLAY-UPDATE.
IF SQLCODE NOT = OK
IF SQLCODE = MULTIPLEROWS
DISPLAY "WARNING: More than one row qualifies!"
PERFORM DISPLAY-UPDATE
ELSE
IF SQLCODE NOT = NOTFOUND
DISPLAY "Row not found."
ELSE
PERFORM S100-SQL-ERROR.
Note that the PARTS table in the sample database has a unique index on
PARTNUMBER, so a test for multiple rows is not required. This test is
useful for the ORDERITEMS table which does not have a unique index.
Detecting Log Full Condition.
When the log file is full, log space must be reclaimed before ALLBASE/SQL
can process any additional transactions. Your program can detect the
situation, and it can be corrected by the DBA.
SQLEXPLAIN retrieves the following message:
Log full. (DBERR 14046)
In the following example, SQLCODE is checked for a log full condition.
If the condition is true, ALLBASE/SQL has rolled back the current
transaction. The program issues a COMMIT WORK command, the
S100-SQL-STATUS-CHECK routine is executed to display any messages, and
the program is terminated.
IF SQLCODE = -14046
EXEC SQL COMMIT WORK END-EXEC
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM S200-TERMINATE-PROGRAM THRU S200-EXIT.
Handling Out of Space Conditions.
It is possible that data or index space may be exhausted in a DBEFileSet.
This could happen as rows are being added or an index is being created or
when executing queries which require that data be sorted. Your program
can detect the problem, and the DBA must add index or data space to the
appropriate DBEFileSet.
SQLEXPLAIN retrieves the following message:
Data or Index space exhaused in DBEFileSet. (DBERR 2502)
In the following example, SQLCODE is checked for an out of space
condition. If the condition is true, the transaction is rolled back to
an appropriate savepoint. The program issues a COMMIT WORK command, the
S100-SQL-STATUS-CHECK routine is executed to display any messages, and
the program is terminated.
IF SQLCODE = -2502
EXEC SQL ROLLBACK WORK TO :SavePoint END-EXEC
EXEC SQL COMMIT WORK END-EXEC
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM S200-TERMINATE-PROGRAM THRU S200-EXIT.
Checking for Authorizations.
When the DBEUserID related to an ALLBASE/SQL command does not have the
authority to execute the command, the following message is retreived by
SQLEXPLAIN:
User ! does not have ! authorization. (DBERR 2300)
In the following example, SQLCODE is checked to determine if the user
has proper connect authority. If the condition is true, the
S100-SQL-STATUS-CHECK is executed to display any messages, and the
program is terminated.
EXEC SQL CONNECT TO 'PARTSDBE' END-EXEC
IF SQLCODE = -2300
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM S200-TERMINATE-PROGRAM THRU S200-EXIT.