 |
» |
|
|
|
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 sqlwarn[0].)
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 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
sqlwarn[0] after processing any SQL command except the following:
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 function 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.
int Procedure2()
{
EXEC SQL SQLCommand2;
}
int Procedure1()
{
EXEC SQL WHENEVER SQLERROR GOTO a2000;
EXEC SQL SQLCommand1;
}
.
.
.
{
Procedure1();
Procedure2();
}
EXEC SQL WHENEVER SQLERROR CONTINUE;
|
The code that the preprocessor generates depends on the
condition and action in a WHENEVER command. In the example above,
the preprocessor inserts a test for a negative sqlcode and
a statement that invokes the code routine
located at Line Label a2000:
#if 0
EXEC SQL WHENEVER SQLERROR GOTO a2000;
#endif
#if 0
EXEC SQL SQLCommand1;
#endif
Statements for executing SQLCommand1 appear here.
if (sqlca.sqlcode < 0) {
goto a2000;
}
|
As the previous example illustrates, you pass control to an
exception-handling routine with a
WHENEVER command, by using a GOTO
statement with an alpha-numeric line label rather than a function name.
Therefore after the
exception-handling routine is executed, control cannot
automatically return to the statement which invoked it.
You must use another GOTO statement
to explicitly
pass control to a specific point in your program:
/* WHENEVER Routine -- SQL Error */
a2000:
if ((sqlca.sqlcode <= -14024) || (sqlca.sqlcode == -4008)) {
TerminateProgram();
}
else
do {
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
goto a500; /* Goto Restart/Reentry point of function */
|
This exception-handling routine explicitly checks
the first sqlcode returned. The
program either 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 routine 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" later in the chapter. Program Illustrating Implicit and Explicit Status CheckingThe program in Figure 4-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
RELEASE
|
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 routine at Label a1000 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 a2000 is executed when an error
occurs during the processing of the SELECT command. This code
routine explicitly examines the sqlcode value to
determine whether it is -10002, in which case it displays
a warning message. If sqlcode contains
another value, function 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 less than -14024): If a deadlock or shared memory problem occurred,
the program attempts to execute
the SelectData function
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 a3000 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 a4000.
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 4-1 Program cex5: Implicit and Explicit Status Checking
/* Program cex5 */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* This program illustrates the use of SQL's SELECT command to */
/* retrieve one row or tuple of data at a time. */
/* This programs is the same as cex2 with added status checking */
/* and deadlock routines. */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
typedef int boolean;
char response[2];
boolean Abort;
boolean SQLCommandDone;
int TryCounter;
#include <stdio.h>
#define OK 0
#define NotFound 100
#define MultipleRows -10002
#define DeadLock -14024
#define FALSE 0
#define TRUE 1
#define NoMemory -4008
#define TryLimit 3
sqlca_type sqlca; /* SQL Communication Area */
/* Begin Host Variable Declarations */
EXEC SQL BEGIN DECLARE SECTION;
char PartNumber[17];
char PartName[31];
double SalesPrice;
sqlind SalesPriceInd;
char SQLMessage[133];
EXEC SQL END DECLARE SECTION;
/* End Host Variable Declarations */
int SQLStatusCheck() /* Function to Display Error Messages */
{
if ((sqlca.sqlcode == DeadLock) || (sqlca.sqlcode == NoMemory)) {
if (TryCounter == TryLimit) {
SQLCommandDone = TRUE;
printf("\n Could not complete transaction. You may want to try again.");
}
else
SQLCommandDone = FALSE;
}
else {
Abort = FALSE;
if ((sqlca.sqlwarn[6] = 'W') || (sqlca.sqlwarn[6] = 'w'))
Abort = TRUE;
}
do {
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
if (Abort) {
EndTransaction();
ReleaseDBE();
}
} /* End SQLStatusCheck Function */
EXEC SQL WHENEVER SQLERROR GOTO a1000; 1
boolean ConnectDBE() /* Function to Connect to PartsDBE */
{
boolean ConnectDBE;
ConnectDBE = TRUE;
printf("\n Connect to PartsDBE");
EXEC SQL CONNECT TO 'PartsDBE';
goto exit;
a1000: /* WHENEVER SQLERROR entry point 1 */
SQLStatusCheck();
EndTransaction();
ReleaseDBE();
exit:
return (ConnectDBE);
} /* End of ConnectDBE Function */
boolean BeginTransaction() /* Function to Begin Work */
{
boolean BeginTransaction;
BeginTransaction = TRUE;
printf("\n");
printf("\n Begin Work");
EXEC SQL BEGIN WORK;
goto exit;
a1000: /* WHENEVER SQLERROR entry point 1 */
SQLStatusCheck();
EndTransaction();
ReleaseDBE();
exit:
return (BeginTransaction);
} /* End BeginTransaction Function */
int EndTransaction() /* Function to Commit Work */
{
printf("\n");
printf("\n Commit Work");
EXEC SQL COMMIT WORK;
goto exit;
a1000: /* WHENEVER SQLERROR entry point 1 */
SQLStatusCheck();
ReleaseDBE();
exit:
return(0);
} /* End EndTransaction Function */
int ReleaseDBE() /* Function to Release PartsDBE */
{
printf("\n");
printf("\n Release PartsDBE");
printf("\n");
EXEC SQL RELEASE;
goto exit;
a1000: /* WHENEVER SQLERROR entry point 1 */
SQLStatusCheck();
EndTransaction();
exit:
return(0);
} /* End ReleaseDBE Function */
EXEC SQL WHENEVER SQLERROR CONTINUE; 2
int DisplayRow() /* Function to Display Parts Table Rows */
{
printf("\n");
printf(" Part Number: %s\n", PartNumber);
printf(" Part Name: %s\n", PartName);
if (SalesPriceInd < 0) {
printf("\n Sales Price: is NULL");
}
else
printf(" Sales Price: %10.2f\n", SalesPrice);
} /* End of DisplayRow Function */
EXEC SQL WHENEVER SQLERROR GOTO a2000; 3
EXEC SQL WHENEVER SQLWARNING GOTO a3000; 4
EXEC SQL WHENEVER NOT FOUND GOTO a4000; 5
int Select() /* Function to Query Parts Table */
{
do {
if (SQLCommandDone) {
printf("\n");
printf("\n Enter Part Number within Parts Table or '/' to STOP > ");
scanf("%s",PartNumber);
printf("\n");
TryCounter = 0;
}
if (PartNumber[0] != '/') {
BeginTransaction();
TryCounter = TryCounter + 1;
printf("\n 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 the row. */
SQLCommandDone = TRUE;
DisplayRow();
EndTransaction();
} /* End if */
} /* End do */
while (PartNumber[0] != '/');
goto exit;
a2000: /* WHENEVER SQLERROR entry point 2 */
if (sqlca.sqlcode == MultipleRows) {
printf("\n");
printf("\n WARNING: More than one row qualifies!");
EndTransaction();
SQLCommandDone = TRUE;
Select(); /* Call Restart/Reentry point */
}
else
SQLStatusCheck();
Select(); /* Call Restart/Reentry point */
a3000: /* WHENEVER SQLWARNING entry point */
printf("\n SQL WARNING has occurred. The following row");
printf("\n of data may not be valid!");
DisplayRow();
EndTransaction();
SQLCommandDone = TRUE;
Select(); /* Call Restart/Reentry point */
a4000: /* WHENEVER NOT FOUND entry point */
printf("\n");
printf("\n Row not found!");
EndTransaction();
SQLCommandDone = TRUE;
Select(); /* Call Restart/Reentry point */
exit:
return(0);
}/* End of Select Function */
main() /* Beginning of program */
{
printf("\n Program to SELECT specified rows from");
printf("\n the Parts Table - cex5");
printf("\n");
printf("\n Event List:");
printf("\n CONNECT TO PartsDBE");
printf("\n BEGIN WORK");
printf("\n SELECT the specified row from the Parts Table");
printf("\n until the user enters a '/'");
printf("\n COMMIT WORK");
printf("\n RELEASE from PartsDBE");
printf("\n");
if (ConnectDBE()) {
SQLCommandDone = TRUE; /* Initialize command done flag */
Select();
ReleaseDBE();
}
else
printf("\n Error: Cannot Connect to PartsDBE!\n");
} /* End of Program */
|
Explicit Status Checking Techniques | |
With explicit error handling,
you invoke a function after explicitly
checking sqlca values rather than using the WHENEVER command. The program in
Figure 4-1 has already illustrated several uses of explicit error
handling 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 4-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 4-2 contains four data manipulation
operations: INSERT, UPDATE, DELETE, and SELECT.
Each of these operations is
executed within its
own function. As in the program in Figure 4-1, one
function is used for explicit status checking:
SQLStatusCheck. Unlike the program in Figure 4-1,
however, this function is invoked after an explicit test
of sqlcode is made immediately following each data manipulation
operation. Because status checking is performed in a called function rather than
in a routine following the embedded SQL command, control returns
to the point in the program where SQLStatusCheck is invoked. Figure 4-2 Explicit Status Checking Procedures
#define Deadlock -14024
#define OK 0
#define NotFound 100
#define MultipleRows -10002
#define NoMemory -4008
.
.
.
int SelectActivity()
{
This function prompts for a number that indicates
whether the user wants to SELECT, UPDATE, DELETE,
or INSERT rows, then invokes a function that
accomplishes the selected activity. The DONE flag
is set when the user enters a slash.
}
.
.
.
int InsertData()
{
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) SQLStatusCheck();
.
.
.
}
int UpdateData()
{
This function verifies that the row(s) to be changed
exist, then invokes function DisplayUpdate to accept
new data from the user.
EXEC SQL SELECT PartNumber, PartName, SalesPrice
INTO :PartNumber,
:PartName,
:SalesPrice
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
switch(sqlca.sqlcode) {
case OK: DisplayUpdate();
break;
case NotFound: printf("\n");
printf("\n Row not found!");
break;
case MultipleRows: printf("\n");
printf("WARNING: More than one row qualifies!")
DisplayUpdate();
break;
default: SQLStatusCheck();
break;
}
.
.
.
}
.
.
.
int DisplayUpdate()
{
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 != OK) SQLStatusCheck();
.
.
.
}
int DeleteData()
{
This function verifies that the row(s) to be deleted
exist, then invokes the function DisplayDelete to delete
the row(s).
EXEC SQL SELECT PartNumber, PartName, SalesPrice
INTO :PartNumber,
:PartName,
:SalesPrice
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
switch(sqlca.sqlcode) {
case OK: DisplayDelete();
break;
case NotFound: printf("\n");
printf("\n Row not found!");
break;
case MultipleRows: printf("\n");
printf("WARNING: More than one row qualifies!");
DisplayDelete();
break;
default: SQLStatusCheck();
break;
}
.
.
.
}
.
.
.
int DisplayDelete()
{
Statements that verify that the deletion should
actually occur appear here.
EXEC SQL DELETE FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
if (sqlca.sqlcode != OK) SQLStatusCheck();
.
.
.
}
int SelectData()
{
Statements that prompt for a partnumber appear here.
EXEC SQL SELECT PartNumber, PartName, SalesPrice
INTO :PartNumber,
:PartName,
:SalesPrice
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
switch(sqlca.sqlcode) {
case OK: DisplayRow();
break;
case NotFound: printf("\n");
printf("\n Row not found!");
break;
case MultipleRows: printf("\n");
printf("WARNING: More than one row qualifies!");
DisplayDelete();
break;
default: SQLStatusCheck();
break;
}
.
.
.
}
.
.
.
int SQLStatusCheck()
{
if ((sqlca.sqlcode == DeadLock) || (sqlca.sqlcode=NoMemory)) {
if (TryCounter == TryLimit) {
SQLCommandDone = TRUE;
printf("\n Could not complete transaction. Try again if you
want.");
}
else
SQLCommandDone = FALSE;
}
else
{
Abort = FALSE;
if (sqlca.sqlwarn[6] == 'W') { /* The transaction was rolled back
due to other than deadlock or
shared memory problems. */
Abort = TRUE;
do {
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
}
if (Abort) {
TerminateProgram();
}
else
SQLCommandDone = TRUE;
}
}
.
if (SQLCommandDone) {
.
/* Prompt user for a part number. */
.
TryCounter = 0;
TryLimit = 3;
.
/* A transaction is started. */
.
TryCounter = TryCounter + 1;
} /* 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 4-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[2] 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[2] value can be used in these cases only when sqlcode
does not contain a negative number. When sqlcode is 0,
sqlerrd[2] is always equal to 1 for SELECT, FETCH,
UPDATE WHERE CURRENT, and DELETE WHERE CURRENT operations.
Sqlerrd[2] may be greater
than 1 if more than one row qualifies for an INSERT, UPDATE, or
DELETE operation.
When sqlcode is 100, sqlerrd[2] 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[2] contains the number of rows that could be successfully
retrieved or inserted before an error occurred.
If sqlcode is 0, sqlerrd[2]
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 4-2 could be modified to display the
number of rows inserted, updated, or deleted by using sqlerrd[2].
In the case
of the update operation, for example, the actual number of rows
updated could be displayed after the UPDATE command is executed:
.
.
.
int DisplayUpdate()
{
Code that prompts the user for new data appears here.
EXEC SQL UPDATE PurchDB.Parts
SET PartName = :PartName,
SalesPrice = :SalesPrice,
WHERE PartNumber = :PartNumber;
switch(sqlca.sqlcode) {
case OK: NumberOfRows = sqlerrd[2];
printf("The number of rows updated was: %d\n" NumberOfRows);
break;
default: printf("\n No rows could be updated!");
SQLStatusCheck();
break;
}
}
|
If the UPDATE command is successfully executed, sqlcode is 0
and sqlerrd[2]
contains the number of rows updated. If the UPDATE command cannot
be successfully executed, sqlcode contains a negative number and
sqlerrd[2] contains a 0. When using the BULK SELECT, BULK FETCH, or BULK INSERT commands,
you can use the sqlerrd[2] 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 4-3, the value in
sqlerrd[2] 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[2] 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[2]
is used to control the number of times function DisplayRow
is executed. Figure 4-3 Determining Number of Rows Processed After a BULK SELECT
#define OK 0
#define NotFound 100
#define MaximumRows 200
/*Begin Host Variable Declarations */
EXEC SQL Begin Declare Section;
struct {
char PartNumber[17];
char PartName[31];
double SalesPrice;
} PartsTable[MaximumRows];
char SQLMessage[133];
EXEC SQL End Declare Section;
/* End Host Variable Declarations */
sqlca : sqlca_type; /* SQL Communication Area */
int i;
int NumberOfRows;
int BulkSelect()
{
EXEC SQL BULK SELECT PartNumber,
PartName,
SalesPrice
INTO :PartsTable
FROM PurchDB.Parts;
switch(sqlca.sqlcode) {
case OK: DisplayTable();
break;
case NotFound: printf("\n");
printf("\n No rows qualify for this operation!");
break;
default: NumberOfRows = sqlerrd[2]; 1
printf("\nOnly %d rows were retrieved",NumberOfRows);
printf("\n before an error occurred!");
DisplayTable();
SQLStatusCheck();
break;
}
}
.
.
.
int DisplayTable()
{
if (sqlerrd[2] == MaximumRows) 2
{
printf("\n");
printf("\nWARNING: There may be additional rows that qualify!");
}
The column headings are displayed here.
for (i = 0; i < sqlerrd[2]; i)
DisplayRow(); 3
printf("\n");
}
int DisplayRow()
{
printf(PartNumber[i], "%s\n |");
printf(PartName[i], "%s\n |");
printf(SalesPrice[i], "%.2f\n |");
}
|
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 can be used to determine when all
rows in an active set have been fetched:
int FetchRow()
{
EXEC SQL FETCH CURSOR1
INTO :PartNumber,
:PartName,
:SalesPrice;
switch(sqlca.sqlcode) {
case OK: DisplayRow();
break;
case NotFound: DoneFetch = TRUE;
printf("\n Row not found or no more rows!");
break;
default: SQLStatusCheck();
break;
}
}
.
.
.
EXEC SQL OPEN CURSOR1;
.
.
.
do {
FetchRow();
} while (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 has been fetched from
the active set, will set sqlcode to
NotFound (defined as 100 in the declaration part).
If no rows qualify for the active set, sqlcode is NotFound the first
time function FetchRow is executed. 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 routine
is executed to display any messages, and the program is terminated.
if (sqlca.sqlcode = -14046)
COMMIT WORK;
SQLStatusCheck();
TerminateProgram();
|
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
SQLStatusCheck routine
is executed to display any messages, and the program is terminated.
if (sqlca.sqlcode = -2502)
ROLLBACK WORK TO :SavePoint;
COMMIT WORK;
SQLStatusCheck();
TerminateProgram();
|
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 'PartsDBE';
if (sqlca.sqlcode = -2300)
SQLStatusCheck();
TerminateProgram();
|
|
