Cursors

Informix® OLE DB Provider supports the following ADO cursor types:
  • Client-side scrollable cursors (adUseClient and adOpenStatic)

    Client-side scrollable cursors (adUseClient and adOpenStatic) support bookmarks and have the following limitation: database updates fail when the rowset includes columns of extended data types.

  • Server-side scrollable cursors (adOpenStatic)

    Server-side scrollable cursors are faster than client-side cursors. If a server-side scrollable cursor is opened on a table (adCmdTableDirect) or on a simple SELECT statement (single table, no aggregates, no GROUP BY clause), the cursor can support bookmarks and, with the Version 9.2, or later HCL Informix server, database updates.

  • Server-side nonscrollable cursors (adUseServer and adOpenForwardOnly)

    Server-side nonscrollable cursors (adUseServer and adOpenForwardOnly) are the fastest cursors. Like server-side scrollable cursors, nonscrollable cursors support updates when opened on a table or (with the Version 9.2, or later HCL Informix server) when opened on a simple FOR UPDATE-compatible SELECT statement. In addition, if a server side nonscrollable cursor is opened on a table or on a simple SQL statement without an ORDER BY clause, the cursor is able to display changes made to the database by other users (unless transaction isolation precludes it).

The following caveats apply to the use of cursors:
  • The only scrollable cursor supported by Informix OLE DB Provider is the static cursor. The Provider accepts requests for other types of scrollable cursors (dynamic and keyset), but it supplies a static cursor regardless of which cursor type is requested.
  • Since the scrollable cursor is static, it cannot detect changes made to the database by other users. The DBPROP_OWNINSERT, DBPROP_OTHERINSERT, and DBPROP_OTHERUPDATEDELETE properties for scrollable cursors are read-only VARIANT_FALSE.

    Use a nonscrollable cursor (adOpenForwardOnly) if you want the functionality that corresponds to setting these properties to VARIANT_TRUE.

  • With HCL Informix servers before version 9.2, the server-side nonscrollable cursor adUseServer can update records only when the rowset is opened with IOpenRowset::OpenRowset(). The ADO flag corresponding to IOpenRowset::OpenRowset() is adCmdTableDirect.

    The client-side cursor (adUseClient) does not have this limitation.

  • Server-side scrollable cursors cannot be opened if the record set includes simple large objects (BYTE and TEXT) or collections.

    You can use a server-side nonscrollable cursor (adOpenForwardOnly) or a client-side scrollable cursor (adUseClient) with these types.

  • The DBPROP_IRowsetScroll property is read-only VARIANT_FALSE for rowsets not opened with IOpenRowset::OpenRowset(). It is VARIANT_TRUE for rowsets opened with IOpenRowset::OpenRowset() if bookmarks are requested (corresponding ADO flags are adOpenStatic and adOpenKeyset).
  • To support bookmarks and the modification or deletion of records, a data source table must include a ROWID column. (A ROWID column is not needed to insert records.)

    All fragmented and nonfragmented tables created with the WITH ROWIDS clause (or altered with the WITH ROWIDS clause applied) have this column. The ROWID column itself is not visible to consumers unless it is explicitly selected.

    If consumers require a persistent unique ID, create the necessary columns by using the SERIAL, SERIAL8, or BIGSERIAL data types.

  • Use of DISTINCT, UNIQUE, ORDER BY, GROUP BY, or aggregates in SQL statements makes the cursor unable to detect changes made on the database by other users.
  • Any SELECT statement that cannot be used with FOR UPDATE (for example, because it has joins or aggregates) is incompatible with bookmarks and updatability (but not incompatible with scrolling).
  • When you work with ADO client-side cursors, specify the table name in the same text case that is used on the server. Otherwise, the database server returns an error. To work around this issue, use ADO server-side cursors.