Error handling with optimized message transfers

When the OPTMSG feature is enabled, your application cannot perform error handling on any chained statement. If you are not sure whether a particular statement might generate an error, include error-handling code and do not enable message chaining for that statement.

When an error occurs in a chained statement, the database server stops execution. Any SQL statements that follow the error are not executed. For example, the following code fragment intends to chain five INSERT statements (this fragment assumes that the OPTMSG environment variable is set to 1:

EXEC SQL create table tab1 (col1 INTEGER);
 
/* enable message chaining */
OptMsg = 1;
             
/* these two INSERT statements execute successfully */
EXEC SQL insert into tab1 values (1);
EXEC SQL insert into tab1 values (2);

/* this INSERT statement generates an error because the data
* in the VALUES clause is not compatible with the column type */
EXEC SQL insert into tab1 values ('a');
             
/* these two INSERT statements never execute */
EXEC SQL insert into tab1 values (3);
EXEC SQL insert into tab1 values (4);
             
/* disable message chaining */
OptMsg = 0;
             
/* update one of the tab1 rows */
EXEC SQL update tab1 set col1 = 5 where col1 = 2;
if ( SQLCODE < 0 )
;

In this code fragment, flushes the message queue when it reaches the UPDATE statement, sending the five INSERT statements and the UPDATE statement to the database server for execution. Because the third INSERT statement generates an error, the database server does not execute the remaining INSERT statements or the UPDATE statement. The UPDATE statement, which is the last statement in the chained statements, returns the error from the failed INSERT statement. The tab1 table contains the rows with col1 values of 1 and 2.