(a)8

(b)Fields and Database Tools

  [lb]       Getting Started with Relational Databases

  [lb]       Rule Numero Uno: Create a Primary Key for Each Table!

  [lb]       Using Database Tools to Explore and Manipulate Tables

  [lb]       The Fields Editor

  [lb]       Calculated Fields

  [lb]       TDBGrid at Runtime

  [lb]       Working with TDBGrid Columns

  [lb]       Lookup Fields

  [lb]       Multirecord Objects

This chapter covers a set of visual tools you can use to simplify database development. The major areas of concentration are as follows:

  [lb]       Relational databases

  [lb]       The Fields Editor

  [lb]       TField descendant objects

  [lb]       Calculated fields

  [lb]       The TDBGrid component

  [lb]       Lookup fields

  [lb]       The Database Explorer

  [lb]       The Database Desktop

  [lb]       Query by Example (QBE)

  [lb]       MultiRecord objects[md]TDBCtrlGrid

Using Delphi's visual and programmatic tools to manage relational databases is the theme binding these subjects together. Delphi has become a very sophisticated database tool, so getting a feeling for the breadth of the tools available to client/server developers takes time. One of the goals of this chapter is to give you some sense of the key components used when designing database applications.

One of the most frequently mentioned tools in this chapter is the Fields Editor. By using the Fields Editor, you can create objects that you can use to influence the manner and types of data that appear in visual controls such as TDBEdit and TDBGrid. For instance, you can use the objects made in the Fields Editor to format data so that it appears as currency or as a floating-point number with a defined precision. These same changes can be accomplished through the Data Dictionary in the Database Explorer, or through the Database Desktop. These latter tools, however, have a global impact on the field's potential values, whereas the changes made in the Object Inspector affect only the current application.

The Columns property of the TDBGrid control can be used to change the appearance of a grid so that its columns are arranged in a new order or are hidden. You can also use the Columns property to change the color of columns in a grid, or to insert drop-down combo boxes into a grid.

The lessons you learn in this chapter demonstrate techniques used by most programmers when they present database tables to their users. Much of the material involves manipulating visual tools, but the basic subject matter is fairly technical and assumes an understanding of the Delphi environment and language.

(c)Getting Started with Relational Databases

Many different kinds of databases are possible, but in today's world, only two kinds have any significant market share for the PC:

  [lb]       Flat-file databases

  [lb]       Relational databases

***Begin Note***

Note

Emerging in recent years has been a new system called object-oriented databases. These databases represent an interesting form of technology, but I will omit discussion of them here because they have a small user base at this time.

Oracle has also made significant improvements to its database model, many of which are supported by Delphi 4. However, that topic is not covered in this book because its appeal is limited to users of a specific third-party product rather than to Object Pascal programmers as a whole.

***End Note***

A flat-file database consists of a single file. The classic example would be an address book that contains a single table with six fields in it: Name, Address, City, State, Zip, and Phone. If that is your entire database, what you have is a flat-file database. In a flat-file database, the words table and database are synonymous.

In general, relational databases consist of a series of tables related to each other by one or more fields in each table. The Address program shown in Chapter 9, "Basic Database and ClientDataSet," is an example of a flat-file database. In Chapter 10, "Relational Databases," you will see a second program, called kdAdd, which is a relational database.

Consider these three key differences between relational and flat-file databases:

  1.         A flat-file database, like the address book example outlined previously, consists of one single table. That's the whole database. There is nothing more to say about it. Each table stands alone, isolated in its own solipsistic world.

  2.         Relational databases always contain multiple tables. For instance, the Customer and Orders tables are both part of the DBDEMOS database that ships with Delphi. As you will see, many other tables are included in that database, but for now just concentrate on the Customer and Orders tables.

  3.         Tables in relational databases are tied together on special fields. These fields are called primary and foreign keys. These keys  usually have indices, and they usually, but by no means always, consist of a simple integer value. For instance, the CustNo field relates the Customer and Orders tables to one another. The CustNo field is a primary key in the Customer table and a foreign key in the Orders table. Both fields also have indices.

***Begin Note***

Indices are about searching and sorting. Keys, on the other hand, are about relating tables, and particularly about something called referential integrity.

In practice, these concepts get mixed together in some pretty ugly ways, but the underlying theory relies on the kinds of distinctions I am drawing in this note. For instance, keys are usually indexed, and so people often talk about keys and indices as if they were the same. However, they are distinct concepts.

One way to start to draw the distinction is to understand that keys are part of the theory of relational databases, whereas indices are part of the implementation of relational databases. You'll learn more on this subject as the chapter evolves.

***End Note***

Clearly, relational databases are radically different from flat-file databases. Relational databases typically consist of multiple tables, at least some of which are related together by one or more fields. Flat-file databases, on the other hand, consist of only one single table, which is not related to any other table.

(d)Advantages of the Relational Database Model

What advantages do relational databases have over flat-file databases? Well, this system has many strengths; the following are a few of the highlights:

  [lb]       Relational databases enforce referential integrity. These constraints help you enter data in a logical, systematic, and error-free manner.

  [lb]       Relational databases save disk space. For instance, the Customer table holds information about customers, including their address, phone, and contact information. The Orders table holds information about orders, including their date, cost, and payment method. If you were forced to keep all this information in a single table, each order would also have to list the customer information, which would mean that some customers' addresses would be repeated dozens of times in the database. In a big database, that kind of duplication can easily burn up megabytes of disk space. Using a relational database is better because each customer's address would be entered only once. You could also have two flat-file databases, one holding the customer information and the other holding the orders information. The problem with this second scenario is that flat-file databases provide no means of relating the two tables so that you can easily see which orders belong to which customer.

  [lb]       Relational databases enable you to create one-to-many relationships. For instance, you can have one name that is related to multiple addresses. You cannot capture that kind of relationship in a simple way in a flat-file database. In the KDAdd program, you will see that you can easily relate multiple addresses, phone numbers, and so on, with each name. The flexible structure of relational databases enables programmers to adopt to these kinds of real-world situations. For many entries in a database, you will want to keep track of two addresses: one for a person's home, and the other for his or her work. If someone you know has a summer home or an apartment in the city, you need to add yet more addresses to the listing.  You cannot add these addresses conveniently in flat-file databases. Relational databases handle this kind of problem with ease. In the preceding paragraph, I emphasized that this kind of feature saves space; in this paragraph, I'm emphasizing that it allows for a more logical, flexible, and easy-to-use arrangement of your data.

A relational database offers these possibilities:

  1.         You can view the Customer table alone, or you can view the Orders table alone.

  2.         You can place the two tables in a one-to-many relationship so that you can see them side by side but see only the orders relating to the currently highlighted customer.

  3.         You can perform a join between the two tables so that you can see them as one combined table, much like the combined table you would be forced to use if you wanted to "join" the Customer and Orders table in a single flat-file database. However, you can decide which fields from both tables will be part of the join, leaving out any you don't want to view. The joined table is also temporary and does not permanently take up unnecessary disk space. In short, relational databases can use joins to provide some of the benefits of flat-file databases, whereas flat-file databases cannot emulate the virtues of relational databases.

As you can see, the three concepts that stand out when talking about relational databases are referential integrity, flexibility, and conservation of disk space. In this case, the word flexibility covers a wide range of broad features that can only be fully appreciated over time.

The one disadvantage that relational databases have when compared to flat-file databases is that they are more complicated to use. This disadvantage is not just a minor sticking point. Neophytes are often completely baffled by relational databases. They don't have a clue as to what to do with them. Even if you have a relative degree of expertise, you can still become overwhelmed by a relational database that consists of three dozen tables related to one another in some hundred different ways. (And yes, complexity on that scale is not uncommon in corporate America.) As you will see later in the book, almost the only way to work with big systems of that type is through CASE tools.

(d)Simple Set Logic: The Basis of Relational Databases

The basis for relational databases is a simple form of mathematics. In its simplest case, each table represents a set that can be related to other tables through fundamental mathematics. Because computers are so good at math, and particularly at integer math, they find relational databases easy to manipulate.

One common feature of relational databases is that most records will have a unique number associated with them, and these numbers will be used as the keys that relate one table to another. These keys enable you to group tables together using simple mathematical relationships. In particular, you can group them using simple integer-based set arithmetic. I should add, however, that there is no reason why you can't use a nonnumeric field as a key. In fact, using such a field this way is common practice in many corporate databases. Evidence of this practice can be seen in the strange combination of letters and numbers you find in the serial numbers or account numbers on products and services you might use in your own home. However, you will find an admirable purity in the way tables relate when they are keyed on integer fields.

In the Customer table from DBDEMOS, each record has a unique CustNo field. This CustNo field serves as a key. Furthermore, the Orders table has a unique OrderNo field associated with it. This field is also a key field. The Orders table also has a CustNo field that will relate it to the Customer table. The terminology of relational databases expresses these ideas by saying that the Customer table has a primary key called CustNo, and the Orders table has a primary key called OrderNo and a foreign key called CustNo, as shown here:

Table Name           Primary Key               Foreign Key (Secondary Index)

Customer                 CustNo

Orders                     OrderNo                      CustNo

Given this scenario, you can say: Show me the set of all orders such that their CustNo field is equal to X or within the range of X [nd] Y. Computers love these kinds of simple mathematical relationships. They are their bread and butter. In essence, you are just asking for the intersection of two sets: Show me the intersection of this record from the Customer table with all the records from the Orders table. This intersection will consist of one record from the Customer table with a particular CustNo plus all the records from the Orders table that have the same CustNo in their foreign key.

These CustNo, OrderNo, AuthorNo, BookNo, and similar fields might also be used in flat-file databases as indices, but they play a unique role in relational databases because they are the keys used to relate different tables. They make it possible to reduce the relationship between tables to nothing more than a simple series of mathematical formulas. These formulas are based on keys rather than on indices. It is merely a coincidence that in most tables the keys also happen to be indexed.

(d)Viewing Indices and Keys in DBD or the Database Explorer

In the next few sections, I define primary and secondary keys, and describe how to use them. Prefacing this discussion with a brief description of how to view keys using some of the tools that ship with Delphi might be helpful. Keep in mind that this information provides just a preliminary look at this material. I cover it again in greater depth later in this chapter in a section called "Exploring the Keys and Indices in the DBDEMOS Database."

You can view the indices and keys on a table in two ways. The best way is to use the Database Explorer. Open the Explorer and view the DBDEMOS database, as shown in Figure 8.1.

*** Begin Note ***

Note: The Database Explorer is also known as the SQL Explorer. I use the two terms interchangeably.

***End Note***

***Insert Figure 8.1                                                                                                                                           09fig01                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.1

Viewing the DBDEMOS database in the Database Explorer.

Click the Orders table and open the Referential Constraints branch, as shown in Figure 8.2. Notice that the two constraints on this table: one called RefCustInOrders and the second called RefOrders. The RefCustInOrders field defines CustNo as a foreign key that relates to the CustNo field in the Customer table.

***Insert Figure 8.2                                                                                                                                           09fig02                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.2

The primary and foreign fields of the Orders table.

A second way to view this key is to use the Database Desktop. Set the Working Directory from the File menu to the DBDEMOS alias. Open the Orders table in the Database Desktop, and select Table, Info structure. Drop down Table Properties and select Referential Integrity, as shown in Figure 8.3.

***Insert Figure 8.3                                                                                                                                           09fig03                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.3

Selecting Referential Integrity in the Database Desktop.

Double-click RefCustInOrders to bring up the Referential Integrity dialog, as shown in Figure 8.4.

***Insert Figure 8.4                                                                                                                                           09fig04                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.4

The CustNo field in the Orders table relates to the CustNo field in the Customer table.

The fields on the left side of this dialog belong to the Orders table. On the right is a list of all the tables in the database. In the center, you can see that the CustNo field has been selected from the Orders table, and the CustNo field has been selected from the Customer table. The primary key of the Customer table is related to the foreign key of the Orders table.

Now go back to the Database Explorer and open the Indices branch of the Orders table, as shown in Figure 8.5.

***Insert Figure 8.5                                                                                                                                           09fig05                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.5

The primary and CustNo indices on the Orders table.

Note that you can see the names of the indices, here labeled as <primary> and as CustNo. The fields found in the indices are also displayed. For instance, you can see that the primary index consists of the OrderNo field, and the secondary index consists of the CustNo field.

I am showing these figures to you so that you will begin to see the distinction between keys and indices. The two concepts are distinct. For further proof, open the IBLOCAL database in the Database Explorer. Use SYSDBA as the username and masterkey as the password. Now open the Employee Project table, as shown in Figure 8.6. Note the separate listings for the index, primary key, and foreign key.

***Insert Figure 8.6                                                                                                                                           09fig06                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.6

The Employee_Project table has three indices, one primary key, and two foreign keys.

You can also see the indices for a table inside the Database Desktop. To get started, open the Orders table and select Table, Info Structure. The fields with the stars beside them are part of the primary index. Drop down the Table Properties combo box to view the secondary indices. Double-click the indices you see to view the details of their design. If you want to change the structure of a table, choose Table, Restructure rather than Table, Info Structure.

Most of the time, I find the Database Desktop is the right tool to use when I want to create or modify a table, and the Database Explorer is the right tool to use when I want to view the structure of a table. I find the Database Explorer loads faster than the Database Desktop. However, I often find myself jumping back and forth between the two tools to get the best features of each. Third-party CASE tools are generally superior to either of the products discussed in this section. However, no CASE tools ship with Delphi, so I emphasize the universally available tools in this text.

Throughout the ensuing discussion, you might have occasion to use the Database Explorer to examine the structure of the Customer, Orders, Items, and Parts tables. I use these tables when defining what relational databases are all about.

(c)Rule Numero Uno: Create a Primary Key for Each Table!

The preceding two sections introduced you to some of the key concepts in relational databases. Based on this information, you should begin to see the importance of creating a unique key in the first field of most tables you create. This field is called a primary key. In both Paradox and InterBase, creating a primary key without also simultaneously creating an index is impossible.

If you want to have a list of addresses in a table, don't just list the Address, City, State and Zip. Be sure to also include a CustNo, AddressNo, or Code field. This field will usually be both an index and the first field of the database. It is the primary key for your table and must be, by definition, unique. That is, each record should have a unique Code field associated with it. This field need not be an integer value, but an integer is a logical choice for this kind of field.

The primary key does the following:

  [lb]       It serves as the means of differentiating one record from another.

  [lb]       It is also used in referential integrity.

  [lb]       Because it is usually indexed, it can also help with fast searches and sorts.

As I said earlier, the distinction between indices and keys becomes blurred at times. However, they are distinct concepts, and you should struggle to discover the differences.

Just to make sure this information is clear, I'll list the right and wrong way to create a table:

Right Method

CustNo: Integer

LastName, FirstName, Address, City, State, Zip: string

Wrong Method

LastName, FirstName, Address, City, State, Zip: string

The first example is "correct" because it has a primary index called CustNo. It is declared as a unique Integer value. The second example is "wrong" because it omits a primary index.

I put the words correct and wrong in quotation marks because this discipline really doesn't have any hard-and-fast rules. On some occasions, you might not want to create a table that has a primary index. However, 99 percent of the time, that's exactly what you want to do.

At the height of warm May spring day, there is such a thing as a rose bush that has no buds or flowers. However, the whole point of rose bushes in May is that they flower. I doubt we would feel quite the same way about roses if they did not have beautiful blooms. In the same way, relational databases without primary indices wouldn't garner quite so much attention as they do now.

Even if you don't yet understand how relational databases work, for now I suggest that you automatically add a simple numerical value in a primary index to all your tables. Do so even if you are not using the field at this time. Believe me, as you come to understand relational databases, you will see why I recommend adding this value in most, though not all, cases. At this point, however, you will probably be better off creating the extra field and letting it go to waste, even if you don't understand why you are doing it. After you get a better feeling for relational databases, you will understand intuitively when the field is needed, and when you are encountering one of those rare occasions when it is going to be useless.

When people first work with relational databases, they can get a little hung up about the overhead involved in creating all these extra key fields. The point to remember is that these fields allow the database to be treated as nothing more than sets related together in various combinations. Computers fly through integer math and in general can easily relate tables together on keys. Adding these extra index fields to your tables makes your data become computer friendly. Computers love these keyed fields; your computer will show its thanks by running faster if you add them to your tables.

Computers don't feel weighed down by the extra field any more than a car feels weighed down by a steering wheel, people feel weighed down by their hands, or a rose bush feels weighed down by a rose. Relational databases want you to add an extra field as a primary index to your tables.

Remember, people like beautiful paintings, eloquent words, lovely members of the opposite sex. Computers like logic. They like numbers; they like nice, clean, easily defined relationships. They like primary keys in the first field of a table.

(d)One-to-Many Relationships: The Data and the Index

One good way to start to understand relational databases is by working with the Customer, Orders, Items, and Parts tables from the DBDEMOS database. All four of these tables are related in one-to-many relationships, each-to-each. That is, the Customer table is related to the Orders table, the Orders table to the Items table, and the Items table to the Parts table. (The relationship also works in the opposite direction, but it may be simpler at first to think of it as going in only one direction.)

Master                   Detail              Connector (Primary Key and Foreign Key)

Customer                 Orders             CustNo

Orders                     Items                OrderNo

Items                       Parts                PartNo

Read the preceding table as a series of rows, starting left and moving to the right, as if they were sentences. The preceding list shows that the Customer and Orders tables are related in a one-to-many relationship, with Customer being the master table and Orders being the detail table. The connector between them is the CustNo field. That is, they both have a CustNo field.

The CustNo field is the primary key of the Customer table and the foreign key of the Orders table. The OrderNo field is the primary key of the Orders table and a foreign key of the Items table. The PartNo field is the primary key of the Parts table and a foreign key of the Items table.

You can see all these relationships in action by running the Relate program from the CD that accompanies this book.

The relationship between these tables can be reversed. For instance, the Parts table could become the master table and the Items table the detail table, and so on, back down the line. The reason you can reverse the relationship becomes clear when you think in purely mathematical terms. The Customer table has a series of CustNo fields. Say the CustNo for the first record is 1000. To get the orders associated with that customer, you ask this question: "What are all the rows from the Orders table that have a CustNo of 1000?" That is:

Select * from Orders where CustNo = 1000

Clearly, you could reverse this question. If you select a particular row from the Orders table, you could find which item from the Customer table it is related to by asking for the set of all Customer records with a CustNo of  1000. Because the CustNo field for the Customer table is a unique index, you will get only one record back. However, the way you relate the tables is still the same:

Select * from Customer where CustNo = 1000

(d)Working with Primary Keys and Primary Indices

The Parts, Orders, Items, and Customer tables have various keys. As it happens, these keys are also indices. An index enables you to sort tables on a particular field. A key helps you define the relationship between two tables or otherwise group related bits of information by a set of predefined and automatically enforced rules.

Unfortunately, sadly, and confusingly, you can still relate tables even without the presence of any keys or indices. For instance, if no CustNo primary and foreign keys appeared in the Customer and Orders tables, Paradox would still let you use SQL to relate the tables in a one-to-many relationship. However, in this scenario, performance would be slow because you have no index, and you have no constraints on the data you could enter in the two tables because no primary and foreign keys define referential integrity. You are back to the rose bush without a rose phenomena. The tables are still part of a relational database, but they lack the features that make a relational database appealing. You need both the keys and the indices to make a relational database appealing.

I'll draw a distinction between only two different kinds of keys. The first kind I will discuss is called a primary key. The second is called a foreign key.

  [lb]       A primary key is a unique value used to identify a record in a table. It is usually numerical, and it is usually indexed. It can be combined with a foreign key to define referential integrity. I will talk more about referential integrity later in this chapter.

  [lb]       Because the primary key is indexed, it defines the default sort order for the table. When you first open a table, it will be automatically sorted on this field. If a table does not have a primary index, records will appear in the order in which they were added to the table. For all practical purposes, a table without an index has no defined order in which records will appear.

  [lb]       With Paradox tables, each entry in the primary index must be unique. That is, you can't have two CustNos in the Customer table that are the same. You can, however, have multiple foreign keys that are not unique.

  [lb]       Having multiple fields in the primary index of a Paradox table is legal. This type is called a composite index. These fields must be sequential, starting with the first field in the table. You can't have the primary index consist of the first, third, and fourth fields of a table. A composite index with three fields must consist of the first, second, and third fields. If you have a FirstName and LastName field in your database, they can both be part of the primary index. You should, however, declare the LastName before the FirstName so that your index will list people alphabetically by last name: CustNo, LastName, FirstName. To get a good sort on these fields, you should define a secondary index on the LastName and FirstName fields.

  [lb]       Unlike an index, the primary and foreign keys are never composite. They always consist of one field.

Creating a primary key enables you to list two people with the same name but with different addresses. For instance, you can list a John Doe on Maple Street who has a CustNo of 25, and a John Doe on Henry Street who has a CustNo of 2000. The names may be the same, but the database can distinguish them by their CustNos.

(d)Working with Secondary Indices and Foreign Keys

You're now ready to move on to a consideration of foreign keys. The CustNo field of the Orders table is a foreign key because it relates the Orders table to the primary key of the Customer table. It is also a secondary index that aids in sorting and searching through data. Indices also speed up operations such as joins and other master-detail relationships.

When writing this section, I found it difficult to totally divorce the idea of foreign key and secondary index. However, I will try to split them up into two categories, taking foreign keys first:

  [lb]       A foreign key provides a means for relating two tables according to a set of predefined rules called referential integrity.

  [lb]       In Paradox, you use the Referential Integrity tools from the Database Desktop to define foreign keys. There is no such thing as a composite foreign key.

  [lb]       Using SQL, you can relate two tables in a one-to-many relationship even if no index or key appears in either table. However, your performance will be better if you have indices. You will have no way to enforce referential integrity if you don't define foreign and primary keys.

  [lb]       Using the TTable object, you cannot relate two tables in a one-to-many relationship without indices. (This point doesn't clearly belong in either the section on keys or the one on indices. It relates to both subjects.)

Here are some facts about secondary indices:

  [lb]       A secondary index provides an alternative sort order to the one provided by the primary key.

  [lb]       You need to explicitly change the index if you want to switch away from the primary index to a secondary index. Remember that the default sort order for a Paradox table is provided by the primary index. If you want to switch from the primary index to a secondary index, you need to change the IndexName or IndexFieldName property of your table. If you want to use the primary index, you don't have to do anything; the table will sort on that field automatically.

  [lb]       An index that contains more than one field is called a composite index. You can create composite secondary indices, which means the indices will contain multiple fields. In practice, fields such as FirstName and LastName can often be part of a secondary index because your primary index is usually a unique numerical value.

  [lb]       In Paradox tables, all primary and foreign keys must be indexed. You can't define referential integrity without indices, and in particular, you must have a primary key. Furthermore, in InterBase tables, the act of defining a primary or foreign key will automatically generate an index. (Once again, this item doesn't clearly belong in either the discussion of keys or of indices, but rather it relates to both. As I said earlier, sometimes the distinctions between the two subjects become blurred.)

If you are new to databases, you will undoubtedly be frustrated to discover that different databases have varying rules for setting up indices, keys, and so on. In this book, I tend to use Paradox tables as the default, but I also spend considerable time describing InterBase tables. If you use some other database, such as dBASE, Oracle, or Sybase, you should be sure to read up on the basic rules for using those tools. For instance, some databases let you set up a foreign key that is not an index. In the Paradox and InterBase world, however, foreign keys are always accompanied by an index, so the two words become synonymous, particularly in the hands of people who don't really understand how relational databases work.

The good news is that you will find that, overall, certain basic principles define how databases work. The details may vary from implementation to implementation, but the fundamental ideas stay the same.

(d)Keys are the Keys to the Kingdom!

Let me take this whole paradigm even one step further. When I first looked at a database, I thought of it is a place to store information. After spending a lot of time with relational databases, I now think of them primarily as a way to relate bits of information through keys and indices.

I know this is putting the cart before the horse, but what really interests me about databases now is not the fact that they contain information per se, but that I can query them to retrieve related bits of information. In other words, I'm more interested in the logic that defines how tables relate to one another than I am in the information itself.

No one can get excited about a list of addresses or a list of books. The lists themselves are very boring. What's interesting is the system of keys and indices that relate tables together, and the various SQL statements you can use to ask questions against various sets of tables.

When I picture a table, I see its primary and foreign keys as great big pillars, and I envision all the rest of the data as a little stone altar that is dwarfed by the pillars. Like a pagan temple, it's the pillars that you notice first, the altar is just a small stone structure you might overlook until someone points it out. Of course the temple is built around the altar, and databases are built around their data. But in practice it is easy to overlook the data. You care about the pillars, you care about the primary and foreign keys. The rest tends to fade into the background.

Give me a well-designed database with lots of interrelated tables and I can have fun asking it all sorts of interesting questions. Its not the data per se that is important, but the way the data is related!

The act of properly relating a set of tables in a database is called, tragically enough, "normalizing" the data. Where this dreadful term came from I have no idea, but "normalizing" a database is the fun part of creating a database application.

This is the end of the portion of this chapter that deals entirely with theoretical issues. The remainder of the chapter shows you to use some of the database tools that ship with Delphi, as well as how to write code that demonstrates some of the key features of Delphi database programming.

 (c)Using Database Tools to Explore and Manipulate Tables

 

In the next few sections, I will describe using the SQL Explorer, Database Desktop, and other tools. The programs provide core functionality needed by Delphi database programmers. The Database Desktop is particularly important to developers using local databases such as Paradox or dBASE. However, it provides services that are useful to all programmers. The Database Explorer is one of the most important tools in Delphi, and everyone should be familiar with how it works. Depending on the version of Delphi that you have, this tool may be called either the Database Explorer or the SQL Explorer. With some versions of the product, you can access it from the Database menu in the IDE; with other versions, you can access it from the Start menu.

(d)Exploring the Keys and Indices in the DBDEMOS Database

The following is a list of the indices on the Customer, Orders, Items, and Parts tables:

Table name               Fields in Primary Index         Secondary Indices

Customer                    CustNo                                    Company

Orders                        OrderNo                                  CustNo

Items                          OrderNo, ItemNo                    OrderNo, PartNo

Parts                           PartNo                                  VendorNo, Description

Notice that the Items table has a composite primary index consisting of the OrderNo and ItemNo fields. It also has two secondary indices, one on the OrderNo field and one on the PartNo field. The Parts table has two secondary indices, one on the VenderNo and one on the Description field.

If you do not have a premade list like this one, you can find this information in at least four ways:

  [lb]       Using the Object Inspector

  [lb]       Using the Database Explorer

  [lb]       Using the Database Desktop

  [lb]       Creating a program that leverages the methods of the TSession object. Such a program will be shown in Chapter 10.

I will explain all these methods and then discuss some possible alternative techniques.

If you drag the Customer table off the Explorer and onto a form, you can view its indices in the Object Inspector. If you drop down the IndexName Property Editor, you will see that one index is listed there. It is the secondary index, called ByCompany. If you select this index, the table will sort on the Company field.

If you set the IndexName property back to blank, the table will sort automatically on the primary index, which is the CustNo field. In other words, Delphi never explicitly lists the primary index in the IndexName Property Editor. I suppose that the architects of the VCL assumed that all tables have a primary index, and that if you don't specify a particular index name, you want to sort on that index. Of course, creating a table that has no primary index is not an error, and Delphi can still work with that kind of table.

You can also drop down the IndexFieldNames property, which gives you a list of the fields that are indexed[md]in this case, the CustNo and Company fields. Here you can see the fields included in the primary index, but they are not marked as belonging to any particular index.

***Begin Note***

Note

To study an interesting case, drop down the Items table on a form. Recall that it has a primary index on the OrderNo and ItemNo fields, and secondary indices on the OrderNo and PartNo fields. If you drop down the index field names, you see the following list:

OrderNo

OrderNo; ItemNo

PartNo

The first item is the ByOrderNo index; the second, the primary index; and the third, the PartNo index.

***End Note***

The IndexName and IndexFieldNames properties give you a handy way of tracking indices at design time. They don't, however, give you all the information you might need, such as exactly what fields make up which parts of the primary and secondary indices. In this case, you could probably guess, but it would still be nice to get a more definitive answer.

If you open the Database Explorer, expand the DBDEMOS node, the Tables node, the Customer node, and finally the Indices node, you get (naturally enough) a list of the indices on the Customer table. This feature is great, and you should use it whenever possible. Figure 8.7 shows the expanded nodes of the Indices for the Customer table. (The program kdAddExplore in the Chap11 subdirectory on the CD-ROM that accompanies this book uses the TSession object to do the same thing in a Delphi program.)

***Insert Figure 8.7                                                                                                                                           09fig07                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.7

The indices of the Customer table viewed in the Database Explorer.

While you have the Explorer open, you should also expand the Fields node, as shown in Figure 8.8. This way, you get a list of all the fields and their types. Notice that you can drag and drop individual fields onto a form.

***Insert Figure 8.8                                                                                                                                           09fig08                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.8

The Fields view of the Customer table from the Database Explorer.

A third way to get a look at the structure of a table is through the Database Desktop (DBD). You can open this program from the Tools menu in Delphi. Use the File menu in the DBD to set the Working Directory to the DBDEMOS Alias. Open the Customer table and choose Table, Info Structure. Drop down the Table Properties combo box and look up the secondary indices, as shown in Figure 8.9. The primary index is designated by the asterisks after the keyed fields in the Key Roster. In this case, only the CustNo field is starred because it is the sole keyed field.

***Insert Figure 8.9                                                                                                                                           09fig09                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.9

The Database Desktop struts its venerable features by displaying the indices on the Customer table.

***Begin Note***

Note

Over time, the Database Desktop will probably be replaced entirely by the Explorer. However, the DBD still does some things better than the Database Explorer, so both products ship with Delphi.

***End Note***

Notice the Save As button on the Info Structure dialog. You can use this button to save a table that contains the structure of the Customer table. You can then print this table on a printer using TQuickReports. Be sure to use a fixed-size font, not a proportional font:

Field Name          Type   Size Key

CustNo              N           *

Company             A      30

Addr1               A      30

Addr2               A      30

City                A      15

State               A      20

Zip                 A      10

Country             A      20

Phone               A      15

FAX                 A      15

TaxRate             N

Contact             A      20

LastInvoiceDate     @

In the example shown here, I have printed only the first four fields of the table because of space considerations. (The fields are Field Name, Type, Size, and Key.) If I then recursively print the structure of the table used to house the structure of the Customer table, I get the following report:

Field Name          Type   Size  Key

Field Name          A      25

Type                A      1

Size                S

Key                 A      1

_Invariant Field ID S

_Required Value     A      1

_Min Value          A      255

_Max Value          A      255

_Default Value      A      255

_Picture Value      A      176

_Table Lookup       A      255

_Table Lookup Type  A      1

This information is the same as found in the Data Dictionary; it should prove sufficient under most circumstances.

(d)Using the Database Desktop to Create Indices

To create a unique primary key in a Paradox table, open the Database Desktop, and create a table with the first field declared as an Integer or autoincrement value. Place a star next to the first field, which tells Paradox to create a primary index on it, as shown in Figure 8.10.

***Insert Figure 8.10                                                                                                                                           09fig10                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.10

Place stars next to the first field or fields of a table to designate the primary index.

To create a secondary index, drop down the Table Properties list, and choose Secondary Indexes. Click the Define button. Select the fields from your table that you want to be part of your index, as shown in Figure 8.11. Click OK. A simple dialog will then pop up, asking you to name the index. I usually name an index based on the fields being indexed. For instance, if I want to create an index on the CustNo field, I would call the index CustNo, CustNoIndex, or ByCustNo. If I wanted to create one on a field called Name, I would call the index Name, NameIndex or ByName.

***Insert Figure 8.11                                                                                                                                           09fig11                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 9.11

Creating a secondary index in a Paradox table.

(d)Creating an Alias

In the Data subdirectory on the CD that ships with this book, you will find two tables called MasterTable and DetailTable. A program called MasterDetail in the Chap09 directory uses these tables. To run this program, you must define an alias in the Database Explorer.

To create an alias, start the Database Explorer, and choose Object, New. Select Standard as your Database Driver Name. Click OK, and type the name of your alias; in this case, enter D4UNLEASHED. Set the path for the alias to point to the directory on your hard drive where the data files from the CD that accompanies this book are stored. By default, this directory is called Data; you will recognize it because MasterTable.db and DetailTable.db are located inside this directory. (Other files associated with these tables have extensions such as .xg0, .x02,  and so on.) Note that you can click a button in the editor for the Path name to browse for the file. When you are done, choose Object, Apply to save your work.

You can test the alias by right-clicking on the + (plus) sign to the left of your alias name. A tree with a branch called Tables will open. Open this branch, click on one of the table names, and select the Data tab. If you can see the data from the table, then everything has been set up correctly. If you cannot complete these steps, the most likely problem is that you are not pointing the path in the correct location.

That's all there is to setting up the alias. Most of the database programs in this book will use either the D4UNLEASHED alias or else DBDEMOS, which is created automatically when you install Delphi.

(d)Using the Database Desktop to Create Primary and Foreign Keys

To create a primary or foreign key on a Paradox table, you need to define something called referential integrity. You cannot define referential integrity without first defining primary keys on both tables involved. An index also must be located on the foreign key, but this index will be created automatically for you when you create the foreign key.

In InterBase, the situation is somewhat different. The act of creating primary or foreign keys will automatically define indices. As I said earlier, you will find little variations on the main themes of relational databases, depending on what kind of database you use.

Figure 8.10 shows how to use the Database Desktop to create the MasterTable. These tables look like this, with the MasterTable listed first and the DetailTable listed second:

Field Name               Type             Size              Primary Index?

Code                          +                                        *

Name                         A                  25

Field Name               Type             Size              Primary Index?

Code                          +                                        *

MasterCode               I                   

SubName                   A                  25

To create referential integrity between these two tables, you should open the DetailTable in the Database Desktop. Then choose Table, Restructure. Select Referential Integrity from the Table Properties combo box. Click the Define button and set up the elements so that they look like Figure 8.12. Click the OK button and give this relationship a name, such as RefMasterDetail.

***Insert Figure 8.12                                                                                                                                           09fig12                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.12

Defining referential integrity between the DetailTable and MasterTable.

When you are done, you will have created primary keys and foreign keys on the MasterTable and DetailTable. The best way to see these keys is to use the Database Explorer. On my system, I used the BDE Configuration Utility to create an alias called D4Unleashed that points to the Data subdirectory. If you open this alias in the Database Explorer and go to MasterTable, you can see the primary and foreign keys, which Paradox calls primary and foreign fields.

(d)Exploring Referential Integrity

Referential integrity is one of the most valuable tools in a database programmer's bag of tricks. In particular, referential integrity will help guide the users so that they do not accidentally enter invalid data or accidentally delete needed records.

To see referential integrity in action, use the Database Desktop to enter two records in the MasterTable. The first should have the word Days in the Name field, and the second should have the word Months in the Name field. You do not have to fill in the Code field because it is an autoincrement field (+) and will be updated automatically:

Code                    Name

1                          Days

2                          Months

In the DetailTable, enter a few names of days of the week or months of the year in the SubName field. Give the MasterCode field a 1 if you are entering a day, and 2 if you are entering a month.

Code                    MasterCode                  SubName

1                          1                                     Monday

2                          1                                     Tuesday

3                          2                                     January

4                          2                                     February

5                          2                                     March

With this data in the tables, you can define a one-to-many relationship such that, if you viewed the MasterTable record with Days in the Name field, you would see only the days in the DetailTable. If you selected Months, you would see only the month names from the DetailRecord.

Referential integrity will do two things to help make sure that these tables stay in good shape:

  [lb]       It will prevent you from deleting a record in the MasterTable that has detail records associated with it in the DetailTable. For instance, if you select the MasterTable, set the Database Desktop in Edit mode, and press Cntrl+Delete, you will not be able to delete a record from the MasterTable.

  [lb]       Referential integrity will prevent you from entering a value in the MasterCode field of the DetailTable that is not in the primary key of the MasterTable. For instance, if you tried to enter the number 3 in the DetailTable's MasterCode field, you would get the error message "Master record missing" because no record in the MasterTable with a Code field of 3 even exists. Of course, if you added a record to the MasterTable with a Code field that had 3 in it, the database would let you enter the data.

These rules are also enforced inside Delphi. In your own programs, you might want to create exception handlers that would pop up messages that explained to the users exactly what was wrong and why they could not perform a particular operation. Most users would not respond well to an exception that said no more than "Master field missing!"

The MasterDetail sample program on the CD that accompanies this book shows how to use the MasterTable and DetailTable.

(d)Relational Databases and Joins

Earlier in the chapter, you saw how to relate the Customer, Orders, Items, and Parts tables in a one-to-many relationship that is sometimes called a master-detail relationship. In this section, you will again relate all four tables, but in a different kind of relationship called a join.

 Here is a relatively lengthy query:

SELECT DISTINCT d.Company, d1.AmountPaid, d2.Qty,

                d3.Description, d3.Cost, d3.ListPrice

FROM "Customer.db" d, "Orders.db" d1,

     "Items.db" d2, "Parts.db" d3

WHERE (d1.CustNo = d.CustNo)

      AND (d2.OrderNo = d1.OrderNo)

      AND (d3.PartNo = d2.PartNo)

ORDER BY d.Company, d1.AmountPaid, d2.Qty,

         d3.Description, d3.Cost, d3.ListPrice

Though not horrendously complicated, the syntax shown here is still ugly enough to give some people pause.

The basic principles involved in this kind of statement are simple enough to describe. All that's happening is that the Customer, Orders, Items, and Parts tables are being joined together into one large table of the type you would have to create if you were trying to track all this information in a single flat-file database. The one proviso, of course, is that not all the fields from the four tables are being used; in fact, the only ones mentioned are

    d.Company, d1.AmountPaid, d2.Qty,

    d3.Description, d3.Cost, d3.ListPrice

Here the d, d1, d2, and d3 are described in the following From clause:

    "Customer.db" d, "Orders.db" d1,

    "Items.db" d2, "Parts.db" d3

The Order By clause, of course, simply defines the sort order to be used on the table created by this join.

You can create a program that performs this join by dropping a TQuery, TDataSource, and TDBGrid on a form. Wire the objects together, wire the TQuery to the DBDEMOS database, and set its SQL property to the preceding query. A sample program called RelJoin demonstrates this process. The output from the program is shown in Figure 8.13.

***Insert Figure 8.13                                                                                                                                           09fig13                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.13

The RelJoin program demonstrates a join on four tables.

If you are not familiar with this kind of join, you might want to bring up side by side the Relate and RelJoin programs from the CD that accompanies this book and compare them. Look, for instance, at the Action Club entries in the RelJoin program, and trace them through so that you see how they correspond to the entries in the Relate program. Both programs describe an identical set of relationships; they just show the outcome in a different manner.

Notice that the AmountPaid column in the RelJoin program has the same number repeated twice in the Action Club section, as shown in Figure 8.13. In particular, the numbers $1,004.80 and $20,108.00 both appear twice because two different items are associated with these orders, as you can tell from glancing at the Parts table in the Relate program.

***Begin Note***

Note

Unless you are already familiar with this material, be sure to run the RelJoin and Relate programs, and switch back and forth between them until you understand why RelJoin program works as it does. I find it easy to understand the Relate program at a glance, but the RelJoin program is a bit more subtle.

***End Note***

(d)Joins and QBE

The RelJoin program is a good advertisement for the power of SQL. After you compose the SQL statement, putting the program together is simple. All the work is embodied in just a few lines of code, and constructing everything else is trivial. SQL can help concentrate the intelligence of a program in one small area[md]or at least it does in this one example.

The sticking point, of course, is that not everyone is a whiz at composing SQL statements. Even if you understand SQL thoroughly, trying to string together all those interrelated Select, Order By, From, and Where clauses can still be confusing. What is needed here is a way to automate this process.

Most of the versions of Delphi ship with a useful tool that makes composing even relatively complex SQL statements easy. In particular, I'm talking about the QBE tool in the Database Desktop. If you want, you can use the Query Builder instead or some other third-party tool that you might favor. However, in this section of the book, I will concentrate on the QBE tool because it will be available to nearly all readers of this book.

Start the DBD, and set the Working Directory to the DBDEMOS alias. Choose File, New, QBE Query. A dialog will appear listing the tables in the DBDEMOS database. Select the Customer table. Reopen the Select File dialog by clicking on the Add Table icon in the toolbar. You can find the Add Table icon by holding the mouse cursor over each icon until the fly-by help comes up or until you see the hint on the status bar. You can also simply look for the icon with the plus sign on it. Continue until you have added the Customer, Orders, Items, and Parts tables to the query. You can resize the query window until all four tables are visible, as shown in Figure 8.14.

***Insert Figure 8.14                                                                                                                                           09fig14                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.14

Four tables used in a single QBE example.

To join these tables together, select the Join Tables icon, located just to the right of the lightning bolt. Click once on the Join Tables icon, and then click the CustNo fields for the Customer and Orders tables. The word "join1" will appear in each field. Click the Join Tables icon again, and link the Orders and Items tables on the OrderNo field. Join the Parts and Items tables on the PartNo field.

After joining the tables, select the fields you want to show by clicking once in the check box associated with the fields you want to view. When you are done, the result should look like Figure 8.15.

***Insert Figure 8.15                                                                                                                                           09fig15                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.15

The complete QBE query for joining the Customer, Orders, Items, and Parts tables.

To test your work, click the lightning bolt icon once. You should get a table that looks just like the one in the RelJoin program.

To translate the QBE statement into SQL, first close the result table so that you can view the query shown in Figure 8.15. Click once on the SQL icon to perform the translation. You can save this SQL to disk, or just block-copy it and deposit it in the SQL property of a TQuery object.

On paper, this process takes a few minutes to explain. However, once you understand the QBE tool, you can use it to relate multiple tables in just a few seconds. For most people, QBE is probably the simplest and fastest way to compose SQL Select statements. Don't neglect learning to use this simple, easy-to-use tool because it can save you hours of time.

***Begin Note***

Note

The only peculiarity of the QBE tool is that, by default, it saves its output in a text-based language called QBE rather than in SQL. However, after you click the SQL button, it converts the QBE code to SQL, thereby rendering the exact same results produced by standard SQL query builders. Once again, the great advantage of the QBE tool over other SQL tools is that it ships with the DBD product that accompanies nearly all versions of Delphi. If you have access to a more powerful SQL Builder, you might want to use it instead of the QBE tool. However, QBE works fine in most circumstances, even when running against SQL data in an InterBase table.

***End Note***

That's it for the discussion of the basic principles of relational databases. You've seen how to build master-detail relationships and how to construct joins. More important, you've seen how Delphi encapsulates these key aspects of relational database design. The rest of this chapter digs in further and discusses other tools Delphi uses to help you create client/server or standard database applications.

(c)The Fields Editor

The Fields Editor enables you to associate custom objects with some or all of the fields from a table. By associating a custom object with a field, you can control the way a field displays, formats, validates, and inputs data. The Fields Editor also enables you to add new fields to a table at runtime and to then calculate the values that will be shown in the new fields. When you are done you will end up adding a calculated field to your table.

In this section and the next, you will be building a program called MASS, which illustrates both the Fields Editor and calculated fields. This program is important, so you should try to use your copy of Delphi to follow the steps described.

You can access the Fields Editor from either a TTable or TQuery object. To get started, drop a TQuery object on a form, set up the DBDEMOS alias, enter the SQL statement select * from animals, and make the query active.

Drop down the Object Selector at the top of the Object Inspector. Notice that you currently have two components in use: TForm and TQuery.

Right-click the TQuery object and select the Fields Editor menu choice to bring up the Fields Editor. Right-click the Fields Editor and select Add Fields from the menu to pop up the Add Fields dialog, as shown in Figure 8.16.

***Insert Figure 8.16                                                                                                                                           09fig16                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.16

The Add Fields dialog box from the Fields Editor.

By default, all the fields in the dialog box are selected. Click the OK button to select all five fields, and then close the Fields Editor.

Open the Object Selector a second time; notice that five new objects now appear on your form, as shown in Figure 8.17.

***Insert Figure 8.17                                                                                                                                           09fig17                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.17

The Object Selector lists the objects created in the Fields Editor. You also can find this list in the TForm1 class definition.

These objects help you control your presentation of the Animals table to the user.

Here's a complete list of the objects you just created:

Query1NAME: TStringField;

Query1SIZE: TSmallintField;

Query1WEIGHT: TSmallintField;

Query1AREA: TStringField;

Query1BMP: TBlobField;

I cut and pasted this list from the TForm1 class definition found in the editor window. The origins of the names shown here should be fairly obvious. The Query1 part comes from the default name for the TQuery object, and the second half of the name comes from the fields in the Animals table. If I had renamed the Query1 object to Animal, I would have produced names that looked like this:

AnimalNAME

AnimalSIZE

AnimalWEIGHT

This convention can be very useful if you are working with several tables and want to know at a glance which table and field are being referenced by a particular variable.

The names of the fields in the example shown here are capitalized only because the table in question is a dBASE table. dBASE tables automatically capitalize all letters in field names. If I had chosen to work with some other type of table, the capitalization of the letters in the field name would have followed the rules defined by the current database software.

Each of the objects created in the Fields Editor is a descendant of TField. The exact type of descendant depends on the type of data in a particular field. For instance, the Query1WEIGHT field is of type TSmallIntField, whereas the Query1NAME field is of type TStringField. You will see these two field types most often. Other common types include TDateField and TCurrencyField, neither of which are used in this particular table. Remember that these types were selected to correspond with the field types in the table itself.

TStringField, TSmallIntField, and the other objects shown here are all descendants of TField and share its traits. If you want, you can treat these objects exactly as you would the TField object. For instance, you can write this:

  S :=  TIntegerField.AsString;

And you can write this:

  S := TIntegerField.Name.

However, these descendants of TField are smart objects and have several traits that go beyond the functionality of their common ancestor.

To start getting a feel for what you can do with TField descendants, you should open the browser, turn off the option to view Private and Protected fields, and then scroll through the Public and Published properties and methods.

The most important property you will see is called Value. You can access it like this:

procedure TForm1.Button1Click(Sender: TObject);

var

  i: Integer;

  S: string;

begin

  i := Query1SIZE.Value;

  S := Query1NAME.Value;

  Inc(i);

  S := 'Foo';

  Query1SIZE.Value := i;

  Query1NAME.Value := S;

end;

The code shown here first assigns values to the variables i and S. The next two lines change these values, and the last two lines reassign the new values to the objects. Writing code exactly like this in a program usually wouldn't make much sense, but it illustrates the syntax used by TField descendants.

The Value property always conforms to the type of the field you have instantiated. For instance, TStringFields are strings, whereas TCurrencyFields always return floating-point double values. However, if you show a TCurrencyField in a data-aware control, it will return a string that looks like this: "$5.00". The dollar sign and the rounding to two decimal places are simply part and parcel of what a TCurrencyField is all about.

The preceding example might make you think that these variables are declared as Variants. The point here is that TCurrencyField.Value is declared as a Double. If you tried to assign a string to it, you would get a type mismatch. Likewise, TIntegerField.Value is declared as a LongInt, and so on. TSmallIntField and TWordField are both descendants of TIntegerField, and inherit the Value declaration as a LongInt. However, they have other internal code that has an impact on the Value field, just as the TCurrencyField rings some changes on its Value field to make it look like a monetary value. If you have the source, look up DBTABLES.PAS and DB.PAS to find the details of these constructions. At any rate, the point here is that the preceding code is an example of polymorphism; it is not an example of relaxed type-checking. The Value field has a specific type; it just undergoes polymorphic changes.

If you want the names of each field in the current dataset, you should reference the FieldName property through one of the following two methods:

S := Query1.Fields[0].FieldName;

S := Query1NAME.FieldName;

If you want the name of an object associated with a field, you should use the Name property:

S := Query1.Fields[0].Name;

S := Query1NAME.Name;

When you're using the Animals table, the first two examples shown here yield the string "Name", whereas the second two lines yield "Query1NAME".

Special properties are associated with most of the major field types. For instance, TIntegerFields have DisplayFormat and DisplayEdit properties, as well as MinValue and MaxValue properties. TStringFields, on the other hand, have none of these properties, but they do have an EditMask property, which works just like the TEditMask component found on the Additional page of the Component Palette. All these properties are used to control the way data is displayed to the user or the way that input from the user should be handled.

***Begin Note***

Note

I don't want to get ahead of myself, but properties such as MinValue and MaxValue are also used in the Data Dictionary, as will be explained later in this chapter. Changes made in the Data Dictionary will affect these values as seen in the Object Inspector, but changes in the Object Inspector will not affect the Data Dictionary. Don't worry if this information doesn't make the slightest bit of sense yet, as I will get to the Data Dictionary in just a little while.

***End Note***

You should be aware of one more thing about the Fields Editor. You can use this tool not only to build objects that encapsulate existing fields, but also to build objects that represent new fields. For instance, suppose you wanted to create a sixth field, MASS, which contains the product of the SIZE and WEIGHT fields, in the Animals table.

To create the MASS field, open the Fields Editor again, right-click it, and select the New Field menu choice. In the top part of the New Field dialog, enter the word MASS. Now set its type to Integer and leave its field type set to Calculated, as shown in Figure 8.18.

***Insert Figure 8.18                                                                                                                                           09fig18                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.18

Creating the MASS field in the Fields Editor.

If you close the Fields Editor and add a TDataSource and TDBGrid to your project, you will see that the Animals table now appears to have six fields, the last of which is called MASS.

Of course, creating a field is one thing, but filling it in at runtime with an appropriate value is another. The act of placing a value in the new field you have created involves calculated fields, which are addressed in the next section.

(c)Calculated Fields

Calculated fields are one of the most valuable fruits of the Fields Editor. You can use these fields for several different purposes, but two stand out:

  [lb]       If you need to perform calculations on two or more of the fields in a dataset and want to show the results of the calculations in a third field, you can use calculated fields. A scenario describing this type of situation was set up in the preceding section and will be explained further in this section.

  [lb]       If you are viewing one dataset and want to perform calculations or display data that involves lookups in at least one additional dataset, you can use the Fields Editor and calculate fields to show the results of these calculations in a new field of the first dataset. You also can use a second, much better method for doing lookups. I will talk about that method later in this chapter. As a rule, you should do calculations in calculated fields, and lookups in lookup fields, though calculated fields are powerful enough to fill a number of different roles in your programs.

The MASS program illustrates one example of the first of the two uses for calculated fields. You got this program started in the preceding section when you created the field called MASS and displayed it in a grid.

To continue working with the MASS program, highlight the Query1 object and set the Object Inspector to the Events page. Now create an OnCalcFields event that looks like this:

procedure TForm1.Query1CalcFields(DataSet: TDataset);

begin

  Query1MASS.Value := Query1SIZE.Value * Query1WEIGHT.Value;

end;

The code shown here assigns the value of the Query1MASS object to the product of the Query1SIZE and Query1WEIGHT fields. This kind of multiplication is legal because all the fields are of the same type.

OnCalcField methods get called each time a record is first displayed to the user. As a result, all the MASS fields displayed in the grid are properly filled in, as shown in Figure 8.19.

***Insert Figure 8.19                                                                                                                                           09fig19                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.19

The MASS field contains the product of the WEIGHT and SIZE fields. A TDBImage control contains a bitmap from the BMP field of the table.

To get the image shown in Figure 8.19, I opened the Column property in the TDBGrid object and selected Add All Fields. I then deleted the Area and BMP fields and closed the Column Property Editor. I will talk more about the grid object later in this chapter.

If you choose to never instantiate a particular field in the Fields Editor, the current dataset you are working with no longer contains that field. It can't be accessed programmatically or visually at runtime. Usually, you want to achieve exactly this effect, so this trait can generally be perceived as a strong benefit. However, sometimes it might not serve your purposes, and in those cases, you should either create an object for all the fields in a table or stay away from the Fields Editor altogether.

(c)TDBGrid at Runtime

dbGrids can be completely reconfigured at runtime. You can hide and show columns, and you can change the order, the color, and the width of columns.

The MOVEGRID program, and depicted in Figure 9.20, shows how to take a TDBGrid through its paces at runtime.. Because I space considerations, I do not include the entire program as a listing in the text. Instead,the code for the MOVEGRID program is in the CHAP08 directory on this book's CD-ROM. The program is fairly straightforward except for two brief passages. The first passage involves creating check box controls on-the-fly, whereas the second shows how to change the order of items in a list box on-the-fly.

***Insert Figure 8.20                                                                                                                                           09fig20                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.20

The main MOVEGRID program enables you to change the appearance of a grid at runtime.

When the user wants to decide which fields are visible, MOVEGRID pops up a second form and displays the names of all the fields from the ORDERS table in a series of check boxes. The user can then select the fields that he or she wants to make visible. The selected check boxes designate fields that are visible, whereas the nonselected ones represent invisible fields. The program also enables you to set the order and width of fields, as well as to hide and show the titles at the top of the grid.

 

Listing 8.1[em]The MOVEGRID Program

unit Main;

 

{ Program copyright (c) 1995..98 by Charles Calvert }

{ Project Name: MOVEGRID Version 3.0}

 

{ Taking the grid through a few a few of its paces

  at runtime.

 

  Shows how to set the width of a field in a grid,

  how to set the order of the fields in a grid, as well as

  how to turn the titles and other features of a

  grid on and off. }

 

interface

 

uses

  SysUtils, WinTypes, WinProcs,

  Messages, Classes, Graphics,

  Controls, Forms, Dialogs,

  Grids, DBGrids, DBTables,

  StdCtrls, Buttons, Db,

  Menus, ExtCtrls;

 

const

  NewColor = clYellow;

 

type

  TForm1 = class(TForm)

    DBGrid1: TDBGrid;

    MainMenu1: TMainMenu;

    File1: TMenuItem;

    ShowFieldEditor1: TMenuItem;

    N1: TMenuItem;

    Exit1: TMenuItem;

    Names1: TMenuItem;

    FieldNames1: TMenuItem;

    FieldObjectNames1: TMenuItem;

    Options1: TMenuItem;

    AnimateTitles1: TMenuItem;

    ColorRows1: TMenuItem;

    MarkColumn: TMenuItem;

    N2: TMenuItem;

    ToggleTitles1: TMenuItem;

    ToggleIndicator1: TMenuItem;

    ShowTitlesIndicator1: TMenuItem;

    ColLines1: TMenuItem;

    RowLines1: TMenuItem;

    ShowAllOptions1: TMenuItem;

    N3: TMenuItem;

    MustPressF2orEntertoEdit1: TMenuItem;

    ChangeWidthofField1: TMenuItem;

    HideCurrentColumn1: TMenuItem;

    MoveCurrentColumn1: TMenuItem;

    Help1: TMenuItem;

    Contents1: TMenuItem;

    About1: TMenuItem;

    Timer1: TTimer;

    procedure NameClick(Sender: TObject);

    procedure CustNoIndexClick(Sender: TObject);

    procedure FieldNameClick(Sender: TObject);

    procedure VisibleClick(Sender: TObject);

    procedure ListBox1DblClick(Sender: TObject);

    procedure DBGrid1DblClick(Sender: TObject);

    procedure ToggleTitles1Click(Sender: TObject);

    procedure Contents1Click(Sender: TObject);

    procedure About1Click(Sender: TObject);

    procedure AnimateTitles1Click(Sender: TObject);

    procedure Timer1Timer(Sender: TObject);

    procedure ColorRows1Click(Sender: TObject);

    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;

      DataCol: Integer; Column: TColumn; State: TGridDrawState);

    procedure FormCreate(Sender: TObject);

    procedure FormDestroy(Sender: TObject);

    procedure MarkColumnClick(Sender: TObject);

    procedure ToggleIndicator1Click(Sender: TObject);

    procedure ShowTitlesIndicator1Click(Sender: TObject);

    procedure ColLines1Click(Sender: TObject);

    procedure RowLines1Click(Sender: TObject);

    procedure ShowAllOptions1Click(Sender: TObject);

    procedure MustPressF2orEntertoEdit1Click(Sender: TObject);

    procedure ChangeWidthofField1Click(Sender: TObject);

    procedure HideCurrentColumn1Click(Sender: TObject);

    procedure MoveCurrentColumn1Click(Sender: TObject);

  private

    FDefaultColor: TColor;

    FTaggedColumns: TList;

    FShowTitles: Boolean;

    procedure ColorTitles(UseDefaultColor: Boolean);

    function IsTagged(Column: TColumn): Boolean;

    procedure HandleCaption;

  public

    { Public declarations }

  end;

 

var

  Form1: TForm1;

 

implementation

uses

  VisForm, AboutBox1, ShowNames1, DMod1, ShowOptions1;

{$R *.DFM}

 

procedure TForm1.FormCreate(Sender: TObject);

begin

  FTaggedColumns := TList.Create;

  FDefaultColor := DBGrid1.Font.Color;

  HandleCaption;

end;

 

procedure TForm1.FormDestroy(Sender: TObject);

begin

  FTaggedColumns.Free;

end;

 

procedure TForm1.NameClick(Sender: TObject);

var

  i: Integer;

begin

  ShowNames.ListBox1.Clear;

  for i := 0 to DMod.Query1.FieldCount - 1 do

    ShowNames.ListBox1.Items.Add(DMod.Query1.Fields[i].Name);

  ShowNames.ShowModal;

end;

 

procedure TForm1.CustNoIndexClick(Sender: TObject);

var

  S: String;

begin

  S := '';

  InputQuery('Get Index of CustNo', 'Enter a number between 0 and 10: ', S);

  DMod.Query1CustNo.Index := StrToInt(S);

  DBGrid1.SelectedField := DMod.Query1CustNo;

end;

 

procedure TForm1.FieldNameClick(Sender: TObject);

var

  i: Integer;

begin

  ShowNames.ListBox1.Clear;

  for i := 0 to DMod.Query1.FieldCount - 1 do

    ShowNames.ListBox1.Items.Add(DMod.Query1.Fields[i].FieldName);

  ShowNames.ShowModal;

end;

 

procedure TForm1.VisibleClick(Sender: TObject);

begin

  VisiForm.ShowMe(DMod.Query1);

end;

 

procedure TForm1.ListBox1DblClick(Sender: TObject);

{var

  Input, Temp, CurPos: string;

  StartPos: Integer;}

begin

{  Input := '';

  Temp := ListBox1.Items.Strings[ListBox1.ItemIndex];

  StartPos := ListBox1.ItemIndex;

  CurPos := 'Current Position: ' + IntToStr(StartPos);

  InputQuery(CurPos, 'New Position', Input);

  ListBox1.Items.Delete(ListBox1.ItemIndex);

  ListBox1.Items.Insert(StrToInt(Input), Temp);

  DMod.Query1.Fields[StartPos].Index := StrToInt(Input); }

end;

 

procedure TForm1.DBGrid1DblClick(Sender: TObject);

var

  S, Name: string;

begin

  S := '';

  Name := DBGrid1.SelectedField.Name;

  if not InputQuery(Name, 'Enter New Length', S) then Exit;

  DBGrid1.SelectedField.DisplayWidth := StrToInt(S);

end;

 

procedure TForm1.ToggleTitles1Click(Sender: TObject);

begin

  if FShowTitles then

    DBGrid1.Options := DBGrid1.Options + [dgTitles]

  else

    DBGrid1.Options := DBGrid1.Options - [dgTitles];

  FShowTitles := not FShowTitles;

end;

 

procedure TForm1.Contents1Click(Sender: TObject);

const

  CR = #13#10;

  S :PChar = 'Grid: Double click a column to change its width.' + CR +

       'ListBox: Double click to change order of Fields. ' + CR +

       'Visible Btn: Hide Fields. '+ CR +

       'FieldName Btn: Show names of fields.' + CR +

       'Name Btn: Show names of field objects.' + CR +

       'ToggleTitles Btn: Hide/Show titles & indicators.';

  S1:PChar = 'Example program for changing a Grid at runtime.';

begin

  MessageBox(Handle, S, S1, mb_Ok or mb_IconInformation);

end;

 

procedure TForm1.About1Click(Sender: TObject);

begin

  AboutBox.ShowModal;

end;

 

procedure TForm1.ColorTitles(UseDefaultColor: Boolean);

const

  Colors: array [0..5]of TColor = (clRed, clBlue, clGreen, clLime, clWhite, clFuchsia);

var

  i: Integer;

  Column: TColumn;

  ColumnTitle: TColumnTitle;

begin

  for i := 0 to DBGrid1.Columns.Count - 1 do begin

    Column := DBGrid1.Columns.Items[i];

    ColumnTitle := Column.Title;

    if (UseDefaultColor) then

      ColumnTitle.Font.Color := FDefaultColor

    else

      ColumnTitle.Font.Color := Colors[random(7)];

  end;

end;

 

procedure TForm1.AnimateTitles1Click(Sender: TObject);

begin

  Timer1.Enabled := not(Timer1.Enabled);

  AnimateTitles1.Checked := Timer1.Enabled;

  if not(AnimateTitles1.Checked) then

    ColorTitles(True);

end;

 

procedure TForm1.Timer1Timer(Sender: TObject);

begin

   ColorTitles(False);

end;

 

procedure TForm1.ColorRows1Click(Sender: TObject);

begin

  ColorRows1.Checked := not (ColorRows1.Checked);

  DBGrid1.Repaint();

end;

 

function TForm1.IsTagged(Column: TColumn): Boolean;

var

  i: Integer;

  C: Pointer;

begin

  for i := 0 to FTaggedColumns.Count - 1 do begin

    C := FTaggedColumns.Items[i];

    if (C = Column) then begin

      Result := True;

      Exit;

    end;

  end;

  Result := False;

end;

 

procedure TForm1.HandleCaption;

var

  Column: TColumn;

  S: string;

begin

  Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

 

  S := IntToStr(DBGrid1.SelectedIndex);

  Caption := S;

  if (Column.Font.Color = FDefaultColor) then

    Caption := 'Column ' + S + ' is Default'

  else

    Caption := 'Column ' + S + ' is Marked';

end;

 

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;

  DataCol: Integer; Column: TColumn; State: TGridDrawState);

begin

  if (IsTagged(Column)) then begin

   DBGrid1.Canvas.Brush.Color := clPurple;

   DBGrid1.Canvas.FillRect(Rect);

  end else if (ColorRows1.Checked) then begin

   if (DMod.Query1ItemsTotal.Value < 1000) then

     DBGrid1.Canvas.Font.Color := clRed

   else if (DMod.Query1ItemsTotal.Value < 10000) then

     DBGrid1.Canvas.Font.Color := clBlue

   else

     DBGrid1.Canvas.Font.Color := clGreen;

  end;

  DBGrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);

end;

 

procedure TForm1.MarkColumnClick(Sender: TObject);

var

  Column: TColumn;

begin

  MarkColumn.Checked := not(MarkColumn.Checked);

  Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

  if (MarkColumn.Checked) then begin

    Column.Font.Color := NEWCOLOR;

    Column.Font.Style := [fsBold];

    FTaggedColumns.Add(Column);

  end else begin

    Column.Font.Color := FDefaultColor;

    Column.Font.Style := [];

    FTaggedColumns.Remove(Column);

  end;

  HandleCaption;

end;

 

procedure TForm1.ToggleIndicator1Click(Sender: TObject);

begin

  if dgIndicator in DBGrid1.Options then

    DBGrid1.Options := DBGrid1.Options - [dgIndicator]

  else

    DBGrid1.Options := DBGrid1.Options + [dgIndicator];

end;

 

procedure TForm1.ShowTitlesIndicator1Click(Sender: TObject);

begin

  ShowTitlesIndicator1.Checked :=

    not ((dgIndicator in DBGrid1.Options) and (dgTitles in DBGrid1.Options));

 

  if (ShowTitlesIndicator1.Checked) then

    DBGrid1.Options := TDBGridOptions(DBGrid1.Options) + [dgIndicator, dgTitles]

  else

    DBGrid1.Options := DBGrid1.Options - [dgIndicator, dgTitles];

end;

 

procedure TForm1.ColLines1Click(Sender: TObject);

begin

  if (dgColLines) in (DBGrid1.Options) then

    DBGrid1.Options := DBGrid1.Options - [dgColLines]

  else

    DBGrid1.Options := DBGrid1.Options + [dgColLines];

 

  ColLines1.Checked := dgColLines in DBGrid1.Options;

end;

 

procedure TForm1.RowLines1Click(Sender: TObject);

begin

  if (dgRowLines) in (DBGrid1.Options) then

    DBGrid1.Options := DBGrid1.Options - [dgRowLines]

  else

    DBGrid1.Options := DBGrid1.Options + [dgRowLines];

 

  RowLines1.Checked := dgRowLines in DBGrid1.Options;

end;

 

procedure TForm1.ShowAllOptions1Click(Sender: TObject);

begin

  ShowOptionsForm.ShowOptions(DBGrid1.Options);

end;

 

procedure TForm1.MustPressF2orEntertoEdit1Click(Sender: TObject);

begin

  DBGrid1.Options := DBGrid1.Options - [dgAlwaysShowEditor];

  MustPressF2orEntertoEdit1.Checked := not (dgAlwaysShowEditor in DBGrid1.Options);

 

end;

 

procedure TForm1.ChangeWidthofField1Click(Sender: TObject);

var

  S: string;

  Column: TColumn;

begin

  S := '';

  Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

  if InputQuery('Data Needed', 'New Width of Selected Field', S) then

    Column.Width := StrToInt(S)

end;

 

procedure TForm1.HideCurrentColumn1Click(Sender: TObject);

var

  Column: TColumn;

begin

  if (MessageBox(Handle, 'Hide Column?',

       'Hide Info?', MB_YESNO or MB_ICONQUESTION) = ID_YES) then begin

    Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

    Column.Visible := False;

  end;

end;

 

procedure TForm1.MoveCurrentColumn1Click(Sender: TObject);

var

  S: string;

begin

  S := '';

  if (InputQuery('Data Needed', 'Enter new position of column', S)) then

    DMod.Query1.Fields[DBGrid1.SelectedIndex].Index := StrToInt(S);

end;

 

end.

The VisiForm unit, shown in Listing 8.2, displays a set of check boxes in which the user designates which fields are to be made visible.

Listing 8.2[em]The VisiForm Unit

unit Visform;

 

{ Program copyright (c) 1998 by Charles Calvert }

{ Project Name: MOVEGRID }

 

interface

 

uses

  SysUtils, WinTypes, WinProcs,

  Messages, Classes, Graphics,

  Controls, Forms, Dialogs,

  DBTables, StdCtrls;

 

const

  RadSize = 25;

 

type

  TVisiForm = class(TForm)

  private

    R: array[0..25] of TCheckBox;

    procedure CreateRad(Index: Integer; Name: String; Visible: Boolean);

  public

    { Public declarations }

    procedure ShowMe(Query1: TQuery);

  end;

 

var

  VisiForm: TVisiForm;

 

implementation

 

{$R *.DFM}

 

procedure TVisiForm.CreateRad(Index: Integer; Name: String; Visible: Boolean);

begin

  R[Index] := TCheckBox.Create(Self);

  R[Index].Parent := VisiForm;

  R[Index].Caption := Name;

  R[Index].Left := 10;

  R[Index].Top := Index * RadSize;

  R[Index].Width := 200;

  R[Index].Checked := Visible;

end;

 

procedure TVisiForm.ShowMe(Query1: TQuery);

var

  i: Integer;

begin

  for i := 0 to Query1.FieldCount - 1 do

    CreateRad(i, Query1.Fields[i].Name, Query1.Fields[i].Visible);

  Height := i * (RadSize + 5);

  ShowModal;

  for i := 0 to Query1.FieldCount - 1 do

    Query1.Fields[i].Visible := R[i].Checked;

end;

 

end.

Listing 8.3 shows the ShowOptions unit.

Listing 8.3[em]The ShowOptions Unit

unit ShowOptions1;

 

interface

 

uses

  Windows, Messages, SysUtils,

  Classes, Graphics, Controls,

  Forms, Dialogs, StdCtrls,

  DBGrids;

 

type

  TShowOptionsForm = class(TForm)

    CheckBox1: TCheckBox;

    CheckBox2: TCheckBox;

    CheckBox3: TCheckBox;

    CheckBox4: TCheckBox;

    CheckBox5: TCheckBox;

    CheckBox6: TCheckBox;

    CheckBox7: TCheckBox;

    CheckBox8: TCheckBox;

    CheckBox9: TCheckBox;

    CheckBox10: TCheckBox;

    CheckBox11: TCheckBox;

    CheckBox12: TCheckBox;

    procedure FormCreate(Sender: TObject);

  private

    FCheckBox: array[0..11] of TCheckBox;

  public

    procedure ShowOptions(Options: TDBGridOptions);

  end;

 

var

  ShowOptionsForm: TShowOptionsForm;

 

implementation

 

{$R *.DFM}

 

procedure TShowOptionsForm.FormCreate(Sender: TObject);

var

  i, j: Integer;

begin

  j := 0;

  for i := 0 to ComponentCount - 1 do

    if Components[i] is TCheckBox then begin

      FCheckBox[j] := TCheckBox(Components[i]);

      Inc(j);

    end;

end;

 

procedure TShowOptionsForm.ShowOptions(Options: TDBGridOptions);

var

  i: Integer;

begin

  for i := 0 to 11 do

    if (TDBGridOption(i) in Options) then

      FCheckBox[i].Checked := True

    else

      FCheckBox[i].Checked := False;

  ShowModal();

end;

 

end.

In the  following subsections, you will find descriptions of the key parts of the MOVEGRID program. Understanding its constituent parts will help you to take control over the grids you display in your programs.

(d)Controlling the Options Property of a DBGrid at Runtime

You can use the Options field of a TDBGrid to change its appearance. The Options property has the following possible values:

dgEditing                                          Set to True by default, enables the user to edit a grid. You can also set the grid's ReadOnly property to True or False.

dgTitles                                            Designates whether titles can be seen.

dgIndicator                                      Determines whether to show the small icons on the left of the grid.

dgColumnResize                              Designates whether the user can resize columns.

dgColLines                                      Determines whether to show the lines between columns.

dgRowLines                                     Designates whether to show the lines between rows.

dgTabs                                            Enables the user to press Tab and Shift+Tab between columns.

dgAlwaysShowEditor                       Ensures that you are always in edit mode.

dgRowSelect                                   Can select rows, mutually exclusive with dgAlwaysShowEditor.

dgAlwaysShowSelection                  Allows the selection to remain even when the grid loses focus.

dgConfirmDelete                              Shows a message box when a user presses Ctrl+Delete.

dgCancelOnExit                               Cancels Inserts on exit if no changes were made to row

dgMultiSelect                                   Can select multiple contiguous or noncontiguous rows with Ctrl+click or Shift-Click, etc.

The following is the declaration for the enumerated type where these values are declared:

TDBGridOption = (dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator,

  dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect,

  dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit, dgMultiSelect);

 

TDBGridOptions = set of TDBGridOption;

For instance, you can set the options at runtime by writing code that looks like this:

DBGrid1.Options := [dgTitles];

The preceding code, in effect, turns all the options to False except dgTitles. The following code turns off all options but dgTitles and dgIndicator:

DBGrid1.Options := [dgTitles, dgIndicator];

More specifically, the code sets the DBGrid1.Options property to a set that contains only dgTitles and dgIndicator. This code toggles dgTitles and dgIndicator off and on each time it is called:

procedure TForm1.ToggleTitles1Click(Sender: TObject);

begin

  if FShowTitles then

    DBGrid1.Options := DBGrid1.Options + [dgTitles]

  else

    DBGrid1.Options := DBGrid1.Options - [dgTitles];

  FShowTitles := not FShowTitles;

end;

The set operators shown in ToggleTitles1Click move the dgTitles option in and out of DBGrid.Options property.

The following code shows how to toggle back and forth between showing both indicators and titles and hiding both indicators and titles:

procedure TForm1.ShowTitlesIndicator1Click(Sender: TObject);

begin

  ShowTitlesIndicator1.Checked :=

    not ((dgIndicator in DBGrid1.Options) and (dgTitles in DBGrid1.Options));

 

  if (ShowTitlesIndicator1.Checked) then

    DBGrid1.Options := DBGrid1.Options + [dgIndicator, dgTitles]

  else

    DBGrid1.Options := DBGrid1.Options - [dgIndicator, dgTitles];

end;

This code moves both the dgIndicator and dgTitles elements in and out of the Options array as needed. The + (plus) operator adds elements to a set, and the - (minus) operator moves things out of the set. If you need to move multiple elements in and out of the set, just use brackets to build a multimember set.

The following code shows how to toggle the dgRowLines element of the Options property on and off at runtime:

procedure TForm1.RowLines1Click(Sender: TObject);

begin

  if (dgRowLines) in (DBGrid1.Options) then

    DBGrid1.Options := DBGrid1.Options - [dgRowLines]

  else

    DBGrid1.Options := DBGrid1.Options + [dgRowLines];

 

  RowLines1.Checked := dgRowLines in DBGrid1.Options;

end;

The last line of code in the routine toggles the check mark before the RowLines menu item so that it reflects the current state of the grid. In other words, if dgRowLines is part of the set, the menu item will be checked; if it is not part of the set, the menu item will not be checked.

In this section, you learned how to toggle the elements of the Options set back and forth at runtime. Most of the code for this process is fairly simple, though you need to have a basic grasp of Delphi set operations to understand how it works.

(d)Displaying the DBGrid Options at Runtime

Now that you know how to toggle the Options of a DBGrid, spending a few moments learning how to display the Options to the user at runtime might be worthwhile. As you can see in Figure 8.21, I use a set of 12 check boxes to depict the current state of the 12 DBGrid options. Next, I will explain how the code that drives this form works.

***Insert Figure 8.21                                                                                                                                           09fig21                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.21

Using check boxes to depict the available DBGrid options to the user at runtime.

The code for these operations appears in the ShowOptions1 unit, found in Listing 8.3. In the declaration for the ShowOptions object, I declare an array of check boxes:

FCheckBox: array[0..11] of TCheckBox;

I initialize these check boxes in the constructor for the ShowOptions.pas form:

procedure TShowOptionsForm.FormCreate(Sender: TObject);

var

  i, j: Integer;

begin

  j := 0;

  for i := 0 to ComponentCount - 1 do

    if Components[i] is TCheckBox then begin

      FCheckBox[j] := TCheckBox(Components[i]);

      Inc(j);

    end;

end;

This code iterates over all the Components on the form checking for ones that are of type TCheckBox. When it finds one, it adds the component to the array of check boxes. The code uses the is operator to check whether each item in the components array is of type TCheckBox.

***Begin Note***

Note

A components array is implemented in TComponent and is maintained automatically for all components that descend from TComponent. The concept of ownership is what governs which items are put in the components array. All components that are owned by the form are automatically, and by definition, included in the components array for the form. In other words, if you drop a component on a form, it will be listed in the components array for the form. You can use the ComponentCount property of the form to determine how many items are in the components array. Remember that it is not only forms that maintain components arrays; all components support the concept of components arrays.

***End Note***

After you fill in the array of check boxes, toggling the Checked property of each check box is a simple matter, depending on the current state of each DBGrid option:

procedure TShowOptionsForm.ShowOptions(Options: TDBGridOptions);

var

  i: Integer;

begin

  for i := 0 to 11 do

    if (TDBGridOption(i) in Options) then

      FCheckBox[i].Checked := True

    else

      FCheckBox[i].Checked := False;

  ShowModal();

end;

This code determines which items in the DBGridOptions set are turned on and then toggles the appropriate check box. The code depends, of course, on the fact that the DBGridOptions set is a list of items with values ranging from 0 to 11. To understand this code, you must grasp that DBGridOptions is an enumerated type, as described earlier in this chapter.

(c)Working with TDBGrid Columns

The following subsections of the chapter cover changing the colors of the titles, columns, rows, and even individual cells in a TDBGrid. This type of change is not something you have to do very often, but when the need comes around, it is fairly pressing. Furthermore, this code will help you learn enough about the grid object so that you can find your way around if you need to make other changes to its behavior. Before reading these sections, you should be sure to run the MOVEGRID program, because it will be hard to read the code without some understanding of what it does.

(d)Changing the Titles in a TDBGrid Object

You can color the titles in a TDBGrid this way:

procedure TForm1.ColorTitles(UseDefaultColor: Boolean);

const

  Colors: array [0..5]of TColor =

    (clRed, clBlue, clGreen, clLime, clWhite, clFuchsia);

var

  i: Integer;

  Column: TColumn;

  ColumnTitle: TColumnTitle;

begin

  for i := 0 to DBGrid1.Columns.Count - 1 do begin

    Column := DBGrid1.Columns.Items[i];

    ColumnTitle := Column.Title;

    if (UseDefaultColor) then

      ColumnTitle.Font.Color := FDefaultColor

    else

      ColumnTitle.Font.Color := Colors[random(6)];

  end;

end;

This code first declares an array of colors. The constants seen here are predeclared colors of type TColor. The TColor type is declared in Graphics.pas:

TColor = -$7FFFFFFF-1..$7FFFFFFF;

The actual number of colors in the ColorTitles method was chosen at random. I could have added or subtracted colors from the array without changing the rest of code in the routine, with the exception of the number 6, which is passed at random in the routine's last line of code.

The TColumn object defines how a column in a TDBGrid should look. That is, it defines the font, color, and width of the column. The Columns property of a TDBGrid is of type TDBGridColumns, which is a collection of TColumn objects. Each TColumn object has a title. This title is defined in an object of type TColumnTitle. Finally, a TColumnTitle has color, font, and caption properties:

TDBGrid Object

  Columns Property

    TColumn Object

      TColumnTitle Object

        Font, Color, Caption Objects

The preceding list is not an object hierarchy, but just a way of illustrating the relationship between these different entities. In other words, the grid object contains a Columns property, and the Columns property contains TColumn objects, and each TColumn object contains a TColumnTitle, which in turn contains a Font, Color, and Caption.

To get hold of a TColumn object, you can use the Items property of TDBGridColumns:

Column := DBGrid1.Columns.Items[i];

To move from a Column object to a TColumnTitle object, you can use the Title property of a TColumn object:

ColumnTitle := Column.Title;

After the preceding ColorTitles method has the ColumnTitle in its hands, it can set the ColumnTitle to whatever color it wants:

    if (UseDefaultColor) then

      ColumnTitle.Font.Color := FDefaultColor

    else

      ColumnTitle.Font.Color := Colors[random(6)];

The FDefaultColor variable is of type TColor. In the OnCreate event for the form, I set it to the default color for the grid's font:

FDefaultColor := DBGrid1.Font.Color;

If you understand what has happened here, you will have no trouble with the next two sections, which cover changing the color of columns and rows in a grid.

(d)Changing an Entire Column in a Grid

Delphi makes changing the look of a single column fairly easy. Writing this kind of code will enable you to emphasize a certain part of a dataset or to bring the user's eye to a certain part of your form.

This method changes the appearance of a column in a TDBGrid:

procedure TForm1.MarkColumnClick(Sender: TObject);

var

  Column: TColumn;

begin

  MarkColumn.Checked := not(MarkColumn.Checked);

  Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

  if (MarkColumn.Checked) then begin

    Column.Font.Color := NEWCOLOR;

    Column.Font.Style := [fsBold];

    FTaggedColumns.Add(Column);

  end else begin

    Column.Font.Color := FDefaultColor;

    Column.Font.Style := [];

    FTaggedColumns.Remove(Column);

  end;

  HandleCaption;

end;

This code first grabs hold of a selected column in a grid:

Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

If the user has indicated that he or she wants this column to stand out, it is a simple matter to change its background color and set its font to bold:

Column.Font.Color := NEWCOLOR;

Column.Font.Style := [fsBold];

Notice that the Style property is a set, so you use a template class to manipulate its members. Here, as found in GRAPHICS.pas, are the different styles you can associate with a font:

TFontStyle = (fsBold, fsItalic, fsUnderline, fsStrikeOut);

I don't mention some parts of the code, such as MarkColumn and HandleCaption, because they are merely part of the logic of this program and are not germane to the subject of changing an individual column.

(d)Changing the Color of a Row in a Grid

In the preceding two sections on columns and column titles, you worked with the TColumn object. You can also change the color of the text in a TDBGrid by working with the font associated with the grid's TCanvas object:

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;

  DataCol: Integer; Column: TColumn; State: TGridDrawState);

begin

  if (IsTagged(Column)) then begin

   DBGrid1.Canvas.Brush.Color := clPurple;

   DBGrid1.Canvas.FillRect(Rect);

  end else if (ColorRows1.Checked) then begin

   if (DMod.Query1ItemsTotal.Value < 1000) then

     DBGrid1.Canvas.Font.Color := clRed

   else if (DMod.Query1ItemsTotal.Value < 10000) then

     DBGrid1.Canvas.Font.Color := clBlue

   else

     DBGrid1.Canvas.Font.Color := clGreen;

  end;

  DBGrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);

end;

If you run the MOVEGRID program, you can see the effect of this code by choosing Color Rows from the Options menu of that program. Be sure that none of the other special effects are turned on when you choose this option because they can interfere with your ability to see its results. Be sure to scroll the grid up and down after turning on the effect because the data at the top of the grid is fairly homogenous.

The data shown in the grid is from the Orders table in the DBDEMOS database. The code shown here colors each row in the grid according to the amount of money reported in the ItemsTotal field of the Orders table. For instance, if the ItemsTotal field contains a sum less than $1000, that row is painted Red:

DBGrid1.Canvas.Font.Color := clRed

Here the code sets the font of the TCanvas object for the grid to clRed. Nothing could be simpler.

(d)Changing the Width of a Column

The user can change the width of a column at runtime by using the mouse. But how can you do the same thing programmatically without any input from the user?

If you want to change the width of a column at runtime, just change the DisplayWidth property of the appropriate TField object:

DMod.Query1.FieldByName('CustNo').DisplayWidth := 12;

DMod.Query1.CustNo.DisplayWidth := 12;

The value 12 refers to the approximate number of characters that can be displayed in the control. Various factors, such as whether you are using a fixed-pitch font, affect the interpretation of this value. See the online help for additional information.

You can change the width of the column in the grid as follows without affecting the properties of the underlying field:

procedure TForm1.ChangeWidthofField1Click(Sender: TObject);

var

  S: string;

  Column: TColumn;

begin

  S := '';

  Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

  if InputQuery('Data Needed', 'New Width of Selected Field', S) then

    Column.Width := StrToInt(S)

end;

This code asks the user for the width he or she wants to assign to the currently selected column. The code then makes the change by retrieving the column and changing its Width property.

(d)Hiding or Moving Columns in a TDBGrid

The user can change the order of columns in a TDBGrid simply by clicking them and dragging them with a mouse. But how do you proceed if you want to do the same thing at runtime without the user's direct input?

If you want to hide a field at runtime, you can set its Visible property to False:

DMod.Query1.FieldByName('CustNo').Visible := False;

DMod.Query1CustNo.Visible := False;

Both lines of code perform identical tasks. To show the fields again, simply set Visible to True.

Alternatively, you can retrieve a TColumn object from the Grid and then hide it:

procedure TForm1.HideCurrentColumn1Click(Sender: TObject);

var

  Column: TColumn;

begin

  if (MessageBox(Handle, 'Hide Column?',

       'Hide Info?', MB_YESNO or MB_ICONQUESTION) = ID_YES) then begin

    Column := DBGrid1.Columns.Items[DBGrid1.SelectedIndex];

    Column.Visible := False;

  end;

end;

That'll do it! The column disappears from the grid. You can also set the width of a column to 0, which makes the column itself go away, but not the lines between columns. These lines can, however, be toggled separately if you use the TDBGridOptions property.

(d)Displaying a List of Visible Fields

To allow the user to decide which fields are visible, MOVEGRID pops up a second form with a series of check boxes on it, as shown in the VisiForm unit from Listing 8.2. The program actually creates each of these check boxes at runtime. In other words, it doesn't just pop up a form with the correct number of check boxes on it, but instead iterates through the Query1 object from the data module, finds out how many check boxes are needed, and then creates them dynamically at runtime.

To perform these tasks, MOVEGRID calls on a form that is specially designed to display the check boxes:

procedure TForm1.VisibleClick(Sender: TObject);

begin

  VisiForm.ShowMe(DMod.Query1);

end;

Before I discuss the ShowMe method, I want to show you the code the VisiForm unit uses to initialize an array of check boxes. The CreateRad method does the job by allocating memory for each individual member of the array of check boxes. The ShowMe method, shown later in this section, iterates through the Query1 object and calls the CreateRad function to assign one check box to each field. It also asks TQuery for the names of the fields, and determines whether each field is currently hidden or visible. This code creates a check box on-the-fly:

procedure TVisiForm.CreateRad(Index: Integer; Name: String; Visible: Boolean);

begin

  R[Index] := TCheckBox.Create(Self);

  R[Index].Parent := VisiForm;

  R[Index].Caption := Name;

  R[Index].Left := 10;

  R[Index].Top := Index * RadSize;

  R[Index].Width := 200;

  R[Index].Checked := Visible;

end;

*** Begin Note ***

Note

Delphi 3 and higher supports a control called TCheckListBox on the Additional page of the Component Palette. This control allows you to work with lists of check boxes, and would be a good alternative to the code shown here.

***End Note***

R is a simple array of check boxes:

R: array[0..25] of TCheckBox;

Most of the code in this example performs relatively mundane tasks such as assigning names and locations to the check boxes. The following are the two key lines:

R[Index] := TCheckBox.Create(Self);

R[Index].Parent := VisiForm;

The first line actually creates the check box and gives it an owner. The second line assigns a parent to the check box.

***Begin Note***

Note

The difference between a parent and an owner can be confusing at times. A form is always the owner of the components that reside inside it. As such, it is responsible for allocating and deallocating memory for these components. A form might also be the parent of a particular component, which means that Windows will ensure the component will be displayed directly on the form. However, one component might also find that another component is its parent, even though both components are owned by the form. For instance, if you place a TPanel on a form and then two TButtons on the TPanel, all three components will be owned by the form; however, the buttons will have the panel as a parent, whereas the TPanel will have the form as a parent. Ownership has to do with memory allocation. Parenthood usually describes what surface a component will be displayed on. Ownership is a Delphi issue; Parenthood is mostly a Windows API issue. In particular, Windows cares about parenting, and Windows handles the actual drawing of the controls. If you get confused about this issue while in the midst of a lengthy programming session, you can look it up in the online help by searching on the topic Parent.

***End Note***

The rest of the code in the CreateRad method just gives each check box a caption derived from the name of the field it represents and places the check box on the form in a location commensurate with the position of its field in the table.

The ShowMe method of the VisiForm first calls CreateRad, then displays the form, and finally sets the state of the check boxes:

procedure TVisiForm.ShowMe(Query1: TQuery);

var

  i: Integer;

begin

  for i := 0 to Query1.FieldCount - 1 do

    CreateRad(i, Query1.Fields[i].Name, Query1.Fields[i].Visible);

  Height := (Query1.FieldCount - 1) * (RadSize + 5);

  if Height > 470 then Height := 470;

  ShowModal;

  for i := 0 to Query1.FieldCount - 1 do

    Query1.Fields[i].Visible := R[i].Checked;

end;

(d)Moving Columns at Runtime

To move the location of a column at runtime, you can simply change its index, which is a zero-based number:

DMod.Query1.FieldByName('CustNo').Index := 0;

DMod.Query1CustNo.Index := 2;

By default, the CustNo field in the Orders table is at the second position, which means its index is 1. The code in the first example moves it to the first position, whereas the code that reads Query1CustNo.Index = 2; moves it to the third position. Remember, the Index field is zero-based, so moving a field to Index 1 moves it to the second field in a record. The first field is at Index 0.

When you change the index of a field, you do not need to worry about the indexes of the other fields in a record; they will be changed automatically at runtime.

That is all I'm going to say about DBGrid objects. I've gone on at considerable length about this one component, but it is one of the tools that lie at the heart of many database programs. Now that you know what you can do with the Borland's grid, some readers are likely to say: "Hey, that's all good and well, but I want even more power!" That's a reasonable request, so you might want to point your browser to http://www.woll2woll.com and check out the InfoPower product. TurboPower also has a nice advanced grid; you can find it at http://www.turbopower.com. Another is "Queen of Grids" at www.topsupport.com. For a full listing of third party components, visit the Inprise web site at www.inprise.com.

The TDBGrid object features the ability to add combo boxes to the grid. This subject is covered in the next section on lookups.

(c)Lookup Fields

You can use Lookup fields to look up a value in one table that you want to use in a second table. For instance, suppose you had two tables, one of which contained a list of books, and the other contained a list of authors. It would be nice if you could automatically view a list of the existing authors whenever you needed to add a new book to the Book table. That way, you could enter the book's name, then just look up the author in a drop-down list, and presto, you would be done. The Book table would then automatically contain a reference to the appropriate author in the Author table. That is, the author number from the Author table would automatically be inserted in the Book table.

I wouldn't go into all this detail unless Delphi provided good support for using Lookup fields. In particular, Delphi allows you to perform automatic lookups inside grids, list boxes, and combo boxes. The Lookup program on the CD shows how to proceed. The code for this application is shown in Listings 8.4 through 8.6. Two views of the program are shown in Figures 8.22 and 8.23.

***Insert Figure 8.22                                                                                                                                           09fig22                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.22

The AuthorLookup field in the grid for the Book table is a lookup into the Author table.

***Insert Figure 8.23                                                                                                                                           09fig23                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.23

This form features a list box and combo box that let you perform lookups from the Book table into the Author table.

***Begin Note***

Note

Delphi 1 had a dbLookupCombo control and a dbLookupList control that had certain limited capabilities. Both of these controls are still present in Delphi 4, but they have been moved off the Data Controls page onto the Win 3.1 page. They are being kept around solely for compatibility with 16-bit programs. The dbLookupComboBox control and the dbLookupListBox control now replace the old 16-bit controls, and they outperform them on several fronts. In particular, the dbLookupComboBox and dbLookupListBox will automatically be filled up with the data from the lookup table. Don't confuse the old controls with the new ones. In general, you would be wise to ignore everything on the Win 3.1 page, unless you explictly need it for backward compatibility.

***End Note***

Listing 8.4[em]  The datamodule for the program. t

unit DMod;

 

interface

 

uses

  Windows, Messages, SysUtils,

  Classes, Graphics, Controls,

  Forms, Dialogs, DBTables,

  Menus, Db;

 

type

  TDMod1 = class(TDataModule)

    tblAuthor: TTable;

    tblAuthorAuthNo: TIntegerField;

    tblAuthorFirst: TStringField;

    tblAuthorLast: TStringField;

    tblAuthorDates: TStringField;

    tblAuthorLastFirst: TStringField;

    dsAuthor: TDataSource;

    dsBook: TDataSource;

    tblBookBookNo: TIntegerField;

    tblBookAuthNo: TIntegerField;

    tblBookAuthorLookup: TStringField;

    tblAuthorBirthPlace: TStringField;

    tblBook: TTable;

    tblBookTitle: TStringField;

    procedure tblAuthorCalcFields(DataSet: TDataSet);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

 

var

  DMod1: TDMod1;

 

implementation

 

{$R *.DFM}

 

procedure TDMod1.tblAuthorCalcFields(DataSet: TDataSet);

begin

  tblAuthorLastFirst.Value := tblAuthorLast.Value +

    ', ' + tblAuthorFirst.Value;

end;

 

end.

Listing 8.5[em] The main form for the program.

unit main;

 

interface

 

uses

  Windows, Messages, SysUtils,

  Classes, Graphics, Controls,

  Forms, Dialogs, DB,

  Grids, DBGrids, DBTables,

  DBCtrls, Menus;

 

type

  TForm1 = class(TForm)

    DBGrid1: TDBGrid;

    DBGrid2: TDBGrid;

    MainMenu1: TMainMenu;

    File1: TMenuItem;

    ViewEntryForm1: TMenuItem;

    OneToMany1: TMenuItem;

    Exit1: TMenuItem;

    procedure Form21Click(Sender: TObject);

    procedure Exit1Click(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

 

var

  Form1: TForm1;

 

implementation

 

uses

  DMod, fieldvew;

 

{$R *.DFM}

 

procedure TForm1.Form21Click(Sender: TObject);

begin

  Form2.Show;

end;

 

procedure TForm1.Exit1Click(Sender: TObject);

begin

  Close;

end;

 

end.

Listing 8.6[em]Form2 Shows How to Use dbLookupListBoxes and dbLookupComboBoxes

unit FieldVew;

 

interface

 

uses

  Windows, Messages, SysUtils,

  Classes, Graphics, Controls,

  Forms, Dialogs, ExtCtrls,

  DBCtrls, StdCtrls, Mask;

 

type

  TForm2 = class(TForm)

    Panel1: TPanel;

    DBEdit3: TDBEdit;

    DBLookupComboBox1: TDBLookupComboBox;

    DBLookupListBox1: TDBLookupListBox;

    DBNavigator1: TDBNavigator;

  private

    { Private declarations }

  public

    { Public declarations }

  end;

 

var

  Form2: TForm2;

 

implementation

 

uses dmod;

 

{$R *.DFM}

 

end.

The Lookup program enables you to easily fill in the key fields of the Book table by looking them up in the Author table. To understand why this capability is important, notice that the only way to tell which author is associated with which book is by placing the appropriate author number in the AuthNo field of the Book table. This approach is convenient from the point of view of the programmer who wants to construct a well-made relational database. In particular, it allows for saving space through the construction of one-to-many relationships. However, the user isn't going to want to have to remember that Herman Melville is associated with the number 2, Jack Kerouac with the number x, and so on. The point of a lookup field is that it lets you look up a list of authors in the Author table and then automatically assigns the chosen author number to the AuthNo field in the Book table.

This program uses two tables called, not surprisingly, AUTHOR.DB and BOOK.DB. Both of these tables are found on the CD that comes with this book. Tables 8.1 and 8.2 display the schema for both tables.

Table 8.1[em]AUTHOR.DB Table Structure

Name                            Type                                     Keyed

AuthNo                         AutoInc                                 Key

First                               Character(25)

Last                               Character(25)

Dates                             Character(25)

BirthPlace                      Character(25)

Table 8.2[em]BOOK.DB Table Structure

Name                           Type                                       Keyed

BookNo                       AutoInc                                  Key

AuthNo                        LongInt

Title                              Character(35)

Notice the use of the autoincrement fields in the table definitions shown in Tables 8.1 and 8.2. These fields will automatically be filled in when the user adds a new record at runtime. For instance, when you add the first record to the Book table, it will automatically be given a BookNo of 1. The second record will automatically be given a BookNo of 2, and so on. Autoincrement fields are read-only, and frequently they do not need to be shown to the user at runtime.

Little actual work is required to construct this program. In particular, look over the source code shown earlier, and you will see that the only significant line of code in the whole program is the one for the OnCalcFields event. Other than that, it's just a matter of manipulating the visual tools.

To get started, create a new application and add a data module to it. Drag both the Author and Book tables out of the Explorer and onto the data module. Hook them up to two grids on the main form. Bring up the Fields Editor for both tables and create objects for all their fields. Give the tables and their data sources appropriate names, as shown in Figure 8.24.

***Insert Figure 8.24                                                                                                                                           09fig24                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.24

The data module for the Lookup program.

Inside the Author table, create a calculated field called LastFirst. To create the calculated field, first right-click the TTable object; then right-click the Field's editor, and select New from the menu. After you create the calculated field, assign the following method to the OnCalcFields event:

procedure TData.tblAuthorCalcFields(DataSet: TDataSet);

begin

  tblAuthorLastFirst.Value := tblAuthorLast.Value +

    ', ' + tblAuthorFirst.Value;

end;

This field will be the one that is looked up in the second table. The issue here is that just looking up the last name of an author is not sufficient; you need to look up both first and last names to be sure you are finding a unique name for an author. Permanently adding a field to the table that combined the first and last names would be a waste of disk space, but you can create a temporary copy of that field with a calculated field.

Bring up the Fields Editor for the Book table. Right-click it, and create a new field called AuthorLookup. Set its Type to String and its Field Type to Lookup. The Key Fields should be set to AuthNo, the Dataset to tblAuthor, the Lookup Keys to AuthNo, and the Result field to LastFirst. Figure 8.25 shows how the New Field dialog should look when you are done. Notice that you can also fill in this same information in the Object Inspector if you first select the tblBookAuthorLookup object. (In other words, you could create a new object and then close the Fields Editor without specifying any of its properties. Later, you could select the object and designate its type, its lookup fields, and so on.)

***Insert Figure 8.25                                                                                                                                           09fig25                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.25

Filling in the New Field dialog.

Go back to Form1, and make sure the two DBGrids are arranged one above the other and are hooked up properly to the tables on the data module. Then run the application.

The AuthorLookup field in the dbGrid object associated with the Book table is now a drop-down combo box that can be seen at runtime. If you click it once and then drop down its list, you can then perform a lookup into the LastFirst field of the Author table. This lookup will automatically fill in the AuthNo field of the Book table. You can use this lookup to insert a new author into a new record or to change the author of an existing record.

***Begin Note***

Note

The ability to have a drop-down list in a grid object is included in Delphi 4. Go back to design mode, and open the Columns property of a grid object. Add all the fields to the Columns list box. You can now select one of the fields, such as Title, and choose the PickList button to create a set of default values available for the field. The user can access these values at runtime by clicking the field and dropping down the combo box, per the lookup example discussed previously.

***End Note***

Besides the TDBGrid object, two other controls in Delphi understand lookup fields. Both of these controls are shown on Form2. The first is a TDBLookupComboBox, which is the default control you will get if you drag and drop the AuthorLookup field from the Fields Editor onto a form. If you perform the drag-and-drop operation, then the control will be hooked up automatically. If you want to hook it up manually, just connect its DataSource to the dsBook object and its DataField to the AuthorLookup field. The TDBLookupListBox also works exactly the same way as the TDBLookupComboBox.

***Begin Note***

Note

Both the TDBLookupListBox and TDBLookupComboBox have fields that correspond to the ones you filled in with the New Field dialog shown in Figure 8.25. However, you don't need to fill in these fields a second time. Just hook up the DataSource and DataField properties, and you are ready to go.

***End Note***

When you're working with Form2, notice how easily you can type a new book name, then click an author's name in the list box, and click the Post button on the TDBNavigator. The process is very simple from the user's point of view. In particular, a new BookNo is assigned automatically by the autoincrement field, and the new AuthNo is filled in automatically by the lookup process.

If you want to change the author associated with a particular record, then you just click a new item in the list box. The author number will be changed automatically for you by the lookup. This process is all very simple and intuitive when viewed from the user's perspective.

(c)Multirecord Objects

Another object that deserves mention is the dbCtrlGrid, shown in Figure 8.26, and on the program called CtrlGrid from the CD that accompanies this book. You can use this object to view multiple records from a single table at one time without using the TDBGrid component. In other words, you can drop down TDBEdit controls onto a TDBCtrlGrid, and these edit controls will automatically be duplicated in a series of rows, where the first set of controls shows the first record; the second set, the second record; and so on. You have to drop down only one set of controls; the extra sets are duplicated for you automatically by the dbCtrlGrid, as shown in Figure 8.26.

***Insert Figure 8.26                                                                                                                                           09fig26                                                                                                                                           PC                                                                                                                                           X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Figure 8.26

The TDBCtrlGrid object on the form the CtrlGrid application.

To get started with this object, drag and drop the Country table off the Database Explorer. Delete the TDBGrid object, and add the TDBCtrlGrid object off the Data Controls page of the Component Palette. Use the Fields Editor to drag and drop all the fields from the Country table onto the top section of the DBCtrlGrid. Arrange them as shown in Figure 8.26.

Notice that DBCtrlGrids have RowCount and ColCount properties, which enable you to define the number of rows and columns in the object. In this case, I have set the RowCount to 7.

The TDBCtrlGrid component doesn't bring any new functionality to Delphi. It's useful, however, as it eliminates the need to have the user slide the scrollbar back and forth on a TDBGrid object.

 

(c)Summary

In this chapter, you learned some fairly sophisticated methods for displaying the data from multiple tables. In particular, you saw how Delphi handles the key features of a relational database.

The tools discussed in this chapter include

  [lb]       The Fields Editor

  [lb]       The Database Explorer

  [lb]       The Database Desktop

  [lb]       The Query by Example tool in the DBD

The components discussed in this chapter include

  [lb]       TField descendant objects

  [lb]       The TDBCtrlGrid, TDBLookupComboBox, and TDBLookupListBox objects

  [lb]       The TDBGrid component

The properties discussed in this chapter include

  [lb]       Calculated fields

  [lb]       Lookup fields

Good database programmers will find that a considerable amount of power is hideen in the TField object and in the Fields Editor, as well as the other tools and components mentioned in this chapter. These database tools represent a very strong aspect of Delphi, which is further expanded on by the work of third-party developers.