The LA Fox Developer Newsletter

May 2000

ADO Jumpstart for Microsoft

Visual FoxPro Developers

Part 4

John V Petersen

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

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

LA Fox Developer.)

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.AddNew

?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 recon-

nected, 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 Enter-

prise Development, by Rod Paddock, John V. Petersen, and

Ron Talmage (Prima Publishing), illustrates how to generate a

GUID:

Local oGuid

oGuid = CreateObJect(”guid”)

?oGuid.GetNextGuid( )

*/ Class Definition

Define Class guld AS Custom

*/ Create protected members to hold parts of GUID

Protected datal

Protected data2

Protected data3

Protected data4

Procedure \

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

GUID]

Local cGuld

cGuid = This.Export()

UuldCreate(@cGuid)

Thls.lmport(cGuld)

cGuid = This.Convert(cGuld)

Return cGuid

EndProc

Protected Procedure bintoHex(cBIn)

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

Hextochar)

*/

Method

Local cChars, nBin

(Con’t, page 4)

Page 3