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 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 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
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 Checking.
The 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.
____________________________________________________________________________
| |
| /* 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 */ |
| |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 4-1. Program cex5: Implicit and Explicit Status Checking
___________________________________________________________________________________
| |
| 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 */ |
___________________________________________________________________________________
Figure 4-1. Program cex5: Implicit and Explicit Status Checking (page 2 of 6)
__________________________________________________________________
| |
| 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; |
| |
| |
| |
| |
| |
__________________________________________________________________
Figure 4-1. Program cex5: Implicit and Explicit Status Checking (page 3 of 6)
_______________________________________________________________________________
| |
| 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; |
| |
_______________________________________________________________________________
Figure 4-1. Program cex5: Implicit and Explicit Status Checking (page 4 of 6)
___________________________________________________________________________
| |
| 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 */ |
___________________________________________________________________________
Figure 4-1. Program cex5: Implicit and Explicit Status Checking (page 5 of 6)
____________________________________________________________________
| |
| 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 */ |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________
Figure 4-1. Program cex5: Implicit and Explicit Status Checking (page 6 of 6)
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.
______________________________________________________________
| |
| |
| |
| |
| |
| |
| |
| #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(); |
| . |
| . |
| . |
| } |
| |
| |
| |
| |
| |
| |
______________________________________________________________
Figure 4-2. Explicit Status Checking Procedures
_______________________________________________________________________________
| |
| 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(); |
| . |
| . |
| . |
| } |
| |
| |
_______________________________________________________________________________
Figure 4-2. Explicit Status Checking Procedures (page 2 of 5)
________________________________________________________________________________
| |
| 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(); |
| . |
| . |
| . |
| } |
| |
| |
________________________________________________________________________________
Figure 4-2. Explicit Status Checking Procedures (page 3 of 5)
______________________________________________________________________________
| |
| 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; |
| } |
| |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 4-2. Explicit Status Checking Procedures (page 4 of 5)
______________________________________________________________________________
| |
| |
| 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 */ |
| |
| |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 4-2. Explicit Status Checking Procedures (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 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 Processed.
Sqlerrd[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 Operations.
The 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.
BULK Operations.
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.
________________________________________________________________________________
| |
| #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; |
| } |
| } |
| |
| |
| |
| |
| |
| |
________________________________________________________________________________
Figure 4-3. Determining Number of Rows Processed After a BULK SELECT
______________________________________________________________________________
| |
| . |
| . |
| . |
| 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 |"); |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
______________________________________________________________________________
Determining Number of Rows Processed After a BULK SELECT (page 2 of 2)
Detecting End of Scan.
Previous examples in this chapter have illustrated how an sqlcode of 100
can be detected and handled for data manipulation commands that do not
use a cursor. When a cursor is being used, this sqlcode value 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.
Determining When More Than One Row Qualifies.
If more than one row qualifies for a non-BULK SELECT or FETCH operation,
ALLBASE/SQL sets sqlcode to -10002. In the following example, when
sqlcode is MultipleRows (defined as -10002 in the declaration part), a
status checking function is not invoked; instead a warning message is
displayed:
int UpdateData()
{
This function verifies that the row(s) to be changed
exist, then invokes the 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("\n WARNING: More than one row qualifies!");
DisplayUpdate();
break;
default: SQLStatusCheck();
break;
}
}
NOTE The PARTS table in the sample database has a unique index on
PARTNUMBER, so a test for multiple rows is not required. This test
is useful for the ORDERITEMS table which does not have a unique
index.
Detecting Log Full Condition.
When the log file is full, log space must be reclaimed before ALLBASE/SQL
can process any additional transactions. Your program can detect the
situation, and it can be corrected by the DBA.
SQLEXPLAIN retrieves the following message:
Log full. (DBERR 14046)
In the following example, sqlcode is checked for a log full condition.
If the condition is true, ALLBASE/SQL has rolled back the current
transaction. The program issues a COMMIT WORK command, the
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 Conditions.
It is possible that data or index space may be exhausted in a DBEFileSet.
This could happen as rows are being added or an index is being created or
when executing queries which require that data be sorted. Your program
can detect the problem, and the DBA must add index or data space to the
appropriate DBEFileSet.
SQLEXPLAIN retrieves the following message:
Data or Index space exhaused in DBEFileSet. (DBERR 2502)
In the following example, sqlcode is checked for an out of space
condition. If the condition is true, the transaction is rolled back to
an appropriate savepoint. The program issues a COMMIT WORK command, the
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 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)
SQLStatusCheck();
TerminateProgram();