How to use AutoInc fields with DataSnap

By: John Kaster

Abstract: Dan Miser shows a technique for implementing auto-incrementing fields for any DataSnap server. Updated 28-Oct-2002.

How to use Autoinc fields with DataSnap

By Dan Miser

Dealing with auto increment fields in a distributed application can be a trying experience. You no longer have access to the database directly from the client, and as a result, you have to rely on getting the proper value from the database only when applying updates to the server. One possible way to deal with this is to call ClientDataset.ApplyUpdates and ClientDataset.Refresh every time you Post a record. However, doing this means increase bandwith consumption and limits your ability to run the client while disconnected from the network - both key features in multi-tier applications.

If you must use autoincrement fields in your application, this article will show you how to get it done.

Single table setup

We will start by creating a simple Paradox table that has an ID field of type AutoInc, and a Name field of type Alpha, with a size of 25 characters. Once you have the table created, you can drop a TDatasource, TClientDataset, TDatasetProvider, TTable, and TDBGrid down on a form. Hook up the properties to link all of the components together. If you need more help on how to do this, take a look at the paper "Using ClientDataset as a Replacement for Cached Updates".

If you've done nothing but create the simple application by hooking the components together, the first thing you'll notice when you run this application is that if you insert multiple records without specifying a value for the primary key, you will get a "Key Violation" error. This is the proper error when you think about it. How can you add two records with the same value (in this case, NULL) and have a unique constraint be honored? You can't.

This leads us to creating a temporary ID on the client so we can add multiple records to the ClientDatset without getting the "Key Violation" error. To do this, we will declare FID as an integer in the form, and make sure it assigns negative, unique IDs to the ClientDataset as needed. We can do this by assigning an OnNewRecord event to the ClientDataset, with code code similar to the following:

procedure TForm1.ClientDataSet1NewRecord(DataSet: TDataSet);
begin
  ClientDataset1ID.AsInteger := FID;
  Dec(FID);
end;
Of course, doing this means that we need to be able to set the value of the ID field, so set the ClientDataset.TField.ReadOnly property to false for the autoinc field.

Applying Updates

The update process starts the same way that most updates start in DataSnap: with a call to ClientDataset.ApplyUpdates. The code looks like this:
procedure TForm1.ApplyUpdatesButtonClick(Sender: TObject);
begin
  if ClientDataset1.ApplyUpdates(0) = 0 then
  begin
    ClientDataset1.Refresh;
    FID := -1;
  end;
end;
DataSnap is smart enough to deal with TAutoIncField types during the resolving phase. It simply excludes these fields from the resulting SQL that is generated. In this case, the SQL that gets executed would look similar to this:
insert into "autoinc.db" ("Name") values (?)

The question mark character will be replaced with the proper value when the SQL statement gets executed.

Note: You will need to be sure that TDatasetProvider.ResolveToDataset is set to false (the default) if you want the TAutoIncField to be automatically ignored.

Note: Note the call to ClientDataset.Refresh in the code above. It is used to retrieve the autoinc values with the values that were assigned to the table into the ClientDataset. The TDatasetProvider.Options.poAutoRefresh flag should make the call to Refresh obsolete when it gets implemented. Also, if your database server supports it (e.g. InterBase generators or MS SQL @@identity), you can set DatasetProvider.Options.poPropogateChanges to true and set DeltaDS.FieldByName('ID').NewValue to the new autoinc value in DatasetProvider.BeforeUpdateRecord or DatasetProvider.AfterUpdateRecord. Since we can't accomplish this with a simple one-table Paradox sample, we will use the Refresh method instead. You could also set up a separate key table that keeps track of the IDs assigned to a specific table and use the same technique.

Master/Detail setup

If you are linking a master and detail table together on the autoinc field, you need to provide a way for the detail table to update the temporary IDs it has in the Delta with the new values the master was just assigned from the database. Simply setting DatasetProvider.Options.poCascadeUpdates to true is not sufficient, as the temporary value in the Delta is not updated with the new value from the database.

Using MSSQL, you can execute a query to retrieve the value of the last identity record inserted by issuing a "select @@identity" query. The master table already has the new value in the database, so we need to change the MasterIDs of the detail table in the BeforeUpdateRecord event. This can be accomplished with code like this:

procedure TAutoIncMDForm.DataSetProvider1BeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TClientDataSet; UpdateKind: TUpdateKind;
  var Applied: Boolean);
begin
  if (UpdateKind = ukInsert) and (SourceDS = qryDetail) and
     (DeltaDS.FieldByName('MasterID').NewValue < 0) then then
  begin
    if DeltaDS.BOF then
    begin
      qryIdentity.Close;
      qryIdentity.Open;
    end;
    DeltaDS.FieldByName('MasterID').NewValue := qryIdentity.Fields[0].Value;
  end;
end;
We only want to replace the MasterID field in the detail table if we're inserting a master and detail record at the same time. If we already have a master record with a permanent ID assigned, then the MasterID field in the detail table will be populated with the correct value. We only open the qryIdentity TQuery component once per master record because subsequent calls to the query will return the latest identity value inserted, and not the value of the master record's identity value. Finally, we can assign the value of the master table's identity field to the linked field in the detail table. Notice the assignment to NewValue, which is required syntax in the BeforeUpdateRecord event.

Note: Because this sample set DatasetProvider.Options.poPropogateChanges to true, everything that you assign in the DeltaDS via NewValue will be automatically brought back and merged into the ClientDataset. This means that you do not need to issue a call to Refresh after ApplyUpdates.

Errata

For DBMSes that don't map their auto increment fields to TAutoIncField, you need to remove the TField.ProviderFlags.pfInUpdate flag on the source dataset to allow DataSnap to resolve the record back to the database. You're really doing the same thing that DataSnap does automatically for TAutoIncFields by doing this.

A couple of other approaches may be suitable for some applications. First, you could use gernerators and a plain integer field in your database. This would allow you to call into the server to get the values when you are ready to Apply Updates. Simply replace the temporary values on the client before calling ApplyUpdates.

Secondly, you could use GUIDs. Microsoft SQL Server has a way to store GUIDs in binary format, as opposed to storing them as strings like other databases do. By using GUIDs, you can call CoCreateGUID on the client and still be assured that the keys are unique.

The samples created in this article are available in CodeCentral.

About the Author

Dan Miser is a long-time Delphi user, specializing in multi-tier application design and development. He is active on the Borland newsgroups, where he served as a proud member of TeamB before a "tour of duty" as a Borland employee. Dan is a frequent speaker at the Borland Conference, and was a contributing author to "Delphi 5 Developer's Guide" and "Delphi 6 Developer's Guide".

You can visit his Web site at http://www.distribucon.com/.


Server Response from: ETNASC04