Return to Synature Home         Building a data dictionary in Delphi 1

by Brandon C. Smith

Selected by PC Webopaedia

This article is only of historical interest now, since Delphi 6 introduced dbExpress, marking the beginning of the end for the BDE. The BDE was a fantastic concept, a single engine for accessing many different kinds of databases, from flat file DBF type systems up through high end SQL relational databases. Borland will no doubt continue to support the BDE, but generally speaking, new apps will not be using it. The data dictionary discussed in this article is still present, and still has many of the same problems. To get the kind of data dictionary I was looking for, one has to use the facilities of a high end DB such as Oracle or DB2, and even then it is not a trivial task. This file is my final draft. The published article appears in the April 1996 issue of The Delphi Magazine. The editor made several changes which I think improved the article quite a bit.

The Borland Database Engine (BDE) is a mysterious beast, even after you shell out the extra time and money to obtain the manual. Borland, probably rightly, decided that Delphi should come equipped with its own data base tools, and that the BDE is just there to do the actual work. I suppose if you are new to programming and databases, you'll do much better than someone who is used to working with dBase record numbers, for example. Information hiding is one of those concepts whose time has come, and the Delphi approach to data base programming, like Wirth's original definition of Pascal, is forcing good programming habits on us.

While one can, with some effort, justify a need to work with a record number, I've found that it really isn't that much of a loss. I like to know which record number I'm seeing, but my customers generally don't care. Their attention is on the data not its physical location in the database. By focusing on the tools that Delphi does provide, my attention is also on the data -- the table, the query, and most importantly, the field definitions. These funny animals, TintegerField, TblobField, and so forth, are the key to database programming in Delphi.

But if Tfield is the key, why do you have to go through loops to get at them? Why aren't they simply built into the visual components like TDBedit? Perhaps the answer partly lies in another question. How would you like a component bar that listed TDBIntegerEdit, TDBSmallIntEdit, TDBWordEdit, and so? A separate icon for each kind of visual representation for each kind of field? This is a valid approach, and I'm sure some of the after-market component factories are more than happy to provide these tools. Delphi is already a monster. Even Borland cautions that data-aware components use considerably more resources than the other kinds.

Another part of the reason why the Tfields are not visual components is that they serve as the buffer between an application or form and the BDE. On the BDE side, the Tfield is initialized when you open a table or query with an "accurate" definition of what the physical characteristics of the data is. On the application side, the Tfield provides the programmer with validation properties such as minimum and maximum values. Why the scare quotes around "accurate"? Because the BDE has its own notions about what the data is. For example, suppose you had an old dBase file laying around with a field called SIZE, and that when you look at it with the dBase LIST STRUCTURE command you see that this field is a number, length 10, decimal 0. Now move over to Delphi, drop a table on a form, link the table to that old dBase file, double click on the table, add all, then go to the object inspector and you find Delphi thinks SIZE is a TfloatField!

At first glance, this is not good at all. However, what did length 10 mean in dBase? It meant that the valid range was 0..999999999. In the old dBase, if you had actually wanted the valid range to be 0..1111111111, then you would have to add in additional code to validate user input. In Delphi, you simply fill in the minimum and maximum properties of this TfloatField. Of course, you'll have to go through considerable effort to make the error message presented to the user say something different from the Delphi default. However, I've found most of Delphi's default error messages perfectly clear.

What's not clear, though, is how to easily get at the various definitions stored in Tfields. You can't get to them with a simple click on a visual component. If, part way through development, you have to change some Tfield properties, you have to go looking for them. Also, although Delphi will let you just define some of the Tfields, the manuals caution against it. Either instantiate all the Tfields for a particular table or let Delphi create the runtime Tfields and do your validation in code. You can also create Tfield definitions on the fly, but I've found this doesn't seem to work right unless you create them all. However, what you can do, after a table is opened, is set Tfield properties on the fly. For example:

MyTable.findfield('LASTNAME').required := true;

This statement will set the required property to true and generate a Delphi warning if a user tries to post a record in which this field is blank. Since findfield returns a Tfield, and required is a property of Tfield, no problem. However, when you get to minvalue and maxvalue, properties of each of the numeric Tfields, you have another animal. You have to typecast to the appropriate kind of Tfield:

TFloatField(MyTable.findfield('SIZE')).minvalue := 4;

This statement will work, but is also very dangerous to use. If the BDE decided SIZE was a Tinteger when it opened the table, then when Delphi gets to this statement, it burps and invalidates the whole table. Even when it does work, you have to be sure to set both minimum and maximum, otherwise your users will see "...valid range is 4 to 0."

Rather than go into how we could use 'is,' 'as' and the case structure to get around this problem, let's back up and take a wider view of the problem. What are we using these Tfields for? To define the characteristics of the data elements in our database. In other words, a data dictionary.

According to the advertising, Delphi version 2, developer's desktop, has a data dictionary which allows you to specify things like minimum and maximum values at design time. Since I've found Delphi version 1 runs fine under Windows 95 and the price of the upgrade a bit more than I was expecting, I don't yet know if this is just another way of describing what you can do with tFields or if this is a whole new entity, a real data dictionary.

What is a "real" data dictionary? When I hear the term, I think back to my mainframe days and the thick stacks of paper that were usually called "data element dictionary." Each of the hundreds of pages contained a single data element definition where the data element name, its type, its size and various other pieces of information were presented. "What a waste of paper!" I hear you mumble, especially when you find out that many of the pages are more than half blank. After all, what more can you say about "SIZE" other than it is a number limited to range 4..20?

Quite a bit, when you stop to think about it. In particular, you can describe just exactly what the number stored in this field represents in the real world, why you want it stored in the computer and what you plan to do with it, what name you want the user to see, what hint string you want to display for that field, how it relates to other fields, and so on. "Oh, that's just documentation. Boring, really, let someone else do it. I've got code to write. Just hand me the dictionary and I'll prop it up next to my screen and make sure the validation code works."

But what if the dictionary comes to you as a database, and that you can simply call on it to set up the Tfields? Now we have a bit more than boring old documentation. Now we have documentation that feeds directly into the code making. This is what I think of as a real data dictionary. The remainder of this article describes two applications. The first, DDDICT.DPR, is the data dictionary itself, the second, CONTACTS.DPR, is a sample application that uses a database generated by the data dictionary to modify its Tfields on the fly. The unit DBUtils.pas is common to both programs and is set up as a component, though not used that way in this example. The DictCtrl class contains the pieces that build the dictionary as well as the pieces that enable modifying Tfields. In order to keep down the BDE overhead, when I grab the data dictionary information for the target application, I pull it into a TstringGrid and work on it from there.

DDICT.DPR, the data dictionary itself, generates a dBase table that contains the data element definitions. Generating a blank database is not difficult, though working with the BDE alias scheme is a pain -- more about that later. The following fragment illustrates how to generate a dBase table on the fly:

try

    main.sourceDatabase.close;

    main.SourceDatabase.Params.clear;

    main.SourceDatabase.Params.Add('PATH='+fPathName);

    main.SourceDatabase.open;

    with main.DictTable do begin

      active := false;

      databasename := main.SourceDatabase.databasename;

      tablename := fTableName;

      tabletype := ttdBase;

      with FieldDefs do begin

        clear;

        Add('TABLE_NAME', ftString, 20, false);

        Add('FIELD_NAME', ftstring, 10, false);

        Add('FIELD_TYPE', ftstring, 1, false);

        Add('REQUIRED',   ftBoolean,  0, false);

        { ... more fields as needed }

        end;  {with fielddefs}

      createTable;

      Result := true;

      end;  {with table1}

    except

      on EdatabaseError do

        begin

          MessageDlg('Error attempting to create DD file.',

                   mtInformation, [mbOK], 0);

          Result := false;

        end;

      end;  {except block}

Once the empty data dictionary table is created, we could use the dictionary input form to create each of the fields. However, I find it easier to use the database desktop to do the initial build of my actual application. Another reason to use the desktop to do the initial build is that when I pull in the basic data element information into the data dictionary, I ensure that my dictionary knows what kind of data type the BDE has assigned each field. This way I avoid trying to figure out whether a numeric field with a length of 6 is a TsmallInt, a Tinteger, or a Tfloat. Pulling this basic information is accomplished by code illustrated here. See the program files for more detail.

var
tmpint, thisfield : integer;
tmpstr : string;
FromField : tField;
begin
try
if  {both the dictionary table and the table we are
     importing from are opened successfully}
  then begin with DictTable do begin
     for thisfield := 1 to FromTable.fieldCount -1 do begin
       append;
       findfield('Table_name').text := FromTablename;
       findField('Field_name').text :=
          FromTable.fields[thisfield].fieldname;
       findField('Field_type').text :=
          FieldTypeStr[FromTable.fields[thisfield].datatype];
       FromField := FromTable.fields[thisfield];
       tStringField(findField('Scr_prompt')).value :=
          FromField.DisplayName;
       tbooleanField(FindField('Required')).value :=
          Fromfield.Required;
       { the rest of the fields ....}
       post;
       end;  {for thisfield to fieldcount}
     end;    {with DictTable}
     result := true;
   end  {if was able to open both databases}
   else begin
     {message dlg about could not open both databases already
      done in opendb routines}
     result := false;
     exit;
     end;
   except     { some error occured while trying to import the
               field information}
     on EdataBaseError do begin
     screen.cursor := crDefault;
     MessageDlg('DB error while reading field info...',
        mtInformation, [mbOK], 0);
     result := false;
     end;
   end; {of exceptions}
end;

Once we have the basic information about what the BDE thinks is in our database, we use the DDDICT program to add details to the CONTACT data dictionary. For example, we'll add a hint to FIRSTNAME, we'll check off Required for LASTNAME, and we'll set a minimum and maximum for NumMailTo. Now all we have to do is call on DictCtrl to set the appropriate properties at runtime:

procedure TInputDBForm.FormActivate(Sender: TObject);
begin
  FdataDictName :=
    AddBackSlash(extractfilePath(application.exename))+'datadict.dbf';
  DictCtrl.OpenDictionary(FdataDictName, MyDataBase, DictTable,
    MyQuery, MyDataSource);
  if openDB(MyDataBase, myTable, myQuery, myDataSource,
       ExtractFilePath(application.exename), 'contact.dbf')
     then begin
       MyDataSource.enabled := true;
       MyTable.open;
       DictCtrl.SetCurrentFieldTo('Contact.dbf','FirstName');
       EditFirstName.hint := DictCtrl.hint;
       ShowHint := true;
       DictCtrl.SetCurrentFieldTo('Contact.dbf','LastName');
       MyTable.findfield('LASTNAME').required :=
          DictCtrl.required;
       DictCtrl.setCurrentFieldTo('contact.dbf','NumMailTo');
       TSmallIntField(MyTable.findfield('NumMailTo')).minvalue := DictCtrl.minValue;
       TSmallIntField(MyTable.findfield('NumMailTo')).maxvalue := DictCtrl.maxValue;
       MyTable.edit;
       end
     else begin
       messagedlg('Problem opening database', mtinformation, [mbOK],0);
       end;
end;

The choice of when and where to open the data dictionary depends on your specific application. In this case, doing all the work in the Activate phase makes sense if you need to reopen the database the dictionary applies to each time the form is activated. And, of course, a real application would have try..except and try..finally blocks in addition to the basic error handling shown here.

In addition, the code illustrated above includes a couple of typecasts that are not good programming practice. In the code on disk, in DBUtils.pas, you'll find an initial draft of a procedure called SetUpTable that uses a number of case statements to sort out the typecasting in a safe manner.

However, even if we keep ourselves straight on typecasting, there is the rather sticky mess represented by the Data type mapping tables shown on pages 90 and 91 of the Database Application Developer's Guide which is expanded to six pages (99 to 104) in the BDE User's Guide where it is called "Data type translations" and "IDAPI logical types and driver-specific physical types." Where this mess reaches out and grabs you is when you build an empty database based on the definitions you've got in your dictionary. Suppose we have a field called NumHits that we want to be a large number, so we assigned it a length of 11 when we initially built the dBase table using the database desktop. When we pulled the structure into our data dictionary and started editing, we notice that the BDE has turned this into TfloatType. But we know that NumHits will always be a whole number, so we change the type by clicking on Word in the data types list.

When we try to build an empty database, however, we get the BDE error "capability not supported." Unfortunately, we don't get this when the program executes the place where the FieldDef states that NumHits will be ftWord. Instead, the error occurs when BDE attempts to execute ttable.create. Looking through these tables, I failed to find WORD anywhere. There's Short, Long, Integer, Float, and Number among other variations of expressing a number. Apparently, TwordField is strictly to be used on the application side of the BDE connection -- in other words, only to be used when you create Tfields at design time. You get the same effect by using ftFloatType and setting min and max values to 0 and 65,535, a method that would be clearer to the client than trying to explain that "word" usually refers to the values expressed by two bytes when the significant bit is not used as a sign bit

I tend to think the best place to take care of insuring data types are correct for table types is right in the dictionary editing module. The sample of building an empty database in the code on the disk (in DBUtils.pas), does not sort this out, though it does illustrate how to get the informative "capability not supported" message. Not implemented in the code on disk is a method for generating a Delphi unit to build the database since that was included on a disk with this magazine a few issues back.

Finally, before I close, I'd like to explain how I avoided dealing with the infamous BDE alias. Perhaps you have no problem with the way Borland set up the alias business and made Tdatabase something we're not supposed to have to use. This is all fine and dandy if you are building for yourself on your own machine or you don't mind writing special code to do the BDE configuration at installation, or perhaps giving the user special instructions on how to do that. However, in this data dictionary application, I needed to be able to hop around and grab dictionary data by opening a database in any directory as well as create dictionaries in any directory and, further, create tables based on the dictionary in any directory. Being one who digs around, when I found Tdatabase I figured I'd found the cure. And it is the cure -- just give the database instance a name and ignore the alias field.

The problem with using the alias field of tDatabase is that when you give the database instance an alias name you run the risk of generating spurious errors such as "Duplicate Alias." I knew it was a duplicate. I had just closed the database over in directory x, and now I want to open one here in directory y. No, no, says BDE, not allowed, can't have one alias pointing to two different directories in the same program, even if you close everything down using the Tsession routines. Once the BDE link is started during a run, or set up via the BDE configuration tool, alias 'x' is locked to whatever it is first set to. OK, so we change the alias name. I don't recall what happened then, but that didn't work either. In the end I used the object inspector at design time to give one database the name 'xxx,' and the other the name 'yyy,' and left the alias name blank. The following function illustrates a generic way of opening a database without involving alias considerations. Note that you do have to have each of these components dropped somewhere in your application, and that the alias property of tdatabase must be blank and the database name property of tdatabase must be non-blank. Also, tables created this way will not be listed when you go into the database desktop -- you'll have to navigate to the directory yourself.

function openDB(var whichdb : tdatabase; var whichtable : ttable;
   var whichQuery : tquery; var whichsource : tDataSource;
       const pathname, tablename : string): boolean;
begin
  try
    WhichDB.close;
    WhichDB.Params.clear;
    WhichDB.Params.Add('PATH='+PathName);
    WhichDB.open;
    WhichTable.DatabaseName:= WhichDB.databasename;
    WhichTable.tablename := TableName;
    WhichTable.Active:= True;
    WhichSource.DataSet:= WhichTable;
    WhichQuery.databaseName := WhichDB.databasename;
    WhichQuery.dataSource := WhichSource;
    WhichQuery.close;
    WhichQuery.sql.clear;
    WhichQuery.params.clear;
    result := true;
  except
    on EdataBaseError do begin
      screen.cursor := crDefault;
      MessageDlg('Could not open '+pathname + ' '+tablename, mtInformation, [mbOK], 0);
      result := false;
      end;
    end; {of exceptions}
end;

In conclusion, I'd like to point out that you could use this approach to set up separate data dictionaries so that, for example, the German edition not only has German prompts, but also generates a database with German table and field names. I also included a memo field for inserting the text of help file contents with the idea that different language help files could also be generated on the fly. By centralizing all the data management information in a database dictionary, we assure that future maintenance effort is made much easier.

Delphi provides the tools to create a very useful and powerful linkage between a formal data dictionary database and the tables needed by the application it applies to. While resource files could probably be used to accomplish nearly the same thing, having the data dictionary be a database itself means that it will be easy to produce readable reports and it will be easy to update both the data dictionary and the supported application. The code I've provided is a long way from production, but I hope it will help others who may be looking for these kinds of solutions. I can be reached at main@synature.com.


Return to Synature Home Page
Last update: May 1998.
Copyright © 1997,Brandon C. Smith. All rights reserved.
Comments always appreciated.
This page is from www.synature.com