User's Guide
PART 1. Working with Databases
CHAPTER 9. Using SQL in Applications
When you execute a query in an application, the result set consists of a number of rows. In general, you do not know how many rows you are going to receive before the query is executed. Cursors provide a way of handling query result sets in applications.
The way you actually use cursors, and the kinds of cursor available to you, depends on the programming interface you are using. JDBC 1.0 does not provide more than rudimentary handling of result sets, while ODBC and Embedded SQL have many different kinds of cursor. Open Client cursors are limited to moving forward through a result set.
For information on the kinds of cursor available through different programming interfaces, see Availability of cursors.
What is a cursor?A cursor is a symbolic name that is associated with a SELECT statement or stored procedure that returns a result set. It consists of the following parts:
Cursor result set The set of rows resulting from the execution of a query that is associated with the cursor
Cursor position A pointer to one row within the cursor result set
You can think of a cursor as a handle on the result set of a SELECT statement. It enables you to examine and possibly manipulate one row at a time. In Adaptive Server Anywhere, cursors support forward and backward movement through the query results.
What you can do with cursorsWith cursors, you can do the following:
Loop over the results of a query.
Carry out inserts, updates, and deletes at any point within a result set.
Some programming interfaces allow you to use special features to tune the way in which result sets are returned to your application. This can provide substantial performance benefits for your application
Steps in using a cursorThe steps in using a cursor in Embedded SQL are different from in other interfaces.
To use a cursor in ODBC or Open Client: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.
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.
Fetch results A simple fetch operations moves the cursor to the next row in the result set. Adaptive Server Anywhere permits more complicated movement around the result set using fetches. Which of these you can use depends on the programming interface you are using: not all interfaces support advanced cursor-handling operations.
Close the cursor When you have finished with the cursor, you close it to free resources associated with it.
Free the statement If you used a prepared statement, free it to reclaim memory.
To use a cursor in Embedded SQL:Prepare a statement Cursors generally use a statement handle rather than a string. You need to prepare a statement in order to have a handle available.
Declare the cursor Each cursor refers to a single SELECT statement. When you declare a cursor, you state the name of the cursor and the statement it refers to.
Open the cursor Opening the cursor executes the query up to the point where the first row is about to be obtained.
Fetch results A simple fetch operations moves the cursor to the next row in the result set. Adaptive Server Anywhere permits more complicated movement around the result set using fetches. Which of these you can use depends on the programming interface you are using: not all interfaces support advanced cursor-handling operations.
Close the cursor When you have finished with the cursor, you close it.
Reclaim memory To free the memory associated with the cursor and its associated statement you need to free the statement.
In some cases, the interface library may carry out performance optimizations under the covers (such as prefetching results) so that these steps in the client application may not correspond exactly to software operations: