Check after each SQL statement

To check for an exception, you can include code to explicitly test the value of SQLSTATE (or SQLCODE).

Tip: Decide whether to use SQLSTATE (and the diagnostics area) or SQLCODE (and the sqlca structure) to determine exception values. Use the chosen exception-handling variables consistently. If you mix these two variables unnecessarily, you create code that is difficult to maintain. Keep in mind that SQLSTATE is the more flexible and portable of these two options.
For example, if you want to use SQLSTATE to check whether a CREATE DATABASE statement has executed as expected, you can use the code that the following figure shows.
Figure 1: Using SQLSTATE to test whether an error occurred during an SQL statement
EXEC SQL create database personnel with log;
if(strncmp(SQLSTATE, "02", 2) > 0) /* > 02 is an error */
{
EXEC SQL get diagnostics exception 1 
:message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;
message[messlen] =’\0’; /* terminate the string. */

printf("SQLSTATE: %s, %s\n", SQLSTATE, message);
exit(1);
}

As an alternative, you can write an exception-handling function that processes any exception. Your program can then call this single exception-handling function after each SQL statement.

The sqlstate_exception() function, which the following figure shows, is an example of an exception-handling function that uses the SQLSTATE variable and the diagnostics area to check for warnings, the NOT FOUND condition, and runtime errors. It is called after each SQL statement.
Figure 2: Example of an exception-handling function that uses SQLSTATE
EXEC SQL select * from customer where fname not like "%y";
sqlstate_exception("select");
;

int4 sqlstate_exception(s)
char *s;
{
   int err = 0;

   if(!strncmp(SQLSTATE, "00", 2) ||
         !strncmp(SQLSTATE,"02",2))
      return(SQLSTATE[1]);

   if(!strncmp(SQLSTATE, "01", 2))
      printf("\n********Warning encountered in %s********\n",
         statement);
   else /* SQLSTATE class > "02" */
      {
      printf("\n********Error encountered in %s********\n",
         statement);
      err = 1;
      }

   disp_sqlstate_err(); /* See the getdiag sample program */
   if(err)
      {
      printf("********Program terminated*******\n\n");
      exit(1);
      }

 /*
  * Return the SQLCODE
  */
 return(SQLCODE);
}
The sqlstate_exception() function, which Example of an exception-handling function that uses SQLSTATE shows, handles exceptions as follows:
  • If the statement was successful, sqlstate_exception() returns zero.
  • If a NOT FOUND condition occurs after a SELECT or a FETCH statement, sqlstate_exception() returns a value of 2.
  • If a warning or a runtime error occurs—that is, if the first two bytes of SQLSTATE are "01" (warning) or are greater than "02" (error)—the sqlstate_exception() function calls the disp_sqlstate_err() function to display exception information. (For the code of the disp_sqlstate_err() function, see Lines 32 - 80.)
  • If SQLSTATE indicates an error, the sqlstate_exception() function uses the exit() system call to exit the program. Without this call to exit(), execution would continue at the next SQL statement after the one that had generated the error.

To handle errors, the sqlstate_exception() function can alternatively omit the exit() call and allow execution to continue. In this case, the function must return the SQLSTATE or SQLCODE (for errors specific to HCL OneDB™) value so the calling program can determine what action to take for a runtime error.