Query-Based Updates

Article
07/05/2016

This section describes query-based updates and explains how rows are identified through the WHERE clause. It also describes the conditions under which rowsets can be updated based on query-based updates.

Query-Based Update Definition

Query-based updates use the SQL INSERT, UPDATE and DELETE commands to issue updates to the ODBC database. These commands are not positioned and are independent of, but in the same transaction context as, the rowset cursor. Query-based updates are used when the ODBC driver does not support the features required to use positioned updates, or when you specifically set some property to request a query-based update.

Row Identification

When updating or deleting an existing row, a query-based update must identify the row through the WHERE clause of the UPDATE or DELETE command. Because it does not have any primary key information, a query-based update constructs a hopeful key that might be able to identify the row. The hopeful key consists of all searchable columns in the rowset. By default, all REAL, FLOAT, and DOUBLE numeric values are excluded from the WHERE clause; to include these values, users must set the property KAGPROP_INCLUDENONEXACT to VARIANT_TRUE. If the key does not uniquely identify a row, severe update anomalies are possible.

For example, if a rowset contains columns A, B, C, and D, where columns A and D are searchable, the DELETE command that would be issued by a query-based update would be as follows:

Note

DELETE FROM table-name WHERE (A = value-a AND D = value-d )

Value-x is the value of column x for the row being deleted.

Criteria for Updatable Rowsets

To be updatable using query-based updates, a rowset must satisfy the following criteria. First, a rowset must contain at least one searchable column. Second, the query used to create the rowset must contain only a single table or view name in its FROM clauses.

Ensuring Changes Are Visible

To ensure that changes are visible, the consumer should set DBPROP_OTHERUPDATEDELETE or DBPROP_OTHERINSERT. This enables the ODBC Provider to expose changes to the data store using query-based updates.

Setting the QBUTimeout Value

If your query-based updates are timing out (for example, because of a heavy load on a server), you might want to try changing the QBUTimeout value in the system registry.

Using the registry editor, go to the following key location: HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\Flags. Under this registry key, you can change the DWORD value of QBUTimeout to the desired number. If no value is given, QBUTimeout will default to 30 seconds.