Closing an Insert Cursor

Because HCL OneDB™ does not support Insert cursors in SPL routines, the discussion of Insert cursors in this section applies only to . In SPL routines, the CLOSE statement can reference only a Select cursor or a Function cursor that the DECLARE statement defined. (A FOREACH statement of SPL that has an INSERT statement in its statement block can declare a direct cursor that functionally resembles an Insert cursor, but the CLOSE statement cannot reference a direct cursor that FOREACH declared. HCL OneDB closes the direct cursor automatically at runtime when program control exits from the FOREACH loop where the direct cursor was defined.)

In , the CLOSE statement treats a cursor that is associated with an INSERT statement differently from one that is associated with a SELECT, EXECUTE FUNCTION, or EXECUTE PROCEDURE statement. When a cursor identifier is associated with an INSERT statement, the CLOSE statement writes any remaining buffered rows into the database. The number of rows that were successfully inserted into the database is returned in the third element of the sqlerrd array, sqlca.sqlerrd[2], in the sqlca structure. For information on how to use SQLERRD to count the total number of rows that were inserted, see Error Checking.

The SQLCODE field of the sqlca structure indicates the result of the CLOSE statement for an Insert cursor. If all buffered rows are successfully inserted, SQLCODE is set to zero. If an error is encountered, the SQLCODE field is set to a negative error message number.

When SQLCODE is zero, the row buffer space is released, and the cursor is closed; that is, you cannot execute a PUT or FLUSH statement that names the cursor until you reopen it.

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

If the insert is not successful, the number of successfully inserted rows is stored in sqlerrd. Any buffered rows that follow the last successfully inserted row are discarded. Because the insert fails, the CLOSE statement fails also, and the cursor is not closed. For example, a CLOSE statement can fail if insufficient disk space prevents some of the rows from being inserted. In this case, a second CLOSE statement can be successful because no buffered rows exist. An OPEN statement can also be successful because the OPEN statement performs an implicit close.