Error Checking FLUSH Statements

The SQL Communications Area (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 described 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, any 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. Client-server communication protocols of HCL OneDB™, such as SQLI and DRDA®, support SQLSTATE code values. For a list of these codes, and for information about how to get the message text, see Using the SQLSTATE Error Status Code.

To count the number of rows actually inserted into the database as well as the number not yet inserted

  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.