ASA Programming Guide
  The Open Client Interface
    Using SQL in Open Client applications

Using cursors

The ct_cursor function is used to manage cursors. This function takes a type parameter which describes the action you are taking.

Supported cursor types 

Not all the types of cursor that Adaptive Server Anywhere supports are available through the Open Client interface. You cannot use scroll cursors, dynamic scroll cursors, or insensitive cursors through Open Client.

Uniqueness and updatability are two properties of cursors. Cursors can be unique (each row carries primary key or uniqueness information, regardless of whether it is used by the application) or not. Cursors can be read only or updatable. If a cursor is updatable and not unique, performance may suffer, as no prefetching of rows is done in this case, regardless of the CS_CURSOR_ROWS setting (see below).

The steps in using cursors 

In contrast to some other interfaces, such as Embedded SQL, Open Client associates a cursor with a SQL statement expressed as a string. Embedded SQL first prepares a statement and then the cursor is declared using the statement handle.

To use cursors in Open Client

1. To declare a cursor in Open Client, you use ct_cursor with CS_CURSOR_DECLARE as the type parameter.

2. After declaring a cursor, you can control how many rows are prefetched to the client side each time a row is fetched from the server using ct_cursor with CS_CURSOR_ROWS as the type parameter.

   Storing prefetched rows at the client side cuts down the number of calls to the server and this improves overall throughput as well as turnaround time. Prefetched rows are not immediately passed on to the application; they are stored in a buffer at the client side ready for use.

   The setting of the PREFETCH database option controls prefetching of rows for other interfaces. It is ignored by Open Client connections. The CS_CURSOR_ROWS setting is ignored for non-unique, updatable cursors.

3. To open a cursor in Open Client, you use ct_cursor with CS_CURSOR_OPEN as the type parameter.

4. To fetch each row in to the application, you use ct_fetch.

5. To close a cursor, you use ct_cursor with CS_CURSOR_CLOSE.

6. In Open Client, you also need to deallocate the resources associated with a cursor. You do this using ct_cursor with CS_CURSOR_DEALLOC. You can also use CS_CURSOR_CLOSE with the additional parameter CS_DEALLOC to carry out these operations in a single step.

Modifying rows through a cursor