After each executable SQL statement, your program can find the status of execution either by explicit checking of SQLCA, or by implicit checking using the WHENEVER statement.
WHENEVER Statement
WHENEVER statement is used to specify what action to be taken when there is an error in the subsequent statements.
This statement allows you to do automatic error checking and handling.
The syntax is:
EXEC SQL WHENEVER <condition> <action>;
Oracle automatically checks SQLCA for <condition>, and if such condition is detected, your program will automatically perform <action>.
EXEC SQL WHENEVER {NOT FOUND|SQLERROR|SQLWARNING} {CONTINUE|GOTO label|STOP|DO routine}
The following table gives details of each option.
| NOT FOUND | When no row is found by SELECT |
| SQLERROR | When an error resulted by previous command |
| SQLWARNING | When a warning is signaled by Oracle |
| CONTINUE | Indicates that the program should continue with the next statement. |
| GOTO label | Program should branch to the statement named by label. |
| STOP | The execution of the program should stop |
| DO routine | Invokes the specified routine |
<condition> can be any of the following:
- SQLWARNING – sqlwarn[0] is set because Oracle returned a warning
- SQLERROR – sqlcode is negative because Oracle returned an error
- NOT FOUND – sqlcode is positive because Oracle could not find a row that meets your WHERE condition, or a SELECT INTO or FETCH returned no rows
<action> can be any of the following:
- CONTINUE – Program will try to continue to run with the next statement if possible
- DO – Program transfers control to an error handling function
- GOTO <label> – Program branches to a labeled statement
- STOP – Program exits with an exit() call, and uncommitted work is rolled back
Explanation
EXEC SQL WHENEVER SQLERROR GOTO errexit;
The above statement specifies if there is an error in any of the subsequent statements then control should goto a label called errexit.
The next statement used CONNECT command to connect to Oracle. It uses host variables – uid and pwd to supply username and password. Once connection is successful then it displays a message and then COMMIT WORK RELEASE will release the resources and log off.
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf("Connected to Oracle8i using Scott/Tiger\n");
EXEC SQL COMMIT WORK RELEASE;
return;
Statements given after label errexit will be executed when connection is not successful. It displays an error message.
errexit:
printf("Connection failed");
Example
/* code to find student name given id */
/* ... */
for (;;) {
printf("Give student id number : ");
scanf("%d", &id);
EXEC SQL WHENEVER NOT FOUND GOTO notfound;
EXEC SQL SELECT studentname INTO :st_name
FROM student
WHERE studentid = :id;
printf("Name of student is %s.\n", st_name);
continue;
notfound:
printf("No record exists for id %d!\n", id);
}
/* ... */Scope issue with WHENEVER Statement
** Note that the WHENEVER statement does not follow regular C scoping rules. Scoping is valid for the entire program. For example, if you have the following statement somewhere in your program (such as before a loop):
EXEC SQL WHENEVER NOT FOUND DO break;
All SQL statements that occur after this line in the file would be affected. Make sure you use the following line to cancel the effect of WHENEVER when it is no longer needed (such as after your loop):
EXEC SQL WHENEVER NOT FOUND CONTINUE;
SQLCA
SQLCA (SQL Communications Area) is used to detect errors and status changes in your program. This structure contains components that are filled in by Oracle at runtime after every executable SQL statement.
SQLCA – SQL Communication Area is a structure used to provide extra information about the most recently executed SQL command. The statement EXEC SQL INCLUDE SQLCA.H inserts the declaration of structure and the declaration of variable – sqlca, which is of type struct sqlca. This structure variable can be used to get information about previous execution.
The following table lists some of the important members of this structure.
| sqlcode | Error code |
| sqlerrm.sqlerrmc | Text of error message |
| sqlerrm.sqlerrml | Length of the error message |
| sqlerrd[2] | Number of rows processed |
To use SQLCA you need to include the header file sqlca.h using the #include directive. In case you need to include sqlca.h at many places, you need to first undefine the macro SQLCA with #undef SQLCA. The relevant chunk of sqlca.h follows:
#ifndef SQLCA
#define SQLCA 1
struct sqlca {
/* ub1 */ char sqlcaid[8];
/* b4 */ long sqlabc;
/* b4 */ long sqlcode;
struct {
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ long sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};
/* ... */Example Program
/* program to change the salary of an employee */
#include <stdio.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[80];
VARCHAR pwd[20];
int empno;
int sal;
VARCHAR ename[30];
int newsal;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.H;
void main()
{
strcpy(uid.arr,"SCOTT");
uid.len =strlen(uid.arr);
strcpy(pwd.arr,"TIGER");
pwd.len = strlen(pwd.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
/* take employee number from user */
printf("Enter employee number : ");
scanf("%d",&empno);
/* get the details of the employee */
EXEC SQL WHENEVER NOTFOUND GOTO noemp;
EXEC SQL select ename, sal into :ename, :sal from emp where empno = :empno;
/* display employee name and salary */
ename.arr [ ename.len ] = '\0';
printf("Name : %s Salary : %d\n", ename.arr, sal);
printf("Enter new salary : ");
scanf("%d", &newsal);
/* update employee record */
EXEC SQL update emp set sal = :newsal where empno = :empno;
printf("Updation Successful");
goto normalexit;
noemp:
printf("Sorry. Invalid employee number. Quitting...");
normalexit:
EXEC SQL COMMIT WORK RELEASE;
return;
errexit:
printf("Error: %70s", sqlca.sqlerrm.sqlerrmc);
}
Bits, Bytes and Brains 