Status Codes

SQLCA

THE WHENEVER STATEMENT

Status Codes

SQLCA

Status codes for each executed SQL command are returned into what is called the SQL Communication Area (SQLCA). DBMaker uses variables contained in this data structure to pass status information to the C program, where the information can then be analyzed and any problems handled.

Declaring SQLCA

SQLCA is a structure declared automatically by the preprocessor when it encounters the following statement. The SQLCA structure variable must be accessible globally in any ESQL/C program. SQLCA and DBENVCA are both global variables that must be declared in the ESQL/C source program.

EXEC SQL INCLUDE [EXTERN] SQLCA;

Status Returned in SQLCA

Status information from the database server is always returned through the SQLCA, but it is the responsibility of the application program to analyze this data and handle any errors or warnings.

There are two ways to instruct the application program to examine the status codes in the SQLCA and handle errors and warnings found. You can write the commands for this in C code, or you can use the SQL command, WHENEVER, which will generate error handling C code during preprocessing.

The following is the definition of SQLCA.

#define MAX_ERR_STR_LEN 256

/*----------------------------------------------------------------       
* SQLCA - the SQL Communications Area (SQLCA)       
*-----------------------------------------------------------------*/

typedef struct sqlca      
    {      
     unsigned char  sqlcaid[8];          /* the string "SQLCA   "     */      
     long           sqlcabc;             /* length of SQLCA, in bytes */      
     long           sqlcode;             /* SQLstatus code            */      
     long           sqlerrml;            /* length of sqlerrmc data   */      
     unsigned char  sqlerrmc[MAX_ERR_STR_LEN]; /* name of object cause error */      
     unsigned char  sqlerrp[8];          /* diagnostic information    */      
     long           sqlerrd[6];          /* various count and error code */      
     unsigned char  sqlwarn[8];          /* warning flag array           */      
     unsigned char  sqlext[8];           /* extension to sqlwarn array   */      
    } sqlca_t;

#define SQLCODE sqlca.sqlcode    /* SQL status code */

#define SQLWARN0 sqlca.sqlwarn[0] /* master warning flag */  
#define SQLWARN1 sqlca.sqlwarn[1] /* string truncated */

DBMaker's error code is stored at sqlca.sqlerrd[0]. For detailed error information, please reference the DBMaker Error and Message Reference..

The number of fetched rows is stored at sqlca.sqlerrd[3]. You can reference it when you are fetch more than one rows in FETCH statement.

THE WHENEVER STATEMENT

You can use the WHENEVER statement to handle errors implicitly. When the ESQL/C preprocessor encounters the WHENEVER statement, it generates C error handling code whose execution is dependent on the outcome of an SQL statement.

Condition	Description
SQLERROR	When SQLCODE is SQL_ERROR
SQLWARNING	When SQLCODE is SQL_SUCCESS_WITH_INFO
NOT FOUND	When SQLCODE is SQL_NO_DATA_FOUND

You can use these conditions in the WHENEVER statement to direct your application program to do any of the following operations: STOP, CONTINUE, or GOTO.

• STOP- rolls back your work and disconnect from database, and terminate your application program when an SQL statements return status meets the specified condition.
• CONTINUE- disables the condition set in the previous WHENEVER statement and continues execution with the next statement after the statement that caused the error.
• GOTO label_name - directs execution to the label for an error-handling routine within your application program.
• DO c_action_statement - Do a specified action when the returned status meets the specified condition.
  For example:

WHENEVER SQLERROR DO break;    
WHENEVER SQLWARNING DO print_warning();    
while () 
   { 
   EXEC SQL .....; 
   EXEC SQL....; 
   }

The default value of each condition is CONTINUE. A WHENEVER statement affects all SQL statements that come after it in your application program, up to the next WHENEVER for the same condition. In other words, the most recent setting of the WHENEVER statement remains in effect for all of the following EXEC SQL statements to the end of the file, unless another WHENEVER statement in the middle overrides it.

To prevent the possibility of an infinite loop, do not forget to set WHENEVER check_case CONTINUE in your error handler, or in any other functions containing an EXEC SQL statement in the same program that don¹ require error handling using the WHENEVER statement.

For example:

int func() 
    { 
    ... 
    EXEC SQL WHENEVER SQLERROR goto error_handle;    
    EXEC SQL INSERT INTO emp_table VALUES (:emp_id, :emp_name, :emp_addr); 
    ... 
    ... 
    return 0; 
    error_handle: 
    EXEC SQL WHENEVER SQLERROR CONTINUE;    
    printf("ERR:%s\n", sqlcode.sqlerrmc);    
    return -1; 
    }