Cursor principles

To use a cursor in ADO.NET, ODBC, JDBC, or Open Client, follow these general steps:

1. Prepare and execute a statement.

   Execute a statement using the usual method for the interface. You can prepare and then execute the statement, or you can execute the statement directly.

   With ADO.NET, only the SACommand.ExecuteReader method returns a cursor. It provides a read-only, forward-only cursor.

2. Test to see if the statement returns a result set.

   A cursor is implicitly opened when a statement that creates a result set is executed. When the cursor is opened, it is positioned before the first row of the result set.

3. Fetch results.

   Although simple fetch operations move the cursor to the next row in the result set, SAP Sybase IQ permits more complicated movement around the result set.

4. Close the cursor.

   When you have finished with the cursor, close it to free associated resources.

5. Free the statement.

   If you used a prepared statement, free it to reclaim memory.

The approach for using a cursor in embedded SQL differs from the approach used in other interfaces. Follow these general steps to use a cursor in embedded SQL:

1. Prepare a statement.

   Cursors generally use a statement handle rather than a string. You need to prepare a statement to have a handle available.

2. Declare the cursor.

   Each cursor refers to a single SELECT or CALL statement. When you declare a cursor, you state the name of the cursor and the statement it refers to.

3. Open the cursor.

   For a CALL statement, opening the cursor executes the procedure up to the point where the first row is about to be obtained.

4. Fetch results.

   Although simple fetch operations move the cursor to the next row in the result set, SAP Sybase IQ permits more complicated movement around the result set. How you declare the cursor determines which fetch operations are available to you.

5. Close the cursor.

   When you have finished with the cursor, close it. This frees any resources associated with the cursor.

6. Drop the statement.

   To free the memory associated with the statement, you must drop the statement.

• Cursor positioning
  When a cursor is opened, it is positioned before the first row. You can move the cursor position to an absolute position from the start or the end of the query results, or to a position relative to the current cursor position. The specifics of how you change cursor position, and what operations are possible, are governed by the programming interface.
• Cursor behavior when opening cursors
  You can configure the following aspects of cursor behavior when you open the cursor:
• Row fetching through a cursor
  The simplest way of processing the result set of a query using a cursor is to loop through all the rows of the result set until there are no more rows. You can accomplish this task by performing these steps:
• Multiple-row fetching
  Multiple-row fetching should not be confused with prefetching rows. Multiple row fetching is performed by the application, while prefetching is transparent to the application, and provides a similar performance gain. Fetching multiple rows at a time can improve performance.
• Scrollable cursors
  ODBC and embedded SQL provide methods for using scrollable cursors and dynamic scrollable cursors. These methods allow you to move several rows forward at a time, or to move backward through the result set.
• Cursors used to modify rows
  Cursors can do more than just read result sets from a query. You can also modify data in the database while processing a cursor. These operations are commonly called positioned insert, update, and delete operations, or PUT operations if the action is an insert.
• Updatable statements
  This section describes how clauses in the SELECT statement affect updatable statements and cursors.
• Cursor operations that are canceled
  You can cancel a request through an interface function. If you cancel a request that is carrying out a cursor operation, the position of the cursor is indeterminate. After canceling the request, you must locate the cursor by its absolute position, or close it.