Handling Exceptions

Overview
Using SQLSTATE
Using SQLCODE
Using WHENEVER
Using GET DIAGNOSTICS
Example program

Overview

Unless you explicitly include exception-handling code in your FESQLC application, the program continues when an SQL error occurs. Uncontrolled SQL errors may cause your program to have unexpected behavior.

Suggested exception-handling strategies are:

Check after each SQL statement by testing the value of SQLSTATE (or SQLCODE).
Use the WHENEVER statement to specify the action to be taken when an SQL statement is not successful.
Use GET DIAGNOSTICS to obtain additional information about any exceptions.

Using SQLSTATE

Warning: Using SQLSTATE is the preferred ANSI method of checking for exceptions. However, not all database servers do support SQLSTATE. You must check the database server documentation to verify if SQLSTATE is supported.

This global variable defined by FESQLC returns the status of the SQL statement most recently executed. The status can be:

Success
Success, but no rows found
Success, but warnings generated
Failure, runtime error generated

FESQLC automatically copies the status information from the diagnostics area into the SQLSTATE global variable; you do not have to define the variable, and it is accessible anywhere in your program.

SQLSTATE can contain only digits and capital letters. The first two characters of the five-character SQLSTATE string indicate the class of the execution code; the remaining characters provide additional information. Check the first two characters to determine whether the most recently executed SQL statement was successful:

Class (first 2 chars)	Description
00	SQL statement was successful
01	SQL statement was successful, with warnings
02	SELECT or FETCH statement resulted in NOT FOUND condition
>02	SQL statement resulted in a runtime error

The following code checks SQLSTATE for the NOT FOUND case:

if (strncmp(SQLSTATE, "02", 2) == 0) {
  break;
}

If an SQL error occurred, you can get specific information from the other three characters of SQLSTATE. The characters 0 to 4 and A to H are used for the SQLSTATE class codes in ANSI and X/OPEN standard implementations:

First two characters	Remaining characters	Description
00	000	SQL statement was successful
01	000	SQL statement was successful, with warnings:
01	002	Warning: Disconnect error, transaction rolled back
01	003	Warning: Null value eliminated in set function
01	004	Warning: String data right truncated
01	005	Insufficient item descriptor areas
01	006	Privilege not revoked
02	000	SELECT or FETCH statement resulted in NOT FOUND condition
>02		SQL statement resulted in a runtime error
0A	000	Feature not supported.
0A	001	Multiple database server transactions
21	000	Cardinality violation
22	000	Data exception
22	001	String data, right truncation
22	002	Null value, no indicator parameter
22	003	Numeric value out of range
22	012	Division by zero
22	024	Un-terminated string
23	000	Integrity-constraint violation
24	000	Invalid cursor state
2B	000	Dependent privilege descriptors still exist
2D	000	Invalid transaction termination
2E	000	Invalid connection name
33	000	Invalid SQL descriptor name
34	000	Invalid cursor name
3C	000	Duplicate cursor name
40	000	Transaction rollback
40	003	Statement completion unknown
42	000	Syntax error or access violation

Check your database server documentation for additional server-specific implementations of SQLSTATE codes.

Using SQLCODE

SQLCODE is a global variable defined by FESQLC to determine the success of the most recently executed SQL statement.

SQLCODE holds the Informix SQL error code. Using SQLSTATE is the preferred method; SQLCODE is provided for backwards compatibility.

The database server returns information about the execution of an SQL statement to the SQL Communications Area (sqlca) C structure. FESQLC copies the value of the sqlca.sqlcode field to the SQLCODE global variable. Your program can check the value in the SQLCODE variable for the following information:

Value	Description
0	SQL statement was successful
100	SELECT or FETCH statement resulted in NOT FOUND condition
<0	SQL statement resulted in a runtime error. The number specifies the particular Informix error number.

Native SQL error codes

If an SQL error occurs, the database server specific error code is available in sqlca.sqlerrd[1].

Warnings

If a warning was generated by the SQL statement, sqlca.sqlwarn.sqlwarn0 is set to "W". To test for warnings, check the first warning field. If this indicates the database server has generated a warning, you can check the values of the other fields in sqlca.sqlwarn to identify the specific condition.

See your database server documentation for additional information about specific errors reported in SQLCODE, and the sqlca structure.

Using WHENEVER

Your program can use the WHENEVER statement to trap exceptions that occur during the execution of SQL statements, and to specify the action to be taken when a specific class of error occurs:

WHENEVER <exception class> <exception action>

Exception classes:

SQLERROR - exception occurs when an SQL statement has failed.
SQLWARNING - exception occurs when an SQL statement has generated a warning.
NOT FOUND - exception occurs when the NOTFOUND condition is returned by an SQL statement.

Using WHENEVER SQLERROR statements allows your program to react to errors and warnings, in the same way as checking the SQLSTATE or SQLCODE variables.

Actions to be taken:

CONTINUE - the program ignores the exception and continues with the next instruction in the program. This is the default.
STOP - the program stops executing immediately when an exception occurs.
CALL <function name>- the specified function in the program is called if an exception occurs.
GOTO <label> - control is transferred to the program code identified by the specified label. The program code specified by GOTO <label> must be included in every program block that contains SQL statements, or another WHENEVER statement in that program block must re-set the condition.

Examples:

WHENEVER SQLERROR STOP;
EXEC SQL CONNECT TO mydb;
...

WHENEVER SQLERROR GOTO error_handling_code;
EXEC SQL DELETE FROM orderstab WHERE cust_num = "101";
...

The default action is CONTINUE if no WHENEVER condition is set. The actions for the exception classes can be set independently.

The GET DIAGNOSTICS statement

The diagnostics area is an internal structure that the database server updates after the execution of each SQL statement. The GET DIAGNOSTICS statement can provide additional information about the most recently executed SQL statement.

Statement fields return overall information about the most recently executed SQL statement. For example, you can determine the number of exceptions generated by an SQL statement, indicating whether you need to seek additional information.

Syntax:

EXEC SQL GET DIAGNOSTICS :host-variable = field-name [,...] ;

The keywords for the statement fields of the diagnostic area, and the data types of the returned information, are:

Field name	Data type	Information type	Description
NUMBER	mint	Statement	Number of exceptions generated. Even a successful execution will generate one exception.
MORE	char[2]	Statement	Contains Y or N plus a null terminator; Y indicates that the diagnostics area contains all the exceptions information. N indicates that there is more information than the database server can store in the diagnostics area.
ROWCOUNT	mint	Statement	Number of rows that a successful SELECT, UPDATE, or DELETE statement has affected. ROWCOUNT for a SELECT statement is undefined.

GET DIAGNOSTICS can return multiple exceptions; each exception that is generated has a number. Exception number 1 is the exception generated by the last SQL statement executed in your program. No set order exists for any additional exceptions that are generated. To get information about a particular exception, use the following syntax:

EXEC SQL GET DIAGNOSTICS EXCEPTION exception-number
              :host-variable = field-name [,...] ;

Some of the keywords for the exception fields associated with a specific exception, and the data types of the information, are:

Field name	Data type	Information type	Description
RETURNED_SQLSTATE	char[6]	Exception	Status of the exception. FESQLC automatically copies the information from this field for exception 1 to the SQLSTATE global variable.
INFORMIX_SQLCODE	int4	Exception	Contents of sqlca.sqlcode; FESQLC automatically copies the information from this field to the SQLCODE global variable.
MESSAGE_TEXT	char[8191]	Exception	Variable length character string that describes the exception.
MESSAGE_LENGTH	mint	Exception	Number of characters that are in the message stored in MESSAGE_TEXT.
SERVER_NAME	char[255]	Exception	Variable length character string holding the name of the database server used in the most recent connection statement; will be blank if there is no server connected.
CONNECTION_NAME	char[255]	Exception	Variable length character string holding the name of the connection associated with the most recent connection statement; will be blank if there is no current connection.

See the SQL documentation for your database server for additional information about exception fields in the diagnostics area.

To return information about your SQL statement and any exceptions to your program:

First, declare a host variable in your program to contain the name of the field in the diagnostics area that you want to access:

EXEC SQL BEGIN DECLARE SECTION;
  mint numrows;
  mint numerr;
  char messagetext[8191];
  mint messagelength; 
EXEC SQL END DECLARE SECTION;

To get statement information after the SQL statement has executed, specify the diagnostics area field name. For example, to get the number of rows updated, inserted, or deleted by the SQL statement, and the number of exceptions generated:

EXEC SQL DELETE FROM orders WHERE store_num = 111;
EXEC SQL GET DIAGNOSTICS :numrows = ROWCOUNT, :numerr = NUMBER;

Use the exception clause to get information about a specific exception generated by your SQL statement. Exception 1 is the exception associated with the last SQL statement executed.

EXEC SQL GET DIAGNOSTICS EXCEPTION 1 :messagetext = MESSAGE_TEXT;

You do not have to use GET DIAGNOSTICS to check the RETURNED_SQLSTATE and INFORMIX_SQLCODE fields for exception 1, since FESQLC copies the values from those fields to the SQLSTATE and SQLCODE global variables.

Note: The GET DIAGNOSTICS statement does not change the contents of the diagnostics area.

Example program

01 #include <stdio.h>
02
03 EXEC SQL define NAME_LEN 20;  /* customer.store_name is a CHAR(20) */
04 EXEC SQL define ADDR_LEN 20;  /* customer.addr is a CHAR(20)       */
05
06 void errlog(void)
07 {
08  fprintf(stderr, "Error occurred:\n");
09  fprintf(stderr, "  SQLSTATE = [%s]  SQLCODE = %d\n\n",
10                  SQLSTATE, SQLCODE);
11  EXEC SQL DISCONNECT ALL;
12  exit(1);
13 }
14
15 int main()
16 {
17  EXEC SQL BEGIN DECLARE SECTION;
18    int  p_num;
19    varchar p_name[ NAME_LEN + 1 ];
20    int2 i_name;
21    varchar p_addr[ ADDR_LEN + 1 ];
22    int2 i_addr;
23    varchar p_addr2[ ADDR_LEN + 1 ];
24    int2 i_addr2;
25    char p_state[3]; 
26  EXEC SQL END DECLARE SECTION;
27
28  EXEC SQL WHENEVER ERROR CALL errlog; /* set error handling */
29
30  printf( "Connecting...\n\n");
31  EXEC SQL CONNECT TO 'custdemo';
32
33  EXEC SQL DECLARE c1 CURSOR FOR
34    SELECT store_num, store_name, addr, addr2
35      FROM customer
36      WHERE state = :p_state;
37
38  strcpy(p_state, "IL");
39  EXEC SQL OPEN c1;
40
41  for (;;)
42  {
43
44   EXEC SQL FETCH c1 INTO :p_num,
45                           :p_name INDICATOR :i_name,
46                           :p_addr INDICATOR :i_addr,
47                           :p_addr2 INDICATOR :i_addr2;
48
49   if (strncmp(SQLSTATE, "02", 2) == 0) {
50        /* No more rows */
51        break;
52   }
53
54   printf("%6d %-20s\n      %s %s\n",
55            p_num,
56            i_name == 0 ? p_name : "<no name>",
57            i_addr == 0 ? p_addr : "<no address>",
58            i_addr2 == 0 ? p_addr2 : ""
59   );
60  }
61
62  EXEC SQL CLOSE c1;
63  EXEC SQL FREE c1;
64
65  printf("\nDisconnecting...\n\n");
66  EXEC SQL DISCONNECT CURRENT;
67
68  return 0;
69 }