User's Guide
PART 1. Working with Databases
CHAPTER 9. Using SQL in Applications
This section describes how to carry out different kinds of operation using cursors.
Configuring cursors on openingYou can configure the following aspects of cursor behavior when you open the cursor:
Isolation level You can set the isolation level of operations on a cursor explicitly, to be different from the current isolation level of the transaction.
Holding Unless you explicitly require that a cursor be kept open, cursors are closed at the end of a transaction. Opening a cursor with hold allows you to keep it open until the end of a connection.
Fetching rows through a cursorThe 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. The steps in this process are as follows:
Declare and open the cursor (Embedded SQL), or execute a statement that returns a result set.
Fetch the next row until you get a Row Not Found error.
Close the cursor.
The way these operations are carried out depends on the interface you are using. For example:
In ODBC SQLFetch advances the cursor to the next row and returns the data.
For information on using cursors in ODBC, see Working with result sets in ODBC.
In JDBC, the next method of the ResultSet object advances the cursor and returns the data.
For information on using the ResultSet object in JDBC, see Queries using JDBC.
In Embedded SQL, the FETCH statement carries out the same operation.
For information on using cursors in Embedded SQL, see Cursors in Embedded SQL.
In Open Client, ct_fetch advances the cursor to the next row and returns the data.
For information on using cursors in Open Client applications, see Using cursors.
Fetching multiple rowsThis section discusses how you can use fetching of multiple rows at a time: a technique that can improve performance.
Some interfaces provide methods for fetching more than one row at a time into the next several fields in an array. In general, the fewer separate fetch operations you can execute, the fewer individual requests the server must respond to, and the better the performance. Multiple-row fetches are also sometimes called wide fetches. Cursors that use multiple-row fetches are sometimes called block cursors or fat cursors.
Multiple-row fetching should not be confused with prefetching rows. Multiple row fetches read the next several rows of the cursor into the application at one time.
In ODBC, if you are using a block cursor, several rows are fetched by default whenever you do a SQLFetch.
In ODBC, SQLFetchScroll (SQLExtendedFetch prior to ODBC 3.0) permits multiple rows to be fetched in a single call. SQLFetchScroll provides this control at fetch time, in contrast to block cursors, which provide this feature when the cursor is declared.
In Embedded SQL, the FETCH statement provides control over the number of rows fetched at a time, by providing an ARRAY clause.
Open Client and JDBC do not support multi-row fetches.
Prefetching rowsPrefetches are different from multiple-row fetches. Prefetches can be carried out without explicit instructions from the client application. Prefetching retrieves rows from the server into a buffer on the client side, but does not make those rows available to the client application until the appropriate row is fetched by the application.
By default, the Adaptive Server Anywhere client library prefetches multiple rows whenever a single row is fetched by an application. The additional rows are stored in a buffer by the Adaptive Server Anywhere client library.
Prefetching assists performance by cutting down on client/server traffic, and increases throughput by making many rows available without a separate request to the server.
For information on controlling prefetches, see PREFETCH option.
You can control whether or not prefetching occurs using the PREFETCH option. This can be set for a single connection to ON or OFF. By default it is set to ON. The number of rows fetched at a time is determined by the server.
In Embedded SQL, you can control prefetching when you open a cursor and on a FETCH operation, by using the BLOCK clause. (This should not be confused with ODBC block cursors, which fetch blocks of rows into an application.)
The application can specify a maximum number of rows that should be contained in a single fetch from the server by specifying the BLOCK clause. For example, if you are fetching and displaying 5 rows at a time, you could use BLOCK 5. Specifying BLOCK 0 causes 1 record at a time to be fetched and also cause a FETCH RELATIVE 0 to always fetch the row again.
In Open Client, you can control prefetching behavior using ct_cursor with CS_CURSOR_ROWS after the cursor is declared, but before it is opened.
Fetching with scrollable cursorsODBC and Embedded SQL provide methods for using scrollable and dynamic cursors. These methods allow you to move several rows forward at a time, or to move backwards through the result set.
Scrollable cursors are not supported by the JDBC or Open Client interfaces.
Prefetching does not apply to scrollable operations. That is, if you fetch a row before the current row, you do not get several previous rows prefetched also.
Modifying rows through a cursorCursors are not only used for reading result sets from a query. You can also modify data in the database while processing a cursor. These operations are commonly called positioned update and delete operations, or put operations if the action is an insert.
Not all query result sets allow positioned updates and deletes. If you carry out a query on a non-updateable view then no changes can be made to the underlying tables. Also, if the query involves a join then you must specify which table you wish to delete from, or which columns you wish to update, when you carry out the operations.
Insertions through a cursor can only be executed if any non-inserted columns in the table allow NULL or have defaults.
ODBC, Embedded SQL, and Open Client permit data modification using cursors, but JDBC does not. With Open Client, you can delete and update rows, but you can only insert rows on a single-table query.
If you attempt a positioned delete on a cursor, the table from which rows are deleted is determined as follows:
If no FROM clause is included, the cursor must be on a single table only.
If the cursor is for a joined query (including using a view containing a join), then the FROM clause must be used. Only the current row of the specified table is deleted. The other tables involved in the join are not affected.
If a FROM clause is included, and no table owner is specified, the table-spec value is first matched against any correlation names.
If a correlation name exists, the table-spec value is identified with the correlation name.
If a correlation name does not exist, the table-spec value must be unambiguously identifiable as a table name in the cursor.
If a FROM clause is included, and a table owner is specified, the table-spec value must be unambiguously identifiable as a table name in the cursor.
The positioned DELETE statement can be used on a cursor open on a view as long as the view is updateable.
Canceling cursor operationsYou can cancel a request through an interface function. From Interactive SQL, you can cancel a request by pressing Stop.
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, following the cancel.
Bookmarks and cursorsODBC provides bookmarks, which are values used to identify rows in a cursor. Adaptive Server Anywhere supports bookmarks for all kinds of cursor except dynamic cursors.
Before ODBC 3.0, a database could specify only whether it supports bookmarks or not. There was no way for a database server to indicate for what kind of cursor bookmarks were supported. Adaptive Server Anywhere returns that it does support bookmarks. There is therefore nothing in ODBC to prevent you from trying to use bookmarks with dynamic cursors; however, you should not use this combination.