Cursors in Transactions

Return the results of a SELECT in the form of a data type called a cursor.

A cursor is similar to a table, but one row is identified as the present, or current row. Various commands allow you to navigate through the rows of a cursor. For example, the FETCH command retrieves a row from the cursor and identifies it as the current row. You can step through all the rows in a cursor by calling this command repeatedly.

You may find cursors useful when you write procedures, or applications that access a database using Embedded SQL. They are also used by many front-end query tools. They are unavailable when you use Interactive SQL.

SAP Sybase IQ cursors are updatable, which allows you to modify the underlying data in the database while processing a cursor.

The rows in a cursor, like those in a table, have no order associated with them. The FETCH command steps through the rows, but the order may appear random and inconsistent. Impose an order by appending an ORDER BY phrase to your SELECT statement.

The sp_iqcursorinfo stored procedure displays information about open cursors on the server.

• Cursors and Versioning
  When you use cursors, SAP Sybase IQ must be able to manage multiple versions within a single transaction.
• Cursor Sensitivity
  A cursor is said to be sensitive if its membership—the data rows it returns—can vary from the time it is opened until the time it is closed. An insensitive cursor has its membership fixed when it is opened.
• Cursor Scrolling
  SAP Sybase IQ cursors can be either scrolling or non-scrolling.
• Hold Cursors
  Specifying the HOLD option when you open a cursor keeps the cursor open past the end of the transaction, if the transaction ends in a COMMIT.
• Positioned Operations
  In a positioned operation, the current location of the cursor determines where a read or write operation begins.
• Message Logging for Cursors
  By default, cursor operations are not logged in the SAP Sybase IQ message file.