Applications require different means to sense changes in the tables that are associated with a result set. Various cursor models are designed to meet these needs, each of which requires different sensitivities to changes in the tables.
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 that are set as dynamic by using SQLSetStmtAttr() closely resemble static cursors. solidDB dynamic cursor behavior is static in the sense that changes made to the result set 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. However, this behavior is affected by the solidDB AUTOCOMMIT mode setting. For details, see Cursors and autocommit. For an example of cursor behavior when using SQLSetPos(), see Cursors and positioned operations.
Another characteristic of solidDB 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, when Transaction_A starts, it does not see any changes made by any other transaction that did not commit work before Transaction_A started. The following conditions in solidDB cause changes to be invisible to the user that made them:
▪ in a SELECT statement when an ORDER BY clause or a GROUP BY clause is used and solidDB caches the result set,
▪ in applications written by using ADO or OLE DB, solidDB cursors are more like dynamic ODBC cursors in order to enable functions such as a rowset update.
Specifying the cursor type
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() or SQLFetchScroll() to scroll the cursor backwards or forwards.
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, see Using cursors.
When using block cursors and positioned updates and deletes, you must be aware of the following requirements:
▪ Set the commit mode to SQL_AUTOCOMMIT_OFF in the application.
▪ Commit changes in the application only when all the fetch and positioned operations are done.
▪ Do not commit changes in between positioned operations.
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 might experience unpredictable behavior while browsing through the result set, as explained in the following section.
Positioned cursor operations and SQL_AUTOCOMMIT_ON
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 might be moved from its original position in the result set. 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 might also become invalid because of a change in position of the updated row. Hence the application might see incorrect behavior when it does the next fetch or SQLSetPos() operation.
To understand this limitation, assume an application performs the following steps:
1 sets the commit mode to SQL_AUTOCOMMIT_ON,
2 sets the rowset size to 5,
3 executes a query to generate a result set containing n rows,
4 fetches the first rowset of 5 rows with SQLFetchScroll().
A sample result set is shown below. In the sample, the result set 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
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 in the following table:
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 would not be updated until the SQLEndTran() function is called to commit the changes.
For solidDB-specific information about cursors and autocommit, read Committing transactions.
Cursors and positioned operations
When an application is performing positioned operations (such as updates and deletes) when calling SQLSetPos(), there are limitations in result set visibility.
The following cases illustrate cursor behavior when using SQLSetPos().
In Case 1, the cursor scrolls back and forth within the same block after the update is applied. Although this case is intended to illustrate the visibility of updates in the result set when using cursors, the exact circumstances under which visibility occurs depends on several factors. These include the size of the result set relative to the size of the memory buffer, the transaction isolation level, and the frequency with which you commit data.
In Case 2, the cursor scrolls backward within a rowset or the cursors move back and forth within a different rowset after an update is applied.
Case 1
The following example shows cursor behavior that uses positioned operations and shows how positioned updates can be visible to users.
Assume an application performs the following steps:
1 Sets the commit mode to SQL_AUTOCOMMIT_OFF. This is a requirement described in Cursors and autocommit.
2 Sets the rowset size to 5.
3 Executes a query to generate a result set of n rows.
4 Fetches the first rowset of 5 rows with SQLFetchScroll().
A sample result set is shown below. In the sample, the result set 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
Assume now that the application calls SQLSetPos() to update the third and fourth rows of the result set with the names Caroline and Debbie. After the updates, the actual row values now contain Caroline and Debbie, as shown in the following table:
Row counter stored internally by the driver
Row value
1
Antony
2
Ben
3
Caroline
4
Debbie
5
Edgar
Note In some cases, the result set for a SELECT statement might be too large to fit in memory. As the user scrolls back and forth within the result set, the ODBC driver might discard some rows from memory and read in others. This can cause unexpected results: in some situations, updates to data in the cursor can 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
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:
1 Sets the commit mode to SQL_AUTOCOMMIT_OFF. This is a requirement explained in Cursors and autocommit.
2 Sets the rowset size to 5.
3 Executes a query to generate a result set of n rows.
4 Fetches the first rowset of 5 rows with SQLFetchScroll.
A sample result set is shown below. In the sample, the first two rowsets are shown. The result set 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
Assume that after the first 4 steps above, the application calls SQLSetPos() to perform the following tasks:
5 Updates the third row of the result set.
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 (see Positioned cursor operations and SQL_AUTOCOMMIT_ON), commits cannot be done until all work that is related to block cursors and positioned operations is completed.