TClientDataSet, InternalCalc, AutoIncrement, and Key Violations

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

Installing Gnostice eDocEngine in C++ Builder with TRichView

I have been using the TRichView RTF editor in C++ Builder XE. I needed PDF creation for my project, and had been planning to use the Gnostice eDocEngine product, which has a component for exporting from TRichView. However, the Gnostice eDocEngine installation failed for both the TRichView and THtmlViewer components.

I considered other PDF creation libraries mentioned on the TRichView website. However, the llionsoft product does not work with amy versions of C++ Builder since 2006 (according to their website), and the wPDF license prohibits sending PDF files created by it over the Internet (and one of the important functions of my program is emailing the .pdf’s created).  Thus, since I would not be able to email the .pdf’s created by wPDF, that license was unacceptible. Gnostice has a much better license.

In reviewing the error message, it appeared that the Gnostice component was not finding the TRichView component, because TRichView was installed into C++ Builder rather than into Delphi.

The solution was to install TRichView into Delphi, so that Gnostice could find it, but so that C++ Builder could also use it. Sergey Tkachenko (the author of TRichView) helpfully provided this info to do that:

Well, there is a way for installing a Delphi package both for Delphi and C++Builder.

How to do it
1) Uninstall all RichView-related C++Bulder packages. Delete all RichView-related obj hpp and dcu files.
2) Open RVPkgDXE.dproj, right click in the Project Manager, choose “Options”.
In the Options dialog, choose Build configuration (combobox)=”Base”. On the page Delphi Compiler | Output – C/C++, choose C/C++ Output file generation = Generate all C++Builder files. OK to close the dialog. Save the package and install.
Repeat for all RichView packages.
3) In all your projects, change references from RVPkgCBXE to RVPkgDXE and so on.

Differences from the old approach:
– HPP files are not placed in the same directory as pas-files, they are placed in $(BDSCOMMONDIR)hpp (such as Documents and SettingsAll UsersRad Studio8.0hpp)
– OBJ files are not created. Instead, they are assempled in LIB file placed in $(BDSCOMMONDIR)Dcp (such as RVPkgDXE.lib)

In the final point, once you do this, you will have to add the TRichView .lib files into the project manually, since C++ Builder will no longer do that.  That’s inconvenient, but not a deal-killer.

The Gnostice eDocEngine installation for TRichView only works when TRichView is installed in Delphi. Thus, I had to uninstall and reinstall the entire TRichView stack.

I had to remove all of the .bpl, .hpp, etc. files so they wouldn’t be found, and everything installed into C++ Builder was uninstalled using Components / Install

Then, reinstall the entire stack into Delphi, but for each component, be sure to set the ‘Create All C++ Files’ for each one. That creates the .hpp files, etc. in the /Users/Public/Documents/RadStudio/8.0 (for XE) folders.

Most components will require the previously-installed and used components put into the Requires portion of the project. You will know that is needed because, on installation (or sometimes use) you will get an error that a component cannot be installed because it contains a unit that is also used by another component. When you get that error, it means you have to go back and add the other component (which was compiled first) into the required section of the new, later component.

Ultimately, it was possible to install the TRichView eDocEngine connector by installing into Delphi first, and then into C++ Builder (it didn’t work when installing into Delphi and C++ Builder at the same time)

FastReport connectors installed without problem. I could not get the THtmlViewer connector to install using the automatic installation program, but it did install using the same technique — install into Delphi, creating the C++ Builder files. The installation program produces a log file (which is named by the installation program when it fails).

The THtmlViewer component installation program failed. The manual installation went as follows:

Build the gtHtmlVwExpD15.dproj project first. It does not install, and the context menu in Delphi does not offer an Install option. Then, build and then install DCLgtHtmlVwExpD15.dproj . Having created the C++ Builder files, the HTMLViewer connector for eDocEngine worked.

Obviously, this will only work if you have RAD Studio rather than just C++ Builder.

The same technique worked for the DevExpress ExpressPrinting component. The automated install failed because it requires (in the literal sense) the Delphi-only version of the DevExpress libraries. However, I was able to get a manual install to work by first loading the gtXPressExpD15.dproj project (from the Source folder of the eDocEngine installation) into Rad Studio. I changed the project to Activate the Release build, and to create all C++ files. However, the build failed because of the requirement for the Delphi-only library. I therefore removed the reference to that library, and added a reference to dxPSCoreRS15.dcp from the DevExpress Library folder. The build then succeeded. Then, I loaded the DCLgtXPressExpD15.dproj project, activated the Release build, changed the project options to create the C++ files (no need to change the requires), and the Build and then Install succeeded, and I was able to use the component in my Delphi and C++ Builder projects.

For the PDFToolkit (starting with 3), the installation went OK, but compiling a program with a TgtPDFViewer component fails with a slew of link errors, starting with “Unresolved external ‘GdipCloneMatrix’ referenced from c: . . . GTPDF32DXE.LIB . Goolging those functions reveal that they are part of the GDI library. The solution was to add the gdiplus.lib library into the project. That file is in the C:Program Files (x86)EmbarcaderoRAD Studio8.0libwin32releasepsdk folder. Right click on the project, select Add. . ., and pick that file to add to the project. Then, it will compile and run.

As of this writing, the PDFToolkit version 4 installation program does not work. It includes the gtPDFViewer.hpp file, which tries to include files such as System.SysUtils.hpp, System.Classes.hpp, and Vcl.Controls.hpp. None of those files exist. Of course, there are files such as SysUtils.hpp, Classes.hpp, Controls.hpp, and Forms.hpp, and includes for those files would work. However, PDFTooklit version 3 does install correctly.

Update Dec 8, 2011:  PDFToolkit version 4 (4.0.1.105) does the same thing when installed in both C++ Builder XE and C++ Builder XE2, becauses the XE2 installation causes an extra include of the XE2 files EVEN IN XE PROJECTS.  The workaround is not to install the XE2 version, only the XE version.  Then, the file compiles, but the link still fails. According to an email from Gnostice:

Please add the following lib’s into your project before building the Project

(PDF toolkit installation path)PDFtoolkit VCLLibRADXEgtPDFkitDXEProP.lib
(PDF toolkit Installation path)SharedLibRADXEcbcrypt32.lib
(PDF toolkit Installation path)SharedLibRADXEcbgdiplus.lib
(PDF toolkit Installation path)SharedLibRADXEfreetype2.lib
(PDF toolkit Installation path)SharedLibRADXEgtPDF32DXE.lib
(PDF toolkit Installation path)SharedLibRADXEgtusp.lib

With those changes, a project using the PDF Toolkit 4 compiles and links. Hopefully they will come up with a fix for the install problem before my other libraries are ready for use with XE2.

Compiling THtmlViewer to use in C++ Builder XE

The THtmlViewer component is a component that displays HTML in a Delphi/C++ Builder form.  It is also used by the outstanding TRichView component for importing HTML.  It is available under the MIT license, so it can be used in commercial projects.  It is hosted on Google Code : http://code.google.com/p/thtmlviewer/.  It can be downloaded using subversion as described here: http://code.google.com/p/thtmlviewer/source/checkout, and as noted on that page,

# Non-members may check out a read-only working copy anonymously over HTTP.
svn checkout http://thtmlviewer.googlecode.com/svn/trunk/ thtmlviewer-read-only

The author of TRichView recommends that you NOT use the trunk version, but rather branch 11:

svn checkout http://thtmlviewer.googlecode.com/svn/branches/11/ thtmlviewer-read-only

The 2010 Delphi project imports into Delphi XE and compiles, and you can install the resulting package, but the components only show up when running Delphi!

To create the components for C++ projects, you need to create a C++ package, but NOT using File/New/Package — C++ Builder

Instead, Component/Install Component, Install into new package, select the same .pas files used in the Delphi package (basically, all of them in the source folder)
You then need to name the package and choose its save location (for the package folder).  You can give it a description, which will later show up in the Component/Install Packages… dialog

Make sure to specify that you want a C++ Package, not a Delphi package.  Select Finish, and your package will be created, although linking will fail with an error.

You have more work to do before it will work properly.  You need to specify the -LUDesignIDE option to the Delphi compiler — in the Project Options/Delphi Compiler/Compiling/Other options/Additional options to pass to the compiler, include “-LUDesignIDE” (without the quotes).  Be sure to use the correct build configuration at the top of the dialog — you will want a Release build, so select Base or Release.

Also, Delphi needs to know to make the .hpp files, etc.  In the — in the Project Options/Delphi Compiler/Compiling/Output – C/C++ / C/C++ Output file generation, pick “Generate all C++ Builder files (including package libs)” so you get the header files as well as the package lib to install.

Finally, when you try to install the package, you will get an error that it conflicts with a file included in the vclimg150 package.  The solution is to include vclimg150.bpl in the Requires list for the package.  Right-click on Requires, and add vclimg150.bpl (just type the name — you don’t need to browse to the file, and when it shows up in the requires list, it will be vclimg.bpi, even though you typed vclimg150.bpl)

Now, pick the Release build, and build it (In the Project Manager, open up Build Configurations, right click on Release, and select Build)

Then, you need to install it, using Component/Install Packages .

First, save and close your THTMLViewer C++ Project.  Then, WITH NO PROJECTS OPEN, Select Component/Install Packages…  THTMLViewer should not be listed in the design packages check list box.

Click Add… , and go to the directory where your library was placed (this is set under Project/Options for the project that made the THTMLViewer component — by default under Windows 7 and RADStudio XE it is C:UsersPublicDocumentsRAD Studio8.0Bpl .  Select the .bpl package library that you just made, and click OK.

Then, you can create a new C++ Project, and you should be able to select the THtmlViewer component and drop it on the form.  Make a FormCreate handler, containing the line HtmlViewer1->LoadFromString(WideString(“Hello”)); .  Compile the project, and it may complain about missing .h files.  Just browse to the source directory, where the .hpp files should be.  You can select the .hpp file even though RAD Studio is looking for the .h file.  If it compiles and you see “Hello” in the window, you know you are done!

Incidentally, creating the component project under Delphi is easier than under C++ Builder — Delphi automatically recognizes and fixes the vclimg150 problem, presenting it in a dialog box, with “OK” to add the reference and rebuild the component.  Also, Delphi automatically installs the component.  However, the component does not install under both C++ Builder and Delphi at the same time (I could not figure out how to do that), and since I don’t really need it under Delphi, I did not pursue it.