XMLUPDATEGRAM()

XMLUPDATEGRAM(), available starting in VFP 7, is a way to capture changes to the data in a set of tables in an XML format. SQL Server can use XML updategrams to update data, eliminating the need for tedious coding techniques to update SQL Server databases.

Usage

cUpdateGram = XMLUpdateGram( [ cTableList [, nFlags ] ] )

First, some ground rules. The cursor(s) on which you’re tracking changes must be buffered, and MULTILOCKS must be set ON for XMLUPDATEGRAM() to work. You will probably want to use the View Designer or CURSORSETPROP() to set the key field list, which limits the fields in the before and after representations to the key fields and only those fields that changed. If you don’t specify key fields, all fields are listed in the before and after representations, and you’ll end up with a huge XML file. Warning: If you have memo or general fields containing characters used in the markup language (<, >, &, and “), you could generate errors unless you include nFlags 8.

So, what does an XML updategram look like? Here’s the output from the example code:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync>
    <updg:before>
      <customer>
        <cust_id>ALFKI</cust_id>
        <contact>Maria Anders</contact>
      </customer>
    </updg:before>
    <updg:after>
      <customer>
        <cust_id>ALFKI</cust_id>
        <contact>none</contact>
      </customer>
    </updg:after>
  </updg:sync>
</root>

The first line is the declaration line, which defines some specifics about the file. Then comes the root element, required in XML; it defines this as an updategram. Then come <updg:sync> elements, each of which is a transaction that can be committed, or is rolled back in the event of a failure of any one of the elements contained. Each sync element contains at least one pair of <updg:before> and <updg:after> elements, which corresponds to an insert, update, or delete operation. Within each before and after element is an element corresponding to each table to be updated. It’s conveniently named the same as the table to update. And within these table elements are field elements, also conveniently named identically to the fields they reference. In the before element, these fields must identify one and only one record to update. If you’ve used CURSORSETPROP() to specify a key fields list, the key fields as well as the original values for those that fields that changed are listed. If you did not specify a key fields list, all fields in the table are listed. The after element contains the same fields, with the changed values. An element with an empty before element represents an insert operation, while an empty after element represents a delete operation.

Note that this function returns a character string. You must save the character string to a file (StrToFile() is a good function to use), which is the most common way to use updategrams.

Visual FoxPro can write updategrams, but it has no native capacity to read them. You’re entirely on your own if you want to incorporate this into an application that uses only FoxPro tables. This is specifically designed to update SQL Server tables. For example, your application may use SQLExec() to execute a SELECT statement or stored procedure, which puts SQL Server data into a FoxPro cursor. Your application allows the user to edit any or all of the records in the cursor (after setting MULTILOCKS and buffering on, and specifying a key fields list), then writes out the updategram when the user is finished editing. Then, in SQL Server, execute the updategram file like any other XML query template file.

Note that SQL Server 2000 supports updategrams only after you have installed the XML for SQL Web Release, which can be freely downloaded from msdn.microsoft.com.

Example

LOCAL cUpdateGram
USE _SAMPLES + "Data\customer.dbf"
* Turn on MULTILOCKS and buffering.
SET MULTILOCKS ON
CURSORSETPROP("Buffering",5,"customer")
CURSORSETPROP("KeyFieldList","cust_id","customer")
* Update some data.
REPLACE Customer.Contact WITH "none"
* Create updategram.
lcUpdateGram = XMLUPDATEGRAM("customer")
STRTOFILE(lcUpdateGram, "NewUpdateGram.XML")
* Restore the record to its original form.
TABLEREVERT()

See Also

CursorSetProp(), CursorToXML(), Set MultiLocks, SQLExec(), StrToFile(), XMLToCursor(), VFPXMLProgID