Skip to content
≡

Administrator

Developer

Tools

External Tools

4. Using Cursors#

This chapter explains how to use cursors provided by the Altibase CLI driver.

Cursor Characteristics#

The Altibase CLI driver supports the following cursors:

  • Diverse types of cursors
  • Scrolling and positioning within cursors
  • Multiple concurrency options
  • Positioned updates

Cursor characteristics are controlled by statement attributes set with SQLSetStmtAttr before executing SQL statements. Altibase CLI API functions for processing result sets support all-inall cursor functionality, such as fetch, scroll and positioned update.

Setting Cursor Attributes#

An application can control cursor behavior by setting one or more cursor attributes before executing a SQL statement. There are two ways of specifying cursor characteristics in the ODBC Standard:

  • Cursor Type
    Cursor types can be set using the SQL_ATTR_CURSOR_TYPE attribute of SQLSetStmtAttr. The Altibase CLI driver supports forward-only, static, and keyset-driven cursor types.

  • Cursor Behavior
    Cursor behavior can be set using the SQL_ATTR_CURSOR_SCROLLABLE, SQL_ATTR_CURSOR_SENSITIVITY, and SQL_ATTR_CONCURRENCY attributes of SQLSetStmtAttr.

Cursor types are ODBC-style and cursor behavior is SQL-92/ISO style.

Cursor Types#

The Altibase CLI driver supports the following three cursor types:

  • Forward-only cursors
    Scrolling is not supported; fetching rows are only supported from the start to the end of the cursor.

  • Static cursor
    Displays the result set as it was when the cursor was opened, and is readonly.

  • Keyset-driven cursors
    Row order is fixed when the cursor is opened. Data modifications made to nonkey columns are visible through the cursor.

Cursor Behavior#

Cursor scrollability, sensitivity and concurrency can be specified using the SQL_ATTR_CURSOR_SCROLLABLE, SQL_ATTR_CURSOR_SENSITIVITY, and SQL_ATTR_CONCURRENCY attributes of SQLSetStmtAttr.

  • Scrollability
    If SQL_ATTR_CURSOR_SCROLLABLE is set to SQL_SCROLLABLE, the cursor supports scrolling in various directions; if SQL_ATTR_CURSOR_SCROLLABLE is set to SQL_NONSCROLLABLE, the cursor supports scrolling only in the SQL_FETCH_NEXT direction.

  • Sensitivity
    If SQL_ATTR_CURSOR_SENSITIVITY is set to SQL_SENSITIVE, the cursor reflects data modifications of the database, and retrieves the latest data from the database upon a repeated request of a rowset; If SQL_ATTR_CURSOR_SENSITIVITY is set to SQL_INSENSITIVE, the cursor does not reflect data modifications, and retrieves cached data upon a repeated request of a rowset.

  • Concurrency
    Cursor concurrency is set using the SQL_ATTR_CONCURRENCY attribute of SQLSetStmtAttr. the Altibase CLI driver supports the following two types of cursor concurrency:

    • SQL_CONCUR_READONLY: Read-only

    • SQL_CONCUR_ROWVER: Concurrency control with row versions

Implicit Cursor Conversionse#

Instead of specifying the cursor type, an application can specify each characteristic related to cursor behavior separately, by setting the SQL_ATTR_CURSOR_SCROLLABLE, SQL_ATTR_CURSOR_SENSITIVITY, and SQL_ATTR_CONCURRENCY statement attributes before opening a cursor associated with the statement handle. The Altibase CLI driver then selects the cursor type which most efficiently provides the characteristic requested by the application.

Whenever an application sets either of the SQL_ATTR_CURSOR_SCROLLABLE, SQL_ATTR_CURSOR_SENSITIVITY, SQL_ATTR_CONCURRENCY, or SQL_ATTR_CURSOR_TYPE statement attributes, the driver converts another attribute value for the four attribute values to be compatible. If an application specifies an attribute related to cursor behavior, the driver can convert an attribute which defines the cursor type, on the basis of implicit selection; if an application specifies the cursor type, the driver can convert other attributes to ensure compatibility among attribute values and characteristics of the selected cursor type.

Caution is required as there is a possibility of begetting a cursor of unintentional characteristics if an application specifies both the cursor type and cursor behavior.

The following table shows basic cursor behaviors according to cursor types.

Cursor Type Sensitivity Scrollability Concurrency
Forward-only insensitive non-scrollable read-only
Static insensitive scrollable read-only
Keyset-driven sensitive scrollable updatable
Dynamic - - -

The Altibase CLI driver does not support dynamic cursors. For forward-only and static cursors, any other behavior combination apart from that of the table above is not supported. For keyset-driven cursors, apart from the combinations of the table above, the (SENSITIVE, SCROLLABLE, READ-ONLY) combination is supported.

Cursor conversion rules are as follows:

  • If the user-specified cursor attribute is not compatible with the cursor type, the driver converts the cursor type in the following order:

    • dynamic → keyset-driven → static → foward-only

  • If an application specifies the cursor type, the driver converts the remaining attributes in the following order, until they are compatible with the characteristics of the selected type:

    • sensitive → insensitive

    • scrollable → non-scrollable

    • updatable → read-only


Scrolling and Fetching Rows#

To use scrollable cursors, the following must be executed in Altibase CLI applications:

  • The cursor attributes must be set using SQLSetStmtAttr.
  • The cursor must be opened using SQLExecute or SQLExecDirect.
  • Rows must be scrolled and fetched using SQLFetch or SQLFetchScroll.

ODBC cursors do not limit one fetch to one row. Multiple rows can be fetched whenever SQLFetch or SQLFetchScroll is called. For client/server model database operations like ODBC application programs, it is efficient to fetch multiple rows at one time. The number of rows returned from a fetch is said to be the size of the rowset, and this can be specified using SQL_ATTR_ROW_ARRAY_SIZE of SQLSetStmtAttr.

Cursors with a rowset size larger than 1 are called block cursors, and data can be queried from block cursors by binding arrays.

For more detailed information, refer to the SQLFetch function in Chapter 2: SQLFetch can be used only for forward-only cursors and SQLFetchScroll can scroll cursors in various directions.

Setting Bookmarks For Rowsets#

A bookmark is a value used to identify a row of data. In Altibase CLI applications, bookmarks for certain rows are requested, stored, and then passed to the cursor to return to the row.

When fetching rows with SQLFetchScroll, an application can select the starting row, using a bookmark as a basis. This bookmark takes the form of an absolute address, since it does not refer to the current cursor position. An application can scroll a bookmarked row by calling SQLFetchScroll, using SQL_FETCH_BOOKMARK. This operation uses the bookmark specified in the SQL_ATTR_FETCH_BOOKMARK_PTR statement attribute; rowsets are returned, starting from the row identified by the bookmark. An application can take an offset as an argument when calling SQLFetchScroll. When the offset is specified, the first row of the returned rowset is determined by adding the number of offsets to the row identified by the bookmark. The Altibase CLI driver only supports bookmarks on static and keyset-driven cursors. If bookmarks are set on and a dynamic cursor is requested, a keyset-driven cursor is opened instead.


Restrictions#

In order to use Altibase CLI driver cursors, the SELECT statement which retrieves the result set is restricted in the following manner:

  • Only one table can be specified in the FROM clause.
  • The column name must be specified in the ORDER BY clause.

In order to execute positioned updates in a cursor, the following restrictions apply additionally:

  • Only regular tables can be specified in the FROM clause.
  • Only pure columns can be specified in the SELECT list; neither expressions, nor functions can be included. Columns that have a NOT NULL constraint, but do not have a DEFAULT value must be included in the SELECT list.