Informix Guide to SQL: Syntax SQL Statements

Use the FLUSH statement to force rows that a PUT statement buffered to be written to the database.

Use this statement with ESQL/C.

The PUT statement adds a row to a buffer, and the buffer is written to the database when it is full. Use the FLUSH statement to force the insertion when the buffer is not full.

If the program terminates without closing the cursor, the buffer is left unflushed. Rows placed into the buffer since the last flush are lost. Do not expect the end of the program to close the cursor and flush the buffer automatically.

The following example shows a FLUSH statement:

FLUSH  icurs 

The sqlca structure contains information on the success of each FLUSH statement and the number of rows that are inserted successfully. The result of each FLUSH statement is contained in the fields of the sqlca: sqlca.sqlcode, SQLCODE and sqlca.sqlerrd[2].

When you use data buffering with an insert cursor, you do not discover errors until the buffer is flushed. For example, an input value that is incompatible with the data type of the column for which it is intended is discovered only when the buffer is flushed. When an error is discovered, rows in the buffer that are located after the error are not inserted; they are lost from memory.

The SQLCODE field is set either to an error code or to zero (0) if no error occurs. The third element of the sqlerrd array is set to the number of rows that are successfully inserted into the database:

• If a block of rows is successfully inserted into the database, SQLCODE is set to zero (0) and sqlerrd to the count of rows.
• If an error occurs while the FLUSH statement is inserting a block of rows, SQLCODE shows which error, and sqlerrd contains the number of rows that were successfully inserted. (Uninserted rows are discarded from the buffer.)

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value also exists. For information about how to get the message text, check the GET DIAGNOSTICS statement.

To count the number of rows actually inserted into the database as well as the number not yet inserted, perform the following steps:

1. Prepare two integer variables, for example, total and pending.
2. When the cursor opens, set both variables to 0.
3. Each time a PUT statement executes, increment both total and pending.
4. Whenever a FLUSH statement executes or the cursor is closed, subtract the third field of the SQLERRD array from pending.

Related statements: CLOSE, DECLARE, OPEN, and PREPARE

For a task-oriented discussion of FLUSH, see the Informix Guide to SQL: Tutorial.

For information about the sqlca structure, see the Informix ESQL/C Programmer's Manual.