For example, the following code creates a view that displays data from the Products table and the Inventory table from the online database. Since no update criteria are specified, this view is read-only:
CREATE SQL VIEW showproducts ; CONNECTION dsource ;
AS SELECT * FROM Products INNER JOIN Inventory ; ON Products.ProductID = Inventory.ProductID ; CREATEOFFLINE("showproducts")
Displaying and Modifying Offline Data
After you create the view for your offline data, you can use it as you would any view in your application: you can add, change, and delete records. Multiple users can access the offline view concurrently using the same database in shared mode. If you decide you do not want to keep any of the changes, you can revert the information to reflect the original information.
Using Data Offline
Using the offline view, you can display and update data much as you do online with the same forms, reports, or applications. For example, the following code opens the view Showproducts:
USE Showproducts
Tip If you are not getting the subset of data that you expected, check the optimization settings for the remote view.
If you set the MaxRecords property using the DBSETPROP( ) function, only that many records appear in your views offline. However, if you include a Memo field in the field list of your view, it is automatically included in the result set even if FetchMemo is set to false (.F.).
Administering Data Offline
In some cases — especially in a multiple-user environment where data is modified by numerous people — you might want to examine changes made to the offline view before committing the changes to the source database. With the USE command and the ADMIN clause, you can see all changes that have been committed to a view since it was taken offline. You can then selectively revert changes that have been made without being connected to the data source. For example, the following code opens the view Showproducts in administrator mode:
USE Showproducts ADMIN
Updating to Online Data
After you're finished offline, you can update the data on the sever 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.
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)
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.