INFORMIX-ESQL/C Programmer's Manual Working with the Database Server

ESQL/C provides a feature called optimized message transfers, which allow you to minimize message transfers with the database server for most ESQL/C statements. ESQL/C accomplishes optimized message transfers by chaining messages together and even eliminating some small message packets. When the optimized message transfer feature is enabled, ESQL/C expects that SQL statements will succeed. Consequently, ESQL/C chains, and in some cases eliminates, confirmation messages from the database server.

ESQL/C does not chain the following SQL statements even when you enable optimized message transfers:

• COMMIT WORK
• DESCRIBE
• EXECUTE
• FETCH
• FLUSH
• PREPARE
• PUT
• ROLLBACK WORK
• SELECT INTO (singleton SELECT)

When ESQL/C reaches one of the preceding statements, it flushes the message out to the database server. ESQL/C then continues message chaining for subsequent SQL statements. Only SQL statements that require network traffic cause ESQL/C to flush the message queue.

SQL statements that do not require network traffic, such as the DECLARE statement, do not cause ESQL/C to send the message queue to the database server.

To enable optimized message transfers, or message chaining, you must set the following variables in the client environment:

1. Set the OPTMSG environment variable at run time to enable optimized message transfers for all qualifying SQL statements.
2. Set the OptMsg global variable within an ESQL/C application to control which SQL statements use message chaining.

The OPTMSG environment variable enables the optimized message transfers for all SQL statements in the application. You can assign the following values to the OPTMSG environment variable:

The default value of the OPTOMSG environment variable is 0 (zero). Setting OPTMSG to 0 (zero) explicitly disables message chaining. You might want to disable optimized message transfers for statements that require immediate replies, or for debugging purposes.

To enable optimized message transfers, you must set OPTMSG before you start the ESQL/C application.

On UNIX operating systems, you can set OPTMSG within the application with the putenv() system call (as long as your system supports the putenv() function). The following call to putenv(), for example, enables optimized message transfers:

putenv("OPTMSG=1");

In Windows environments, you can set OPTMSG within the application with the ifx_putenv() function. The following call to ifx_putenv(), for example, enables optimized message transfers:

ifx_putenv("OPTMSG=1");

When you set OPTMSG within an application, you can activate or deactivate optimized message transfers for each connection or within each thread. To enable optimized message transfers, you must set OPTMSG before you establish a connection.

The OptMsg global variable is defined in the ESQL/C sqlhdr.h header file.

After you set the OPTMSG environment variable to 1, you must set the OptMsg global variable to specify whether message chaining takes effect for each subsequent SQL statement. You can assign the following values to OptMsg:

With the OPTMSG environment variable set to 1, you must still set the OptMsg global variable to 1 to enable the message chaining. If you omit the following statement from your program, ESQL/C does not perform message chaining:

OptMsg = 1;

When you have set the OPTMSG environment variable to 1, you might want to disable message chaining for the following reasons:

• Some SQL statements require immediate replies.

See Restrictions on Optimized Message Transfers for more information on these SQL statements. Re-enable the OPTMSG feature once the restricted SQL statement completes.

• For debugging purposes

You can disable the OPTMSG feature when you are trying to determine how each SQL statement responds.

• Before the last SQL statement in the program to ensure that the database server processes all messages before the application exits. If OPTMSG is enabled, the message is queued up for the database server but it is not sent for processing.

To avoid unintended chaining, reset the OptMsg global variable immediately after the SQL statement that requires it. The following code fragment enables message chaining for the DELETE statement:

OptMsg = 1;

EXEC SQL delete from customer;

OptMsg = 0;

EXEC SQL create index ix1 on customer (zipcode);

This example enables message chaining because the execution of the DELETE statement is not likely to fail. Therefore, it can be safely chained to the next SQL statement. ESQL/C delays sending the message for the DELETE statement. The example disables message chaining after the DELETE statement so that ESQL/C flushes all messages that have been queued up when the next SQL statement executes. By disabling the message chaining after the DELETE, the code fragment avoids unintended message chaining. When unintended chaining occurs, it can be difficult to determine which of the chained statements has failed.

At the CREATE INDEX statement, ESQL/C sends both the DELETE and the CREATE INDEX statements to the database server.

When the OPTMSG feature is enabled, your ESQL/C 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.

Once 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, ESQL/C 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 executes neither the remaining INSERT statements nor 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.