Skip to content

ERR-410D2 ( 266450) Fetch out of sequence.#

Overview#

This document describes the cause of the ERR-410D2 ( 266450) Fetch out of sequence. error that can occur while processing the result of a SELECT statement that returns multiple rows using a cursor, and explains how to resolve it.

Version#

Altibase 6.3.1 or later

Symptom#

While fetching multiple rows using a cursor, the application successfully processes a certain number of rows, but the following error occurs even though additional rows remain to be fetched.

ERR-410D2 ( 266450) Fetch out of sequence.

Cursor Usage Example#

Declare cursor

Open cursor

/* Repeat FETCH until SQL_NO_DATA is returned. */
while(1)
{
    FETCH cursor;

    if (sqlca.sqlcode == SQL_SUCCESS) 
    {
        ...
    }
    else if (sqlca.sqlcode == SQL_NO_DATA)
    {
        break;
    }
    else 
    {   
        /* ERR-410D2 occurs even though rows remain to be fetched. */
        printf("Error : [%d] %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
        break;
    }  
}

Error Example#

$ ./cursor1

[Success declare cursor]

[Success open cursor]

[Fetch Cursor]                                                    
------------------------------------------------------------------
DNO     DNAME                          DEP_LOCATION       MGR_NO 
------------------------------------------------------------------
1       BUSINESS DEPT                  Seoul              100
2       BUSINESS DEPT                  Seoul              100
... omitted ...
583     BUSINESS DEPT                  Seoul              100
584     BUSINESS DEPT                  Seoul              100

Error : [-266450][HY000] Fetch out of sequence.
------------------------------------------------------------------
[Close Cursor]                                                    
------------------------------------------------------------------
Success close cursor

Cause#

This error occurs when, in non-autocommit mode, COMMIT or ROLLBACK is executed on the same connection while a cursor is open and rows are being fetched, and the application then attempts to FETCH from the cursor that is no longer valid.

If the application executes COMMIT or a full ROLLBACK while a regular cursor is open, the cursor is closed. If the application continues to perform FETCH operations on the closed cursor, ERR-410D2 ( 266450) Fetch out of sequence. occurs.

The cursor is not closed in the following cases.

  • A partial rollback that rolls back only to a savepoint, such as ROLLBACK TO SAVEPOINT
  • COMMIT or ROLLBACK executed inside a stored procedure

The error may not occur immediately after the transaction ends, but only after a certain number of rows have been processed.

To improve FETCH performance, the client retrieves multiple rows from the server at once and stores them in the client cache. Even after the cursor has been closed, the application can continue to retrieve the rows remaining in the client cache. After all rows in the client cache have been processed, the client performs another FETCH to retrieve the next rows from the server. Because the cursor has already been closed, ERR-410D2 occurs. Therefore, when the error occurs depends on the number of rows retrieved at a time (the prefetch count).

Resolution#

To prevent the cursor from being closed when a transaction ends, do not execute COMMIT or ROLLBACK on the same connection inside the FETCH loop. Apply one of the following methods depending on the application's processing requirements.

  • Use separate connections for cursor-based queries and data modification operations.
  • Limit the number of rows processed at a time and reopen the cursor for each batch.
  • Use a WITH HOLD cursor if the cursor must remain open after the transaction ends.

Method 1: Use Separate Connections for Queries and Data Modifications#

Use two connections in the application to separate cursor-based query operations from data modification operations.

For example:

  • CONN1: Declares, opens, and fetches from the cursor
  • CONN2: Executes INSERT, UPDATE, DELETE, and COMMIT or ROLLBACK

Set CONN2, which is used for data modification operations, to non-autocommit mode. With separate connections, COMMIT or ROLLBACK executed on CONN2 does not affect the cursor being used on CONN1.

The following example illustrates this approach.

/* Connection for queries */
EXEC SQL AT conn1 CONNECT; 
     
/* Connection for data modifications */
EXEC SQL AT conn2 CONNECT;     
EXEC SQL AT conn2 AUTOCOMMIT OFF; 
     
/* Declare and open the cursor on conn1 */
EXEC SQL AT conn1 DECLARE cursor; 
EXEC SQL AT conn1 OPEN cursor; 

while (1)
{
    /* Fetch on conn1 */
    EXEC SQL AT conn1 FETCH cursor; 

    if (sqlca.sqlcode == SQL_SUCCESS) 
    {           
        /* Execute a data modification statement on conn2 */ 
        EXEC SQL AT conn2 INSERT or UPDATE or DELETE ;

        /* check sqlca.sqlcode */
        if (sqlca.sqlcode == SQL_SUCCESS) 
        {
            /* Data modification succeeded */
            EXEC SQL AT conn2 COMMIT;
        }
        else 
        {
            /* Data modification failed */
            EXEC SQL AT conn2 ROLLBACK;
        }                                           
    }  
    else if (sqlca.sqlcode == SQL_NO_DATA) 
    {
        ...
    }
    else 
    {
        ...
    }             
}

Method 2: Limit the Number of Rows Processed and Reopen the Cursor#

Limit the number of rows processed at a time. After all rows in the current range have been fetched, close the cursor and end the transaction. Then reopen the cursor to process the next range of rows.

To prevent rows from being processed more than once or skipped during repeated queries, define each query range using a column with a stable and deterministic order, such as a primary key whose value does not change during processing.

For example, if ID is the primary key, retrieve a fixed number of rows whose ID values are greater than the last processed ID.

SELECT ...
  FROM ...
 WHERE ID > :LAST_ID
 ORDER BY ID
 LIMIT :BATCH_SIZE;

After processing a range of rows, close the cursor and end the transaction. Then reopen the cursor to process the next range.

Method 3: Use a WITH HOLD Cursor#

If the cursor must remain open after the transaction ends, Precompiler (APRE) applications can use a WITH HOLD cursor. A WITH HOLD cursor remains open after the transaction ends and can be used only in non-autocommit mode.

/* Set this before declaring and opening the cursor. */
EXEC SQL AUTOCOMMIT OFF;

EXEC SQL DECLARE cursor_name
    CURSOR WITH HOLD FOR
    SELECT ...;

Caution A WITH HOLD cursor can also be closed when ROLLBACK is executed. For example, if data modified in a transaction before the cursor was opened is queried or modified again after the cursor is opened and the transaction is then rolled back, the cursor is closed and ERR-410D2 occurs on the next FETCH. If ROLLBACK is required inside the FETCH loop, use separate connections as described in Method 1.

CLI and JDBC provide their own settings for keeping a cursor open across transaction boundaries. In both interfaces, the cursor is closed on commit by default, so this setting must be configured explicitly to keep the cursor open.

Interface Setting Default Value to keep the cursor open
CLI SQL_ATTR_CURSOR_HOLD of SQLSetStmtAttr() SQL_CURSOR_HOLD_OFF SQL_CURSOR_HOLD_ON
JDBC ResultSet Holdability CLOSE_CURSORS_AT_COMMIT HOLD_CURSORS_OVER_COMMIT

For details, refer to the manuals listed below.

References#

Error Messages by Version#

The error message displayed for this condition varies depending on the Altibase version.

Version Error message
Altibase 6.3.1 or later ERR-410D2 ( 266450) Fetch out of sequence.
Altibase 5.3.3 ~ 6.1.1 Not found data
Altibase 4.3.9 ERR-4103C ( 266300) Request of fetching data to an unprepared SQL statement

The configuration used to keep a cursor open after a transaction ends depends on the interface. For details, refer to the following manuals.