Management of Updates with Views

You can use the update conflict management technology built into Visual FoxPro views to handle multi-user access to data. Views control what is sent to the base tables underlying the view by using the WhereType property. You can set this property for both local and remote views. The WhereType property provides four settings:

• DB_KEY
• DB_KEYANDUPDATABLE
• DB_KEYANDMODIFIED (the default)
• DB_KEYANDTIMESTAMP

By choosing one of these four settings, you control how Visual FoxPro builds the WHERE clause for the SQL Update statement sent to the view's base tables. You can choose the setting you want using the Update Criteria tab of the View Designer, or you can use DBSETPROP( ) to set the WhereType for a view definition. To change the WhereType setting for an active view cursor, use CURSORSETPROP( ).

For example, suppose you have a simple remote view based on the Customer table that includes seven fields: cust_id, company, phone, fax, contact, title, and timestamp. The primary key for your view is cust_id.

You've made only two fields updatable: contact_name and contact_title. You want the user to be able to change the company contact and their title from the view. However, if other facts about the company change, such as the company address, you want the changes to go through a coordinator who'll identify the impact of the changes on your company, such as whether the sales region for the customer will change. Now that your view has been set up to send updates, you can choose the WhereType according to your preferences.

Now suppose that you change the name in the contact field for a customer, but you don't change the value in the other updatable field, title. Given this example, the following section discusses how the WhereType setting would impact the WHERE clause that Visual FoxPro builds to send the new contact name to the base tables.

Comparing the Key Field Only

The least restrictive update uses the DB_KEY setting. The WHERE Clause used to update remote tables consists of only the primary key field specified with the KeyField or KeyFieldList property. Unless the value in the primary key field has been changed or deleted in the base table since you retrieved the record, the update goes through.

In the case of the previous example, Visual FoxPro would prepare an update statement with a WHERE clause that compares the value in the cust_id field against the cust_id field in the base table row:

WHERE OLDVAL(customer.cust_id) = CURVAL(customer_remote_view.cust_id)

When the update statement is sent to the base table, only the base table's key field is verified.

The key field in your view is compared against its base table counterpart.

Comparing the Key Field and Fields Modified in the View

The DB_KEYANDMODIFIED setting, the default, is slightly more restrictive than DB_KEY. DB_KEYANDMODIFIED compares only the key field and any updateable fields you've modified in the view against their base table counterparts. If you modify a field in the view, but the field isn't updateable, the fields are not compared to the base table data.

The WHERE clause used to update base tables consists of the primary fields specified with the KeyFieldList property and any other fields that are modified in the view. In the case of the previous example, Visual FoxPro would prepare an update statement that compares the values in the cust_id field because it is the key field, and the contact field because the contact name has been changed. Even though the title field is updateable, title is not included in the update statement because we haven't modified it.

The key and modified fields in your view are compared against their base table counterparts.

Comparing the Key Field and All Updateable Fields

The DB_KEYANDUPDATABLE setting compares the key field and any updateable fields (whether modified or not) in your view against their base table counterparts. If the field is updateable, even if you haven't changed it in the view, if anyone else has changed that field in the base table, the update fails.

The WHERE clause used to update base tables consists of the primary fields specified with the Key Field or KeyFieldList property and any other fields that are updateable. In the case of the example, Visual FoxPro would prepare an update statement that compares the values in the cust_id, contact, and title fields against the same fields in the base table row.

All the updateable fields in your view are compared against their base table counterparts.

Comparing the Timestamp for All Fields in the Base Table Record

The DB_KEYANDTIMESTAMP is the most restrictive type of update, and is only available if the base table has a timestamp column. Visual FoxPro compares the current timestamp on the base table record against the timestamp at the time the data was fetched into the view. If any field in the base table's record has changed, even if it's not a field you're trying to change, or even a field in your view, the update fails.

In the case of the example, Visual FoxPro prepares an update statement that compares the values in the cust_id field and the value in the timestamp field against those fields in the base table row.

The timestamp for your view's record is compared against the timestamp on the base table record.

In order to successfully update data using the DB_KEYANDTIMESTAMP setting with a multi-table view, you must include the timestamp field in your view for each table that is updateable. For example, if you have three tables in a view and want to update only two of them, and you choose the DB_KEYANDTIMESTAMP setting, you must bring down the timestamp fields from the two updateable tables into your result set. You can also use logical values in the CompareMemo property to determine whether memo fields are included in conflict detection.

See Also

Managing Performance | Management of Conflicts | Programming for Shared Access | Update Criteria | DBSETPROP( ) | CURSORSETPROP( )