TOCBACKFORWARD

Charlie Calvert's C++ Builder Unleashed

- 14 -

Sessions and Relational Real-World Databases

Overview

Now you can take a look at a real relational database in action. The preceding six chapters have really been nothing but a long prelude to this chapter, where all the pieces finally come together.

This chapter features another address book program, but this time it will be based on a relational database. This second database will allow you to add multiple addresses, phone numbers, and e-mail addresses to each name in the address book.

Subjects covered in this chapter include the following:

After you get a look at the address book program, I start a second program called kdAddExplore. This program looks and feels a lot like a miniature version of the Database Explorer. You can use this program to explore the structure of the five tables used in the address book program found in the first half of this chapter.

The main purpose of the kdAddExplore program is to let you see some of the functionality of the global TSession object that is automatically available in all BCB database applications. The Session object is created automatically when you call Application->Initialize at the startup of a database program. This call is found in the program source generated for you by BCB. To view the program source, choose View | Program Source from the main BCB menu. Don't forget to check the readme file on the CD that comes with this book for additional information about the aliases used in this book.

Data in the Real World

The code in this chapter addresses the kinds of problems you find in real-world situations. In particular, the conflict that needs to be resolved is between the rigid, inflexible nature of simple tables and the fluid, kaleidoscope-like nature of information in the real world.

When most people first try to build database programs, they tend to create one simple table, like the one shown in the Address2 program from the preceding chapter. The limitations with that kind of system might well emerge on the first day of use. For example, you might start transferring handwritten addresses into the database. At first, this process might go fairly well, but then you are likely to encounter a situation in which one person has multiple phone numbers or multiple addresses. It's not at all unusual for one person to have three or more e-mail addresses. The Address2 program does not have a good solution for that kind of problem.

In professional settings, this problem can be multiplied many times. For example, I need to track all the Borland offices in the world. This task involves tracking addresses in Germany, England, France, Australia, Hong Kong, Japan, and various other locations throughout the world.

My job puts me in contact with a number of software vendors (ISVs) that use or create Borland tools. Many of these people maintain offices both at home and at their businesses. Some of them frequent certain sites, and others have complex relationships with their companies that I can track only via freehand notes.

As you can see, information in the real world is messy and complex. The Address2 program is simple and straightforward. Many people can make do with simple tools, but others need to have a more sophisticated system.

The kdAdd program found in this chapter is an attempt to resolve the kinds of problems you find in real-world situations. In the form you see it here, it is not quite as polished as the Address2 program. It is, however, much more sophisticated and much more powerful. With some work, it could easily form the basis for a professional-level database used in an office.

Examining the Relational Address Program

The kdAdd program uses five tables called kdNames, kdAddress, kdPhone, kdMemo, and kdEmail. The kdNames table is the master table that "owns" the other four tables. The other tables are detail tables.

When the program first appears, it looks like the image shown in Figure 14.1. As you can see, the program uses a TPageControl with five pages, one for each of the tables. The Address and Phone tables are also shown on the first page so that the user can see them easily. If you want to delete items from either an address or phone number, then you should turn to the respective pages for those items.

The data for the program is kept in the Data directory on the CD that comes with this book. You should create a Standard Paradox alias called CUNLEASHED that points to a copy of this directory, which resides on your hard drive. Refer to the readme file on the CD that comes with this book if you need additional information setting up the alias. The fields for the tables in the database are shown in Table 14.1 through Table 14.5.

FIGURE 14.1. The main screen for the kdAdd program.

Table 14.1. The format for the kdName table.
Table name Type Size Primary index
NameCode + *
FirstName A 30
LastName A 30
Company A 30


Table 14.2. The structure for the kdAdd table.
Table name Type Size Primary index
AddCode + *
Address1 A 30
Address2 A 30
City A 30
State A 3
Zip A 10
NameCode I


Table 14.3. The structure for the kdPhone table.
Table name Type Size Primary index
PhoneCode + *
Description A 15
Number A 25
Ext A 5
NameCode I


Table 14.4. The structure for the kdEMail table.
Table name Type Size Primary index
EMailCode + *
Address A 50
Description A 65
Service A 25
NameCode I


Table 14.5. The structure for the kdMemo table.
Table name Type Size Primary index
MemoCode + *
Description A 25
MemoData M 15
NameCode I


Four constraints are placed on the table in the form of foreign keys called NameCode. They are placed in each of the program's tables except for the master table. These constraints are shown in Figure 14.2.

When you're viewing this information in the Database Explorer (DBX), you should highlight the name of each constraint and then look at the definition page to read the Reference Type and Reference To fields. As you can see from Figure 14.2, these fields show which table the constraint references. The view shown here is of the kdNames table, and it is the master table in these relationships. The riAddNameCode constraint references the kdAdds table.

FIGURE 14.2. By opening the Referential Integrity section of kdNames in DBX, you can see the foreign keys used by the database.

Table 14.6 shows another way to think about the referential integrity in this database.

Table 14.6. The keys in the database shown in table format.
Table Primary key Foreign key
kdName NameCode
kdAdd AddCode NameCode references kdName.NameCode
kdPhone PhoneCode NameCode references kdName.NameCode
kdEMail EMailCode NameCode references kdName.NameCode
kdMemo MemoCode NameCode references kdName.NameCode

As you can see, kdAdd, kdPhone, kdEmail, and kdMemo all have a single foreign key called NameCode that references the NameCode primary key in kdNames. The number in the foreign keys therefore must be a number found in the primary key of the kdNames table. Furthermore, you cannot delete a row from the kdNames table unless all its related fields in the other tables have been deleted first. You also cannot change the value in the NameCode field of kdNames if it will leave records in the other tables "stranded."

NOTE: If you are new to referential integrity, take the time to play with the database and test these constraints. Referential integrity exists to prevent the user from accidentally deleting needed data, and from accidentally entering erroneous data. During the course of this chapter, you should test the restraints on this database so you can see how it establishes rules that help both the programmer and the user maintain a valid set of data.

The referential integrity relationships you see here represent the classic simplest case for constructing a real relational database. Most databases in the real world have more tables and more foreign keys. However, this one has all the elements of a real relational database, and the complexity level is sufficient for programmers who are new to this kind of programming.

Several secondary indexes are used in this program, as shown in Table 14.7 through Table 14.11. Most of these indexes are the result of the foreign keys on the NameCode fields of kdAdds, kdPhone, kdEMail, and kdMemo. However, indexes are also set up on the Company, FirstName, and LastName fields of the kdNames table. Note that idxLastName consists of both the last name and first name of each entry in the kdNames table. This convention is helpful when sorting lists wherein you have more than one entry with a particular last name. For instance, if you have two people with the last name of Jones, creating a key on the last and first names will ensure that Able Jones is listed before Betty Jones. If you further study the tables shown here, you will see that the kdPhone and kdMemo tables also have indexes on their description fields.

Table 14.7. The indexes on the kdNames table.
Table Index fields
idxCompany Company
idxFirstName FirstName
idxLastName LastName, FirstName


Table 14.8. The index on the kdAdds table.
Table Index fields
NameCode NameCode


Table 14.9. The indexes on the kdPhone table.
Index Index fields
idxDescription Description
NameCode NameCode


Table 14.10. The index on the kdEmail table.
Index Index fields
NameCode NameCode


Table 14.11. The indexes on the kdMemo table.
Index Index fields
NameCode NameCode
idxDescription Description


You now know all the core facts about the kdAdds program. After you have laid out the tables as shown here, all the really heavy work in constructing the program is completed. You still have considerable work to do in creating a front end for the program, but the core work for the project is done after you have created the tables and defined the ways in which they interrelate.

The Code for kdAdds

The code for the kdAdds program is shown in Listings 14.1 through 14.8. Notice the custom RC file and the include file for the project. These two tiny files store error strings. In later versions of the program, these files will become larger as more error strings are added to the code. For this release, all I have done is stub out these files so that they can easily be expanded later.

Listing 14.1. The header for the main module in the kdAdds program.

///////////////////////////////////////

// File: Main.h

// Project: kdAdd

// Copyright 
(c) 1997 by Charlie Calvert

//

#ifndef MainH

#define MainH

#include <Forms.hpp>

#include <StdCtrls.hpp>

#include <Controls.hpp>

#include <Classes.hpp>

#include <DBCtrls.hpp>

#include <Mask.hpp>

#include 
<DBGrids.hpp>

#include <Grids.hpp>

#include <Menus.hpp>

#include <ExtCtrls.hpp>

#include <ComCtrls.hpp>

#include <DBCGrids.hpp>

#include <vcl\Buttons.hpp>

class TForm1 : public TForm

{

__published:

  
TMainMenu *MainMenu1;

  TMenuItem *Sorts1;

  TMenuItem *Last1;

  TMenuItem *First1;

  TMenuItem *Company1;

  TPanel *Panel1;

  TDBGrid *FirstLastGrid;

  TPageControl *PageControl1;

  TTabSheet *tsAddress;

  TTabSheet *tsPhone;

  TTabSheet 
*tsMemo;

  TDBGrid *DBGrid2;

  TDBMemo *DBMemo1;

  TDBCtrlGrid *DBCtrlGrid2;

  TLabel *Label1;

  TLabel *Label2;

  TLabel *Label3;

  TLabel *Label4;

  TLabel *Label5;

  TDBEdit *DBEdit1;

  TDBEdit *DBEdit2;

  TDBEdit *City;

  TDBEdit 
*DBEdit4;

  TDBEdit *Zip;

  TTabSheet *tsEMail;

  TDBCtrlGrid *DBCtrlGrid3;

  TLabel *Label12;

  TDBEdit *DBEdit10;

  TLabel *Label13;

  TDBEdit *DBEdit11;

  TLabel *Label14;

  TDBEdit *DBEdit12;

  TMenuItem *Table1;

  TMenuItem *Insert1;

  
TMenuItem *Delete1;

  TMenuItem *Post1;

  TSpeedButton *InsertBtn;

  TSpeedButton *PostBtn;

  TSpeedButton *DeleteBtn;

  TSpeedButton *CancelBtn;

  TMenuItem *Cancel1;

  TMenuItem *Seach1;

  TMenuItem *First2;

  TMenuItem *Last2;

  TMenuItem 
*Company2;

  TMenuItem *Help1;

  TMenuItem *Contents1;

  TMenuItem *N1;

  TMenuItem *About1;

  TTabSheet *AddInfo1;

  TLabel *Label15;

  TLabel *Label16;

  TLabel *Label17;

  TDBEdit *DBEdit13;

  TDBEdit *DBEdit14;

  TDBEdit *DBEdit15;

  
TDBCtrlGrid *DBCtrlGrid4;

  TLabel *Label18;

  TLabel *Label19;

  TLabel *Label20;

  TLabel *Label21;

  TLabel *Label22;

  TDBEdit *DBEdit17;

  TDBEdit *DBEdit18;

  TDBEdit *DBEdit19;

  TDBEdit *DBEdit20;

  TDBCtrlGrid *DBCtrlGrid5;

  
TLabel *Label23;

  TLabel *Label24;

  TLabel *Label25;

  TDBEdit *DBEdit21;

  TDBEdit *DBEdit22;

  TDBEdit *DBEdit23;

  TDBEdit *DBEdit6;

  TDBCtrlGrid *DBCtrlGrid1;

  TLabel *Label6;

  TLabel *Label7;

  TLabel *Label8;

  TDBEdit *DBEdit7;

  
TDBEdit *DBEdit8;

  TDBEdit *DBEdit16;

  void __fastcall Last1Click(TObject *Sender);

  void __fastcall Insert1Click(TObject *Sender);

  void __fastcall Cancel1Click(TObject *Sender);

  void __fastcall Delete1Click(TObject *Sender);

  void 
__fastcall Last2Click(TObject *Sender);

  void __fastcall About1Click(TObject *Sender);

  void __fastcall PostBtnClick(TObject *Sender);

private:

public:

  virtual __fastcall TForm1(TComponent* Owner);

};

extern TForm1 *Form1;

#endif

Listing 14.2. The main module for the kdAdds program.

///////////////////////////////////////

// File: Main.cpp

// Project: KdAdd

// 
Copyright (c) 1997 by Charlie Calvert

//

#include <vcl.h>

#pragma hdrstop

#include "Main.h"

#include "DMod1.h"

#include "Globals.h"

#include "AboutBox1.h"

#pragma resource "*.dfm"

TForm1 
*Form1;

__fastcall TForm1::TForm1(TComponent* Owner)

  : TForm(Owner)

{

  PageControl1->ActivePage = AddInfo1;

}

void __fastcall TForm1::SetupIndex(TObject *Sender)

{

  switch (dynamic_cast<TComponent *>(Sender)->Tag)

  {

    
case 100:

      DMod->NamesTable->IndexName = "idxLastName";

      break;

    case 101:

      DMod->NamesTable->IndexName = "idxFirstName";

      break;

    case 102:

      DMod->NamesTable->IndexName = 
"idxCompany";

      break;

    case 103:

      DMod->NamesTable->IndexName = "";

      break;

  }

}

void __fastcall TForm1::IndexClick(TObject *Sender)

{

  SetupIndex(Sender);

  
DMod->NamesTable->FindNearest(OPENARRAY(TVarRec, ("AAAA")));

}

void __fastcall TForm1::Insert1Click(TObject *Sender)

{

  switch (dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

  {

    case 1:

      
DMod->NamesTable->Insert();

      break;

    case 2:

      DMod->AddressTable->Insert();

      break;

    case 3:

      DMod->PhoneTable->Insert();

      break;

    case 4:

      DMod->EMailTable->Insert();

      
break;

  }

}

void __fastcall TForm1::Cancel1Click(TObject *Sender)

{

  AnsiString S;

  switch (dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

  {

    case 1:

      DMod->NamesTable->Cancel();

      break;

    
case 2:

      DMod->AddressTable->Cancel();

      break;

    case 3:

      DMod->PhoneTable->Cancel();

      break;

    case 4:

      DMod->EMailTable->Cancel();

      break;

    default:

      ShowMessage(GetError(1, S));

  
}

}

void __fastcall TForm1::Delete1Click(TObject *Sender)

{

  AnsiString S;

  AnsiString Msg("Are you sure you want to delete \r %s?");

  Set<TMsgDlgBtn, 0, 8> Btns;

  Btns << mbYes << mbNo;

  switch 
(dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

  {

    case 1:

      Msg = Format(Msg, OPENARRAY(TVarRec,

        (DMod->NamesTableFirstLastCompany->AsString)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == 
ID_YES)

        DMod->CascadingDelete();

      break;

    case 2:

      Msg = Format(Msg, OPENARRAY(TVarRec, (DMod->Address)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == ID_YES)

        DMod->AddressTable->Delete();

      
break;

    case 3:

      Msg = Format(Msg, OPENARRAY(TVarRec, (DMod->Phone)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == ID_YES)

        DMod->PhoneTable->Delete();

      break;

    case 4:

      Msg = Format(Msg, 
OPENARRAY(TVarRec, (DMod->EMail)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == ID_YES)

        DMod->EMailTable->Delete();

      break;



    default:

      ShowMessage(GetError(1, S));

  }

}

void __fastcall 
TForm1::SearchClick(TObject *Sender)

{

  AnsiString S, IndexName;

  if (InputQuery("Search for Name", "Enter Name: ", S))

  {

    IndexName = DMod->NamesTable->IndexName;

    SetupIndex(Sender);

    
DMod->NamesTable->FindNearest(OPENARRAY(TVarRec, (S)));

    DMod->NamesTable->IndexName = IndexName;

  }

}

void __fastcall TForm1::About1Click(TObject *Sender)

{

  AboutBox->ShowModal();

}

void __fastcall 
TForm1::PostBtnClick(TObject *Sender)

{

  DMod->PostAll();

}

Listing 14.3. The header for the data module.

///////////////////////////////////////

// File: DMod1.h

// Project: KdAdd

// Copyright (c) 1997 by Charlie Calvert

//

#ifndef DMod1H

#define DMod1H

#include <Forms.hpp>

#include <DBTables.hpp>

#include 
<DB.hpp>

#include <Classes.hpp>

class TDMod : public TDataModule

{

__published:

  TTable *NamesTable;

  TDataSource *dsNames;

  TTable *AddressTable;

  TTable *PhoneTable;

  TTable *MemoTable;

  TDataSource *dsAddress;

  
TDataSource *dsPhone;

  TDataSource *dsMemo;

  TAutoIncField *PhoneTablePhoneCode;

  TStringField *PhoneTableDescription;

  TStringField *PhoneTableNumber;

  TStringField *PhoneTableExt;

  TIntegerField *PhoneTableNameCode;

  TTable 
*EMailTable;

  TDataSource *dsEmail;

  TAutoIncField *EMailTableEMailCode;

  TStringField *EMailTableAddress;

  TStringField *EMailTableDescription;

  TStringField *EMailTableService;

  TIntegerField *EMailTableNameCode;

  TQuery 
*EMailDeleteQuery;

  TQuery *MemoDeleteQuery;

  TQuery *PhoneDeleteQuery;

  TQuery *AddressDeleteQuery;

  TQuery *NamesDeleteQuery;

  TAutoIncField *MemoTableMemoCode;

  TStringField *MemoTableDescription;

  TMemoField *MemoTableMemoData;

  
TIntegerField *MemoTableNameCode;

  TAutoIncField *NamesTableNameCode;

  TStringField *NamesTableFirstName;

  TStringField *NamesTableLastName;

  TStringField *NamesTableCompany;

  TAutoIncField *AddressTableAddCode;

  TStringField 
*AddressTableAddress1;

  TStringField *AddressTableAddress2;

  TStringField *AddressTableCity;

  TStringField *AddressTableState;

  TStringField *AddressTableZip;

  TIntegerField *AddressTableNameCode;

  TStringField *NamesTableFirstLastCompany;

  
void __fastcall NamesTableCalcFields(TDataSet *DataSet);

private:

  AnsiString __fastcall GetAddress();

  AnsiString __fastcall GetPhone();

  AnsiString __fastcall GetEMail();

public:

  virtual __fastcall TDMod(TComponent* Owner);

  void 
PostAll(void);

  void CascadingDelete(void);

  __property AnsiString Address={read=GetAddress};

  __property AnsiString Phone={read=GetPhone};

  __property AnsiString EMail={read=GetEMail};

};

extern TDMod *DMod;


#endif

Listing 14.4. The code for the data module.

///////////////////////////////////////

// File: DMod1.cpp

// Project: 
KdAdd

// Copyright (c) 1997 by Charlie Calvert

//

#include <vcl.h>

#pragma hdrstop

#include "DMod1.h"

#pragma resource "*.dfm"

TDMod *DMod;

__fastcall TDMod::TDMod(TComponent* Owner)

  : TDataModule(Owner)

{

  
NamesTable->Open();

  AddressTable->Open();

  PhoneTable->Open();

  MemoTable->Open();

  EMailTable->Open();

}

void __fastcall TDMod::NamesTableCalcFields(TDataSet *DataSet)

{

  AnsiString Temp = NamesTableFirstName->Value+ 
" " +NamesTableLastName->Value;

  if (Temp == " ")

    NamesTableFirstLastCompany->Value = NamesTableCompany->Value;

  else

    NamesTableFirstLastCompany->Value = Temp;

}

void DoPost(TDataSet *Data)

{

  if 
((Data->State == dsInsert) || (Data->State == dsEdit))

    Data->Post();

}

void TDMod::PostAll(void)

{

  int i;

  for (i = 0; i < ComponentCount; i++)

    if (dynamic_cast<TTable*>(Components[i]))

      
DoPost((TDataSet*)(Components[i]));

}

void TDMod::CascadingDelete(void)

{

  EMailDeleteQuery->ParamByName("NameCode")->AsInteger =

    EMailTableNameCode->Value;

  EMailDeleteQuery->ExecSQL();

  
MemoDeleteQuery->ParamByName("NameCode")->AsInteger =

    MemoTableNameCode->Value;

  MemoDeleteQuery->ExecSQL();

  PhoneDeleteQuery->ParamByName("NameCode")->AsInteger =

    PhoneTableNameCode->Value;

  
PhoneDeleteQuery->ExecSQL();

  AddressDeleteQuery->ParamByName("NameCode")->AsInteger =

    AddressTableNameCode->Value;

  AddressDeleteQuery->ExecSQL();

  
NamesDeleteQuery->ParamByName("NameCode")->AsInteger =

    NamesTableNameCode->Value;

  NamesDeleteQuery->ExecSQL();

  NamesTable->Refresh();

}

AnsiString __fastcall TDMod::GetAddress()

{

  return 
DMod->AddressTableAddress1->AsString + `\r' +

    DMod->AddressTableAddress2->AsString + `\r' +

    DMod->AddressTableCity->AsString + `\r' +

    DMod->AddressTableState->AsString + `\r' +

    
DMod->AddressTableZip->AsString;

}

AnsiString __fastcall TDMod::GetPhone()

{

  return DMod->PhoneTableDescription->AsString + `\r' +

    DMod->PhoneTableNumber->AsString + `\r' +

    DMod->PhoneTableExt->AsString;

}


AnsiString __fastcall TDMod::GetEMail()

{

  return DMod->EMailTableAddress->AsString + `\r' +

    DMod->EMailTableDescription->AsString + `\r' +

    DMod->EMailTableService->AsString;

}

Listing 14.5. The header for the Globals unit.

///////////////////////////////////////

// File: Globals.h

// Project: KdAdd

// Copyright (c) 1997 by Charlie 
Calvert

//

#ifndef GlobalsH

#define GlobalsH

AnsiString GetError(int ErrNo, AnsiString &S);

#endif

Listing 14.6. The main module for the Globals unit.

///////////////////////////////////////

// File: Globals.cpp

// Project: KdAdd

// Copyright (c) 1997 by Charlie Calvert

//

#include <vcl\vcl.h>

#pragma hdrstop

#include "Globals.h"

#define 
ERR_STRING_SIZE 255

AnsiString GetError(int ErrNo, AnsiString &S)

{

  S.SetLength(ERR_STRING_SIZE);

  LoadString(HINSTANCE(HInstance), 1, S.c_str(), ERR_STRING_SIZE);

  return S;

}

Listing 14.7. The custom RC file for the project. This is a stub to be filled out later.

#include "kderrs.inc"

STRINGTABLE

{

  KDERR_CASESTATEMENT, "Command fell through 
case statement"

}

Listing 14.8. The include file for the project has only one entry. This is a stub to be filled out later.

#define 
KDERR_CASESTATEMENT 1

The pages in the TPageControl are hidden from view in Figure 14.1. In Figure 14.3 through Figure 14.6, you can see the remaining TTabSheet objects.

FIGURE 14.3. The tab sheet for the Address table.

FIGURE 14.4. The tab sheet for the Phone table.

FIGURE 14.5. The tab sheet for the E-mail table.

Using the kdAdd Program

The kdAdd program has the minimal functionality needed to support the user's needs. For example, you can perform Insert, Post, Delete, and Cancel operations on all the tables. Access to these features is provided through both the menus and a speedbar. You can also set the index to the Company, First, or Last fields of the kdNames table. Finally, you can search on either the Company, First, or Last fields.

FIGURE 14.6. The tab sheet for Memo.

Setting Up the Index for kdAdd

One of the hubs around which the kdAdd program revolves involves the code that controls the index for the program. This code is called from several different places in the program. The obvious place to start studying it, however, is in the response method for the menu items that let the user change the index:

void __fastcall TForm1::SetupIndex(TObject *Sender)

{

  switch (dynamic_cast<TComponent *>(Sender)->Tag)

  {

    case 100:

      
DMod->NamesTable->IndexName = "idxLastName";

      break;

    case 101:

      DMod->NamesTable->IndexName = "idxFirstName";

      break;

    case 102:

      DMod->NamesTable->IndexName = 
"idxCompany";

      break;

    case 103:

      DMod->NamesTable->IndexName = "";

      break;

  }

}

void __fastcall TForm1::IndexClick(TObject *Sender)

{

  SetupIndex(Sender);

  
DMod->NamesTable->FindNearest(OPENARRAY(TVarRec, ("AAAA")));

}

The code has three menu choices for changing the index. The first lets the user set the index to the last name; the second, to the first name; and the third, to the company name. All three menu items are attached to the IndexClick method shown here.

IndexClick calls SetupIndex to do the real work. You use the tag property of the TMenuItem that is clicked to decide which index to choose:

switch (dynamic_cast<TComponent *>(Sender)->Tag)

This way, you can call the function with a simple one-line command:

SetupIndex(Sender);

After the index has been set up properly, you can search for the first relevant record in the database:

DMod->NamesTable->FindNearest(OPENARRAY(TVarRec, 
("AAAA")));

The goal of this line is to skip over all the records that contain blanks in the field on which you're searching. For example, if you switch to the company index, you might find 20, 100, or even 5,000 records in the table that have no information in the Company field. To skip over these records, you can search for the first row that begins with the letter A.

Searching for Records

The program also uses the SetupIndex method when it is conducting searches. As I stated previously, you can use three possible menu items to start a search. The first searches on last names; the second, on first names; and the third, on a company name. I have assigned the same values to the Tag fields of these TMenuItems that I did to the Tag fields of the TMenuItems concerned with switching indexes. That way, I can set up the index properly with a simple call to SetupIndex:

  AnsiString IndexName, S;

  if (InputQuery("Search for Name", "Enter Name: ", S))

  {

    IndexName = DMod->NamesTable->IndexName;

    SetupIndex(Sender);

    
DMod->NamesTable->FindNearest(OPENARRAY(TVarRec, (S)));

    DMod->NamesTable->IndexName = IndexName;

  }

As you can see, the code also saves the current index so that the current state of the index can be restored after the search:

  AnsiString IndexName;

  IndexName = DMod->NamesTable->IndexName;

  ... // Code omitted here

  DMod->NamesTable->IndexName = IndexName;

The big point to notice here is how easily you can take care of these chores by using the VCL. BCB makes database programming easy, even when you're working with a fairly complex program.

Inserting Data, Canceling Operations

Because this database has five tables, you have to devise a technique for specifying the name of the table on which you want to perform an insertion, deletion, or post. I use the TPageControl to handle these chores. In particular, I assume that if the user is looking at the Address page, then he or she wants to perform an action on the kdAdds table, and if the user is looking at the first page, then he or she wants to perform an operation on the kdNames table, and so on:

void __fastcall TForm1::Insert1Click(TObject *Sender)

{

  switch (dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

  {

    case 1:

      DMod->NamesTable->Insert();

      break;

    
case 2:

      DMod->AddressTable->Insert();

      break;

    case 3:

      DMod->PhoneTable->Insert();

      break;

    case 4:

      DMod->EMailTable->Insert();

      break;

  }

}

As you can see, I have set the Tag field for each of the pages to a unique value so that I can easily determine the current page:

switch (dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

If the user accidentally makes a wrong decision, he or she can undo the most recent operation on the currently selected table by clicking Cancel:

void __fastcall TForm1::Cancel1Click(TObject *Sender)

{

  AnsiString S;

  
switch (dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

  {

    case 1: DMod->NamesTable->Cancel(); break;

    case 2: DMod->AddressTable->Cancel(); break;

    case 3: DMod->PhoneTable->Cancel(); break;

    
case 4: DMod->EMailTable->Cancel(); break;

    default:

      ShowMessage(GetError(1, S));

  }

}

This system is easy to implement, but it can be a bit confusing to the user when he or she is looking at the first page, which holds information about not only the kdNames table, but also the kdAdds and kdPhone tables. The issue here is that the database itself won't be much fun if you have to flip pages to get even the most basic information about a name. To remedy this problem, I put the Name, Address, and Phone information on the first page but really expect the user to perform edits on these fields by turning to the Address or Phone page.

To enforce this rule, you can set up an options menu that can be turned on and off:

void __fastcall TForm1::SetAddressPhoneClick(TObject *Sender)

{

  SetAddressPhone->Checked = !SetAddressPhone->Checked;

  DBEdit6->ReadOnly = 
SetAddressPhone->Checked;

  DBEdit17->ReadOnly = SetAddressPhone->Checked;

  DBEdit18->ReadOnly = SetAddressPhone->Checked;

  DBEdit19->ReadOnly = SetAddressPhone->Checked;

  DBEdit20->ReadOnly = 
SetAddressPhone->Checked;

  DBEdit8->ReadOnly = SetAddressPhone->Checked;

  DBEdit17->ReadOnly = SetAddressPhone->Checked;

  DBEdit7->ReadOnly = SetAddressPhone->Checked;

}

If you include this code in the program as a response to a TMenuItem called SetAddressPhone, then the user can decide whether the views on the kdAdds and kdPhones table will be editable. By default, this function should be turned off, because it really is better that the user does not input information in these fields.

I should perhaps add that the user does not have to understand that he or she is entering data in separate tables. For example, the user doesn't have to know that the kdAdds and kdPhones tables exist. All he or she has to understand is that inputting information about phones is a separate operation from entering data about addresses or names. This much conceptual background the user must have; otherwise, he or she will not be able to use the tool at all.

The design of the program helps the user by putting each table on a separate page. That way, the user can rely on the page metaphor when thinking about the underlying structure of the database. Providing metaphors for the user is a useful way to simplify the operation of an application.

NOTE: As you might recall, when I first talked about the Address2 program, I said that for many users, a simple flat-file database is best. When I said that, I was thinking particularly about the kinds of problems currently under discussion. Using a relational database takes a certain amount of conceptual ability that some users may not have the patience to master. The actual ideas involved are simple, but many users are still so overwhelmed by the very idea of computers that they can't clear their heads sufficiently to grasp concepts that would be easy for them to assimilate in some other field with which they are more familiar.

It may sound as though I am being overly polite in this note, but I'm trying to state the facts as I see them. Many intelligent people's minds really do become inexplicably opaque when it comes to thinking about computers. This problem will disappear as more and more children grow up using these machines, but for now programmers have to think seriously every time they add any level of complexity to their programs. Programmers will understand relational databases, and so will the small subset of users that are targeted by a program of this type; but it is important to understand that at this point in history, many users will find relational databases perhaps too difficult to understand.

My point is simply that not everyone is ready to work with relational databases. Some people need them, but others will be confused by them. It is good to use relational databases when necessary, but programmers should also be aware that some users might not properly understand them.

Deleting Data: A First Look at the Programs Data Module

Deleting data is the last topic to be covered before moving over to examination of the TDataModule for the application. In fact, you will find that this subject touches on matters that are specific to the program's data module, so it serves as a good segue into new territory.

Here is the method the main form uses to delete data:

void __fastcall TForm1::Delete1Click(TObject *Sender)

{

  AnsiString S;

  AnsiString Msg("Are 
you sure you want to delete \r %s?");

  Set<TMsgDlgBtn, 0, 8> Btns;

  Btns << mbYes << mbNo;

  switch (dynamic_cast<TComponent&>(*PageControl1->ActivePage).Tag)

  {

    case 1:

      Msg = Format(Msg, 
OPENARRAY(TVarRec,

        (DMod->NamesTableFirstLastCompany->AsString)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == ID_YES)

        DMod->CascadingDelete();

      break;

    case 2:

      Msg = Format(Msg, OPENARRAY(TVarRec, 
(DMod->Address)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == ID_YES)

        DMod->AddressTable->Delete();

      break;

    case 3:

      Msg = Format(Msg, OPENARRAY(TVarRec, (DMod->Phone)));

      if (MessageDlg(Msg, 
mtInformation, Btns, 0) == ID_YES)

        DMod->PhoneTable->Delete();

      break;

    case 4:

      Msg = Format(Msg, OPENARRAY(TVarRec, (DMod->EMail)));

      if (MessageDlg(Msg, mtInformation, Btns, 0) == ID_YES)

        
DMod->EMailTable->Delete();

      break;

    default:

      ShowMessage(GetError(1, S));

  }

}

As you can see, this code uses the Tag field of the TTabSheet to determine which table is focused.

The code then pops up a message box asking the user if he or she is sure about continuing with the deletion. In some cases, you can easily give the user an intelligent prompt about the contents of the current field:

Msg = 
Format(Msg, OPENARRAY(TVarRec,

  (DMod->NamesTableFirstLastCompany->AsString)));

In this case, the string garnered from one of the fields of the NamesTable provides all the information the user needs. In fact, the FirstLast field of the database is a calculated field. This calculated field consists of combined information from the First, Last, and Company fields of the kdNames. This combined information uniquely identifies a record so the user can feel secure when deleting it:

 void __fastcall TDMod::NamesTableCalcFields(TDataSet *DataSet)

{

  AnsiString Temp = NamesTableFirstName->Value + " " 
+NamesTableLastName->Value;

  if (Temp == " ")

    NamesTableFirstLastCompany->Value = NamesTableCompany->Value;

  else

    NamesTableFirstLastCompany->Value = Temp;

}

As you can see, this code combines the first and last names into a single string. If the string is not empty, it is shown to the user as if it were a standard field of the database. If the current record has no information in either the first or last field, then the program assumes that the record must contain only company information:

NamesTableFirstLastCompany->Value = NamesTableCompany->Value;

The end result of this system is to show the user records that contain either someone's first or last name or just a company name. This way, you can ask the database to perform double duty as both a way of tracking company names and as a means of tracking the names of people.

This calculated field can be used not only to help with deletions, but also as an index appearing on the extreme left of the main form, as shown in Figure 14.1. The user will never edit this field directly but will use it as a guide to all the nearby records in the database. This kind of index is useful if you're searching for a particular name. For example, I use the database to track the members of my family. As a result, it has lots of Calverts in it. I can use the Last Name search to find the section where the Calverts are stored and then use the index to move back and forth between members of the family.

NOTE: You actually have no guarantee that the string generated by this calculated field will be unique. The program is designed to make sure the NameCode in the kdNames table is unique, but nothing in the program prevents you from entering two identical names, addresses, phone numbers, and so on.

If the user wants to delete an address, you once again need to provide information from several different fields to identify a record uniquely, as you can see in Figure 14.7.

FIGURE 14.7. A prompt that uniquely identifies the address shown in a particular row.

This time, I found it more convenient simply to add to the data module a method that would return a string uniquely identifying a record:

AnsiString __fastcall TDMod::GetAddress()

{

  return DMod->AddressTableAddress1->AsString + `\r' +

    DMod->AddressTableAddress2->AsString + `\r' +

    
DMod->AddressTableCity->AsString + `\r' +

    DMod->AddressTableState->AsString + `\r' +

    DMod->AddressTableZip->AsString;

}

I thought the most sensible approach was to add a read-only property to the data module to aid in retrieving this information:

__property AnsiString Address={read=GetAddress};

You can access this property by writing code that looks like this:

AnsiString S = DMod->Address;

In this particular case, it is arguable that a property doesn't do much for you other than cover the remote contingency that you might change the parameters of the GetAddress method. On the other hand, the property doesn't cost you anything either because the compiler will obviously map any calls to the Address property directly to the GetAddress method. In other words, this programming is very cautious because it is unlikely that the GetAddress method will ever change its spots. However, being conservative when writing code is almost always best, as long as you're not doing serious damage to the performance of your program.

I won't bother discussing any of the means for deleting from the other tables in this program, as they follow the same pattern already established. The key point to grasp is that you have to show several fields to the user to identify a record uniquely. Furthermore, placing the burden of generating these strings on the program's data module is probably best. The reason for doing so is simply that the generation of these strings is dependent on the structure of the tables underlying the program. Isolating all code dependent on these structures inside one object is best so that you won't have to hunt all over your program to find code that might need to be modified because of a change in the program's database.

The Data Module: Cascading Deletes

You have already seen that the data module contains special properties that retrieve strings uniquely identifying certain records. You have also seen the calculated field that generates a string "uniquely" identifying records from the kdNames table. What's left to explore are methods that aid in posting and deleting records.

The issue here is simply that the database contains a number of tables. If the user wants to delete a name from the database, then he or she is really asking to not just delete the name, but also the addresses, phone numbers, and other information associated with that name. This process is known as a cascading delete.

Delphi provides support for cascading deletes via the referential integrity dialog found in the Database Desktop. You can see this option in Figure 14.8.

Many databases do not support cascading deletes, so you can implement it on the client side with just a few lines of code:

void TDMod::CascadingDelete(void)

{

  EMailDeleteQuery->ParamByName("NameCode")->AsInteger =

    EMailTableNameCode->Value;

  
EMailDeleteQuery->ExecSQL();

  MemoDeleteQuery->ParamByName("NameCode")->AsInteger =

    MemoTableNameCode->Value;

  MemoDeleteQuery->ExecSQL();

  PhoneDeleteQuery->ParamByName("NameCode")->AsInteger =

    
PhoneTableNameCode->Value;

  PhoneDeleteQuery->ExecSQL();

  AddressDeleteQuery->ParamByName("NameCode")->AsInteger =

    AddressTableNameCode->Value;

  AddressDeleteQuery->ExecSQL();

  
NamesDeleteQuery->ParamByName("NameCode")->AsInteger =

    NamesTableNameCode->Value;

  NamesDeleteQuery->ExecSQL();

  NamesTable->Refresh();

}


FIGURE 14.8. Choose Cascade or Restrict to get support for cascading deletes in databases that support this feature.

This code looks a bit complicated, in part because some of the lines are long and need to be wrapped. Underneath, however, its structure is very simple. I simply walk down the list of tables in the database, accessing the kdNames table last. I have created a SQL statement for each table that will delete all the records in the table that have a particular NameCode. For example, here are the SQL statements for deleting records in the kdNames or kdAdds tables:

Delete from kdNames where 
NameCode = :NameCode

Delete from KDAdds where NameCode = :NameCode

As I said, this technology is very simple, and the act implementing cascading deletes in your application is trivial. The key to the whole process is simply recognizing that this act is the responsibility of the program's data module. Then you can create a simple method in the data module to handle the logic of the operation, and after about five minutes work, you have a method that can be called from anywhere in your application with a single line of code. (You should, however, take more than five minutes to test your code against sample data to make sure that it is working properly.)

NOTE: You should use the alias system built into the BDE to aid in the process of creating sample data against which you can run tests. In particular, the ideal way to set up this situation is to include a TDatabase object in your database and then attach each of your tables and queries to that single TDatabase object. That way, you can change the alias for the TDatabase object and thereby globally change the data all your tables are accessing. Needless to say, you should also make a dummy copy of your data and place it in a separate database (directory, in Paradox).

I don't use this structure in the current program, but I will in the sections on InterBase programs that are coming up in the next few chapters. For example, the Music program from Chapter 16, "Advanced InterBase Concepts," uses this structure.

The Data Module: Mass Posts

The opposite problem from deleting records occurs when you have to post the data in your program. In these cases, you want to be sure that all the data in all the tables is posted. You wouldn't want to post just the data in the kdNames table and then leave updates to the kdAddress or kdPhones tables stranded.

The methods that handle posting the data look like this:

void DoPost(TDataSet *Data)

{

  
if ((Data->State == dsInsert) || (Data->State == dsEdit))

    Data->Post();

}

void TDMod::PostAll(void)

{

  int i;

  for (i = 0; i < ComponentCount; i++)

    if (dynamic_cast<TTable*>(Components[i]))

      
DoPost(dynamic_cast<TDataSet*>(Components[i]));

}

This code iterates through all the components on the program's data module looking for TTable objects. When the code finds one, it passes the object to a method called DoPost that calls the Post method for the table. The code in DoPost first checks to make sure the table is in dsInsert or dsEdit mode, as it is an error to call Post on a table that is in dsBrowse or some other mode where a Post can't occur.

Notice that I use the ComponentCount property of TDataModule to determine how many components I need to check. I then call dynamic_cast to check whether it is safe to assume the current component is a TTable. The preceding code is actually a bit wasteful of clock cycles, in that it is not necessary to support the overhead of a dynamic_cast after you're sure the cast will succeed:

void TDMod::PostAll(void)

{

  int i;

  for (i = 0; i < ComponentCount; i++)

    if (dynamic_cast<TTable*>(Components[i]))

      DoPost((TDataSet*)(Components[i]));

}

In this case, the first cast has additional overhead but is safe, in that no exceptions will be raised if it fails. The second cast is then guaranteed to succeed, so you can ask the compiler to do it at compile time rather than generate runtime code:

DoPost((TDataSet*)(Components[i]));

Putting Error Strings in String Resources

The other subject worth touching on briefly in regard to this program involves the matter of using string resources to handle error strings. The program has a very small string resource that contains only one string:

#include "kderrs.inc"

STRINGTABLE

{

  
KDERR_CASESTATEMENT, "Command fell through case statement"

}

In a program used in the real world, you would probably want to generate many more error strings.

You can use the following method to retrieve error strings from the program's resource:

#define ERR_STRING_SIZE 255

AnsiString GetError(int ErrNo, AnsiString &S)

{

  S.SetLength(ERR_STRING_SIZE);

  LoadString(HINSTANCE(HInstance), 1, S.c_str(), ERR_STRING_SIZE);

  
return S;

}

This code calls the Windows API routine called LoadString to do the actual grunt work. Several built-in VCL routines also provide this same functionality. Notice that an include file defines the KDERR_CASESTEMENT constant:

#define KDERR_CASESTATEMENT 1

That's all I'm going to say about this error-handling code. If you want more information, you can refer to the discussion of string resources at the end of Chapter 5, "Exceptions."

The About Box

For the sake of completeness, I will wrap up the examination of this program by showing you the About box for the program, shown in Figure 14.9. You can generate About boxes automatically in BCB by choosing File | New, turning to the Forms page of the Object Repository, and choosing About Box, as shown in Figure 14.10. I choose to Copy the code rather than to Inherit or Use it, as you can see in the bottom left of Figure 14.10. Proceeding this way is usually the best and simplest method unless you're sure you have mastered the technology involved with form inheritance.

FIGURE 14.9. The About box for the kdAdds program.

FIGURE 14.10. Creating an About box from the Object Repository.

You've now come to the end of the discussion of the kdAdds program. The rest of the chapter consists of a discussion of two utilities associated with this program. One will transfer data from the Address2 program to the kdAdds program, and the second will show how to use the TSession object to explore the structure of the tables used in the kdAdds program.

Using the kdAddExplore Program

The kdAddExplore program uses the TSession object to explore the tables used in the kdAdds program. The TSession object is often overlooked by VCL programmers because its operation is usually handled behind the scenes without need for intervention. However, if you want to explore the structure of a database at runtime, this is the object to use. In fact, this object might have been more usefully called the TDataExplorer object rather than TSession.

NOTE: The TSession is created automatically by an application on startup. If you're working inside a DLL or in a console mode application, then no default TApplication object exists to start up a session. As a result, you may have to create your own TSession object or else call the Initialize method of a TApplication object. Otherwise, you will not be able to use databases inside your program.

Before you begin the technical part of this chapter, spending a few moments running the kdAddExplore program found on disk might be helpful. This program demonstrates techniques of examining the structure of an existing database.

NOTE: The kdAddExplore program bears a close resemblance to a cut-down version of the Database Explorer (DBX). There is nothing coincidental about this similarity. However, I have never seen the source to DBX nor discussed its structure with its author. My intention here is not to create a substitute for the DBX, but only to provide a simple means of showing the reader how to explore database objects at runtime.

You can use this kind of information to provide utilities for your users or merely to extend your own knowledge of the BDE and VCL. The code also provides an example of how to use the TTreeView object.

Throughout the rest of this chapter, I will use a global TSession object created automatically whenever you include database code in your programs. However, you also can drop down a TSession component on your forms if you would like to look at a visual object. I do not use it here because it would bring no additional functionality to my program. However, you might want to at least view the TSession component and take a look at its password-related properties.

Working with TSession

TSession is used to manage all the database connections within a session. It is a global object that wraps up not only TTable and TQuery, but also TDatabase. A single TSession object might manage many tables, queries, and databases.

The TSession object has two sets of methods. The first has to do with managing a session. The methods encompassing this set of functionality are shown in Table 14.12.

Table 14.12. The session management routines from the TSession object.
Routine Description
Close Closes all databases
CloseDatabase Closes a particular database
Open Opens the session: Active = True;
OpenDatabase Opens a specific database
AddPassword Creates a password for the session
RemovePassword Deletes a password
RemoveAllPasswords Clears the password list
DropConnections Closes all currently inactive databases and datasets


The second set of routines found in TSession are the methods that are of interest in the current context of this book. These routines are shown in Table 14.13.

Table 14.13. Routines for querying a session regarding the available databases, tables, drivers, and stored procedures.
Routine Description
GetAliasNames Gets the list of BDE aliases for a database
GetAliasParams Gets the list of parameters for a BDE alias
GetAliasDriverName Gets the BDE driver for an alias of a database
GetDatabaseNames Gets a list of BDE aliases and TDatabase objects
GetDriverNames Gets the names of installed BDE drivers
GetDriverParams Gets parameters for a BDE driver
GetTableNames Gets tables associated with a database
GetStoredProcNames Gets stored procedures for a database


Routines such as GetDatabaseNames and GetTableNames can retrieve a list of all the available databases and tables on the current system. You can see this data on display inside the kdAddExplore program. For example, all the databases in my system at the time of this writing are visible in the main screen of the kdAddExplore program, as shown in Figure 14.11.

You can open up the nodes of the kdAddExplore program to see a list of all the tables in a particular database, as shown in Figure 14.12. You can then drill down even further to the names of the fields and indexes in a particular table. Finally, you can even see the names of the fields involved in a particular index, as shown in Figure 14.13.

Another set of TSession functionality tapped into by the kdAddExplore program involves looking at the alias found on a system. You can drill down in this dialog to see the parameters passed to a particular alias, as shown in Figure 14.14.

FIGURE 14.11. The kdAddExplore program displays all the available databases on my system.

FIGURE 14.12. The tables in the CUnleashed database, which holds most of the data used in this book.

FIGURE 14.13. The fields and indexes on the kdNames table. Notice that you can drill down to see the fields in each index.

FIGURE 14.14. The kdAddExplore program shows all the aliases on the system. The open branch is from an InterBase database.

The Code for the kdAddExplore Program

Most of the complexity in the kdAddExplore program comes from manipulating the TTreeView object. The code for querying the TSession object is fairly straightforward in most cases; the TTreeView makes the code a bit tricky in places. The source for this program is shown in Listings 14.9 through 14.15.

Listing 14.9. The header file for the main module in the kdAddExplore program.

///////////////////////////////////////

// FILE: Main.h

// PROJECT: KdAddExplore

// Copyright (c) 1997 Charlie Calvert

//

#ifndef MainH

#define MainH

#include 
<vcl\Classes.hpp>

#include <vcl\Controls.hpp>

#include <vcl\StdCtrls.hpp>

#include <vcl\Forms.hpp>

#include <vcl\ExtCtrls.hpp>

#include <vcl\ComCtrls.hpp>

#include <vcl\Menus.hpp>

class TDBNames : 
public TForm

{

__published:    // IDE-managed Components

  TPanel *Panel1;

  TTreeView *TView;

  TImageList *ImageList1;

  TMainMenu *MainMenu1;

  TMenuItem *File1;

  TMenuItem *AliasView1;

  TMenuItem *N1;

  TMenuItem *Exit1;

  void 
__fastcall FormShow(TObject *Sender);

  void __fastcall TViewExpanding(TObject *Sender, TTreeNode *Node,

    Boolean &AllowExpansion);

  void __fastcall Exit1Click(TObject *Sender);

  void __fastcall AliasView1Click(TObject *Sender);

private:        
// User declarations

  void DeleteTemp(TTreeNode *Node);

  void AddTables(TTreeNode *Node);

  void FindFieldsAndIndices(TTreeNode *Node);

  void FindFields(TTreeNode *Node, TTable *Table);

  void FindIndices(TTreeNode *Node, TTable *Table);


public:         // User declarations

  virtual __fastcall TDBNames(TComponent* Owner);

};

extern TDBNames *DBNames;

#endif

Listing 14.10. The main module in the kdAddExplore program.

///////////////////////////////////////

// FILE: MAIN.CPP

// PROJECT: KDADDEXPLORER

// Copyright (c) 1997 Charlie Calvert

//


#include <vcl\vcl.h>

#pragma hdrstop

#include "Main.h"

#include "AliasView.h"

#pragma resource "*.dfm"

#pragma resource "image.res"

TDBNames *DBNames;

__fastcall TDBNames::TDBNames(TComponent* 
Owner)

  : TForm(Owner)

{

}

void __fastcall TDBNames::FormShow(TObject *Sender)

{

  int i;

  AnsiString S;

  TStringList *DBNamesList = new TStringList;

  TTreeNode *Node;

  ImageList1->ResourceLoad(rtBitmap, "FolderShut", 
clPurple);

  Session->GetDatabaseNames(DBNamesList);

  for (i = 0; i < DBNamesList->Count; i++)

  {

    S = DBNamesList->Strings[i];

    Node = TView->Items->Add(TView->Selected, S);

    TView->Items->AddChild(Node, 
"TEMP");

  }

  DBNamesList->Free();

}

void TDBNames::DeleteTemp(TTreeNode *Node)

{

  TTreeNode *TempNode;



  if (Node->Count == 1)

  {

    TempNode = Node->getFirstChild();

    if (TempNode->Text == "TEMP")

      
TempNode->Delete();

  }

}

void TDBNames::AddTables(TTreeNode *Node)

{

  int j;

  AnsiString S(Node->Text);

  TTreeNode *TempNode, *ChildNode;

  TStringList *List = new TStringList;

  DeleteTemp(Node);

  Session->GetTableNames(S, 
"*.*", True, False, List);

  for (j = 0; j < List->Count; j++)

  {

    TempNode = TView->Items->AddChild(Node, List->Strings[j]);

    ChildNode = TView->Items->AddChild(TempNode, "Fields");

    
TView->Items->AddChild(ChildNode, "TEMP");

    ChildNode = TView->Items->AddChild(TempNode, "IndexNames");

    TView->Items->AddChild(ChildNode, "TEMP");

  }

  List->Free();

}

void 
TDBNames::FindFields(TTreeNode *Node, TTable *Table)

{

  int i;

  for (i = 0; i < Table->FieldCount; i++)

    TView->Items->AddChild(Node, Table->Fields[i]->FieldName);

}

void TDBNames::FindIndices(TTreeNode *Node, TTable 
*Table)

{

  int i;

  TStringList *List = new TStringList;

  TTreeNode *ChildNode;

  AnsiString S;

  Table->GetIndexNames(List);

  TIndexDefs *IndexDefs = Table->IndexDefs;

  for (i = 0; i < IndexDefs->Count; i++)

  {

    S = 
IndexDefs->Items[i]->Name;

    if (S.Length() == 0)

      S = "Primary";

    ChildNode = TView->Items->AddChild(Node, S);

    S = IndexDefs->Items[i]->Fields;

    TView->Items->AddChild(ChildNode, S);

  }

  
List->Free();

}

void TDBNames::FindFieldsAndIndices(TTreeNode *Node)

{

  TTable *Table = new TTable(this);

  Table->DatabaseName = Node->Parent->Parent->Text;

  Table->TableName = Node->Parent->Text;

  Table->Open();



  
DeleteTemp(Node);

  if (Node->Count < 1)

    switch (Node->Index)

    {

      case 0:

        FindFields(Node, Table);

        break;

      case 1:

        FindIndices(Node, Table);

        break;

    }

  Table->Free();

}

void 
__fastcall TDBNames::TViewExpanding(TObject *Sender, TTreeNode *Node,

      Boolean &AllowExpansion)

{

  switch (Node->Level)

  {

    case 0:

      if (Node->Count <= 1)

        AddTables(Node);

      break;

    case 2:

      
FindFieldsAndIndices(Node);

      break;

  }

}

void __fastcall TDBNames::Exit1Click(TObject *Sender)

{

  Close();

}

void __fastcall TDBNames::AliasView1Click(TObject *Sender)

{

  AliasForm->Show();

}

Listing 14.11. The header for the unit that displays information on aliases.

///////////////////////////////////////

// FILE: AliasView.h

// PROJECT: KDADDEXPLORER

// Copyright (c) 1997 Charlie Calvert

//

#ifndef AliasViewH

#define AliasViewH

#include <vcl\Classes.hpp>

#include 
<vcl\Controls.hpp>

#include <vcl\StdCtrls.hpp>

#include <vcl\Forms.hpp>

#include <vcl\DBTables.hpp>

#include <vcl\DB.hpp>

#include <vcl\Menus.hpp>

class TAliasForm : public TForm

{

__published:

  TListBox 
*ListBox1;

  TListBox *ListBox2;

  void __fastcall ListBox1Click(TObject *Sender);

  void __fastcall FormShow(TObject *Sender);

private:

public:

  virtual __fastcall TAliasForm(TComponent* Owner);

};

extern TAliasForm *AliasForm;

#endif

Listing 14.12. The main source file of the unit that displays information about the aliases on the system.

///////////////////////////////////////

// FILE: AliasView.cpp

// PROJECT: KDADDEXPLORER

// Copyright (c) 1997 Charlie Calvert

//

#include <vcl\vcl.h>

#pragma hdrstop

#include "AliasView.h"

#pragma 
resource "*.dfm"

TAliasForm *AliasForm;

__fastcall TAliasForm::TAliasForm(TComponent* Owner)

  : TForm(Owner)

{

}

void __fastcall TAliasForm::ListBox1Click(TObject *Sender)

{

  AnsiString 
S(ListBox1->Items->Strings[ListBox1->ItemIndex]);

  Session->GetAliasParams(S, ListBox2->Items);

}

void __fastcall TAliasForm::FormShow(TObject *Sender)

{

  Session->GetAliasNames(ListBox1->Items);

}

Listing 14.13. The header file for the programs data module.

///////////////////////////////////////

// FILE: DMOD1.h

// PROJECT: KDADDEXPLORER

// Copyright (c) 
1997 Charlie Calvert

//

#ifndef DMod1H

#define DMod1H

#include <vcl\Classes.hpp>

#include <vcl\Controls.hpp>

#include <vcl\StdCtrls.hpp>

#include <vcl\Forms.hpp>

#include <vcl\DB.hpp>

#include 
<vcl\DBTables.hpp>

class TDMod : public TDataModule

{

__published:

    TDatabase *Database1;

private:

public:

    virtual __fastcall TDMod(TComponent* Owner);

};

extern TDMod *DMod;


//--------------------------------------------------------------------------

#endif

Listing 14.14. The data module for the main program contains a TDatabase object but no custom code.

///////////////////////////////////////

// FILE: DMOD1.CPP

// PROJECT: KDADDEXPLORER

// Copyright (c) 1997 Charlie Calvert

//

#include 
<vcl\vcl.h>

#pragma hdrstop

#include "DMod1.h"

#pragma resource "*.dfm"

TDMod *DMod;

__fastcall TDMod::TDMod(TComponent* Owner)

  : TDataModule(Owner)

{

}

Listing 14.15. The custom resource file holding the bitmap displayed in the TTreeView.

FolderShut BITMAP 
"FLDRSHUT.BMP"

Using a TTreeView to Display the Databases on a System

When the kdAddExplore program is launched, it first iterates through the available databases on the system and displays to the user in a TTreeView:

void __fastcall TDBNames::FormShow(TObject *Sender)

{

  int i;

  AnsiString S;

  TStringList 
*DBNamesList = new TStringList;

  TTreeNode *Node;

  ImageList1->ResourceLoad(rtBitmap, "FolderShut", clPurple);

  Session->GetDatabaseNames(DBNamesList);

  for (i = 0; i < DBNamesList->Count; i++)

  {

    S = 
DBNamesList->Strings[i];

    Node = TView->Items->Add(TView->Selected, S);

    TView->Items->AddChild(Node, "TEMP");

  }

  DBNamesList->Free();

}

This code needs to have an icon that it can use to spruce up the nodes of the TTreeView. It stores the bitmap used as an icon in a resource file that looks like this:

FolderShut BITMAP "FLDRSHUT.BMP"

FLDRSHUT.BMP is one of the files that ships in the Images subdirectory that is created when you install BCB.

The code loads the bitmap into a TImageList object, which is associated with the TTreeView object via the Images property. You can view the Images property of the TTreeView object in the Object Inspector, and you can use a drop-down list to select the TImageList you want to use with this component. Because only one image appears in this image list, it will automatically be associated with all the nodes of the TTreeView object. In this particular case, that is a satisfactory solution to the problem of how to give some visual interest to the object.

After getting the icon set up, the program retrieves the list of available aliases from the TSession object and stores them inside a TStringList:

TStringList *DBNamesList = new TStringList;


Session->GetDatabaseNames(DBNamesList);

After you have the list of items, you can easily store each one inside a TTreeNode object that can be hung on the TTreeView for display to the user:

TTreeNode *Node;

for (i = 0; i < DBNamesList->Count; i++)

{

  S = DBNamesList->Strings[i];

  Node = TView->Items->Add(TView->Selected, S);

  TView->Items->AddChild(Node, "TEMP");

}

Clearly, the TTreeNode object is the key to working with TTreeViews. This object represents an individual node on a TTreeView. It encapsulates a bitmap and a caption, and can be identified by a unique index number.

Notice that I call two methods of the Items property of the TTreeView object, which in this program is called TView; it is not a type, but an abbreviation for TreeView. The first call adds the name of the database as a node. The next call adds a child to that database node containing a string consisting of the word "Temp". The "Temp" node is never shown to the user but exists only to force the TTreeView to display a plus sign indicating to the user that the node can be expanded further. When it comes time to expand the node, I delete the word Temp and substitute a word that actually displays the name of the one of tables in the database.

The use of the "Temp" node may seem like a nasty kluge at first, but it really is more intelligent to do things this way rather than force the user to sit still while I open all the databases, including those that might need a password, and find all the tables inside them. When you think of things from this perspective, adding a temporary node to each item in the tree suddenly seems very logical. If the user wants to expand a particular node, then you can retrieve detailed information about that particular database. This approach is much better than trying to retrieve information about every table on the system.

Expanding the Nodes of the TTreeView

The program must respond appropriately when the user clicks on a node of the TTreeView object. In particular, if the user is first opening a particular database node, then the code needs to retrieve the list of tables in that database and display them to the user. If the user clicks one of the tables, then a list of fields and indexes must be retrieved, and so on.

An OnExpanding event gets called automatically when the user wants to open a node. Here is how the kdAddExplore program responds to this event:

void __fastcall TDBNames::TViewExpanding(TObject *Sender, TTreeNode *Node,

      Boolean &AllowExpansion)

{

  switch (Node->Level)

  {

    case 0:

      if (Node->Count 
<= 1)

        AddTables(Node);

      break;

    case 2:

      FindFieldsAndIndices(Node);

      break;

  }

}

As you can see, the program calls a method named AddTables if the user is working at the first level of the tree, and it calls a method called FindFieldsAndIndices if the user is working at the second level of the tree. The level the user is currently exploring appears in the Level field of the TTreeNode passed to the OnExpanding event handler.

Before calling AddTables, I check to see if more than one child node already appears on this particular node of the TTreeView. If more than one node exists, then I assume that the database has already been explored and that the node can be opened without any further querying of the system. If only one node exists, then I assume that this is the "Temp" node created in the program's OnShow event, and I call AddTables so that the node can be updated.

Adding a List of Available Tables to the TTreeView

Here is the code that is called when it's time to explore the tables on the system:

void TDBNames::DeleteTemp(TTreeNode *Node)

{

  TTreeNode *TempNode;



  if (Node->Count == 1)

  {

    TempNode = Node->getFirstChild();

    if (TempNode->Text 
== "TEMP")

      TempNode->Delete();

  }

}

void TDBNames::AddTables(TTreeNode *Node)

{

  int j;

  AnsiString S(Node->Text);

  TTreeNode *TempNode, *ChildNode;

  TStringList *List = new TStringList;

  DeleteTemp(Node);

  
Session->GetTableNames(S, "*.*", True, False, List);

  for (j = 0; j < List->Count; j++)

  {

    TempNode = TView->Items->AddChild(Node, List->Strings[j]);

    ChildNode = TView->Items->AddChild(TempNode, 
"Fields");

    TView->Items->AddChild(ChildNode, "TEMP");

    ChildNode = TView->Items->AddChild(TempNode, "IndexNames");

    TView->Items->AddChild(ChildNode, "TEMP");

  }

  
List->Free();

}

The first method shown here, called DeleteTemp, is used to delete the "Temp" nodes created in the OnShow event. The code checks to make sure the string is actually set to "Temp" just to be sure that I haven't stumbled across a database that has only one table in it. The program would, of course, behave badly if it encountered a database with a single table in it called "Temp"!

The next step is for the program to retrieve the list of tables in a database from the Session object:

Session->GetTableNames(S, "*.*", True, False, List);

The code uses the string name from the node passed to the OnExpanded event to query TSession for the proper set of tables. You can look up GetTableNames in the online help for detailed explanation of this call, but most readers should be able to figure out what is going on from this declaration:

void __fastcall GetTableNames(

  const System::AnsiString DatabaseName,

  const System::AnsiString Pattern,

  bool Extensions,

  bool SystemTables,

  
Classes::TStrings* List);

Set Extensions to True if you want to retrieve the extension for a dBASE or Paradox table. Also set Extensions to True if you want to retrieve system tables for SQL databases such as InterBase.

The program is at last ready to add the tables to the TTreeView:

TempNode = TView->Items->AddChild(Node, List->Strings[j]);

ChildNode = 
TView->Items->AddChild(TempNode, "Fields");

TView->Items->AddChild(ChildNode, "TEMP");

ChildNode = TView->Items->AddChild(TempNode, "IndexNames");


TView->Items->AddChild(ChildNode, "TEMP");

This code first adds a table name to the TTreeView:

TempNode = TView->Items->AddChild(Node, 
List->Strings[j]);

It then adds two child nodes labeled Fields and IndexNames to the Table Name. Once again, I resort to the trick of placing a "Temp" node under these two fields to indicate to the user that the nodes can be expanded further. However, I do not actually expand the nodes at this time because the user may not ever want to see the data in question.

Finding Out About Indexes and Fields

To find out about indexes and fields, I abandon the TSession object and instead create a TTable object, since that is the object that can give me the information I need:

void 
TDBNames::FindFieldsAndIndices(TTreeNode *Node)

{

  TTable *Table = new TTable(this);

  Table->DatabaseName = Node->Parent->Parent->Text;

  Table->TableName = Node->Parent->Text;

  Table->Open();



  DeleteTemp(Node);

  
if (Node->Count < 1)

    switch (Node->Index)

    {

      case 0:

        FindFields(Node, Table);

        break;

      case 1:

        FindIndices(Node, Table);

        break;

    }

  Table->Free();

}

The program first queries TTreeView to retrieve the name of the database the user wants to explore and the name of the particular table under examination:

Table->DatabaseName = 
Node->Parent->Parent->Text;

Table->TableName = Node->Parent->Text;

The table is then opened, and the "Temp" node associated with it is deleted:

Table->Open();


DeleteTemp(Node);

I hung the nodes with the labels Fields and IndexNames in a particular order, so I can use the Index field of the current Node to know when to retrieve information on fields and when to retrieve information on indexes:

    switch (Node->Index)

    {

      case 0:

        FindFields(Node, Table);

        break;

      case 1:

        FindIndices(Node, Table);

        break;

    }

The FindFields method is very simple, in large part because it is a leaf node on the tree and does not need to be expanded further:

void TDBNames::FindFields(TTreeNode *Node, TTable *Table)

{

  
int i;

  for (i = 0; i < Table->FieldCount; i++)

    TView->Items->AddChild(Node, Table->Fields[i]->FieldName);

}

I have to do a little coaxing to get the system to give up information on indexes:

void TDBNames::FindIndices(TTreeNode *Node, TTable *Table)

{

  int i;

  TStringList *List = new TStringList;

  TTreeNode *ChildNode;

  AnsiString S;

  Table->GetIndexNames(List);

  TIndexDefs *IndexDefs = Table->IndexDefs;

  
for (i = 0; i < IndexDefs->Count; i++)

  {

    S = IndexDefs->Items[i]->Name;

    if (S.Length() == 0)

      S = "Primary";

    ChildNode = TView->Items->AddChild(Node, S);

    S = IndexDefs->Items[i]->Fields;

    
TView->Items->AddChild(ChildNode, S);

  }

  List->Free();

}

I first get the list of index names from the TTable object and then retrieve the relevant TIndexDefs object. This object contains information on a particular index. I iterated through the Items in the IndexDefs and displayed the information to the user.

You might think that I would have to have a second loop inside the first loop to handle the case in which an index consists of more than one field. However, a second loop is not necessary, because the list is sent to me in the form of a single string, with each index delimited by a semicolon. For example, the primary index of the Items table from BCDEMOS consists of two fields. This information is displayed by TIndexDefs as follows:

OrderNo;ItemNo

Displaying Aliases and Alias Parameters

After all the work involved with displaying information about databases, tables, indexes, and fields, you will find that querying the system about aliases is relatively trivial. One of the main reasons this process is so much simpler is that I use list boxes rather than a TTreeView to display information. TTreeViews are great for the user, but not much fun for the programmer!

Here is the custom code from the unit that displays the alias to the user. All the other code in the unit is generated by the system:

void __fastcall TAliasForm::ListBox1Click(TObject *Sender)

{

  AnsiString S(ListBox1->Items->Strings[ListBox1->ItemIndex]);

  
Session->GetAliasParams(S, ListBox2->Items);

}



void __fastcall TAliasForm::FormShow(TObject *Sender)

{

  Session->GetAliasNames(ListBox1->Items);

}

The program opts to display this information in a separate form, rather than overlaying it on top of the information about databases. This form has two list boxes in it. The first list box holds the various aliases available on the system, and the second list box holds the parameters for the currently selected alias.

When the form is first shown, I call the GetAliasNames method of the global Session object and then pass it the TStrings-based property of TListBox. That's all I need to do to show the user the aliases!

If the user selects a particular item in the first list box, then the ListBox1Click event handler is called. This code initializes a string to the name of the currently selected alias:

AnsiString 
S(ListBox1->Items->Strings[ListBox1->ItemIndex]);

Then it queries the Session object for the list of parameters associated with that object:

Session->GetAliasParams(S, 
ListBox2->Items);

As you can see, this second list is displayed in the second list box, as shown in Figure 14.14.

Data Migration, Temporary Tables, NULL Fields

On the CD that comes with this book, in the Chapter 14 directory, you will find a program that transfers the data from the Address2 program to the kdAdds program. By and large, this code is too mundane to bother including in the text of this book. However, migration poses a common problem, and one that you may need to tackle. If so, you can examine the program.

A TBatchMove component is on the Data Access page of the Component Palette. This object can be used to move data quickly between two tables. For example, it can be very helpful when you want to move data from an abstract dataset to a temporary table that is saved to disk. For example, Paradox is always creating temporary tables called Answer.db. If you want to create tables of that type, then use the TBatchMove component.

Another powerful tool that ships with BCB is the Data Migration Expert. This tool can help you migrate data from one database to another. For example, if you want to move a database from Paradox to InterBase, then you can use the Data Migration Expert. This tool will re- create all the fields, the aliases, and even the referential integrity that you have built into your first database. Needless to say, no tool of this type is going to be perfect, due the nature of the wide variety of databases available. However, I have had surprisingly good luck with it and have found that it can save me hours, if not days, of work.

The particular problem I deal with in this case cannot be aided much by the kinds of tools mentioned in the preceding few paragraphs. The reason for this failure is simply that I need to completely restructure the database used in Address2 before it can be used in kdAdds. In particular, I need to break up the one table used in Address2 into five tables used in the relational model that underlies kdAdds. I cannot automate that kind of process using any tools that ship with BCB, so I do it by hand in the program called AddressToKd (also found on the CD).

The only code from the AddressToKd program that I want to show you in this text is the code that checks to see whether a field is set to NULL:

void 
TDMod::TransferEMail()

{

  if (!AddressTableEMail1->IsNull)

  {

    KDEMailTable->Insert();

    KDEMailTableAddress->AsString = AddressTableEMail1->AsString;

    KDEMailTable->Post();

  }



  if (!AddressTableEMail2->IsNull)

  
{

    KDEMailTable->Insert();

    KDEMailTableAddress->AsString = AddressTableEMail2->AsString;

    KDEMailTable->Post();

  }

}

This code checks to see whether the Email1 and Email2 fields of the Address table from the Address2 program are set to NULL. This process is necessary because trying to access a field that has no data in it whatsoever is sometimes not a good idea. Because you, too, might run across a situation in which you need to do this, I have included the code here. As you can see, all you need to do is call the IsNull method of the TField object. This method returns True if the field is set to NULL, and it returns False otherwise. A NULL field is a blank or empty field that contains no data.

After I'm sure that it's safe to work with the field, I insert a record into the kdEMail table, add the e-mail information to it, and post the record. That's all I'm going to say about the program here in the text of this book, but you can open it up in BCB and explore it at your leisure if you are interested in it.

Summary

In this chapter, you have had a look at relational databases. The core material was divided into two sections. The first section looked at a simple relational database program consisting of five interrelated tables. You saw how these tables are tied together and how to add, delete, insert, and edit records in these tables. Also included is a relatively lengthy discussion of the indexes and keys in the table and of why they were created. Other subjects include searching and storing strings in a string table.

The second half of the chapter was dedicated to an examination of the global TSession object that is created automatically whenever you use the BDE database tools in your program. You saw that you can use this object to query the system about the aliases, databases, and tables it is using. You also saw how to query a TTable object about its fields and indexes.

Other information included in this chapter relates mostly to using standard BCB components such as the TTreeView. You saw that the TTreeView object is very powerful, and it allows you to display information in a way that the user can easily comprehend. Several portions of the chapter focus on the TTreeNode object used to fill in the nodes of a TTreeView. In particular, you saw how to add child nodes to a TTreeView.

TOCBACKFORWARD

©Copyright, Macmillan Computer Publishing. All rights reserved.