Error Checking

The sqlca structure contains information on the success of each PUT statement as well as information that lets you count the rows that were inserted. The result of each PUT statement is contained in the following fields of the sqlca: sqlca.sqlcode, SQLCODE, and sqlca.sqlerrd[2].

Data buffering with an Insert cursor means that errors are not discovered 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, buffered rows that were not inserted before the error are not inserted; they are lost from memory.

The SQLCODE field is set to 0 if no error occurs; otherwise, it is set to an error code. The third element of the sqlerrd array is set to the number of rows that were successfully inserted into the database:
  • If any row is put into the insert buffer, but not written to the database, SQLCODE and sqlerrd are set to 0 (SQLCODE because no error occurred, and sqlerrd because no rows were inserted).
  • If a block of buffered rows is written to the database during the execution of a PUT statement, SQLCODE is set to 0 and sqlerrd is set to the number of rows that was successfully inserted into the database.
  • If an error occurs while the buffered rows are written to the database, SQLCODE indicates the error, and sqlerrd contains the number of successfully inserted rows. (The uninserted rows are discarded from the buffer.)
Tip: When you encounter an SQLCODE error, a SQLSTATE error value also exists. See the GET DIAGNOSTICS statement for details of how to obtain the message text.

To count the number of pending and inserted rows in the database

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

At any time, (total - pending) represents the number of rows actually inserted. If no statements fail, pending contains zero after the cursor is closed. If an error occurs during a PUT, FLUSH, or CLOSE statement, the value that remains in pending is the number of uninserted (discarded) rows.

Related Statements

Related statements: ALLOCATE DESCRIPTOR statement, CLOSE statement, DEALLOCATE DESCRIPTOR statement, FLUSH statement, DECLARE statement, GET DESCRIPTOR statement, OPEN statement, PREPARE statement, and SET DESCRIPTOR statement

For a task-oriented discussion of the PUT statement, see the HCL OneDB™ Guide to SQL: Tutorial.

For more information about error checking, the system-descriptor area, and the sqlda structure, see the HCL OneDB ESQL/C Programmer's Manual.