May2000

The LA Fox Developer Newsletter

ADO Jumpstart for Microsoft Visual FoxPro Developers

(Ed. Note: Reprinted with the author’s full permission. This is

The fourth installment of a multi-part article begun in February’s

SQL Server Identity Fields and Parent/Child Relationships

SQL Server, like most server RDBMSs and Microsoft Access®, creates an auto-incrementing field that can serve as a primary key for a table. Typically, the data type for this field is Integer. In SQL Server, this type of field is called the Identity field. Fields of this type are read-only. It begs the question, “When adding records, how can one determine what these values are?” Knowing that the next generated value is a requirement for maintaining referential integrity when child tables are involved. The following example code shows a recordset in which the first field, ID, is the auto-incrementing field. After new field is added, checking the value of the ID field yields a character with a length of zero. Attempting to update the field results in an error. However, once the recordset is updated, checking the value again will yield a valid identity value.

?oRecordset.Fields("id").Value && empty string oRecordset.UpdateBatch ?oRecordset.Fields("id").Value && returns new identity value

With the new identity value available, you can add records in child tables, using the identity value in the parent table as the foreign key in the child tables.

But, what do you do in cases where you have disconnected recordsets?

This section details an important capability in ADO—the ability to have recordsets without an active connection to the backend data source. At this point you can freely add new records to disconnected records. When the recordset is eventually reconnected, those newly added records are then sent to the backend data source. How do you know what the identity value will be in those cases? Simply put, you don’t know. At the same time, however, you still need to be able to add both parent and child records locally. You need some method that maintains the relationship locally, while at the same time, supporting the use of the identity value when the data is sent to the backend.

The simplest solution to this problem is to include a field in each table that serves as the local ID. You need this extra field because the identity field will be read-only. On the client side, you can use several methods for producing an ID that is unique. One approach is to use the Windows API to fetch the next Global Unique Identifier (GUID). The following procedure outlines how the local process unfolds:

1. .Add a new parent record.

2.Fetch the next GUID.

3.Update the local primary key column with the GUID.

4.Add a new child record.

5.Update the local primary key column with the GUID.

6.Update the foreign key column of the child with the GUID from its parent.

At some point, you will reconnect to the server. The update process could be performed within the context of a transaction, done one row at a time by navigating through each record. Checking the recordset Status property, which indicates whether the current record has been newly created, modified, deleted, and so on, determines whether the current row should be sent back to the server. If the record should be sent back, the parent record can be updated via the UpdateBatch method. The UpdateBatch method accepts an optional argument that specifies that only the current record be updated. By default, UpdateBatch works on all records. If the value of one is passed—corresponding to the adAffectCurrent constant—only the current record is updated. Once the update occurs, the identity value generated by the server is available. This value would then be used to update the foreign key columns of any related children. Once that process is complete, the records for that parent would be sent back to the server as well. This same process would be used if grandchild and great-grandchild relationships also existed.

The following Visual FoxPro code, from Visual FoxPro 6 Enterprise Development, by Rod Paddock, John V. Petersen, and Ron Talmage (Prima Publishing), illustrates how to generate a

Local oGuid oGuid = CreateObJect(”guid”)

?oGuid.GetNextGuid( )

*/ Class Definition Define Class guld AS Custom

*/ Create protected members to hold parts of GUID

r’ The only public member. This method will return the next

cGuid = This.Export()

cGuid = This.Convert(cGuld)

Return cGuid EndProc

Protected Procedure bintoHex(cBIn)

r’ This method converts a binary value to Char by calling the Hextochar)