Updating Remote Data with SQL Pass-Through

Article
10/23/2006

When you use SQL pass-through functions to update data on a remote server, you control whether data is updated, as well as specific details about the updates, by setting properties on the result set cursor. Visual FoxPro checks these properties when an update is requested before committing the update.

To update remote data you must set five properties: Tables, KeyFieldList, UpdateNameList, UpdatableFieldList, and SendUpdates. You can specify additional properties such as Buffering, UpdateType, and WhereType to best fit the requirements of your application.

To enable updates on an active view cursor

Use the CURSORSETPROP( ) function to specify the view cursor's update properties: Tables, KeyFieldList, UpdateNameList, UpdatableFieldList, and SendUpdates.

Tip SQL pass-through view cursors aren't updatable until you specify update properties for the view cursor. If you want to store update property settings persistently, create a view definition. Visual FoxPro supplies default values that prepare the view to be updatable when you create a view using the View Designer or the language. You can use the CURSORSETPROP( ) function to add additional information to supplement or customize the default values.

The update properties you set on the active view cursor have slightly different names than their DBSETPROP( ) counterparts. The following table lists the names used for both view definitions and active cursors.

View and Cursor Update Properties

Purpose	View definition properties¹	Active cursor properties²
Make remote table updatable.	Tables	Tables
Specify the remote names for view fields.	UpdateName (field-level property)	UpdateNameList
Specify view fields you want to use as keys.	KeyField (field-level property)	KeyFieldList
Specify the view fields that are updatable.	Updatable (field-level property)	UpdatableFieldList
Turn updates on.	SendUpdates	SendUpdates

1 Set with DBSETPROP( ).
2 Set with CURSORSETPROP( ).

For more information on setting update properties, see Creating Views, or see DBSETPROP( ) or CURSORSETPROP( ).

Controlling the Timing of Remote Updates

You control how updates to remote data are buffered by setting the cursor's Buffering property. Of the five possible buffering property settings, two are valid for remote views:

3, or DB_BUFOPTROW, the default, which optimistically locks the row.
5, or DB_BUFOPTTABLE, which optimistically locks the table.

Visual FoxPro supports only optimistic locking on remote cursors.

Note The pessimistic row and table buffering settings, 2 and 4, don't apply to remote views, because Visual FoxPro doesn't take locks on the server data. Buffering property setting 1 doesn't apply to remote views because views are always buffered.

Using Optimistic Row Buffering

The default Buffering setting, DB_BUFOPTROW, optimistically locks remote data on a row-by-row basis. For example, if you want changes to the titles table to be committed on a row-by-row basis, such as when using the SKIP command, you could set the Buffering property to 3:

CURSORSETPROP('buffering', 3, 'titles')

When Buffering is set to row buffering, you have two methods of sending updates to the remote server. You can:

Call the TABLEUPDATE( ) function.
Use a command that moves the record pointer off the row, such as SKIP or GO BOTTOM.

The TABLEUPDATE( ) function updates the server without moving the record pointer. Commands that move the record pointer send updates to the remote server as a by-product of moving off the updated record.

If you use row buffering and want to be able to revert changes to rows, you must wrap the changes in manual transactions using SQL pass-through transaction functions.

Using Optimistic Table Buffering

If you want changes to a table to be committed a batch at a time, such as when the user clicks a Save or OK button in a form, you can set the Buffering property to 5, or DB_BUFOPTTABLE. You must call the TABLEUPDATE( ) function to send the update to the server.

In the following example, you set the Buffering property in your form's initialization code and then commit the changes in the save code.

Code	Comments
`CURSORSETPROP('buffering', 5, 'sqltitles')`	Set in Init code
`* Update batched changes; * ignore changes made by others`
`TABLEUPDATE(.T., .T., 'titles')`	Set in Save code

To restore the original values to a table and prevent updates from being sent to the remote server, you call TABLEREVERT( ). You can control whether a single row or all rows are reverted by combining the setting of the cursor's Buffering property with the TABLEREVERT( ) command. The following example reverts only the current row. You might want to invoke this code when the user clicks on a Cancel button in a form:

= TABLEREVERT(.F., 'titles')      && Revert current row

If you wanted to revert all rows, such as when the user presses ESC to leave a form, you could use the same example, this time changing the settings of the Buffering property and TABLEREVERT( ) command to revert all rows, with the entire table buffered:

= TABLEREVERT(.T., 'titles')      && Revert all rows

For more information about buffering, see Programming for Shared Access.

Detecting Changes By Other Users

In multi-user applications, conflicts with other users' updates are detected by the SQL Update query, which is generated when a write is attempted locally. The level of detection depends on the setting of the WhereType property. For more information on setting the WhereType property, see Creating Views.

Forcing Updates

You can use the TABLEUPDATE( ) function to control whether changes made to a table or cursor by another user on a network are overwritten when you send your updates. If you set the Force parameter of TABLEUPDATE( ) to true (.T.), and the CURSORSETPROP( ) UpdateType property is set to the default value, 1, old data is updated with the new data you send, as long as the value in the record's key field on the remote table hasn't been changed. If the value in the remote table's key field has changed, or if the UpdateType property is set to 2, Visual FoxPro sends a DELETE and then an INSERT statement to the remote table.

Troubleshooting Update Error Messages

The following table lists the Visual FoxPro and ODBC error messages that apply specifically to remote updates. The Action column contains the action you take to resolve the error condition.

Error Message	Meaning	Action
No update table(s) specified. Use the Tables cursor property.	The cursor property Tables contains no remote table names. At least one table is required to enable updates to the remote server.	Use the Tables property to specify at least one table for the cursor.
No key column(s) specified for the update table table_name. Use the KeyFieldList cursor property.	The primary key for the remote table specified in the error message isn't included in the KeyFieldList property for the cursor; a primary key is required for each table being updated.	Use the KeyFieldList property to specify the primary key for the remote table.
No valid update table specified for column column_name. Use the UpdateNameList and Tables cursor properties.	The UpdateName property for column column_name has an invalid table qualifier.	Set the table qualifier with the UpdateNameList property, or add the table qualifier to the Tables property setting, or both.
The KeyField List cursor property doesn't define a unique key.	More than one remote record has the same key.	Use the KeyField List property to define a unique key for the remote table.
From ODBC: ODBC invalid object.	ODBC cannot find the remote table or column because it doesn't exist as named. Visual FoxPro field names are validated by Visual FoxPro; remote table and column names are validated only by the remote server.	Check the object name.

For more information on error handling, see Handling SQL Pass-Through Errors.