Cursors versus Select Loops
A loop is an iterative set of fetches. Thus, a cursor loop is a set of fetches using cursors. Select loops are a set of fetches without a cursor defined. The ODBC uses select loops by default. This is true whether or not an ODBC DSN definition is specified.
Declaration of a cursor name is the same as a cursor loop in this discussion.
Select loops fetch multiple sets of rows from the DBMS. This is sometimes referred to as block fetching. A single fetch may appear to return only one row, but often the ODBC driver has already fetched many more rows that are cached in the driver.
Cursor loops must be specified if the cursor is scrollable or updatable.
Note: Cursors loops may need to be specified for Windows applications such as Microsoft Access or Microsoft ADO. If you are see an error message such as "API function cannot be called in the current state", and are satisfied that your application is coded correctly, try using cursor loops.
Cursor loops may offer better performance for Windows applications, because the ODBC driver returns information that it supports unlimited active statements. This signifies, for example, that ADO applications can re-use existing connections for internal procedures.
Outside of Windows applications, the performance of cursor loops is often comparable to select loops, because the ODBC driver pre-fetches rows in blocks of 100 when cursors are used. The term pre-fetch means that multiple rows are fetched and cached in the ODBC driver before they are presented to the application.
If a cursor is declared as updatable, pre-fetching does not occur in order to preserve the current position for the update. Thus, updatable cursors may be slower that read-only cursors or select loops.
Only one select loop can be active at a time. As a result, select loops cannot be nested. For example, in ADO, multiple recordset objects cannot be retrieved within [Connection].BeginTrans and [Connection].CommitTrans methods. In direct ODBC code, SQLFreeStmt() must be called with the argument SQL_CLOSE before executing another select loop. By contrast, cursors place no limits on the number of active result sets. Cursor loops can be nested.
See the SQL Reference Guide for more information on cursors versus select loops.