How To Work with Databases without BDE in Delphi

Writing full-blown client/server solutions using SQL is quite simple with Delphi. You can also use local databases, of course -- the Delphi components TTable and TQuery handle both. But sometimes, you have very simple needs. For example, you are writing a simple address book or a movie list which should run fast enough even on a low-end computer. In such situations, distributing 2+ megabytes of BDE seems overkill if all you want to do is use a single table without SQL for example.

In this How To you will learn how to write a general purpose but very simple database management system (DBMS). There are no SQL queries, referential integrity, stored procedures, or interesting data types, such as BLOBs. Also, you cannot use the normal TTable or any of the database controls provided by Delphi.

"So why should I use such a system?" you might ask. Well, the purpose of this How To is not to write a BDE replacement. The purpose is to show you that databases exists outside BDE. Personally, I hate when novice Delphi users say that "if this or that cannot be done using a component, it cannot be done." Yes it can. Just read on how to avoid BDE.

If you want to create a database yourself, you have to manage the the database files yourself. Because record layout and contents are determined at run time, we need to use untyped files, which means the BlockRead and BlockWrite procedures. Actually, this is not very difficult. All you have to do is make sure that you read and write to correct amount of data to the correct position.

On the left you can see the overall structure. First few bytes of the file are taken by the header, which contains information about the record layout. After the header comes the actual database data.

In this system, records are of fixed length. This allows easy "jumping" in the database, as not every previous record has to be read to get to a middle point in the database. Of course, this wastes disk space, but this is sufficient for the purposes of this How To.

Records itself consist of the actual field data. The number and types of the fields stored depend on the field structure defined in the header. After the fields comes a flag telling the system if the record has been deleted. When a record is deleted, only a flag is modified to mark that the record is deleted. This is called a "soft delete", and if you know dBase, you should be familar with the concept.

The following general table handling functions are available:

TFieldType = (ftInteger,ftString,ftMoney,ftDate);

Procedure CreateAndOpenDatabase(FileName : String;
                                Fields : Array of TFieldType);
Procedure DeleteDatabase(FileName : String);
Procedure OpenDatabase(FileName : String);
Procedure CloseActiveDatabase(Pack : Boolean);
Procedure PackDatabase;

From the above, you can immediately see that only four data types are supported. You could very easily extend this, however.

The following four routines allow you to manipulate and access table data:

TDisplayProc = Procedure(Items : TStringList;
                         Var Continue : Boolean) of Object;

Procedure AddRecord(FieldData : Array of Const);
Procedure DeleteRecords(MatchingFields : Array of Const);
Procedure UpdateRecord(OldFields,NewFields : Array of Const);
Procedure BrowseRecords(Start,Stop : Integer; DisplayProc : TDisplayProc);

There are also few other routines. Note that I'm not going to explain how all of these routines work internally. As you have the code, you should check it and see how it works. You might also learn something new.

Few things still need explanation. Firstly, you can see that I've used open array parameters as the function parameters. This allows variable number of parameters, which is necessary to make the functions general purpose.

Here is the implementation of the AddRecord procedure:

Procedure AddRecord(FieldData : Array of Const);
Var
  I,J : Integer;
  B   : Boolean;
  V   : TVarRec;

  Procedure AddField(Index : Integer; Data : TVarRec);
  Var
    I : Integer;
    S : ShortString;
    C : Array[1..8] of Byte { Currency };
    D : Double { TDateTime };
    E : Extended;

  Begin
    Case ActiveDatabaseFields[Index+1] of
      ftInteger : Begin
                    I := Data.VInteger;
                    BlockWrite(ActiveDatabase,I,SizeOf(I));
                  End;
      ftString  : Begin
                    If (Data.VString = nil) Then S := ''
                    Else Begin
                      I := StrLen(PChar(Data.VString));
                      Move(Data.VString^,S[1],I);
                      S[0] := Char(I);
                    End;
                    BlockWrite(ActiveDatabase,S,SizeOf(S));
                  End;
      ftMoney   : Begin
                    If (Data.VCurrency = nil) Then FillChar(C,SizeOf(C),0)
                    Else Move(Data.VCurrency^,C,SizeOf(C));
                    BlockWrite(ActiveDatabase,C,SizeOf(C));
                  End;
      ftDate    : Begin
                    If (Data.VExtended = nil) Then D := 0
                    Else Begin
                      E := Data.VExtended^;
                      D := E;
                    End;
                    BlockWrite(ActiveDatabase,D,SizeOf(D));
                  End;
    End;
  End;

Begin
  J := ActiveDatabaseFieldCount;
  Seek(ActiveDatabase,FileSize(ActiveDatabase)); { append }
  V.VInteger := 0; { or "null" }
  For I := 0 to J-1 do Begin
    If (I > High(FieldData)) Then AddField(I,V)
    Else AddField(I,FieldData[I]);
  End;
  B := False;
  BlockWrite(ActiveDatabase,B,SizeOf(B)); { deleted field }
End;

Here are few thing you should note. Firstly, our system doesn't support "null" values. Instead, null values are just plain zeroed values.

Secondly, the AddField nested procedure implementation is particularly interesting. Because we are using open array parameters here, we must use a TVarRec. Internally, open array parameters are stored as arrays of TVarRecs. TVarRec is internally a variable record, which can contain values whose types must be determined from the context.

The method for saving a string (a short string) is like this:

ftString  : Begin
              If (Data.VString = nil) Then S := ''
              Else Begin
                I := StrLen(PChar(Data.VString));
                Move(Data.VString^,S[1],I);
                S[0] := Char(I);
              End;
              BlockWrite(ActiveDatabase,S,SizeOf(S));
            End;

Here, I could use a "S := String(Data.VPChar)" if "S" were a long string. However, I made this string saving a bit more difficult so that you can learn something new -- here you can see how to do what "StrPas" does internally. The BlockWrite call is also easier to understand this way.

The "money" type handling is also interesting:

ftMoney   : Begin
              If (Data.VCurrency = nil) Then FillChar(C,SizeOf(C),0)
              Else Move(Data.VCurrency^,C,SizeOf(C));
              BlockWrite(ActiveDatabase,C,SizeOf(C));
            End;

Here, I first thought that I could use a "C : Currency" type variable, but: variables of type Currency cannot be passes as variable parameters! (This is because Currency type is a calculated value.) Thus, I made an equal size array (8 bytes), and made a typecast.

The Date type also required a bit of "hacking":

ftDate    : Begin
              If (Data.VExtended = nil) Then D := 0
              Else Begin
                E := Data.VExtended^;
                D := E;
              End;
              BlockWrite(ActiveDatabase,D,SizeOf(D));
            End;

The above is done because TVarRec doesn't support TDateTime (equal to Double) type directly. Instead, we must use an Extended variable first, and then assign the value to a Double.

Of course, the AddField procedure would have been easier to implement if I simply had used a untyped parameter. But hopefully this will teach you something about open arrays - at least it did to me.

The rest of the implementation is pretty boring and is not worth explaining here. Instead, I urge you to look at the code - I've tried to comment it.

However, what is interesting is the usage of the system. Here is how the sample program opens or creates the table:

OpenDatabase(DemoDB);
If (Not DatabaseOpen) Then
  CreateAndOpenDatabase(DemoDB,[ftString,ftInteger,ftMoney]);

Here, an attempt is made to open the table. If this fails, the table is created with a string, integer and money field (corresponding to name, age and salary information in the sample app).

When adding a new value to the table, the following is used:

AddRecord([Name.Text,StrToInt(Age.Text),
           MoneyStringToCurrency(Salary.Text)]);

Here, we simply construct a open array which defines the field data itself. MoneyStringToCurrency is our procedure, and it strips currency and other "non-related" information from a string to leave only a numeric value. (Tip: this a generally useful function.)

When records have been added, it would be nice to display them too. The following does this:

Procedure TForm1.MyDisplayProc(Items : TStringList;
                               Var Continue : Boolean);
Var S : String;
Begin
  S := Items[0]+#9+Items[1]+#9+Items[2];
  RecordList.Items.Add(S);
End;

Procedure TForm1.UpdateRecordList;
Var I : Integer;
Begin
  RecordList.Clear;
  I := GetNumberOfRecords;
  BrowseRecords(1,I,MyDisplayProc);
  Count.Caption := IntToStr(RecordList.Items.Count)+' record(s)';
End;

Here, the BrowseRecords procedure is called to create a list if the records. BrowseRecords calls a "callback" procedure, which adds the field values to a listbox. Note that the listbox is a tabbed list - something Delphi doesn't directly support.

Again, this is one example that almost everything can be done. In this How To you learned how to create a simple DBMS which is sufficient for small applications. Because the BDE is not required, you only need to distribute your EXE file.

Also, you learned how to use open array parameters, and the sample application also demonstrates how to use tabs in a listbox. You might also learn other things, and if so, this How To has reached its goal. As always, have fun with Delphi!