Multiple exceptions

The database server can generate multiple exceptions for a single SQL statement. A significant advantage of the GET DIAGNOSTICS statement is its ability to report multiple exception conditions.

To find out how many exceptions the database server has reported for an SQL statement, retrieve the value of the NUMBER field from the statement information of the diagnostics area. The following GET DIAGNOSTICS statement retrieves the number of exceptions that the database server generated and stores the number in the :exception_num host variable. 
EXEC SQL get diagnostics :exception_num = NUMBER;
When you know the number of exceptions that occurred, you can initiate a loop to report each of them. Execute GET DIAGNOSTICS within this loop and use the number of exceptions to control the loop. The following code illustrates one way to retrieve and report multiple exception conditions after an SQL statement. 
EXEC SQL get diagnostics :exception_count = NUMBER, 
   :overflow = MORE;
printf("NUMBER: %d\n", exception_count);
printf("MORE : %s\n", overflow);
for (i = 1; i <= exception_count; i++)
   {
   EXEC SQL get diagnostics  exception :i 
      :sqlstate = RETURNED_SQLSTATE,
      :class = CLASS_ORIGIN, :subclass = SUBCLASS_ORIGIN,
      :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;

      printf("SQLSTATE: %s\n",sqlstate);
      printf("CLASS ORIGIN: %s\n",class);
      printf("SUBCLASS ORIGIN: %s\n",subclass);
      message[messlen] =’\0’; /* terminate the string. */
      printf("TEXT: %s\n",message);
      printf("MESSAGE LENGTH: %d\n",messlen);
   }

Do not confuse the RETURNED_SQLSTATE value with the SQLSTATE global variable. The SQLSTATE variable provides a general status value for the most-recently executed SQL statement. The RETURNED_SQLSTATE value is associated with one particular exception that the database server has encountered. For the first exception, SQLSTATE and RETURNED_SQLSTATE have the same value. However, for multiple exceptions, you must access RETURNED_SQLSTATE for each exception.

To define a host variable in your application that receives the RETURNED_SQLSTATE value, you must define it as a character array with a length of six (five for the field plus one for the null terminator). You can assign this variable whatever name you want.

The following statements define such a host variable and assign it the name sql_state: 
EXEC SQL BEGIN DECLARE SECTION;
    char sql_state[6];
EXEC SQL END DECLARE SECTION;

A database system that is compliant with X/Open standards must report any X/Open exceptions before it reports any errors or warnings that are specific to HCL OneDB™. Beyond this, however, the database server does not report the exceptions in any particular order. The getdiag sample program (Guide to the getdiag.ec file) includes the disp_sqlstate_err() function to display multiple exceptions.