ASA Programming Guide
  Using SQL in Applications
    Introduction to cursors

Steps in using cursors

Using a cursor in embedded SQL is different than using a cursor in other interfaces.

To use a cursor (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.

   For information on preparing a statement, see Preparing statements.

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.

   For more information, see DECLARE CURSOR statement [ESQL] [SP].

3. Open the cursor.

   For more information, see OPEN statement [ESQL] [SP].

   In the case of a CALL statement, opening the cursor executes the query 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, Adaptive Server Anywhere permits more complicated movement around the result set. How you declare the cursor determines which fetch operations are available to you.

   For more information, see FETCH statement [ESQL] [SP], and Fetching data.

5. Close the cursor.

   When you have finished with the cursor, close it. This frees any locks held on the underlying data.

   For more information, see CLOSE statement [ESQL] [SP].

6. Drop the statement.

   To free the memory associated with the cursor and its associated statement, you must free the statement.

   For more information, see DROP STATEMENT statement [ESQL].

To use a cursor (ODBC, ADO.NET, JDBC, Open Client)

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 AsaCommand.ExecuteReader command 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, Adaptive Server Anywhere 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.

Prefetching rows 

In some cases the interface library may carry out performance optimizations under the covers (such as prefetching results), so these steps in the client application may not correspond exactly to software operations.