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 SAVEPOINTCOMMITorROLLBACKexecuted 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 HOLDcursor 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 HOLDcursor can also be closed whenROLLBACKis 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 andERR-410D2occurs on the nextFETCH. IfROLLBACKis required inside theFETCHloop, 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 |
Related Manuals#
The configuration used to keep a cursor open after a transaction ends depends on the interface. For details, refer to the following manuals.
- Error Message Reference > 7. MM Error Code
- Precompiler User's Manual > 8. Using Cursor > Cursor-Related SQL Statements > DECLARE CURSOR
- CLI User's Manual > 2. Altibase CLI Functions > SQLSetStmtAttr
- JDBC User's Manual > 3. Advanced Functions > How to Use ResultSet > Holdability
- JDBC User's Manual > 3. Advanced Functions > How to Use ResultSet > Fetch Size