Using cursors

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

Supported cursor types

Not all the types of cursor that SQL 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.

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, 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 by using ct_cursor with CS_CURSOR_ROWS as the type parameter.

    Storing prefetched rows at the client side reduces 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, use ct_cursor with CS_CURSOR_OPEN as the type parameter.

  4. To fetch each row in to the application, 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 by using ct_cursor with CS_CURSOR_DEALLOC. You can also use CS_CURSOR_CLOSE with the additional parameter CS_DEALLOC to perform these operations in a single step.

Modifying rows through a cursor

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0