Cursor support

Applications require different means to sense changes in the tables underlying a resultset. Various cursor models are designed to meet these needs, each of which requires different sensitivities to changes in the tables underlying the resultset.

For example, when balancing financial data, an accountant needs data that appears static; it is impossible to balance books when the data is continually changing. When selling concert tickets, a clerk needs up-to-the minute, or dynamic, data on which tickets are still available.

solidDB^® cursors which are set with SQLSetStmtAttr as “dynamic” closely resemble static cursors, with some dynamic behavior. solidDB^® dynamic cursor behavior is static in the sense that changes made to the resultset by other users are not visible to the user, as opposed to ODBC dynamic cursors in which changes are visible to the user.

In solidDB^®, as long as the cursor scrolls forward from block to block and never scrolls backward or the cursors move back and forth within the same block after an update is done, then the user gets the dynamic cursor behavior. This means that all changes are visible. Note, however that this behavior is affected by the solidDB^® AUTOCOMMIT mode setting. For details, read Cursors and autocommit. For an example of cursor behavior when using SQLSetPos, read Cursors and positioned operations.

Another characteristic of solidDB^®’s cursor behavior is that transactions are able to view their own data changes (with some limitations), but cannot view the changes made by other transactions that overlap in time. (For more details about the limitations on users seeing their own data changes, see Cursors and positioned operations). For example, once Transaction_A starts, it will not see any changes made by any other transaction that did not commit work before Transaction_A started. The conditions in solidDB^® that cause a user’s own changes to be invisible to that user are:

▪In a SELECT statement when an ORDER BY clause or a GROUP BY clause is used, solidDB^® caches the resultset, which causes the user’s own change to be invisible to the user.

▪In applications written using ADO or OLE DB, solidDB^® cursors are more like dynamic ODBC cursors to enable functions such as a rowset update.

To specify the cursor type, an application calls SQLSetStmtAttr with the SQL_CURSOR_TYPE option. The application can specify a cursor that only scrolls forward, a static cursor, or a dynamic cursor.

Unless the cursor is a forward-only cursor, an application calls SQLExtendedFetch (ODBC 2.x) or SQLFetchScroll (ODBC 3.x) to scroll the cursor backwards or forwards.

This section describes the cursor type supported by solidDB^®.

Three types of cursors are defined in ODBC 3.51:

solidDB^® cursors are server supported cursors.

This section provides information about cursors and autocommit.

For solidDB^®-specific information about cursors and autocommit, read Committing read-only transactions.

There are also some limitations in using the solidDB^® Autocommit mode if your application uses block cursors and positioned updates and deletes. For a brief description of these cursor features, read Using cursors.

When using block cursors and positioned updates and deletes, you must:

Note If the application uses commit mode as SQL_AUTOCOMMIT_ON or commits the changes before it is done with all the positioned operation, then the application may experience unpredictable behavior while browsing through the resultset. Read the section below for details.

The solidDB^® ODBC Driver keeps a row number/counter for every row in the rowset, which is the data rows retrieved from the database. When an application has the commit mode set to SQL_AUTOCOMMIT_ON and then executes a positioned update or a delete on a row in the rowset, the row is immediately updated in the database. Depending on the new value of the row, the row may be moved from its original position in the resultset. Since the updated row has now moved and its new position is unpredictable (since it is totally dependent on the new value), the driver loses the counter for this row.

In addition, the counter for all other rows in the rowset may also become invalid because of a change in position of the updated row. Hence the application may see incorrect behavior when it does the next fetch or SQLSetPos operation.

Following is an example that explains this limitation.

Assume an application performs the following steps:

A sample resultset is shown below. In the sample, the resultset has only 1 column (defined as varchar(32)). In the table below, the first column shows the row number maintained by the driver internally. The second column shows the actual row values.

Assume now that the application calls SQLSetPos to update the third row with a new value of Gerard. To perform the update, the new row value is moved and positioned as shown below:

Row counter stored internally by the driver	Row value
1	Antony
2	Ben
Empty row
4	David
5	Edgar
New row	Gerard

Now the row counter for “David” becomes 3 and not 4, while the counter for “Edgar” becomes 4 and not 5. Since some row counters are now invalid, they will give wrong results when used by the driver to do relative or absolute positioning of the cursor.

If the commit mode had been set to SQL_AUTOCOMMIT_OFF, the database is not updated until the SQLEndTran function is called to commit the changes.

For solidDB^®-specific information about cursors and autocommit, read Committing read-only transactions.

When an application is performing positioned operations (such as updates and deletes when calling SQLSetPos), there are limitations in resultset visibility.

Case 1 illustrates cursor behavior when using SQLSetPos. In Case 1, the cursor scrolls back and forth within the same block after the update is applied.

Although Case 1 is intended to illustrate the visibility of updates in the resultset when using cursors, the exact circumstances under which visibility occurs depends on several factors. These include the size of the resultset relative to the size of the memory buffer, the transaction isolation level, and the frequency with which you commit data, and so on.

Case 2 shows how cursor behavior is limited using SQLSetPos when the cursor scrolls backward within a rowset or the cursors move back and forth within a different rowset after an update is applied.

Following is an example that shows cursor behavior using positioned operations and shows how positioned updates can be visible to users.

Assume now that the application calls SQLSetPos to update the third and fourth rows of the resultset with the names Caroline and Debbie. After the updates, the actual row values now contain Caroline and Debbie, as shown below:

Note In some cases, the resultset for a SELECT statement may be too large to fit in memory. As the user scrolls back and forth within the resultset, the ODBC Driver may discard some rows from memory and read in others. This can cause unexpected results: in some situations, updates to data in the cursor may seem to “disappear” and then “reappear” if the cursor re-reads (for example, from disk) the original values for a row that it previously modified.

Case 2 shows the limitations when using positioned operations. The following example shows cursor behavior using positioned operations and shows when position updates are not visible to users.

Assume an application performs the following steps:

A sample resultset is shown below. In the sample, the first two rowsets are shown. The resultset has only 1 column (defined as varchar(32)). In the table below, the first column shows the row number maintained by the driver internally. The second column shows the actual row values.

Row counter stored internally by the driver	Row value
1	Antony
2	Ben
3	Charlie
4	David
5	Edgar
6	Fred
7	Gough
8	Harry
9	Ivor
10	John

6 Scrolls to the next rowset by calling SQLFetchScroll. This will get rows 6 to 10 and the cursor will be pointing to row 6.

7 Scrolls backward one rowset to get to the first rowset. This is done by calling SQLScrollFetch with the FETCH_PRIOR option.

After these tasks are performed, the value of the third row that was updated in step 5 still has the old value rather than the updated value as in “Case 1". The updated value is only visible in the Case 2 situation when the change is committed. But due to the unpredictable behavior when setting SQL_AUTOCOMMIT_ON as described in section Positioned cursor operations and SQL_AUTOCOMMIT_ON, commits cannot be done until all work related to block cursors and positioned operations is completed.