Testing SQLCODE Directly
Go Up to Standard Error Handling
A program can test SQLCODE directly after each SQL statement instead of relying on WHENEVER to trap and handle all errors. The main advantage to testing SQLCODE directly is that custom error-handling routines can be designed for particular situations.
For example, the following C code fragment checks if SQLCODE is not zero after a SELECT statement completes. If so, an error has occurred, so the statements inside the if clause are executed. These statements test SQLCODE for two specific values,
–1, and 100, handling each differently. If SQLCODE is set to any other error value, a generic error message is displayed and the program is ended gracefully.
EXEC SQL
SELECT CITY INTO :city FROM STATES
WHERE STATE = :stat:statind;
if (SQLCODE)
{
if (SQLCODE == –1)
printf("too many records found\n");
else if (SQLCODE == 100)
printf("no records found\n");
else
{
printf("Database error, SQLCODE = %d\n", SQLCODE);
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT;
exit(1);
}
}
printf("found city named %s\n", city);
EXEC SQL
COMMIT;
EXEC SQL
DISCONNECT;
The disadvantage to checking SQLCODE directly is that it requires many lines of extra code just to see if an error occurred. On the other hand, it enables errors to be handled with function calls, as the following C code illustrates:
EXEC SQL
SELECT CITY INTO :city FROM STATES
WHERE STATE = :stat:statind;
switch (SQLCODE)
{
case 0:
break; /* NO ERROR */
case –1
ErrorTooMany();
break;
case 100:
ErrorNotFound();
break;
default:
ErrorExit(); /* Handle all other errors */
break;
}
. . .
Using function calls for error handling enables programs to resume execution if errors can be corrected.