Share via

Updating to Online Data

  • Article

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

Optimization of View Performance | Creating Queries | Setting the Maximum Number of Records Downloaded | USE | TABLEUPATE( ) | SQLCOMMIT( )