February 1998

Editing relational tables

by Jim Bailey

I have a company table and a contact table. Both use AutoIncrement fields as the primary key. The contact table also has an INTEGER field named CompanyID. Is there a way to show the CompanyName from the company table with the list of contacts? Further, is it possible to edit the contact's CompanyID field by typing in the linked CompanyName field?

One of the most difficult C++Builder database tasks is editing JOINed tables. Borland included several techniques to handle this problem, but none are as easy to use as the methods I've grown to know and love in Access. As a result, I fretted over this problem for months. I don't like the standard approach, which uses lookup or calculated fields. These solutions require quite a bit of unnecessary code (from my perspective), yet still require the user to edit the primary key. For example, I wanted to design the user interface shown in Figure A.

Figure A: This user interface, which has a TDBIncLookupCombo attached to a TDBGrid, displays two columns in the dropdown list.
[ Figure A ]

Just to get everybody using the same syntax, the Contact Table's CompanyID field is a foreign key into the Company (or foreign) table. Figure B provides a graphical representation of the design; AI indicates the AutoIncrement data type, PK means a Primary Key, and FK means a Foreign Key.

Figure B: This graphic representation shows the relationships between two tables and a TDBIncLookupCombo.
[ Figure B ]

(You can download our sample application, including source code, from www.cobb.com/cpb.)

As you can see, each contact references one company, but the company name isn't included in the contact table--just the number representing that company. This setup is handy for the computer, all right. It saves disk space, and if the Company field changes in the company table, the change cascades to all the contacts referencing the company. But from the user's perspective, this scheme is a horror. Users typically want to deal with text rather than memorize long lists of numbers that represent companies.

My proposal

In my mind, the best solution to this problem employs a TQuery object, an SQL statement JOINing multiple tables, and CachedUpdates. When the user selects a company name from the dropdown list, I want the system to update the foreign key. Is that asking too much? I don't think so, and I finally found a VCL component developer who agrees with me. Meet Jeff Taylor, the brain behind Softouch Development. (Jeff is waving. To wave back, visit www.softouchdev.com.)

You can try this at home

The Softouch Grid Collection includes three combo boxes that work with or without a grid, just the way my old friend MS Access used to do it. The design shown in Figure A employs two data sets: one for the grid and one for the lookup combo box, as defined by the SQL statements shown in Listing A and Listing B, respectively. The grid is the standard VCL TDBGrid; the combo box is Softouch's TDBIncLookupCombo (incremental lookup).

Listing A: Selecting related fields from two tables for presentation in a DBGrid

SELECT p.ContactID, p.CompanyID, 
p.LastName, p.FirstName, c.Company 
FROM "Contact.db" p 
INNER JOIN "Company.DB" c
ON p.CompanyID = c.ID
ORDER BY p.LastName, p.FirstName
Listing B: Setting up a dataset for the TDBIncLookupCombo
SELECT ID, Company, City 
FROM "Company.DB"
ORDER BY Company

Setting the properties

The Datasource property for both the grid and lookup combo references the data set created in Listing A. Other relevant lookup combo property settings are shown in Table A; omit the two GridParent settings when no grid is involved.

Table A: Lookup combo property settings
PropertySetting
DataField CompanyID
DataSource Listing A
LinkField ID
LookupDisplay Company;City
LookupSource Listing B
GridParent->Field Company
GridParent->Grid dgContact (grid name)

Using this configuration, when the user selects a company in the lookup combo, it updates the contact's CompanyID field based on the selected text. That's just what I ordered.

Did you notice how easy it is to show multiple columns? If not, look again at the LookupDisplay property in Table A. And there's more. As the user types, the Softouch component searches the data set for possible matches--functionality similar to that in Quick Books. The TDBIncLookUpCombo (and several other Softouch Grid Collection components) are also compatible with the TDBGrid, TDBCtrlGrid, and InfoPower database grid. (I haven't tested the InfoPower grid.)

All this work and no code?

That's right. No lookup field. No calculated fields. No code. However, you can't complete the task without one line of code. When you select a company name, the Softouch component automatically updates the foreign key--but the BDE doesn't update the data set. When the user posts the record, the old company value magically reappears in the grid, even though the change was accepted. I like persistent fields (as I discussed in the December 1997 "DB->Dilemmas") almost as much as TQuery objects and SQL. I set up an OnChange event on the foreign key's persistent field component, as follows:
void CompanyIDChange(TField *Sender)
{    qContactCompany->Value =  //ForeignKey
         qCoListCompany->Value;  //PrimaryKey
}
Translated, this code means "Take the (new) company name value from the Lookup Combo and update the grid's data set." qContact is the name of the grid's TQuery object, and qCoList is the name of the Lookup Combo's TQuery.

 

But I can't use a lookup

It's not always practical to use a lookup combo with long selection lists. The system can't always keep up with the keyboard. What are my choices?

There are, of course, lots of choices. And there are other reasons the lookup combo may not work. For example, I'm a big Interbase fan--but the Softouch lookup combo doesn't work on an Interbase query because it can't find the requisite unique index. (The component does work in Interbase in conjunction with a properly indexed TTable.) I was a little disappointed at this, but the TDBIncCombo offers an attractive solution. In addition to the standard Items property in Borland's TDBCombo, Softouch cleverly added Values and UseValues properties. Here's how they work.

The Values property is a pointer to another TList object and is identical in every way to Items. When UseValues is true, the user can scroll through the list of items and make a selection. However, the DataField is updated with the corresponding value from the Values list. When UseValues is false, the update is pulled from Items.

For example, Figure C shows three entries in the Company table.

Figure C: These three entries appear in the Company table.
[ Figure C ]

To use the TDBIncCombo instead of the TDBIncLookupCombo, copy the Company column to the Items property list, then copy the ID column to the Values property list. I used the generic code shown in Listing C to build the dual list. The procedure assumes the query will return at least two fields: the text value viewed by the user and the foreign key value.

Listing C: Copying a data set to the Values and Items properties of a Softouch DBIncCombo

bool CreateList(String &SQL, bool &AddBlank, 
  TStrings *items, TStrings *values)
{ items->Clear();   //clear items in dropdown
  values->Clear();
  if (AddBlank)
  {    items->Add("");  //add null selection
    values->Add(""); 
  }
  TQuery *q=new TQuery(Application);
  q->DatabaseName = dbName;
  q->Close();         //close existing query
  q->SQL->Clear();    //clear items
  q->SQL->Add (SQL);  //set the query
  q->Prepare();       //prepare the query
  q->Active = True;   //activated the query
  while (!q->Eof)     //read every record
  {  items->Add(q->Fields[0]->AsString);
    values->Add(q->Fields[1]->AsString);
       q->Next();     //move to the next record
  }
  q->Active = false;  //deactivate the query
  delete q;
 }

Interbase lightning round

Now, let's tackle a few Interbase questions. I find Interbase to be a robust, vibrant product--I can't understand why there isn't more talk about it in the market.

What's Interbase?

Good question! Interbase is a powerful, yet reasonably simple, client/server database. The server portion of the database runs in Windows 95, NT, and UNIX, making it one of the most scalable database products around. Coming from a well-grounded background in MS Access, I found Interbase easy to learn and simple to use. It's a grown-up version of almost everything I ever wanted in Access. I still leverage my Access knowledge to great advantage, though. For example, I develop database designs in Access because no other product I know offers as much design-time flexibility. Features like drag-and-drop relationship constraints, dynamic field definition, and the Access QBE grid are missing in Interbase. I developed an SQL code generator in Access; it produces Interbase SQL script files I use to port the database design to Interbase, yielding the best of both worlds.

What's a client/server database?

In a client/server paradigm, the client (or end user) sends commands to the server. In C++Builder, TTable and TQuery data objects process the commands. The server is a program that interprets commands from the client. It processes the client commands and returns filtered, sorted data to the client. Products like Access and Paradox offer considerable multi-user capabilities, but they create a lot of network traffic. For example, let's say you want 12 oranges, each weighing 4 ounces. You wouldn't drive to the store, haul the entire produce department back to your house, sort out the 12 oranges you want, then drive the rest back to the store. That would be absurd. I simply send my son to the store with specific instructions, and he makes the selection at the store! That's how a client/server operation works. Multiple users accessing a local database chew up considerable resources hauling data around.

What is Interbase's greatest strength?

Interbase has many strengths, but the power of its programming language is the hands-down winner in my book. I'm not talking about C++. Interbase provides a programming language for stored procedure development. A stored procedure runs on the server. Enhancements and changes to a stored procedure cascade to all the users, often without requiring any changes to the client's application software. I use stored procedures to create data sets that would be impossible, extremely difficult, or ridiculously slow if constructed in straight SQL. The server can do all the work of checking for nulls, casting field types, and blending aggregate query results with simple SELECT queries. This process greatly simplifies client software requirements.

Generally speaking, Interbase stored procedures create data sets and execute action queries. C++Builder treats data sets returned by a stored procedure like any other data set. You can even update them, using a cached update.

The fundamental structure of a stored procedure is simple. The language is a mixture of C, Pascal, Basic, and SQL. For example, the data set returned from Listing A could also be created in a stored procedure without the overhead of a JOIN.

Listing D shows the code to create such a stored procedure in Interbase. The FOR SELECT structure is similar to the C language's for statement. It loops until EOF is true. A colon (:) precedes variable names when they're used in WHERE or INTO blocks. Otherwise, variables are accessed without the colon prefix.

Listing D: An Interbase stored procedure

CREATE PROCEDURE ContactList ( NamePrefix CHAR(1)) 
   (RETURNS 	ContactID INTEGER, CompanyID INTEGER, 
            LastName CHAR(12), FirstName 
		CHAR(12), CompanyName CHAR(32))

/* NamePrefix is a single character pass 
	parameter or argument.
  Variables declared in the RETURNS 
	section are the fields in the returned data set */
AS
  BEGIN
    FOR 	SELECT ContactID, CompanyID, 
	LastName, FirstName 
          FROM Contact      /*get the contact records */
          WHERE LastName STARTING WITH : NamePrefix
          INTO :ContactID, :CompanyID, :LastName, :FirstName
          /* Fill declared variables with 
		contents of one contact record */
    DO
          SELECT Company FROM Company c WHERE c.ID = :CompanyID
          INTO :CompanyName  /* get the related company name */
          DO
            BEGIN
              IF (CompanyName IS NULL) THEN CompanyName
			 = "No Company";
              SUSPEND; 
          END
    END
The data set created by a stored procedure is available to C++Builder clients. For instance, you could add the following command to the SQL property in a TQuery object:

SELECT * FROM ContactList("B")
The ContactList stored procedure, running on the server, would return all contacts with a last name beginning with the letter B, along with the contact's associated company. ContactList contains two SELECT statements. The second is nested within the first; so the second SELECT statement executes once for every contact record returned by the first SELECT statement.

Are you interested? If you have C++ Professional or Client/Server, the WISQL program's help files contain one of the best tutorials I've seen on any topic. The samples actually work! If you're approaching the limit in your Paradox, Access, or dBase application, or if you'd like to avoid hitting that wall in your next project, give Interbase a try. It's a robust system with low ownership cost. I consider my time studying Interbase a prudent investment in my career. If you've used Interbase, please write to me and share your experience.