Performing Batch Updates

The batch update feature is similar to multiple Informix SQL PREPARE statements. You can issue batch update statements as in the following example:

PREPARE stmt FROM "insert into tab values (1); 
   insert into tab values (2);
   update table tab set col = 3 where col = 2";

The batch update feature in IBM Informix JDBC Driver follows the Sun Microsystems JDBC 3.0 specification, with these exceptions:

• SQL statements
• Return value from Statement.executeBatch()

The following sections give details.

SQL Statements and Batch Updates

The following commands cannot be put into multistatement PREPARE statements:

• SELECT (except SELECT INTO TEMP) statement
• DATABASE statements
• CONNECTION statements

For more details, refer to IBM Informix: Guide to SQL Syntax.

Return Value from Statement.executeBatch() Method

The return value differs from the Sun Microsystems JDBC 3.0 specification in the following ways:

• If the IFX_BATCHUPDATE_PER_SPEC environment variable is set to 0, only the update count of the first statement executed in the batch is returned. If the IFX_BATCHUPDATE_PER_SPEC environment variable is set to 1 (the default), the return value equals the number of rows affected by all SQL statements executed by Statement.executeBatch(). For more information, see Using Informix Environment Variables.
• When errors occur in a batch update executed in a Statement object, no rows are affected by the statement; the statement is not executed. Calling BatchUpdateException.getUpdateCounts() returns 0 in this case.
• When errors occur in a batch update executed in a PreparedStatement object, rows that were successfully inserted or updated on the database server do not revert to their pre-updated state. However, the statements are not always committed; they are still subject to the underlying autocommit mode.

The BatchUpdate.java example file shows how to send batch updates to the database server.