Collection Contents Index Types of cursor Describing result sets pdf/chap9.pdf

User's Guide
   PART 1. Working with Databases
     CHAPTER 9. Using SQL in Applications       

Working with cursors

This section describes how to carry out different kinds of operation using cursors.

Top of page  Configuring cursors on opening

You can configure the following aspects of cursor behavior when you open the cursor:

Top of page  Fetching rows through a cursor

The 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:

  1. Declare and open the cursor (Embedded SQL), or execute a statement that returns a result set.

  2. Fetch the next row until you get a Row Not Found error.

  3. Close the cursor.

The way these operations are carried out depends on the interface you are using. For example:

Top of page  Fetching multiple rows

This section discusses how you can use fetching of multiple rows at a time: a technique that can improve performance.

Multiple-row fetches 

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.

Using multiple-row fetching 

Top of page  Prefetching rows

Prefetches 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 Info     For information on controlling prefetches, see PREFETCH option.

Controlling prefetching from an appliation 

Top of page  Fetching with scrollable cursors

ODBC 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.

Top of page  Modifying rows through a cursor

Cursors 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.

Which table are rows deleted from? 

If you attempt a positioned delete on a cursor, the table from which rows are deleted is determined as follows:

  1. If no FROM clause is included, the cursor must be on a single table only.

  2. 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.

  3. If a FROM clause is included, and no table owner is specified, the table-spec value is first matched against any correlation names.

  4. If a correlation name exists, the table-spec value is identified with the correlation name.

  5. If a correlation name does not exist, the table-spec value must be unambiguously identifiable as a table name in the cursor.

  6. 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.

  7. The positioned DELETE statement can be used on a cursor open on a view as long as the view is updateable.

Top of page  Canceling cursor operations

You 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.

Top of page  Bookmarks and cursors

ODBC 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.

Top of page  

Collection Contents Index Types of cursor Describing result sets pdf/chap9.pdf