Skip to end of metadata
Go to start of metadata

 

 

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 version 6.3.1 or later

 

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

Although the cursor is used as above, the 3. FETCH process proceeds to some extent in the CURSOR FETCH stage, and the 'ERR-410D2 (266450) Fetch out of sequence' error occurs even though there are still records to be fetched at some point.

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

Example of using cursor-SESC
Example of error occurrence result

 

Cause


When COMMIT/ROLLBACK is performed in the OPEN CURSOR state


Altibase complies with the ANSI standard and is configured not to support the fetch across commit method by default. Therefore, if COMMIT or ROLLBACK is performed after opening the cursor, the cursor is forcibly closed according to the ANSI standard.

Icon

This is a method that performed COMMIT while fetching the unit record after opening the cursor, which is not recommended by the ANSI standard.

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 may cause an error by performing COMMIT or ROLLBACK in the cursor OPEN state.

 

Solution


When COMMIT/ROLLBACK is performed in the OPEN CURSOR state


Here are three solutions to deal with this error.

 

1. Separation of fetch session and change 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.

  • 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 session (CONN2) that executes the modified DML statement is set to non-autocommit mode.

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

2. Declare a cursor only enough to be stored in the communication buffer, and then repetitively open the cursor

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 32K. 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.

3. fetch across commit 

Although not recommended by the ANSI standard, it is a function added from version 6.3.1 for the convenience of users familiar with other DBMSs.

In the case of Precompiler (APRE), when declaring a cursor, declare and use a WITH HOLD cursor.

Example of declaring a cursor in APRE

 

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.

VersionError codeError messageReference page
Altibase 4.3.9ERR-4103CRequest of fetching data to an unprepared SQL statement.http://aid.altibase.com/x/6YKZ
Altibase 5.3.3 ~ 6.1.1100Not found datahttp://aid.altibase.com/x/7YKZ
Altibase 6.3.1 or laterERR-410D2Fetch out of sequence. 

 

Reference manual


This section introduces the manuals related to fetch across commit provided from Altibase 6.3.1.

  • Precompiler User's Manual: 8. Cursor processing SQL statement -> Cursor-related embedded SQL statement -> DECLARE CURSOR

  • CLI User's Manual: 2. ALTIBASE HDB CLI Function -> SQLSetStmtAttr

  • JDBC User's Manual: 3. Advanced Features -> Using ResultSet -> Holdability

The manual can be downloaded from the pages below.

 

 

 

 

 

 

 

  • No labels