Embarcadero’s C++ Builder/Delphi/RAD Studio product includes a TClientDataSet, which allows the programmer to operate an in-memory data table. It is part of a larger functionality for data transfer and storage. For example, you can easily persist the dataset in a binary or XML format and save it in a file or dataset field. You can also load it with data from a persistent database, allow the user to edit the data, and then post the changes back to the database. It not only keeps track of the current state of the data, but also which records have been added, deleted, or changed, and within those records, what the field values were, and can use that information to automatically create the SQL that will update the database. When this was introduced by Borland, they charged $5,000 for a developer to license it, but slowly started liberalizing their licensing as other components (e.g. ADO Recordsets and then ADO.NET) developed the same capability.
However, it has some strange behavior and interactions with other components that can trip you up in strange ways. How it handles AutoInc fields is one of those.
TClientDataSet (CDS) AutoInc fields can operate in one of two ways, depending (generally) on whether you have used a TDataSetProvider to load data into the CDS. If you have not, then when a new record is posted to the CDS, a new value for AutoInc, starting at 1, will be put into the AutoInc field. This happens even if AutoGenerateValue is arNone, and regardless of the AutoInc fields ReadOnly flag. Even if you put your own value into the AutoInc field, your value will be overwritten with the new, auto incremented value. If there is a way to stop this behavior, other than loading data from a TDataSetProvider (cds->Data = prov->Data), I haven’t found it.
However, when you go to ApplyUpdates(), the generated SQL will not have those AutoInc values, which is good, because the persisted database will assign its own AutoInc values.
So, what happens if you first load data from the database, using the TDataSetProvider? Well, something completely different, which is good, because the AutoInc fields will already have values from the persisted database. Now, if you create a new record in the CDS with Append(), the AutoInc field will be Null. That record can be posted to the CDS. However, when you then Append() a second record and attempt to Post() it, your Post() will fail with a “Key Violation” exception, because otherwise there would have been two records with the same AutoInc value in the CDS (i.e., Null).
The workaround for this problem (other than using GUIDs rather than AutoInc fields for your primary key, which might be a great choice if it is an option) is to assign a unique value to the AutoInc field in your AfterInsert handler for the CDS. Something like:
static int AutoIncValue = -1;
DataSet->FieldByName("ID") = AutoIncValue--;
will work (don’t forget to turn off the default ‘ReadOnly’ flag for the AutoInc field in the CDS), and this will generate a series of negative numbers for the AutoInc field in the CDS. (No need to call Edit(), since the DataSet will already be in State dsInsert when the AfterInsert handler is called. ) That way, if the AutoInc field is not generating its own values (again, generally after you have loaded some records using the TDataSetProvider from the persistent database), AutoInc will get a series of progressively negative values which will not clash with any of your records loaded from the database. Just be aware that, if you have NOT loaded any records from your persistent data store, including the case where you loaded a data packet that did not happen to have any records, your AutoInc values will be ignored EVEN IN THE CDS once you call Post() to post the record to the CDS. Thus, your first record, to which you assigned an AutoInc of -1 in AfterInsert, will become 1 after the Post() call. (Of course, it will likely become something else in the persistent data store, unless it is the first record there as well).
This strange behavior makes the CDS harder to use, because you cannot use the AutoInc field to link tables in your briefcase, and have to use another field that won’t be changed by the CDS underneath you. Unfortunately, while the InternalCalc field would seem to be ideal for that purpose, it won’t work, for two reasons.
The first reason, which makes absolutely no sense to me, is that THE PRESENCE OF AN INTERNAL CALC FIELD IN THE CDS CAUSES THE AUTOINC FIELD TO ASSIGN ITS OWN INCREASING VALUES, EVEN WHEN YOU HAVE LOADED DATA FROM A DATA PACKET, AND EVEN IF YOU HAVE ALREADY ASSIGNED A DIFFERENT VALUE TO THAT AUTOINC FIELD! That means that, if the data you loaded happens to have an AutoInc value less than the number of records you are adding to the CDS, you will get a “Key Violation” when you call Post() on the record that matches. For example, if you load a record with “1″ in the AutoInc field, then Append() a record, assign -1 to AutoInc in your AfterInsert handler, and then call Post(), your -1 gets replaced with 1, and the Post() fails, because otherwise there would have been two two records with 1 in the AutoInc field. If you load a record with “2″ in AutoInc, the first new record in the CDS will get the 1, and the second record will cause the “Key Violation”.
The second problem with InternalCalc fields when using them in a briefcase is that they do not get included in the Delta DataSet passed to BeforeUpdateRecord, where you could use them to update any linked tables you have in your briefcase.
Thus, my workaround for these problems:
1) Create an AfterInsert handler for the CDS. Use it to assign a progressively negative number to the AutoInc field. Get the progressively negative number from a centralized routine, so it won’t clash with other progressively negative numbers in other CDSs in your briefcase. Do NOT use the AutoInc field for anything else, and certainly not for linking tables, because, should you happen to load a Data packet which happens not to have any records, your AutoInc values will be overwritten with positive numbers which (probably) match the AutoInc values of records in your persistent data store which you did not load.
2) Create a second field, called “LinkingID”, in your CDS. Make that a fkData so that it will be passed in your DeltaDS to the BeforeUpdateRecord handler and so it does not make AutoInc assign progressively positive numbers, which could clash with the AutoInc of your loaded records, as a fkInternalCalc would. You will also need LinkingID in the DataSet you are loading the data packet from through the TDataSetProvider, but it should NOT be part of your persistent data store. Otherwise, you will get a “Field ‘LinkingID’ not found” Exception when you try to assign the data packet from the provider. A fkCalculated field in the source dataset is ideal for this LinkingID field in the source dataset. Use the OnCalcFields handler of the source dataset to set the value of this field to that of the AutoInc field in the source. If you are loading from pure SQL, you can include something like “ID AS [LinkingID]” in your SELECT clause. Note that this will make the “LinkingID” field act as ReadOnly in the CDS for records that have been loaded from the data store, even though ReadOnly is false for that field, and even though you can edit “LinkingID” in the CDS for newly-inserted records.
3) In your CDS’s AfterInsert, along with assigning your progressively decreasing negative number to the AutoInc field (where it may be destroyed by Post()), also assign it to both your LinkingID field, where it will NOT be destroyed by Post(). Although you cannot edit LinkingID for records loaded from the data store, you CAN edit it for new records. Note that you should not call Edit() and Post() in AfterInsert, but can just assign the new value to LinkingID.
4) Now, you can use LinkingID to link data sets in your briefcase. You can create new records in linked tables, and assign the LinkingID value to foreign keys in those tables. However, remember that your negative LinkingID values for new records in the CDS will NOT wind up in your persistent data store, so the foreign keys will need to be updated when the data is persisted.
5) You can do that in the BeforeUpdateRecord handler of the TDataSetProvider. You should ApplyUpdates() for your master table first. In BeforeUpdateRecord, you will have UpdateKind of ukInsert when you get the inserted record. You can then get DeltaDS->FieldByName(“LinkingID”)->AsInteger, which will be the LinkingID, and which will be negative. The trick is that you have to post the inserted record yourself, using a second DataSet or whatever method you choose, and get the new AutoInc value from the persistent data store, all within the BeforeUpdateRecord call. Now, save both the negative, temporary LinkingID and the new, permanent AutoInc value returned by the persistent data store. If you use a single, centralized (within your app) source of those temporary negative ‘AutoInc’s, you can use a single table or array to store the corresponding permanent AutoInc’s for all of your tables. Don’t forget to set “Applied” true in BeforeUpdateRecord to tell the provider that you have inserted the new record in the permanent data store.
6) For the detail tables, call ApplyUpdates() after the master’s ApplyUpdates(). In their BeforeUpdateRecord, for either ukInsert or ukModify, check the foreign keys for references to your master table. If the foreign key is negative, that means it points to a temporary LinkingID. Replace it with the permanent AutoInc from the data store from the table you got back in step 5. You just look up the negative value and replace it with the corresponding positive value you got back from the data store for that temporary negative LinkingID. (This is why you can’t use the AutoInc field directly instead of the LinkingID — if the CDS changes your negative AutoInc values to positive values, and you had used those positive values for your foreign keys, when you are saving the detail table records you won’t know if the positive foreign key references the primary key value of your new record or the primary key of some other record in the data store)
Or, you can just use GUIDs to assign your primary keys and forget AutoInc fields altogether!
(BTW, another way in which InternalCalc fields and TClientDataSet don’t get along is that, if you have a CDS with an InternalCalc field, you can only call CreateDataSet() once. If you try to call it again, even after setting the CDS->Active = false, you get a “Name not unique in this context” exception. Don’t ask me why that error message makes sense. If there is no InternalCalc field, then no problem calling CreateDataSet() and setting Active false as many times as you want. As noted on Quality Central, Embarcadero doesn’t consider this behavior (or the non-helpful error message) a bug).