Updating Data Using Transactions
After you're finished offline, you can update the data on the server using the same table update transactions you usually use with online data. When working with remote data, keep in mind the following tips:
For single record updates, use automatic transactions.
For batch updates, use manual transactions.
As necessary, include code to detect update conflicts, create a log of conflicts, and resolve conflicts.
Before you can process your updates, you need to use the USE command and the ONLINE keyword to reconnect to the host database. After you issue the command, Visual FoxPro attempts to locate the host database using the data source information stored in the view. After the connection is established, you can use TABLEUPATE( ) to process the updates stored in the offline data.
To make sure the connection information is correct regardless of the location of either the host or view tables, you need to use connection string syntax rather than a named connection.
Updating Batches of Records in Local Tables
To process a batch of changes against local tables, you can use manual transactions that allow you to process the entire batch of changes within a single transaction rather a series of separate transactions.
Updating local tables with offline views
Code |
Comments |
|---|---|
USE myofflineview ONLINE EXCLUSIVE |
Reconnect to the host and open the view. |
BEGIN TRANSACTION IF TABLEUPDATE (2, .F., "myofflineview") END TRANSACTION ELSE MESSAGEBOX("Error Occurred: Update unsuccessful.") ROLLBACK ENDIF |
Check for update conflicts and update as appropriate. |
Updating Batches of Records in Remote Tables
To process a batch of changes against remote tables, use manual transactions: begin with TABLEUPDATE( ) and finish processing with either SQLCOMMIT( ) or SQLROLLBACK( ).
To set the connection to manage your transactions manually, you need to use CURSORGETPROP( ) on the view cursor to get the connection handle, then set the Transactions property to manual mode.
In the following code, the current connection identification for the view, myview, is stored into hConn1. hConn1 is used to set the Transactions property to "2" for manual transactions.
hConn1 = CURSORGETPROP("CONNECTHANDLE","myview") ;
SQLSETPROP(hConn1,"TRANSACTIONS",2)
After you set the connection to handle the updates, you can use TABLEUPDATE( ) to handle your transactions.
If the host tables reside on a remote server, such as SQL Server, you might use the following code as a guideline.
Updating remote tables with offline views
Code |
Comment |
|---|---|
USE myofflineview ONLINE EXCLUSIVE |
Reconnect to the host and open the view. |
SQLSetProp(liviewhandle,"transactions",2) SQLSetProp(custviewhandle,"transactions",2) SQLSetProp(ordviewhandle,"transactions",2) |
Setting the connections on the views to handle transaction manually. |
IF NOT TABLEUPDATE(.T.,.F.,"lineitemsview") =SQLROLLBACK(ordviewhandle) =MESSAGEBOX("Can't update line items table") IF NOT TableUpdate(.T.,.F.,"ordersview") =SQLROLLBACK(liviewhandle) =MESSAGEBOX("unable to update the orders table") IF NOT TABLEUPDATE(.T.,.F.,"customerview") =SQLROLLBACK(custviewhandle) =MESSAGEBOX("Can't update customer table") Else *# check out failure scenarios IF NOT SQLCOMMIT(liviewhandle) =SQLROLLBACK(liviewhandle) IF NOT SQLCOMMIT(ordviewhandle) =SQLROLLBACK(ordviewhandle) IF NOT SQLCOMMIT(custviewhandle) =SQLROLLBACK(custviewhandle) ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF |
Handling updates and update conflicts. |
Updating One Record
If you are updating a single row, you can use automatic transactions. Because each statement to process an update, delete, or insert is handled as a separate transaction, rollbacks against prior transaction statements are not possible.
USE customerview ONLINE EXCLUSIVE
GO TO 3
IF TABLEUPDATE (0, .F. workarea)
* conflict handling code
ENDIF
Tip
To update a single record in a local table, use the GETNEXTMODIFIED( ) function.
Canceling Offline Updates
If you decide you want to delete the offline data and convert the view back to an online view, you can use the DROPOFFLINE( ) function.
To cancel offline updates, use DROPOFFLINE( ) with the name of the view.
Be sure to check the return values. True (.T.) indicates success and false (.F.) indicates that the view was not closed before the command was issued.
The following code drops all of the changes made to the subset of data in myview. The view remains part of the database, but its current set of data is dropped:
DROPOFFLINE("myview")
You can delete offline records, but you can't use the PACK, ZAP, or INSERT commands with an offline view.
See Also
Tasks
How to: Optimize View Performance
How to: Create Queries (Visual FoxPro)
How to: Set the Maximum Number of Records Downloaded