Overview

This document describes an error that occurs during the record fetch process by using a cursor to process a select statement that returns multiple records.

Version

• Altibase 5.3.3
• Altibase 5.5.1
• Altibase 6.1.1

Symptom

To process a query statement that returns multiple records, a cursor (CURSOR) must be used in the following process.

1. DECLARE CURSOR
2. OPEN CURSOR
3. FETCH CURSOR
4. CLOSE/RELEASE CURSOR

The cursor was used as above, but not found data error occurs at some point, even though the record to FETCH is still remaining during the "3. Cursor FETCH" state.

Below is an example where an error occurs when using the cursor and the result of the error.

Cause

When COMMIT/ROLLBACK is performed in the cursor (CURSOR) OPEN state

Altibase follows the ANSI standard and does not support the fetch across commit method. Therefore, if COMMIT or ROLLBACK is executed after opening the cursor, the cursor is forcibly closed according to the ANSI standard.

For this reason, if an application executes COMMIT or ROLLBACK after opening the cursor, an error may occur.

The reason an error occurs while performing FETCH to some extent is that the first large amount of records is stored in the communication buffer during FETCH. An error occurs when fetching all the records in the communication buffer and fetching the next certain amount of records into the communication buffer.

The following is an example of creating an application that performed COMMIT or ROLLBACK in the cursor OPEN state.

Solution

When COMMIT/ROLLBACK is performed in the cursor (CURSOR) OPEN state

Here are two methods to solve this error.

• To separate fetch and change DML operations using multiple connections
• To open the cursor repeatedly after declaring the cursor only enough to be contained in the communication buffer

All of the above methods require application changes.

1. Separation of fetch session and modified DML session

By using multiple connections within one application, COMMIT or ROLLBACK does not affect the cursor.

• Create a session that uses a cursor and a session that executes modified DML statements. It is described by defining it as CONN1 and CONN2, respectively.

• Set the session (CONN2) that executes the modified DML statement to non-autocommit mode.

• Whenever cursor FETCH is executed in the session using the cursor (CONN1), change DML is executed in CONN2, and COMMIT or ROLLBACK is executed.

The following is an example of creating an application that reflects this action.

2. Repetitively open the cursor after declaring the cursor only enough to be contained in the communication buffer

After calculating the number of records enough to put in the communication buffer with one FETCH, declare the cursor using the LIMIT clause.

The communication buffer size of Altibase 5 and above is 64K. Since the number of records in the communication buffer depends on the record size, the last value of the LIMIT clause varies depending on the operating environment.

Specify the number of records to be stored in the communication buffer in the LIMIT clause, and open the cursor again and use it while changing the start value of the LIMIT clause before opening the cursor.

The following is an example of creating an application that reflects this action.

Reference

Differences by version

Depending on the Altibase version, the error messages that occur in the same situation may be different.

The difference in error messages that occurs when COMMIT/ROLLBACK is executed among FETCHs in a non-autocommit environment is as follows.