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 syntax format is:
EXEC SQL WHENEVER Condition Action;
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 code
block 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 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:
.
.
.
procedure Procedure2;
begin
EXEC SQL SQLCommand2;
end;
procedure Procedure1;
begin
EXEC SQL WHENEVER SQLERROR GOTO 2000;
EXEC SQL SQLCommand1;
end;
.
.
.
Procedure1;
Procedure2;
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 the
code at Line Label 2000, as follows:
$Skip_Text ON$
EXEC SQL WHENEVER SQLERROR GOTO 2000;
$Skip_Text OFF$
.
$Skip_Text ON$
EXEC SQL SQLCommand1;
$Skip_Text OFF$
Statements for executing SQLCommand1 appear here
if SQLCA.SQLCODE < 0 then
goto 2000;
As the previous example illustrates, you can pass control to an
exception-handling paragraph with a WHENEVER command, but you use a GOTO
statement with a numeric line label, rather than a procedure name.
Therefore after the exception-handling paragraph is executed, control
cannot automatically return to the paragraph which invoked it. You must
use another GOTO to explicitly pass control to a specific point in your
program:
(* WHENEVER Routine -- SQL Error *)
2000:
if SQLCA.SQLCODE < -14024 then
TerminateProgram;
else
repeat
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
until SQLCA.SQLCODE = 0;
GOTO 500; (* Goto Restart/Reentry point of main program *)
This exception-handling routine explicitly checks the first SQLCODE
returned. The program terminates, or it continues from the
Restart/Reentry point after all warning and error messages are displayed.
Note that a GOTO statement was required in this paragraph in order to
allow the program to continue. Using a GOTO 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 Techniques" later
in the chapter.
Program Illustrating Implicit and Explicit Status Checking
The program in Figure 5-1 contains five WHENEVER commands to demonstrate
implicit status checking. It also uses two explicit status checking
routines.
* The WHENEVER command numbered 1 handles errors associated with the
following commands:
CONNECT
BEGIN WORK
COMMIT WORK
* The WHENEVER command numbered 2 turns off the first WHENEVER
command.
* The WHENEVER commands numbered 3 through 5 handle warnings and
errors associated with the SELECT command.
The code routine located at Label 1000 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 code routine located at Label 2000 is executed when an error occurs
during the processing of the SELECT command. Procedure SQLStatusCheck is
executed.
SQLStatusCheck 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), for example:
* If a deadlock or shared memory problem occurred, the program
attempts to execute the SelectData procedure 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 prompts for another part number.
The code routine located at Label 3000 is executed when only a warning
condition results during execution of the SELECT command. This code
routine displays a message and the row of data retrieved.
The NOT FOUND condition that may be associated with the SELECT command is
handled by the code routine located at Label 4000. This code routine
displays the message "Row not found!", then passes control to
EndTransaction. SQLEXPLAIN does not provide a message for the NOT FOUND
condition, so the program must provide one.
____________________________________________________________________________
| |
| $Heap_Dispose ON$ |
| $Heap_Compact ON$ |
| Standard_Level 'HP_Pascal$ |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| (* This program illustrates the use of SQL's SELECT command to *)|
| (* retrieve one row or tuple at a time. *)|
| (* Same as pasex2 with added status checking and deadlock routines *)|
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| |
| Program pasex5(input, output); |
| |
| label |
| 500, |
| 1000, |
| 2000, |
| 3000, |
| 4000, |
| 9999; |
| const |
| OK = 0; |
| NotFound = 100; |
| DeadLock = -14024; |
| NoMemory = -4008; |
| TryLimit = 3; |
| var |
| |
| EXEC SQL INCLUDE SQLCA; |
| |
| (* Begin Host Variable Declarations *) |
| EXEC SQL Begin Declare Section; |
| PartNumber : packed array[1..16] of char; |
| PartName : packed array[1..30] of char; |
| SalesPrice : longreal; |
| SalesPriceInd : SQLIND; |
| SQLMessage : packed array[1..132] of char; |
| EXEC SQL End Declare Section; |
| (* End Host Variable Declarations *) |
| |
| Abort : boolean; |
| SQLCommandDone : boolean; |
| TryCounter : integer; |
| |
| procedure SQLStatusCheck; (* Procedure to Display Error Messages *) |
| Forward; |
| $PAGE $ |
| |
____________________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Routines
_________________________________________________________________________
| |
| (* Directive to set SQL Whenever error checking *) |
| EXEC SQL Whenever SqlError goto 1000; 1|
| |
| Procedure ConnectDBE; (* Procedure to Connect to PartsDBE *) |
| begin |
| |
| writeln('Connect to PartsDBE'); |
| EXEC SQL CONNECT TO 'PartsDBE'; |
| |
| end; (* End of ConnectDBE Procedure *) |
| |
| |
| Procedure BeginTransaction; (* Procedure to Begin Work *) |
| begin |
| |
| writeln; |
| writeln('Begin Work'); |
| EXEC SQL BEGIN WORK; |
| |
| end; (* End BeginTransaction Procedure *) |
| |
| |
| procedure EndTransaction; (* Procedure to Commit Work *) |
| begin |
| |
| writeln; |
| writeln('Commit Work'); |
| EXEC SQL COMMIT WORK; |
| |
| end; (* End EndTransaction Procedure *) |
| |
| |
| (* Directive to reset SQL Whenever error checking *) |
| EXEC SQL Whenever SqlError CONTINUE; 2|
| |
| procedure TerminateProgram; (* Procedure to Release PartsDBE *) |
| begin |
| |
| writeln('Release PartsDBE'); |
| EXEC SQL COMMIT WORK RELEASE; |
| |
| writeln; |
| writeln('Terminating Program'); |
| Goto 9999; (* Goto exit point for main program *) |
| |
| end; (* End TerminateProgram Procedure *) |
| $PAGE $ |
_________________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Routines (page 2 of 6)
___________________________________________________________________________
| |
| procedure DisplayRow; (* Procedure to Display Parts Table Rows *) |
| begin |
| |
| writeln; |
| writeln('Part Number: ', PartNumber); |
| writeln('Part Name: ', PartName); |
| if SalesPriceInd < 0 then |
| writeln('Sales Price is NULL') |
| else |
| writeln('Sales Price: ', SalesPrice:10:2); |
| |
| end; (* End of DisplayRow *) |
| $PAGE $ |
| (* Directives to set SQL Whenever error checking *) |
| EXEC SQL Whenever SqlError goto 2000; 3 |
| |
| EXEC SQL Whenever SqlWarning goto 3000; 4 |
| |
| EXEC SQL Whenever Not Found goto 4000; 5 |
| |
| $PAGE $ |
| procedure SelectData; (* Procedure to Query Parts Table *) |
| begin |
| |
| repeat |
| |
| if SQLCommandDone then |
| begin |
| writeln; |
| prompt('Enter Part Number within Parts Table or "/" to STOP> ');|
| readln(PartNumber); |
| writeln; |
| |
| TryCounter := 0; |
| end; |
| |
| if PartNumber[1] |
| '/' then |
| begin |
| |
| BeginTransaction; |
| TryCounter := TryCounter + 1; |
| writeln('SELECT PartNumber, PartName, SalesPrice'); |
| EXEC SQL SELECT PartNumber, PartName, SalesPrice |
| INTO :PartNumber, |
| :PartName, |
| :SalesPrice :SalesPriceInd |
| |
___________________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Routines (page 3 of 6)
___________________________________________________________________________
| |
| FROM PurchDB.Parts |
| WHERE PartNumber = :PartNumber; |
| (* If no errors occur set command done flag and display row *) |
| SQLCommandDone := TRUE; |
| DisplayRow; |
| EndTransaction; |
| |
| end; (* End if *) |
| until PartNumber[1] = '/'; |
| |
| end; (* End of SelectData Procedure *) |
| $PAGE $ |
| |
| procedure SQLStatusCheck; (* Procedure to Display Error Messages *)|
| begin |
| |
| if ((SQLCA.SQLCODE = DeadLock) or (SQLCA.SQLCODE = NoMemory) then |
| begin |
| if TryCounter = TryLimit then |
| begin |
| SQLCommandDone := TRUE; |
| writeln('Transaction incomplete. You may want to try again.');|
| end |
| else |
| SQLCommandDone := FALSE; |
| end |
| else |
| begin |
| Abort := FALSE; |
| if SQLCA.SQLWARN[6] = 'W' then Abort := TRUE; |
| end; |
| repeat |
| |
| EXEC SQL SQLEXPLAIN :SQLMessage; |
| writeln(SQLMessage); |
| |
| until SQLCA.SQLCODE = 0; |
| |
| if Abort then TerminateProgram; |
| |
| |
| end; (* End SQLStatusCheck Procedure *) |
| $PAGE $ |
| |
| begin (* Beginning of Program *) |
| |
| |
___________________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Routines (page 4 of 6)
____________________________________________________________________
| |
| write('Program to SELECT specified rows from '); |
| writeln('the Parts Table - PASEX5'); |
| writeln; |
| writeln('Event List:'); |
| writeln(' Connect to PartsDBE'); |
| writeln(' Begin Work'); |
| writeln(' SELECT specified row from Parts Table'); |
| writeln(' until user enters "/" '); |
| writeln(' Commit Work'); |
| writeln(' Disconnect from PartsDBE'); |
| writeln; |
| |
| ConnectDBE; |
| |
| (* Initialize command done flag to true *) |
| SQLCommandDone := True; |
| (* Restart/Reentry point for Main Program *) |
| 500: |
| |
| SelectData; |
| TerminateProgram; |
| |
| (* Whenever Routine - Serious DBE Error *) |
| (* SQL Whenever SQLError Entry Point 1 *) |
| 1000: |
| |
| (* Begin *) |
| SQLStatusCheck; |
| TerminateProgram; |
| (* End *) |
| $PAGE $ |
| |
| (* Whenever Routine - SQL Error *) |
| (* SQL Whenever SQLError Entry Point 2 *) |
| 2000: |
| |
| (* Begin *) |
| |
| SQLStatusCheck; |
| |
| Goto 500; (* Goto Restart/Reentry point of main program *)|
| (* End *) |
| |
| |
| |
| |
| |
____________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Routines (page 5 of 6)
____________________________________________________________________
| |
| (* Whenever Routine - SQL Warning *) |
| (* SQL Whenever SQL Warning Entry Point *) |
| 3000: |
| |
| (* Begin *) |
| writeln('SQL WARNING has occurred. The following row'); |
| writeln('of data may not be valid.'); |
| DisplayRow; |
| EndTransaction; |
| SQLCommandDone := True; |
| Goto 500; (* Goto Restart/Reentry point of main program *)|
| (* End *) |
| |
| (* Whenever Routine - Not Found Error *) |
| (* SQL Whenever Not Found Entry Point *) |
| 4000: |
| |
| (* Begin *) |
| writeln; |
| writeln('Row not found!'); |
| EndTransaction; |
| SQLCommandDone := True; |
| Goto 500; (* Goto Restart/Reentry point of main program *)|
| (* End *) |
| |
| (* Exit Point for main program *) |
| 9999: |
| |
| end. (* End of Program *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________
Figure 5-1. Implicitly Invoking Status-Checking Routines (page 6 of 6)
Explicit Status Checking Techniques
With explcit error handling, you invoke a function after explicitly
checking sqlca values rather than using the WHENEVER command. The
program in Figure 5-1 has already illustrated several uses of explicit
error handling to do the following:
* 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 a simple 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 procedure.
As in the program in Figure 5-1, one procedure is used for explicit
status checking: SQLStatusCheck. Unlike the program in Figure 5-1,
however, this procedure is invoked after an explicit test of SQLCODE is
made immediately following each data manipulation operation.
Because the status checking is included in a procedure rather than a
routine following the embedded SQL command, control returns to the point
in the program where SQLStatusCheck is invoked.
__________________________________________________________________________________
| |
| const |
| OK = 0; |
| NotFound = 100; |
| MultipleRows = -10002; |
| DeadLock = -14024; |
| NoMemory = -4008; |
| TryLimit = 3; |
| . |
| . |
| . |
| procedure SelectActivity; |
| begin |
| This procedure prompts for a number that indicates whether the |
| user wants to SELECT, UPDATE, DELETE, or INSERT rows, then invokes|
| a procedure that accomplishes the selected activity. The DONE |
| flag is set when the user enters a slash. |
| end; |
| . |
| . |
| . |
| procedure InsertData; |
| begin |
| Statements that accept data from the user appear here. |
| |
| EXEC SQL INSERT |
| INTO PurchDB.Parts (PartNumber, |
| PartName, |
| SalesPrice) |
| VALUES (:PartNumber, |
| :PartName, |
| :SalesPrice); |
| |
| if SQLCA.SQLCODE <> OK then SQLStatusCheck; |
| . |
| . |
| . |
| end; |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Procedure
______________________________________________________________________________
| |
| procedure UpdateData; |
| begin |
| This procedure verifies that the row(s) to be changed exist, then|
| invokes procedure DisplayUpdate to accept new data from the user.|
| |
| EXEC SQL SELECT PartNumber, PartName, SalesPrice |
| INTO :PartNumber, |
| :PartName, |
| :SalesPrice |
| FROM PurchDB.Parts |
| WHERE PartNumber = :PartNumber; |
| case SQLCA.SQLCODE of |
| OK : begin |
| DisplayUpdate; |
| end; |
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| MultipleRows : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| otherwise : begin |
| SQLStatusCheck; |
| end; |
| end; |
| : |
| end; |
| : |
| procedure DisplayUpdate; |
| begin |
| Code that prompts the user for new data appears here. |
| EXEC SQL UPDATE PurchDB.Parts |
| SET PartName = :PartName, |
| SalesPrice = :SalesPrice, |
| WHERE PartNumber = :PartNumber; |
| if SQLCA.SQLCODE <> 0 then SQLStatusCheck; |
| . |
| . |
| . |
| end; |
| |
| |
______________________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Procedure (page 2 of 5)
______________________________________________________________________________
| |
| procedure DeleteData; |
| begin |
| This procedure verifies that the row(s) to be deleted exist, then|
| invokes procedure DisplayDelete to delete the tow(s) |
| |
| EXEC SQL SELECT PartNumber, PartName, SalesPrice |
| INTO :PartNumber, |
| :PartName, |
| :SalesPrice |
| FROM PurchDB.Parts |
| WHERE PartNumber = :PartNumber; |
| |
| case SQLCA.SQLCODE of |
| OK : begin |
| DisplayDelete; |
| end; |
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| MultipleRows : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| OtherWise : begin |
| SQLStatusCheck; |
| end; |
| end; |
| : |
| end; |
| : |
| procedure DisplayDelete; |
| begin |
| Statements that verify that the deletion should actually occur |
| appear here. |
| EXEC SQL DELETE FROM PurchDB.Parts |
| WHERE PartNumber = :PartNumber; |
| if SQLCA.SQLCODE <> 0 then SQLStatusCheck; |
| : |
| |
| |
| end; |
| |
| |
| |
| |
______________________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Procedure (page 3 of 5)
_________________________________________________________________
| |
| procedure SelectData; |
| begin |
| Statements that prompt for a partnumber appear here.|
| |
| EXEC SQL SELECT PartNumber, PartName, SalesPrice |
| INTO :PartNumber, |
| :PartName, |
| :SalesPrice |
| FROM PurchDB.Parts |
| WHERE PartNumber = :PartNumber; |
| |
| case SQLCA.SQLCODE of |
| OK : begin |
| DisplayRow; |
| end; |
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| MultipleRows : begin |
| writeln; |
| writeln('Row not found!'); |
| end; |
| otherwise : begin |
| SQLStatusCheck; |
| end; |
| end; |
| . |
| . |
| . |
| end; |
| . |
| . |
| . |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Procedure (page 4 of 5)
_____________________________________________________________________________
| |
| procedure SQLStatusCheck; |
| begin |
| |
| if ((SQLCA.SQLCODE = DeadLock) or (SQLCA.SQLCODE = NoMemory) then |
| begin |
| if TryCounter = TryLimit then |
| begin |
| SQLCommandDone := TRUE; |
| writeln('Transaction incomplete. You may want to try again.');|
| end |
| else |
| SQLCommandDone := FALSE; |
| end |
| else |
| begin |
| Abort := FALSE; |
| if SQLCA.SQLCODE < DeadLock then Abort := TRUE; |
| |
| repeat |
| |
| EXEC SQL SQLEXPLAIN :SQLMessage; |
| writeln(SQLMessage); |
| |
| until SQLCA.SQLCODE = 0; |
| |
| if Abort then TerminateProgram; |
| end; |
| |
| end; (* End SQLStatusCheck Procedure *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 5-2. Explicitly Invoking Status-Checking Procedure (page 5 of 5)
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.
A SELECT command is executed, and, if an error occurs, function
SQLStatusCheck is executed. If the first error detected was a deadlock
or a shared memory problem, the SELECT command is automatically
re-executed as many as three times before the user is notified of the
situation. If other errors occurred before the deadlock or shared memory
problem, the transaction is not automatically re-applied. If an error
with an SQLCODE less than -14024 occurred, the program is terminated
after the error messages are displayed.
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
and 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 appear on the following pages.
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:
.
.
.
procedure DisplayUpdate;
begin
Code that prompts the user for new data appears here.
EXEC SQL UPDATE PurchDB.Parts
SET PartName = :PartName,
SalesPrice = :SalesPrice,
WHERE PartNumber = :PartNumber;
case SQLCA.SQLCODE of
OK : begin
NumberOfRows := SQLERRD[3];
writeln('The number of rows updated was: ' NumberOfRows);
end;
OtherWise : begin
writeln('No rows could be updated!');
SQLStatusCheck;
end;
end;
until Done;
end;
If the UPDATE command is successfully executed, SQLCODE is 0 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 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 DisplayRow is executed.
_________________________________________________________________________________
| |
| const |
| OK = 0; |
| NotFound = 100; |
| MaximumRows = 200; |
| |
| var |
| (*Begin Host Variable Declarations *) |
| EXEC SQL Begin Declare Section; |
| PartsTable : packed array[1..200] of |
| packed record |
| PartNumber : packed array[1..16] of char; |
| PartName : packed array[1..30] of char; |
| SalesPrice : longreal; |
| end; |
| SQLMessage : packed array[1..132] of char; |
| EXEC SQL End Declare Section; |
| (* End Host Variable Declarations *) |
| |
| SQLCA : SQLCA_type; (* SQL Communication Area *) |
| |
| I : integer; |
| NumberOfRows : integer; |
| |
| procedure BulkSelect; |
| begin |
| EXEC SQL BULK SELECT PartNumber, |
| PartName, |
| SalesPrice |
| INTO :PartsTable |
| FROM PurchDB.Parts; |
| |
| case SQLCA.SQLCODE of |
| OK : DisplayTable; |
| NotFound : begin |
| writeln; |
| writeln('No rows qualify for this operation!'); |
| end; |
| OtherWise : begin |
| NumberOfRows := SQLERRD[3]; 1 |
| writeln('Only ' NumberOfRows 'rows were retrieved ');|
| writeln(' before an error occurred!'); |
| DisplayTable; |
| SQLStatusCheck; |
| end; |
| end; |
| |
| |
_________________________________________________________________________________
Figure 5-3. Using SQLERRD[3] After a BULK SELECT Operation
________________________________________________________________________________
| |
| . |
| . |
| . |
| procedure DisplayTable; |
| begin |
| if SQLERRD[3] = MaximumRows then 2 |
| begin |
| writeln; |
| writeln('WARNING: There may be additional rows that qualify!');|
| end; |
| The column headings are displayed here. |
| for I := 1 to SQLERRD[3] do |
| DisplayRow; 3 |
| writeln; |
| end; |
| |
| procedure DisplayRow; |
| begin |
| writeln(PartNumber(I), '|'); |
| writeln(PartName(I), '|'); |
| writeln(SalesPrice(I), '|'); |
| end; |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
________________________________________________________________________________
Figure 5-3. Using SQLERRD[3] After a BULK SELECT Operation (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:
procedure FetchRow;
begin
EXEC SQL FETCH CURSOR1
INTO :PartNumber,
:PartName,
:SalesPrice;
case SQLCA. SQLCODE of
OK : DisplayRow;
NotFound : begin
DoneFetch := TRUE;
writeln;
writeln('Row not found or no more rows.');
end;
OtherWise : begin
SQLStatusCheck;
end;
end;
end;
.
.
.
EXEC SQL OPEN CURSOR1;
.
.
.
repeat
FetchRow
until DoneFetch := TRUE;
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 the declaration part). If no rows
qualify for the active set, SQLCODE is NotFound the first time procedure
FetchRow is executed.
Determining When More Than One Row Qualfies.
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 the declaration part) a
status checking procedure is not invoked, but a warning message is
displayed:
procedure UpdateData;
begin
This procedure verifies that the row(s) to be changed exist, then invokes
procedure DisplayUpdate to accept new data from the user.
EXEC SQL SELECT OrderNumber, ItemNumber, OrderQty
INTO :OrderNumber,
:ItemNumber,
:OrderQty
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber;
case SQLCA.SQLCODE of
OK : DisplayUpdate;
NotFound : begin
writeln;
writeln('Row not found.');
end;
MultipleRows : begin
writeln;
writeln('WARNING: More than one row qualifies');
DisplayUpdate;
end;
OtherWise : begin
SQLStatusCheck;
end;
end;
end;
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
SQLStatusCheck function routine is executed to display any error or
warning messages, and the program is terminated.
if SQLCA. SQLCODE = -14046 then
begin
CommitWork;
SQLStatusCheck;
TerminateProgram;
end;
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 exhausted in DBEFileSet. (DBERR 2502)
In the following example, SQLCODE is checked for an out of space
condition. If the condition is true, the program rolls back the
transaction to an appropriate savepoint. The program issues a COMMIT
WORK command, the SQLStatusCheck routine is executed to display any
messages, and the program is terminated.
if SQLCA. SQLCODE = -2502 then
begin
RollbackWork;
CommitWork;
TerminateProgram;
end;
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 SQLStatusCheck
routine is executed to display any messages, and the program is
terminated.
EXEC SQL CONNECT TO 'PartsDBE';
if SQLCA. SQLCODE = -2300 then
begin
SQLStatusCheck;
TerminateProgram;
end; (* End if *)