 |
» |
|
|
|
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 syntax 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 ProceduresThe 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 GeneratesThe 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. Figure 5-1 Implicitly Invoking Status-Checking Paragraphs
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 OCT 1987.
DATE-COMPILED. 23 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-USER PIC X(34).
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 * * * * * * *
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 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 Tabl
- "e - 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 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 1
END-EXEC.
DISPLAY "Connect to ../sampledb/PartsDBE".
EXEC SQL CONNECT TO '../sampledb/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.
B100-SELECT-DATA.
MOVE SPACES TO RESPONSE.
MOVE "Enter Part Number or '/' to STOP> "
TO PROMPT-USER.
DISPLAY " ".
WRITE PROMPT-USER.
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".
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.
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
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.
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.
|
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. Figure 5-2 Explicitly Invoking Status-Checking Paragraphs
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
ELSE
IF SQLCODE NOT = OK
PERFORM S100-SQL-ERROR THRU S100-EXIT.
.
.
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 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.
.
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 = 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 SQLCODE > DEADLOCK
PERFORM S200-SQL-EXPLAIN THRU S200-EXIT
Sentences to COMMIT WORK or ROLLBACK WORK appear here.
Sentences to terminate the program or exit the paragraph
and continue appear next. Specific action can be taken
in the calling routine.
IF SQLWARN0 = 'W'
PERFORM S300-SQL-WARNING THRU S300-EXIT.
S100-EXIT.
EXIT.
|
Handling Deadlock and Shared Memory ProblemsA 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 ProcessedSQLERRD(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 OperationsThe 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. 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. Figure 5-3 Using SQLERRD(3) After a BULK SELECT Operation
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.
.
.
.
DISPLAY-TABLE.
IF SQLERRD(3) = MAXIMUMROWS(9) 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.
|
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 QualifiesIf 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 ConditionWhen 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:
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 ConditionsIt 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 AuthorizationsWhen 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 '/sampledb/PartsDBE' END-EXEC
IF SQLCODE = -2300
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM S200-TERMINATE-PROGRAM THRU S200-EXIT.
|
|
