January 1998

DB->dilemmas

I've worked hard to port my brain from Active X, Visual Basic, and MS Access to VCL, Borland C++Builder, and Interbase with a sprinkling of Paradox. The transition hasn't been easy, but it's been rewarding. In this article, I'll address some of the questions I had to answer in the process. If you have a nagging database question, send it my way at JimBailey@worldnet.att.net. I'll answer as many questions as I can and include some of them in this column.

Where's the database?

It was easier to grasp Borland's principles once I realized Borland is not Microsoft. Actually, after working with Delphi and C++Builder for about a year, it's shocking to see how narrow my perspective was. Borland offers much greater flexibility--along with a different set of problems and headaches. Such is life, I guess.

C++Builder gains database access via the Borland Database Engine (BDE). Visual Basic uses Jet. The BDE uses an alias to identify a database, while Jet uses the exact location, such as C:\Data\MyData.MDB.

Obviously, the BDE must know a database's exact location. C++Builder programs specify an alias name, such as MyDB, when establishing a database link. If the alias is properly configured, the BDE will establish the connection. This technique is handy, because you can move the database to a different directory, a different drive, or different PC without making a single program change. When such changes are registered in the BDE Configuration program, they cascade to every C++Builder program using the affected alias.

Sometimes, however, it's desirable to create an alias at runtime. My first learning application was a single-user food catalog, using Paradox. If I used a pre-configured alias, the database had to be installed in the same directory on every end user's PC. I don't think that's very friendly. I wanted to install the database in a directory relative to the execution file. For example, if my application file was installed in C:\Program Files\Food Catalog\MyProg.EXE, I wanted the database to be in C:\Program Files\Food Catalog\DB. This technique is a holdover from my VB experience, but I still like the idea. To use it, though, I had to create an alias at runtime, because I give the users complete control over where the application is to be installed on their system. The TDatabase object provides that functionality.

To add a TDatabase object to a new project, click the Data Access tab on the VCL control palette and locate the Database component. Double-click on the component to add it to a form.

When a BDE data link is properly established, live data appears in data-aware components, such as grids and text boxes, during design time. It's extremely helpful to see whether the link is working without having to compile, link, and run the application. Configuring the link is easy.

Press [F11] to open the Object Inspector (analogous to the property box in VB and Access). Set the Name property to dbMyDb, then double-click the TDatabase object to view its configuration dialog box, shown in Figure A.

Figure A: Configure your TDatabase object in this dialog box.

Now, type MyData in the Name text box. Doing so sets the DatabaseName property and establishes the BDE alias name.

Set the driver name to Standard, and click the Defaults button. The following text appears in the Parameter Overrides text box, as shown in Figure A:

PATH=
DEFAULT DRIVER=PARADOX
ENABLE BCD=FALSE

Listing A: Setting the database location at runtime

//get the full path and name of current EXE
String Path(ParamStr(0)); 
//find the last back slash
int pos = Path.LastDelimiter("\\"); 
//Strip file name, leaving only the path 
Path.SetLength(pos);
//make sure db object is not connected
dbMyDb->Connected = false; 
//clear current parameters
dbMyDb->Params->Clear(); 
//set properties
dbMyDb->Params->Add "PATH=" + Path + "DB"); 
dbMyDb->Params->Add("DEFAULT DRIVER=PARADOX");
dbMyDb->Params->Add("ENABLE BCD=FALSE");
//reconnect
dbMyDb->Connected = true; 

I recommend a second dataset, because it's almost never practical to sort data for users by an AI value. As a result, the new record probably won't be the last record in the dataset. My preference for the second dataset is a TQuery object with the following SQL property setting:

SELECT MAX(ID) FROM "Company.DB"

Listing B: Locating the last value added

//SELECT MAX(ID) FROM "Company.DB"
qLastID->Active = true; 
//a record was found if true
if(!(qLastID->Bof && qLastID->Eof))
//persistent field value.
{  LastID = qLastIDMAXOFID->AsInteger; 
//toggle Active property to refresh DataSet.
DataSet->Active = false;
//Open the DataSet with fresh data.
DataSet->Active = true; 
TLocateOptions SearchOptions;
      SearchOptions >> loPartialKey >> 
        loCaseInsensitive;
//locate the new record.
      DataSet->Locate("ID",
        LastID,SearchOptions);
}

Table A: Company and Contact table structures.

Table	Field	Type
Company	CoID CoName City	AutoIncrement (Primary Key) CHAR(32) CHAR(24)
Contact	ContactID CoID LastName FirstName	AutoIncrement (Primary Key) Long Integer CHAR(14) CHAR(14)

The CoID (Company ID) field in the Contact table is a foreign key (it corresponds to an existing value in another table) referencing one of the Company table's primary key values. However, you shouldn't force users to enter the company number--this number exists for the convenience of the computer. People, on the other hand, think in terms of company names.

A good solution