Check after each SQL statement
To check for an exception, you can include code to explicitly test the value of SQLSTATE (or SQLCODE).
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.
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);
}
- 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.