Error handling in embedded SQL: SQLCA (SQL Communications Area), SQLCODE

Errors in embedded SQL are of two types:

1) Compile time errors: Errors like wrong commas, spelling mistakes of SQL keywords comes under compile time errors. These errors are reported to the programmer to fix. After they are fixed the program is recompiled.

2) Runtime errors: Runtime errors are logic errors like a user trying to update a table without necessary permissions. These errors are handled by the application program and returned with the error code.

Error handling using SQLCODE: In this process, SQLCA (SQL Communications Area) data structure is used for storing error variables and status indicators. An application program examines SQLCA to find whether the SQL statements have successfully executed or if there is an error. SQLCODE is a variable in the SQLCA data structure and it is declared as: long sqlcode; and is defined as:

#define SQLCODE sqlca.sqlcode

SQL code value can also be checked in a C program to find errors:

/* Other statements */
exec sql delete from employees
where sales < 200000;
if (sqlca.sqlcode < 0)
goto error_routine;
/* Other statements */
printf(“Encountered SQL error: %ld\n, sqlca.sqlcode);
/* Other statements */

For every embedded SQL statement the value of SQLCODE is put in SQLCA. SQLCODE of zero is used to inform that statement has successfully completed, negative value of SQLCODE indicates an error in the statement, and a positive value indicates a warning.

Leave a Reply