I've been working with Delphi since the beginning, with particular emphasis on database development. As a result, it's not often that I encounter a fundamental behavior of data-related components that catches me off guard. Well, it happened last month. And what I observed may be the source of an infrequent yet potentially disastrous bug in a large number of Delphi database applications.
Here is what I observed: The TFields in an open TDataSet changed order at runtime. Specifically, a TField that was originally in the zeroeth position (DataSet.Fields[0]) at the time that I created the TDataset, was in different position in the Fields array a short time later. I discovered this behavior when an exception was raised as a result of my attempting to programmatically read the integer value of the TIntegerField that I created in the first (zeroeth) position in my TDataSet. Between the time I created the TDataSet, and the execution of my code, the integer field had moved.
What happened wasn't magic. The TFields didn't change position by themselves, nor did they change based on anything I did in my code. What caused the TFields to physically change position in the TDataSet was that the user had changed the order of the TColumns in a TDBGrid to which a TClientDataSet was attached (through a TDataSource component, of course). The user's ability to change the position of the TColumns in a TDBGrid, by the way, is the default behavior of a TDBGrid.
Besides being interesting (I kind of assumed that once a DataSet was opened, the position of the TFields in the Fields array was pretty much set), this behavior is the potential source of intermittent exceptions, the type that are particularly difficult to track down. It turns out that this behavior, which I've never seen described before, has been around since Delphi 1. (Actually, I observed this effect in Delphi 7, Delphi 2007, and Delphi 2010. However, I understand that the underlying source of this behavior has been around since Delphi 1, though I have not specifically confirmed this.)
I created a very simple Delphi application that demonstrates this effect. It consists of a single form with one TDBGrid, a TDataSource, a TClientDataSet, and a TButton. The TClientDataSet is bound to the TDBGrid through the TDataSource. The OnCreate event handler of this form looks like the following:
procedure TForm1.FormCreate(Sender: TObject);
begin
with ClientDataSet1.FieldDefs do
begin
Clear;
Add('StartOfWeek', ftDate);
Add('Label', ftString, 30);
Add('Count', ftInteger);
Add('Active', ftBoolean);
end;
ClientDataSet1.CreateDataSet;
end;
Button1, which is labeled Show ClientDataSet Structure, contains the following OnClick event handler.
procedure TForm1.Button1Click(Sender: TObject);
var
sl: TStringList;
i: Integer;
begin
sl := TStringList.Create;
try
sl.Add('The Structure of ' + ClientDataSet1.Name);
sl.Add('- - - - - - - - - - - - - - - - - ');
for i := 0 to ClientDataSet1.FieldCount - 1 do
sl.Add(ClientDataSet1.Fields[i].FieldName);
ShowMessage(sl.Text);
finally
sl.Free;
end;
end;
To demonstrate the moving field effect, run this application and click the button labeled Show ClientDataSet Structure. You should see something like that shown in Figure 1.
Figure 1Next, drag the Columns of the DBGrid to re-arrange the display order of the fields. Click the Show ClientDataSet Structure button once again. This time you will see something similar to that shown in Figure 2.
Figure 2
What is remarkable about this example is that position of the TFields in the TClientDataSet's Fields property changed, such that the field that was in the ClientDataSet.Field[0] position at one point is not necessarily there moments later. And, unfortunately, this is not distinctly a TClientDataSet issue. I performed the same test with BDE-based TTables and ADO-based TADOTables and got the same effect.
It turns out that this behavior has three contributing factors. These are:
- A TDBGrid connects to a DataSet through a DataSource
- The TDBGrid permits the user to move columns at runtime
- The TColumns of the TDBGrid are dynamic, meaning that they are created by the TDBGrid at runtime
If you programmatically refer to the TFields of a DataSet connected to a TDBGrid in which the preceding three conditions exist using a literal indexer, your application can raise an exception, or produce inaccurate results, if the user moves one or more of the TColumns in that TDBGrid. In the following section I will consider several solutions to this problem, as well as share with you the reason for it.
There Are Several Solutions
There are a number of tactics that you can use to eliminating this potential bug from your applications. The first is to define the TColumns of your TDbGrid using persistent TColumns.
Creating persistent TColumns can be done either at design time or runtime. To do it at design time, add the TColumns using the Columns Editor. Display the Columns Editor by right-clicking the TDBGrid and selecting Columns Editor or by clicking the ellipsis in the Columns property of the TDBGrid in Object Inspector. If your DataSet is Active, you can click the Add All Fields button in the Columns Editor toolbar. Otherwise, add one or more TColumns and set their FieldName property in the Property Editor.
To create persistent TColumns at runtime, use the Add or Create methods of the TDBGrid's Columns property. You can then set specific properties of the added or created TColumns.
The second solution, though one that has some negative consequences, it to prevent a user from moving the TColumns of a TDBGrid. This can be done by removing the dgResizeColumn flag from the Options property of the TDBGrid. While this approach is effective, it eliminates a potentially valuable user interface option. Furthermore, removing this flag not only restricts column reordering, it prevents column resizing. (To learn how to limit column reordering without removing the column resizing option, see Zarko Gajic's article How to allow column resize by disable movement (in TDBGrid).
A third solution is to avoid referring to a TDataSet's individual TFields based on a literal indexer to the Fields array property (since this is the essence of the problem). In other words, if you need to refer to the Count TField in the preceding code sample, don't use ClientDataSet1.Fields[2]. So long as you know the name of the TField, you can use something like ClientDataSet1.FieldByName('Count').
There is one rather big drawback to the use of FieldByName, however. Specifically, this method identifies the field by iterating through the Fields property of the TDataSet, looking for a match based on the field name. Since it does this every time you call FieldByName, you should avoid this method in situations where the TField needs to be referenced many times, such as in a loop that navigates a large TDataSet.
If you do need to refer to the field repeatedly (and a large number of times), consider using something like the following code snippet:
var
CountField: TIntegerField;
Sum: Integer;
begin
Sum := 0;
CountField := TIntegerField(ClientDataSet1.FieldByName('Count'));
ClientDataSet1.DisableControls; //assuming we're attached to a DBGrid
try
ClientDataSet1.First;
while not ClientDataSet1.EOF do
begin
Sum := Sum + CountField.AsInteger;
ClientDataSet1.Next;
end;
finally
ClientDataSet1.EnableControls;
end;
end;
The fourth solution is to use the FieldByNumber method of the TDataSet's Fields property. If you already have code that uses an indexer for the Fields array, and it works reliably, so long as the user does not move the TColumns of the bound TDBGrid, there is another solution. Change your code to use the FieldByNumber.
There are two interesting aspects to the use of FieldByNumber. First, you must qualify its reference with the Fields property of your DataSet. Second, unlike the Fields array, which is zero-based, FieldByNumber takes a one-based parameter to indicate the position of the Field you want to reference.
The following is an updated version of the Button1 event handler shown earlier that uses the FieldByNumber method.
procedure TForm1.Button1Click(Sender: TObject);
var
sl: TStringList;
i: Integer;
begin
sl := TStringList.Create;
try
sl.Add('The Structure of ' + ClientDataSet1.Name +
' using FieldByNumber');
sl.Add('- - - - - - - - - - - - - - - - - ');
for i := 0 to ClientDataSet1.FieldCount - 1 do
sl.Add(ClientDataSet1.Fields.FieldByNumber(i + 1).FieldName);
ShowMessage(sl.Text);
finally
sl.Free;
end;
end;
For the sample project, this code produces the following output, regardless of the orientation of the TColumns in the associated TDBGrid. This can be seen in Figure 3.
Figure 3
There is a fifth solution, but this is only available when your TDataSet is a TClientDataSet, like the one in my sample project. In those situations, you can create a clone of the original TClientDataSet, and it will have the original structure. As a result, whichever TField originally appeared in the zeroeth position will still be in that position, regardless of what a user has done to a TDBGrid that displays the TClientDataSet's data.
Note that I am not suggesting that you should reference TFields in a TDataSet using integer literals. Personally, the use of a TField variable that gets initialized through a one-time call to FieldByName is more readable, and is immune to changes in the physical order of a table's structure (though not immune to changes in the names of your fields!).
Wrap Up
There are a couple of final points I want to make. First, the actual structure of the underlying data is not affected. Specifically, if, after changing the order of the TColumns in a TDBGrid, you call the SaveToFile method of a TClientDataSet bound to that TDBGrid, the saved structure is the original (true internal) structure. Similarly, if you assign the Data property of one TClientDataSet to another, the target TClientDataSet also shows the true structure (which is similar to the effect observed when a source TClientDataSet is cloned).
Similarly, changes to the column orders of TDBGrids bound to other tested TDataSets, including TTable and ADOTable, do not affect the structure of the underlying tables. For example, a TTable that displays data from the customer.db sample Paradox table that ships with Delphi does not actually change that table's structure on disk (nor would you expect it to).
The second point is that this is not a bug in either the TDataSet or TDBGrid classes (or TColumn or TField, for that matter). This is how these classes were designed to work. And although this behavior can introduce bugs in your applications, this is because we were not aware of this behavior until now. And, you now know about this behavior, as well as how to prevent it from causing exceptions in your Delphi applications.
The final point comes to us from StackOverflow user Sertac Akyuz, who responded to a question about this behavior that I posted to that Web site. I had inspected the source for both the TDataSet as well as TDbGrid classes, and could not locate where this behavior was originating from. Sertac wrote that the behavior is actually found in the TColumns and TFields classes. Specifically, changing the column position of a dynamic TColumn results in a call to set the corresponding TField's Index property, which affects the position of the TField in its TDataSet's Fields property.
Now that you know that this potential problem exists, under what conditions it can surface, as well as the source of the effect, you should now take a look at your applications to see if you have TDbGrids with dynamic TColumns that the user can move at runtime. If you also refer to the underlying TFields associated with these TColumns using literal indexers to the TDataSet Fields property, you can eliminate potential bugs that will result from your indexer referring to the wrong field at runtime by using one of the solutions I outlined earlier in this article.
Copyright (C) 2010 Cary Jensen. All Rights Reserved
