Updating Remote Data with SQL Pass-Through

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

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.

CURSORSETPROP('buffering', 5, 'sqltitles')

* Update batched changes;
* ignore changes made by others

TABLEUPDATE(.T., .T., 'titles')

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 multiuser 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.

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

See Also

Working with Remote Data Using SQL Pass-Through | Selection of an Efficient SQL Pass-Through Processing Mode | Implementing a Client/Server Application | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views