 |
» |
|
|
|
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.
Figure 5-1 Implicitly Invoking Status-Checking Routines
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
(* 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;
MultipleRows = -10002;
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 $
(* 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 '../sampledb/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 $
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
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(Could not complete transaction. You may want to try again.');
end
else
SQLCommandDone := FALSE;
end
else
begin
Abort := FALSE;
if SQLCA.SQLWARN[6] = 'W' then Abort := TRUE;
repeat
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
until SQLCA.SQLCODE = 0;
if Abort then TerminateProgram;
end;
end; (* End SQLStatusCheck Procedure *)
$PAGE $
begin (* Beginning of Program *)
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 *)
if SQLCA.SQLCODE = MultipleRows then
Begin
writeln;
writeln('WARNING: More than one row qualifies.');
EndTransaction;
SQLCommandDone := True;
end;
SQLStatusCheck;
Goto 500; (* Goto Restart/Reentry point of main program *)
(* End *)
(* 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 *)
|
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 “Explicitly Invoking Status-Checking Procedure ” 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. Figure 5-2 Explicitly Invoking Status-Checking Procedure
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;
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;
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;
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;
.
.
.
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 *)
|
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.
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 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
|
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 OperationsThe example in Figure 5-2 “Explicitly Invoking Status-Checking Procedure ” 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.
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 “Using SQLERRD[3] After a BULK SELECT Operation”, 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.
Figure 5-3 Using SQLERRD[3] After a BULK SELECT Operation
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;
.
.
.
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;
|
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 QualfiesIf 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 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 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 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 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 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
SQLStatusCheck routine
is executed to display any messages, and the program is terminated.
EXEC SQL CONNECT TO '../sampledb/PartsDBE';
if SQLCA.{{SQLCODE}} = -2300 then
begin
SQLStatusCheck;
TerminateProgram;
end; (* End if *)
|
|
