Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Relational Databases
Expertise: Beginner
Mar 19, 1997

How do I get the field structures from a table, then load them into a list?

Question:
How do I get the field structures from a table, then load them into a list of some sort?

Answer:

Accessing and Using the TFieldType

While this isn't the hardest thing to do because you can get all the field information you need from the TTable component's default properties, there's more to it than meets the eye.

This is because it's not intuitively obvious how to communicate field types in a meaningful fashion because the field types are defined as elements in an enumerated type called TFieldType. Enumerated types are merely ordinal values, so they're meaningless when you convert them to strings. For example, a String type field is defined in the TFieldType enumerated type as ftString and has an ordinal value of 1.

It's easy to create an array of type String whose indexes are the same as the ordinal positions of the TFieldType elements and whose associated element values hold the text describing the field type. But, there is one big problem if you want to write a generic utility to list field structures: With the arrival of Delphi 2.0 came the support for more data types, so the TFieldType has more elements. To remedy this situation, you have two options:

  1. Write two procedures: one for Delphi 1.0 and the other for Delphi 2.0, writing the appropriate string arrays to handle both types.
  2. Write just one procedure, but add conditional compiler directives to conditionally compile the array definition source code based on whether Delphi 2.0 is the compiler.

There are a few good reasons not to use the first option. To begin with, there is the issue of code redundancy. How you extract field definitions from a TTable is exactly the same in Delphi 2.0 as in Delphi 1.0. The only differences that could be present in the code are the array definitions, so your procedures would pretty much be equivalent.

The second reason is not as clear, but just as important. When you set up the index range in the array, by convention you would use the minimum and maximum ordinal values from the TFieldType enumerated type list. For example, for 16-bit code, you'd make your declaration as follows const FieldTypeNames: array[ftUnknown..ftGraphic] of string = . However, since the 32-bit version of Delphi has more field types, the declaration would read const FieldTypeNames: array[ftUnknown..ftTypedBinary] of string = .

You could argue that we should just use the 32-bit declaration even in 16-bit, or just use discreet numbers in the range definition of the array, and access the appropriate element when you need it. That won't work either, and I'll show you why. Look at the TFieldType definitions below for 16-bit and 32-bit Delphi.

{16-bit TFieldType definition}
TFieldType = (ftUnknown, ftString,   ftSmallint, ftInteger, 
              ftWord,    ftBoolean,  ftFloat,    ftCurrency, 
              ftBCD,     ftDate,     ftTime,     ftDateTime, 
              ftBytes,   ftVarBytes, ftBlob,     ftMemo, 
              ftGraphic);

{32-bit TFieldType definition}
TFieldType = (ftUnknown, ftString,   ftSmallint, ftInteger, 
              ftWord,    ftBoolean,  ftFloat,    ftCurrency, 
              ftBCD,     ftDate,     ftTime,     ftDateTime, 
              ftBytes,   ftVarBytes, ftAutoInc,  ftBlob, 
              ftMemo,    ftGraphic,  ftFmtMemo,  ftParadoxOle, 
              ftDBaseOle, ftTypedBinary);

Notice that I set up the definitions above in sort of a tabular fashion. The idea of just defining the range values of our array with discrete numbers is a good one. But if you compare the fourth lines of both definitions, you'll see that in the 32-bit definition, ftAutoInc was inserted right after ftVarBytes. That makes using a single array definition impossible. We could write the second procedure with no problem, but if we want to follow convention in our declaration, we'd have to put the second procedure into another file. Otherwise, the 16-bit compiler will produce an error when it reaches ftTypedBinary. In fact, if you have procedures built specifically for 32-bit Delphi, you may just have to do that (especially if you include the procedure to extract a table's field structure in a general purpose utility unit.)

The better way to approach this is to use conditional compiler directives to compile the correct code depending upon the version of Delphi you're using.

Conditional Compiler Directives

The Delphi help file defines a conditional directive as the following: "Conditional directives control compilation of parts of the source text, based on evaluation of a symbol following the directive. You can define your own symbols or you can use the Object Pascal predefined symbols." I won't go into detail about them here because I could probably write another entire article on using them in your code. Basically, what happens with conditional directives is this: When the compiler sees a conditional directive statement, it evaluates the conditional logic, which is defined by a conditional symbol. If the logic is true, it compiles one piece of code; if it's false, it compiles another piece of code. For our purposes, we'll be using the predefined conditional symbol Win32, which indicates whether the system is running under Win32.

This ties in nicely with the string arrays we'll be using to associate with the TFieldTypes. Let's take a look at them now:

  {16-bit array constant}
  const FieldTypeNames: array[ftUnknown..ftGraphic] of string =
      ('Unknown', 'String',   'Smallint', 'Integer',
       'Word',    'Boolean',  'Float',    'Currency',
       'BCD',     'Date',     'Time',     'DateTime', 
       'Bytes',   'VarBytes', 'Blob',     'Memo', 
       'Graphic');

  {32-bit array constant}
  const FieldTypeNames: array[ftUnknown..ftTypedBinary] of string =
      ('Unknown', 'String',   'Smallint', 'Integer',
       'Word',    'Boolean',  'Float',    'Currency',
       'BCD',     'Date',     'Time',     'DateTime', 
       'Bytes',   'VarBytes', 'AutoInc',  'Blob', 
       'Memo',    'Graphic',  'FmtMemo',  'ParadoxOle', 
       'DBaseOle', 'TypedBinary');

Nothing strange in these declarations, though I did make them constants. If we want to conditionally compile them, we'd write out the following declaration:

{$IFNDEF Win32}
  {16-bit array constant}
  const FieldTypeNames: array[ftUnknown..ftGraphic] of string =
      ('Unknown', 'String',   'Smallint', 'Integer',
       'Word',    'Boolean',  'Float',    'Currency',
       'BCD',     'Date',     'Time',     'DateTime', 
       'Bytes',   'VarBytes', 'Blob',     'Memo', 
       'Graphic');
{$ELSE}
  {32-bit array constant}
  const FieldTypeNames: array[ftUnknown..ftTypedBinary] of string =
      ('Unknown', 'String',   'Smallint', 'Integer',
       'Word',    'Boolean',  'Float',    'Currency',
       'BCD',     'Date',     'Time',     'DateTime', 
       'Bytes',   'VarBytes', 'AutoInc',  'Blob', 
       'Memo',    'Graphic',  'FmtMemo',  'ParadoxOle', 
       'DBaseOle', 'TypedBinary');
{$ENDIF}

The {$IFNDEF Win32} conditional directive asks this: "Is Win32 not defined?" If it's not defined, the 16-bit array constant is compiled. Otherwise, the 32-bit array constant is compiled. That's pretty straightforward, and allows us to keep a single procedure in one file. And now that we've established a methodology for including both 16- and 32-bit versions of our array in a single procedure, let's jump into the original subject of this article: getting the field structure from a table.

Getting a Table's Field Structure

As I stated above, translating the field types into a recognizable form isn't intuitively obvious. Besides, getting table structure is really easy, just a matter of perusing through a TTable's Fields property to get the field information you need.

For our discussion, all we'll get are the field names, types, sizes and list identity of the key fields. If you want to extend the functionality of the procedure below, look in the help file for a listing of all the TField's properties. Let's look at the code:

{===============================================================================
 Procedure that extracts a table's structure and loads it into a list. Works in
 either 16-bit or 32-bit Delphi.

 ===============================================================================}

procedure GetTableStruct(dbName, tblName : String; const FldList : TStrings);

{Conditional compiler directive. If Win32 is not defined as it is in
Delphi 2.0,
compile the 16-bit constant. Otherwise, compile the 32-bit
array constant.}

{$IFNDEF Win32}
  {16-bit array constant}
  const FieldTypeNames: array[ftUnknown..ftGraphic] of string =
      ('Unknown', 'String', 'Smallint', 'Integer',
       'Word', 'Boolean', 'Float', 'Currency',
       'BCD', 'Date', 'Time', 'DateTime', 'Bytes',
       'VarBytes', 'Blob', 'Memo', 'Graphic');
{$ELSE}
  {32-bit array constant}
  const FieldTypeNames: array[ftUnknown..ftTypedBinary] of string =
      ('Unknown', 'String', 'Smallint', 'Integer',
      'Word', 'Boolean', 'Float', 'Currency',
      'BCD', 'Date', 'Time', 'DateTime', 'Bytes',
      'VarBytes', 'AutoInc', 'Blob', 'Memo', 'Graphic',
      'FmtMemo', 'ParadoxOle', 'DBaseOle', 'TypedBinary');
{$ENDIF}
var
  I, Idx: Integer;
  FldDef: String;
  Tbl   : TTable;
begin

  {Initialize vars}
  FldDef := '';
  Tbl := TTable.Create(Application);

  {Now search through the field defs.}
  with tbl do begin
    Active        := False;
    DatabaseName  := dbName;
    TableName     := tblName;
    Open;
    for I := 0 to FieldCount - 1 do begin
        {Get the properties we want to use for the listing}
      FldDef := Fields[i].DisplayLabel + ' ' + 
                FieldTypeNames[Fields[I].DataType] + ' ' + 
                IntToStr(Fields[I].Size);

        {Now, get the index definitions for the table to determine if a 
       field is a primary key or not.}
      IndexDefs.Update;
      if Fields[I].IsIndexField then begin
        Idx := IndexDefs.Indexof(Fields[I].Name);
        if (Idx > -1) then
          if ixPrimary in IndexDefs[Idx].Options then
            FldDef := FldDef + ' *';
      end;
      FldList.Add(FldDef);
    end;
    Free;
  end;
end;

See anything familiar? It's the conditional compiler directive statement that we took a lot of time discussing above! As you can see, what gets compiled is based on the Win32 conditional symbol.

So what else is going in the code after the conditional directive statement? The basic functionality of this procedure is to extract a table's structure, then write it to a list. With this, I wanted to be able to write to any type of TStrings type list so that I could write to a TMemo, TListBox, another TStrings or even a TComboBox component. The idea was to able to place the field definitions where I felt they'd be appropriate.

Essentially what's happening in the code is that we step through the TTable's Fields property and use the FldDef String var as a temporary holding buffer to load the various TField property values we want to include. After that we see if the field is a primary key field; if it is, we append a '*' to the end of FldDef, which is then written into the list using the Add method. I told you it was easy -- that was the easy part.

Conclusion

We covered a lot of ground with this tip. Actually, I was just going to include two procedures and basically say: Use this procedure in Delphi 1.0, and use this procedure in Delphi 2.0. But I would have been supporting a practice that I really abhor: code redundancy. So I decided to introduce compiler directives as a way of using your code in either version of Delphi. It may make some procedures notationally longer, but think of the time you'll save not having to keep two libraries up to date.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date