chapter.

This chapter features overviews of

• InterBase and Security: granting rights on a table

  　

• The InterBase Server Manager: backing up a database

  　

• Relational database design

  　

• Referential integrity

  　

• Stored procedures and the TStoredProc components

  　

• Triggers

  　

• Domains

  　

• Querying data with SQL. These techniques help you prepare reports on the information in a database, as well as answer the user's questions about how much data they have and what kind.

  　

• Using SQL to extract facts from a database. How many of this type of item do I have? How can I write a stored procedure that retrieves information from several tables at once while still answering a real-world question about the amount of a particular kind of data?

  　

• Placing forms on a TabControl. The kdAdd program had a huge number of fields in the main form for the application. In this chapter, you will see how to create separate forms for each page in an application that uses a tabbed notebook metaphor. This way, each page exists inside its own discrete object, which helps you create well-organized, robust applications.

  　

• Create the illusion of dynamically changing the shape of a form at runtime. It turns out that placing forms on a TabControl gives you a remarkable amount of flexibility. In particular, you can show one form on a particular page under one set of circumstances and another form on the same page under slightly different circumstances. This gives the user the impression that you know how to dynamically change the shape of a form at runtime.

  　

• Storing multiple types of data in a database and displaying it with one program. This database shows information on both a table that contains books and a table that contains albums and CDs. The TabControl technique described in the last section allows you to seamlessly integrate different types of data in one program. From your user's point of view, it appears that the program morphs to accommodate the type of data currently being displayed.

  　

• Using SQL to alter a table

The burden of the argument for this chapter is again carried by a sample database application. This one is designed to track household items such as books, CDs, or records. However, you can easily expand it to hold many different types of data. The core strength of this program is its flexible, extensible design.

One of the interesting features of the Music database is the way it uses stored procedures to report on the information in the database. For instance, it lets you store CDs, tapes, and records and rate them according to four different extensible criteria:

• What type of music is it? Classical? Jazz? Rock? Do you have categories of your own you want to add?

  　

• How loud is the music? Is it peaceful, moderate, or loud? You can add other categories if you want.

  　

• How good is the music? On a scale of one to ten, how do you rate it?

  　

• Finally, what medium is it on? CD? Tape? Record?

You can easily expand most of these lists to create as many categories as you want. Furthermore, you can query the database to ask questions such as

• How many records do I have?

  　

• How many different artists are listed here?

  　

• How many albums do I have that I rated in a certain range? For instance, which records did I rate as a complete ten? Which ones did I rate as only one or two?

  　

• Which albums did I rate as loud?

  　

• What albums are listed under the category called Jazz or Folk?

By the time you finish this chapter, you should have a pretty good feel for how to tap into the power of InterBase. This chapter is not meant to appeal only to InterBase developers, however. It also contains many general comments about working with relational databases in general and SQL databases in particular. In other words, this chapter is about real client/server database programming.

The Music program uses several advanced database features of Borland C++Builder (BCB). For instance, there are examples of calculated fields, data modules, lookups, filters, and searching a database with FindFirst and FindNearest. This program uses many other standard database techniques, such as calculated fields and working with ranges. There are numerous examples of how to use stored procedures and also an example of how to search on records in the detail table of a master-detail relationship.

My favorite feature of this program is the way it leverages form inheritance to give the user the ability to switch back and forth between the Album and Book tables. I like the idea of having one program that can deal with multiple types of data. The program was designed so you can add new modules to it, such as forms for handling addresses or inventory items and so on.

When working with this chapter, you should remember that a complete copy of the Music database is available on the CD that accompanies this book. Throughout the first half of this chapter, I talk about the incremental steps involved in creating this database, but if you feel the need to see the complete database at any time, then you can retrieve it from the CD. I quote the entire data definition for the database about two thirds of the way through this chapter, and it is available on the CD in the Data directory in the file called Music.ddl.

If you need to set up the alias for the database on the CD, a full description of how to do so is provided in the readme file on the CD that accompanies this book. I also talk about the alias at several points in this chapter.

Music is a fairly complex program, but I assure you that the version on the CD works correctly. If you are having trouble with it, check my Web sites for tips or hints on using the program.

Overall, this chapter aims at taking the discussion of databases to a new level. After you read the text, you will be prepared to write professional-level client/server applications. All the information in this chapter applies to common professional database tasks such as creating an inventory system or even a point of sales application.

Security and the InterBase Server Manager

Before beginning the discussion of the Music program, it might be a good idea to cover a few basic issues regarding security. I included enough information so far to make any BCB programmer dangerous, so I might as well also equip you with some of the tools you need to defend your work against prying eyes. If you have the skill to create programs that others can use, then you have to know how to manage those clients.

When working with passwords, it's important to make a distinction between user security for a whole server and access rights for a particular table. If you open the InterBase Server Manager, log on, and select Tasks | User Security, you will find menu options that let you create new users for the system. By default, these users have access to very little. All you do is let them in the front door. You haven't yet given them a pass to visit any particular rooms in the house. As this discussion matures, I discuss how to grant particular rights to a user after he or she has been admitted into the "building."

If you are interested in setting up real security for your database, then the first thing you should do is change the SYSDBA password. To change the password, sign on to the InterBase Server Manager as SYSDBA using the password masterkey. Select Tasks and then User Security from the menu. Select the username SYSDBA, and choose Modify User. Now enter a new password. Once you do this much, the system is truly under your control. No one else can get at your data unless you decide they should have the right to do so. Even then, you can severely proscribe their activities with a remarkable degree of detail. (If you are a control freak, this is paradise!)

After establishing your sovereignty, the next step is to go out and recruit the peons who will inhabit your domain. Once you find a new user, select Tasks | User Security and choose Add User and give him or her a password. The person who creates users is the one who signs on as SYSDBA. SYSDBA has all the power, which is why it is important to change the SYSDBA password if you are really serious about security.

If you create a new user, this newcomer has no rights on the system by default. To give a user rights, you must use the SQL Grant command, which is discussed in the next section.

Defining Access Rights to a Table

After you create a user in the InterBase Server Manager, you grant them rights to access a table. SQL databases give you extraordinary control over exactly how much access a user can have to a table. For instance, you can give a user only the right to query one or more tables in your database:

grant select on Test1 to user1

Conversely, you may, if you want, give a user complete control over a table, including the right to grant other people access to the table:

grant all on album to Sue with grant option

The with grant option clause shown here specifies that Sue not only has her way with the album table, but also can give access to the table to others.

You can give a user six distinct types of privileges:

Using these keys to the kingdom, you can quickly start handing out passes to particular rooms in the palace. For instance, you can write

grant insert on Test1 to Sue

The last statement in this list comes from the Employee.gdb example that ships with BCB. Notice that it grants rights to the public, which means all users have absurdly liberal rights on the table.

The opposite of the Grant command is Revoke. Revoke removes privileges given with Grant. Here is an example of using Revoke:

revoke select on Test1 from Sue

This brief overview of the Server Manager and some related issues involving the grant command should give you a sense of how to limit access to your database. None of this material is particularly difficult, but you should be sure you understand it because SQL databases can be mysterious and frustrating if you don't know the basics about how to control them. If you need more information than what I presented here, you will probably find all you need to know to cover this small domain of knowledge in the online help.

Backing Up Tables with the Server Manager

Another important feature of the InterBase Server Manager is backing up tables. This task can be especially important if you need to move a table from Windows 95 or Windows NT to UNIX. The highly compressed backup format for InterBase tables is completely version- independent, so you can back up an NT table and then restore it on a UNIX system.

To get started backing up a database, sign on to the InterBase Server Manager. To sign on, all you need to do is specify the masterkey password; everything else is automatic when signing on to the local version of InterBase. Of course, if you changed the SYSDBA password from masterkey to something else, then you need to use the new password you created.

Go to the Tasks menu and select Backup. Enter the path to the local database you want to back up. For instance, you might type e:\data\info.gdb in the edit control labeled Database Path. This means you want to back up the database called info.gdb.

Enter the name of the backup table you want to create in the Backup File or Device field. For instance, you might type e:\data\info.gbk. Use the GDB extension for live tables and GBK for backed-up tables. These are just conventions, but they are good ones.

Select Transportable Format from the Options group box and set any other flags you want to use. Click OK, and then be prepared for a short delay while InterBase contemplates certain knotty passages from the works of the philosopher Immanuel Kant. If all goes well, the results of your work might look something like this:

Backup started on Tue Dec 24 15:26:42 1996...

You can now close the InterBase Server Manager and copy your backed-up file to a floppy disk, zip drive, or other storage medium. Remember, the great thing about these files is that they are small, highly compressed, and can be moved from one operating system to another.

　

---

NOTE: I exist so completely within the Windows world that I neglected to point out that InterBase runs on a wide variety of UNIX platforms.

---

　

The Music Program

It's now time to begin work on the Music program. This program enables you to keep track of CDs, records, tapes, and books. The main goal of the program is to enable you to enter the name of an artist (a musician or a writer) and add one or more titles associated with that artist.

　

---

NOTE: The main table of this program is called Artist for historical reasons. The database was originally intended to hold only CDs, records, and tapes. I expanded the program's scope later when I suddenly saw a way to add the Book table to the project. You can now regard the Artist table as featuring a somewhat quaint conceit for a naming convention. Of course, it is difficult to undo the naming convention at this stage because the names of the Artist table and its fields are already spread throughout the program's code as well as the 12 stored procedures that support the code.

---

　

The Music program uses eight tables, but three of them, called Artist, Book, and Album, dominate the application. The Artist table is the master table, and the Book and Album tables are detail tables.

Besides the three main tables, several lookup tables are used to store the various lists of possible categories to which the albums and books can belong. For instance, a record can be type Jazz, Rock, Folk, Blues, and so on, and a book can be type Fiction, Computer, Mystery, Science Fiction, Reference, and so on. These words are stored in the lookup tables. In some cases, I can store information used by both the Album and Book tables in one lookup, using a filter and the range of the table's primary key to distinguish between the different groups of information. More on this technique later in the chapter.

Even with this relatively simple structure, however, there are still enough tables to provide some food for thought. In particular, how are these tables related, and how can you put constraints on them so that it's difficult for the user to accidentally break a dependency? For instance, if there are six albums associated with an artist, a user should not be able to delete the artist without first deleting or reassigning the albums. How about generating the IDs for each artist and each album? This is not Paradox, so there is no autoincrement field. This means that you must create generators and employ some means of accessing the generators.

Clearly, there are enough questions to keep someone busy for an hour or two. To

resolve these issues, you need to generate a specific database schema.

Creating the Database Schema

It's probably best to start your work at the top with the Artist table:

/* Table: ARTIST, Owner: SYSDBA */

The definition for this table assumes the presence of a domain called CODE_DOM. You can create a domain in WISQL with the following code:

CREATE DOMAIN CODE_DOM AS INTEGER;

This code states that CODE_DOM is a domain of type Integer.

A domain is an alias for a type that is used more than once in the program. For

instance, the Code field used in the Album table is referenced in the Album table in the GroupCode field:

CREATE TABLE ALBUM (CODE CODE_DOM NOT NULL,

Make sure you understand what is happening here. The GroupCode field in the Album table references the Group, or Artist, associated with this particular album. For instance, if Bob Dylan's code is 57, and the name of the current album is Blonde on Blonde, the GroupCode field in the Album table is set to 57. This ties the album Blonde on Blonde to the artist Bob Dylan. (Properly speaking, this is a foreign key, but I get to that subject as it applies to InterBase later in the chapter.)

Creating a domain called CODE_DOM allows you to easily assign the same type to the Code field in the Artist table and the GroupCode field in the Album table. It's not earth-shattering in importance, but it can be helpful.

Altering Tables: To Null or Not to Null

Notice that the Code field is declared as Not Null. This means that the user cannot leave this field blank. This rule is implemented by the server and is enforced regardless of which front end you use to access the data. By definition, all primary keys must be declared Not Null.

The ArtistType field in the Artist table is declared as Not Null. All artists must be distinguished by type; that is, they have to be labeled as either Authors or Musicians. If they don't fit into one of these two categories, then they are never seen by the user because I set up a filter on this field, excluding all but the one type that the user currently wants to see. In short, the table is filtered to show either only musicians or only authors. If an entry in the Artist table does not fit into one of these two categories, then it is never seen by the user. As a result, I declare this field as Not Null and then use a lookup table to give the user only two choices when filling it in. This way, I am sure that no records are lost.

Deciding which fields should get the value Not Null is one of the more difficult chores in creating a database. This is one of those designations that I almost never call right in design mode. Instead, I am forced to go back and massage my data after creating a first draft of the data definition.

To change a table using WISQL, you must call an SQL command called Alter Table:

ALTER TABLE MYTABLE

This code adds a field called NAME to a table and drops a field called NAMES. You don't have to add and drop fields at the same time; for instance, you can write

ALTER TABLE MYTABLE

You can also write

ALTER TABLE MYTABLE

Because you often alter the structure of an existing table, make sure you run many tests on your program before entrusting a large amount of data to your tables.

You cannot alter a field that is part of a unique index, primary key, or foreign key, nor can you drop a unique index, primary key, or foreign key. You can, however, drop a standard index:

drop index myindex

When you start altering tables, you soon need to transfer the values from one field to a new field. In other words, you want to slightly alter the traits of one field. The rest of this section outlines a simple technique for altering a table.

To get started, I will create a simple table that can serve as a scratch pad. All the work shown here was done with the WISQL32 utility that ships with InterBase in the BIN directory. I always keep WISQL on the Tools menu of my copy of BCB:

create table foo (sam Integer not null, Name VarChar(30), primary key (Sam));

The four lines shown here create a table called Foo and place some simple values in it. WISQL lets you use the Previous and Next commands so you can easily alter the insert command without retyping it each time.

After creating the table, I can easily test the data:

select * from foo

Suppose that I now decide I want to change the Name field to be Not Null and somewhat longer. How do I proceed?

The first step is to create a new field with all the traits in it that I want:

alter table foo

Now the table has a field called AName that is longer than the Name field and declared Not Null.

To copy the data from the Name field to AName, issue the following command in WISQL:

update foo

Here is how things stand at this point:

select * from Foo;

You can simply delete the Name column:

alter table foo

Now you have a table that looks like what you want:

select * from foo;

If necessary, you can then repeat the process to copy AName to a field called Name, or else you can just keep the new name for your table.

This whole technique is a bit laborious. However, if you play with SQL for a while, all this work starts to become second nature. For instance, I can copy the AName field back to a new field called name in well under a minute just by rapidly typing the following:

alter table foo add Name varchar(50) not null;

Once you know SQL, and assuming you can type well, you can usually invoke WISQL, enter the commands, and get out faster than you can load the weighty Database Desktop application. Certainly by the time I open Database Desktop, open the Restructure window, and start making changes, I've usually spent more time than it takes to do the whole procedure in WISQL. This argument is a bit like the command line versus the Windows Explorer. The Windows Explorer is considerably more intuitive to use, but it is not necessarily faster than using the command line. Another nice thing about WISQL is that it has a small footprint and can be left in memory without slowing down the system.

Creating Blob Fields

After the discussion of Code fields and the related Null versus Not Null issues, the other fields in the Artist table are pretty straightforward:

CREATE TABLE ARTIST (CODE CODE_DOM NOT NULL,

The code for creating a blob field looks a bit tricky, but basically, you can just repeat this code any time you need to create a text blob in InterBase. If you create a blob field as shown previously, then you can use it with the TDBMemo data-aware control that ships with BCB.

BCB offers two objects for working with blobs, TBlobField and TBlobStream. TBlobField has methods called LoadFromFile, SaveToFile,

LoadFromStream, and SaveToStream that can be used to read and write blob data in and out of a database. You can also usually cut and paste data directly into a TDBMemo or TDBImage control by copying it to the clipboard and then pasting it into the control using Ctrl+V. To copy an image from a blob field to the clipboard, use Ctrl+C or Ctrl+X or else use the built-in CopyToClipBoard feature of both TDBMemo or TDBImage.

You can use blob fields to store bitmapped images, sounds, video segments, and text.

Primary Keys and Foreign Keys

The final line in the definition for the Artist table defines the primary key:

PRIMARY KEY (CODE));

This states that the primary key is the Code field. It's important that Code is a keyed field because it is referenced by a foreign key in the Album table. Furthermore, you want to be sure that no two rows have the same code in it, and the primary key syntax enforces this rule. Remember that all primary keys must be Not Null by definition.

Here, once again in slightly different form, is the definition for the Album table:

CREATE TABLE ALBUM (CODE CODE_DOM NOT NULL,

This time through, I modified the table to include foreign keys. These keys show the dependencies that this table has on the fields of other tables.

Once again, the Code field is the primary key. This field contains a unique number for each new Album record entered by the user. A character field designates the name of the album or book, and the GroupCode field relates each record to the Artist table.

Notice that the GroupCode field is a foreign key referencing the Code field of the Artist table. A foreign key provides referential integrity. The foreign key asserts that

• Every GroupCode entry must have a corresponding Code field in the Artist table.

  　

• You can't delete an Artist record if there is a corresponding record in the Album table with a GroupCode the same as the Code field of the record you want to delete.

These two rules go a long way toward describing what foreign keys are all about. They also help to explain what referential integrity is all about. In particular, note that these rules are enforced by the server, and they are implemented regardless

of what type of front end attempts to alter the table.

You rarely want to make a foreign key unique because the whole point of this exercise is to relate multiple albums with one artist. The artist is the master in the master-detail

relationship because there is only one artist for each set of albums. The artist has a primary key, which means there is only one of each artist entered in the database. The album is a foreign key, and there are usually multiple GroupCode foreign keys for each single Code primary key.

　

---

NOTE: As you saw earlier, referential integrity is not unique to InterBase. In fact, Paradox supplies good tools for supporting referential integrity. It's built right into the Database Desktop, and every Paradox table that you create can have referential integrity if you want it. In most cases, you do indeed want it!

---

　

To see referential integrity in action, run the Music program that comes with this book and try to delete one of the Artist records that has an Album associated with it. For instance, try to delete Bob Dylan, Miles Davis, or Philip Glass. Your efforts are stymied because there are albums associated with all these artists. In particular, you get the lovely message that reads

General SQL Error: Violates FOREIGN KEY constraint "INTEG_19" on table "Album"

You might as well savor this one because it is as close to poetry as you can get in the SQL database world.

Go into the Database Desktop, enter a new album, and try to give it a GroupCode that does not have a corresponding entry in the Code field of the Artist table. The Database Desktop doesn't let you do it. (Note that there are other fields that have foreign keys in this table, so you have to give valid values all the way around, or you aren't able to enter a record. You can, however, leave the other fields blank if you want.)

The key point here is that referential integrity is enforced automatically in BCB and the Database Desktop. In fact, the rules are enforced on the server side, so no matter how you try to get at the data, you must obey the rules. It's not just some client-side code in BCB; the rule is built into the database itself, which is what you want.

This concept is so important that I will repeat it once more: These rules are enforced automatically no matter what front end the user attempts to use on the table!

The Types, Loudness, Medium, and Rating fields are all integers. Types, Loudness, and Medium are all

foreign keys that reference one of three small tables called, logically enough, Types, Loudness, and Medium:

/* Table: LOUDNESS, Owner: SYSDBA */

The structure of these tables ought to be intuitively obvious. The Types table, for instance, is designed to hold the following records:

select * from types

What you have here are six types for albums and five types for books. I separate the two types of types by a large range so that you can add a virtually unlimited number of additional types of either kind. (If you want to work with more that 999 different types of music, you have a problem! Of course, I could have made the split at 10,000 or 100,000 instead of at 1,000, but it's unlikely you want to have more than 999 distinct types of music in this, or any other, database.)

The key point to grasp here is that you cannot add a number to the Types field of the Album table unless it has a corresponding entry in the Types table. The foreign key on the Types field is placed there explicitly to enforce this rule. Furthermore, you can't delete an entry from the Types table if it has a corresponding element in the Types field of the Album table. You can, however, change the content of one of the strings in the Types table and thereby either enhance, or totally trash, your data.

Astute readers probably notice that I designed the relationship between the Types field of the Album table and the Types table itself so that it is easy to perform lookups on the Types table when necessary. You will hear more about this topic later in the chapter, or you can refer to the discussion of lookup fields in Chapter 11, "Working with Field Objects."

Here is the definition for the Book table, which plays the same role in this program as the Album table. The key point to notice, however, is that the two tables differ in several particulars. The interesting thing about the Music program is that it can handle both kinds of tables seamlessly. To the user, the forms involved with displaying this data just seem to morph as needed to accommodate the data.

CREATE TABLE BOOK (CODE CODE_DOM NOT NULL,

Creating Indexes on the Music Table

By now, you have seen most of the data definition for MUSIC.GDB. However, I want to discuss a few more details before moving on to take a look at the interface for the program.

The indexes on the Music database are all defined to enhance your access to the data and provide automatic sorting of the data. Indexes have no other purpose than to speed your access to data and help with sorting. They allow you to search for data very quickly. If any of your searches takes too long, one of the best ways to address the problem is through enhancing your indexes.

It's important to see the difference between the primary and foreign keys that create referential integrity and add constraints to a table and the ordinary indexes, which speed up access to a particular record. A primary key is an index on speed. It gives you everything an index gives you and then a little more.

In particular, when you create a primary key or foreign key in InterBase (or in Paradox), then a unique index is automatically created on that key. For instance, the Artist and Album tables both have unique indexes on the Code field. The Foo table, created in the last section, has a unique index on the field called Sam.

One simple way to see these indexes is to open the Database Explorer and examine the indexes listed under the Album or Artist tables. The Artist table, for instance, has two indexes. One is called Artist_LastFirst_Ndx, and I will describe it later in this chapter. The other index has the strange name RDB$PRIMARY1. This is the index that was created when the code field was designated as a primary key. The only really important part of the name is the word primary, which helps you to understand that this is part of a primary key. The rest is just syntactical sugar used internally by InterBase.

　

---

NOTE: You can add a primary or foreign key after a table is created, as long as doing so does not violate any other database rules. You should make sure that the tables involved are not in use by another program when you make these kinds of modifications.

Here is an example of adding a primary key:

　

ALTER TABLE FOO ADD PRIMARY KEY (Sam);

Here is an example of adding a foreign key:

ALTER TABLE FOO ADD FOREIGN KEY (Foreigner) REFERENCES Book(CODE);

　

The foreign key example shown here assumes that you added an Integer field to the Foo table called Foreigner.

---

　

Besides the primary keys and foreign keys, the following indexes are also defined on the Artist and Album tables:

CREATE INDEX GROUPALBUM_IDX ON ALBUM(GROUPCODE, ALBUM);

If you want to create a new index in WISQL, you can do so with the SQL Create Index command, as shown in the preceding code. The command takes the name of the index, the name of the table on which the index is enforced, and finally, in parentheses, the names of the fields in the index. For more information on this and other commands, see the InterBase Workgroup Server Language Reference, or, more practicable, the online help for WISQL. Also helpful are third-party books such as the Practical SQL Handbook (ISBN: 0-201-62623-3).

I created these two indexes for different reasons. The Artist_LastFirst_Ndx is meant primarily to speed up searches and sorts in the Artist table.

The GroupAlbum_idx is created for a more specific reason. Many of the artists have numerous albums associated with them. I relate the Album table to the Artist table using the standard MasterSource, MasterField, and IndexFieldName gambit you saw in Chapter 9, "Using TTable and TDataSet." To set up this relationship inside BCB, I need an index on the Code field from the Artist table and the GroupCode field from the Album table. Both of those are provided for me automatically by my primary and foreign keys. However, I try to relate the two tables and also make sure the records from the Album table are sorted correctly. To do this, I need to create a new index that both relates the Album table to the Artist table and also makes sure that the Album table is sorted correctly. The GroupAlbum_Idx serves this purpose. (I had a little trouble getting the GroupAlbum_Idx to work properly at first, but things cleared up when I closed the Album table and then reopened it.)

Generators, Triggers, and Stored Procedures

The next few sections of this chapter deal with triggers and generators. You will read a good deal about automatically generating values for primary keys and a little about the relative merits of triggers and generators.

Three generators provide unique numbers to use in the Code fields of the Artist, Book, and Album tables. Generators provide almost the same functionality in InterBase tables that autoincrement fields provide in Paradox tables. That is, they provide numbers to use in the keyed fields that bind tables together.

Autoincrement fields are filled in automatically at runtime. Generators, however, merely generate random numbers in sequence, where the first number generated might be one, the second two, and so on. You can tell a generator to start generating numbers at a particular starting value, where the first number might be x, the next x + 1, and so on.

Here is how you create a generator in WISQL and set it to a particular value:

CREATE GENERATOR MUSIC_GEN;

As a result of this code, the first number generated is 300, the next is 301, and so on.

I will now show how to write a trigger. The Music program uses triggers on the Artist table but not on the Album table. The reason for splitting things up this way is explained in this section and in the upcoming section of this chapter called "Deciding When to Use Triggers."

Here is how you write a trigger that automatically puts this value into the Code field of the Artist table whenever an Insert occurs:

CREATE TRIGGER SETMUSICGEN FOR ARTIST

This code appears on the server side. It's not BCB code. You enter it exactly as shown in WISQL. The procedure runs on the server end; it is not processed by BCB. There is never any need to call this procedure explicitly. The whole point of triggers is that they run automatically when certain events occur. This one is designed to run right before an Insert occurs. In other words, the way to call this procedure from BCB is to perform an Insert.

This code states that you want to create a trigger called SetMusicGen to run on the Artist table. The generator is called before an insert operation:

BEFORE INSERT AS

The actual body of the code is simple:

NEW.CODE = GEN_ID(MUSIC_GEN, 1);

The NEW statement says that you are going to define the new value for the CODE field of the record that is about to be inserted into a table. In this case, you reference the new value for the CODE field of the Artist table.

GEN_ID is a function built into InterBase that produces an integer value. It takes a generator as its first parameter and a step value as its second parameter. The step value increases or decreases the value produced by the generator. For instance, the preceding code increments the value by 1.

　

---

NOTE: You can get a generator to fill in a field automatically with the trigger shown previously. Unfortunately, BCB does not provide particularly good support for triggers, in part because each server generates a different kind of trigger. The developers of BCB didn't want to run around finding out how to handle triggers for 30 different kinds of servers and neither did the developers of the BDE.

Some third-party solutions to this problem include a good one that works with InterBase called the IBEventAlerter. This solution ships with BCB but is found on the samples page. Its presence on the samples page means it lives in a never-never land between the hard code made by the IDE team and the sample code, written by me and many others like me, which appears in the Examples directory of a standard BCB install.

---

　

In the example under discussion, BCB's poor support for triggers is not crucial because the table is not sorted on the Code field. If it were, this trigger might cause BCB to lose track of the current record after the insert operation. BCB would not know that the Code value was inserted because it would not know that the trigger fired. As a result, the current record might be lost because it was sorted on a value of which BCB was not aware. In other words, the index would cause the record to be moved to a particular place in the dataset, but BCB would not know how to follow it. As far as BCB is concerned, the Code field is still blank!

There may be a problem with BCB reporting a referential integrity error because the field appears to be blank and yet the field is defined as Not Null.

Underneath, the field is not really blank, but it appears that way to BCB. If this problem occurs, you can fix it by filling in the field with any randomly chosen value in an OnAfterInsert response method:

void __fastcall TDMod::ArtistTableAfterInsert(TDataSet *DataSet)

Here it doesn't matter what you set for the CODE field, as long as some value is there so that the Not Null constraint is satisfied. The correct value is filled in later by the trigger.

Unfortunately, that is not yet the end of the story. The grid that most programmers use to view a table depends on the current image of a table reported by the VCL.

As a result, having a field updated by a trigger can confuse the grid and cause it to show more than one copy of a record. This leads to all kinds of confusion. The best solution is to either call Refresh after posting an insert or else to close and then reopen the table. This latter step is not quite as drastic as it may seem because the TDatabase object keeps you connected to the database even if you close a table.

The key point to grasp is that this is one of the cases where it is possible to use a trigger to update an index inside BCB. It works because the table is sorted on the Last and First fields, not on the CODE field. Therefore, you can use a trigger to fill in the CODE field. If you tried to fill in the Last field with a trigger, then there would be trouble!

　

---

NOTE: Here is another example of how to create a trigger using WISQL:

　

CREATE TRIGGER SET_COMPANY_UPPER FOR COMPANY

ACTIVE BEFORE INSERT POSITION 1

AS

BEGIN

NEW.COMPANY_UPPER = UPPER(NEW.COMPANY);

END

　

This code is called just before an insert operation on a table called Company. This table contains a string field also called Company and a second field called Company_Upper. The second field is meant to mirror the Company field but with all its characters in uppercase. Having this second field takes up a lot of space, but it allows you to conduct searches and sorts on the Company field without taking into account character case. The goal of the trigger shown previously is to take the new value for the Company field and convert it into an uppercase version of the string for use in the COMPANY_UPPER field. The Upper macro shown here is built into InterBase.

Notice the line that states when this trigger is fired:

ACTIVE BEFORE INSERT POSITION 1

　

For a detailed understanding of how to create triggers, turn to the Language Reference for the InterBase server.

I show you this trigger because it works fine under most circumstances when used with BCB. BCB does not need to know that the Set_Company_Upper trigger occurred. The trigger can take place in the background without impacting BCB's inner workings.

---

　

If you find yourself in a situation where you can't use a trigger, there is no great need for alarm. The absence of trigger support is not a big concern under most circumstances. Instead of using a trigger, you can use a stored procedure to retrieve the next number from a generator. In my opinion, it is simpler and easier to use a stored procedure rather than a trigger if you want to fill in the primary key of a table.

Working with Stored Procedures

In this section, you will see a discussion of the stored procedures used by the Music program to enforce or support referential integrity. These simple stored procedures use a generator to fill in the value of a primary key. Near the end of the chapter, I discuss a series of more complicated stored procedures used to query the data in the database.

A stored procedure is simply a routine that is stored on the server side rather than listed in your Object Pascal source code. Like the language for writing triggers, there is a unique language for writing stored procedures that has nothing to do with Object Pascal or SQL. In fact, you need to keep in mind that there is no particular relationship between BCB and InterBase. They are made by two different teams, using two different languages, with two different goals in mind. The stored procedure language was made up long before anyone thought of creating BCB, and in general, the two languages have absolutely nothing to do with one another.

One key difference between BCB code and InterBase code is that the language of

stored procedures is completely platform independent. If you want to move your database back and forth between Windows and UNIX, then you might find it helpful to create many stored procedures that handle the majority of work for your databases. Then

you can write very thin clients that simply ask the stored procedures to do all the work.

Stored procedures are not difficult to create. Here, for instance, is a stored procedure that returns the next number generated by the Music_Gen generator:

CREATE PROCEDURE GETALBUMGEN

The first line tells WISQL that you are going to create a procedure called GetMusicGen. The next line states that it is going to return a value called Num, which is an integer. The AS statement tells InterBase that you are now ready to define the body of the procedure. The procedure itself appears between a BEGIN..END pair and consists of a call to the GEN_ID function, which returns the next number from the MUSIC_GEN generator. When it retrieves the number, it asks InterBase to increment its value by one.

　

---

NOTE: Stored procedures are handled on the BCB end with either a TStoredProc component or by returning an answer set by way of a SQL statement. In general, if the stored procedure returns several rows of data, you access it by way of a SQL statement in a TQuery component. The SQL statement to use in such a case is Select * from GetAlbumGen, where GetAlbumGen is the name of the procedure that returns one or more rows of data. If the stored procedure returns only a single item of data, you can call it with a TStoredProc component. Examples of both methods for calling stored procedures from BCB appear in the next section, in the form of excerpts from the Music program.

---

　

Stored Procedures from BCB's End

The Album and Book tables of the Music program use stored procedures to fill in their primary index. Because both procedures are identical, I describe only the one on the Album page.

To get started, you need to be sure the stored procedure is set up on the server side. Here is the code you should enter into WISQL to create the procedure:

CREATE PROCEDURE GETALBUMGEN RETURNS (NUM INTEGER)

As you can see, this is a simple stored procedure that does nothing more than return a single value.

　

---

NOTE: If you are having trouble setting up the procedure, the entire data definition for the database is supplied later in this chapter, or you can just use the version of the database that is found on disk. You can add the code shown in this section to your current version of the music program. Or, if you want, you can just start a new project, create a data module, drop a TDatabase object on it, and connect it to the Music database that ships on the CD that accompanies this book. Setting up the alias for the database is described in depth in the readme file from the CD. Once you have the TDatabase object connected to Music.gdb, you can drop down the TStoredProc and start working with it, as described in the rest of this section.

---

　

To get started using a TStoredProc, drop it onto the Album page or onto a data module. Set the StoredProcName alias to the GetAlbumGen stored procedure.

After selecting the procedure to use with the TStoredProc, you can pop up the Params field to see the parameters passed to or returned by the function. In this case, only one parameter returned as the result of the function.

Whenever the user wants to insert a record into the Album table, the following procedure is called:

void __fastcall TAlbumForm::sbInsertClick(TObject *Sender)

As you can see, there is no reference to a stored procedure in these lines of code. Instead, I reference the stored procedure in the BeforePost event for the AlbumTable:

void __fastcall TDMod::AlbumTableBeforePost(TDataSet *DataSet)

The key lines of this procedure are the ones involving the stored procedure:

GetAlbumGen->Prepare();

This code first executes the stored procedure and then snags its return value from the Params field of the TStoredProc. The Params field for stored procedures works the same way as the Params field for TQuery objects.

As you can see, BCB makes it easy for you to use stored procedures in your programs. In this particular case, you could have used a stored procedure rather than a trigger. However, it is not a great crisis if you have to use a stored procedure rather than a trigger. The one advantage triggers have over stored procedures is that they are

called automatically, thereby helping you to ensure data integrity.

Near the end of this chapter, I return to the subject of stored procedures when I discuss techniques for querying the data in a database in the section "Asking the Database a Question."

Deciding When to Use Triggers

When working with triggers, you should keep the following ideas in mind:

• BCB has no support for InterBase events. As a result, it does not know when a trigger occurs. This means you should never use a trigger to alter a field that your program is indexed on. It's okay to use a trigger to alter a field that is indexed; just don't let it fire while your program is actively using that index.

  　

• C++Builder's lack of support for InterBase events usually is not a problem. Trouble only occurs if you index on the field that is updated by a trigger or if you rely on a grid to perform certain operations. If you index on a field that is updated by a trigger, the record disappears out from under the user. It doesn't matter if some index you are not currently using is updated; it must be a live index currently in use by your program to cause this kind of trouble. For instance, if there is a CODE or CUSTNO field on a table, and you currently index on that field, you will have trouble after an insert because the record seems to disappear beneath you. In other words, Builder won't know about the new CUSTNO or CODE number and will not be able to find the record after the insert.

  　

• The best solution to the problem outlined previously is to never have the current index be the same field that is updated by a trigger. If you use this solution, be sure not to show the user the field that is updated by the trigger because it might not be updated automatically by Builder when displayed in a visual control. In other words, Builder doesn't know the trigger was fired, so it doesn't know to update a TDBGrid or TDBEdit control. However, it won't matter if the data is out of date, as long as you do not index on it and the user can't see the field in your grid.

  　

• If you must index on a field that is updated, then the solution is to delete the trigger and insert a stored procedure instead. The stored procedure returns the new number created by a generator. You can then call this stored procedure inside a BeforePost event and use the number returned to fill in the CUSTNO or CODE field. You should do all this inside a TDataModule and then insist that all members of your team use that TDataModule whenever they access the table in question. This is the solution used throughout the Music program and the one that I prefer to use in most cases.

  　

• A third alternative is to use triggers in combination with the IBEventAlerter component from the samples page in the Examples directory that ships with BCB. There is no description of the IBEventAlerter in this book, and you will have to explicitly add the Samples page to the Component Palette before you can use it. A description of how to add it to the IDE is found in the Examples directory set up by BCB during the default installation.

Following is the code for generating both the trigger and the stored procedure described previously. You do not ever want to include both the trigger and the stored procedure in the same database. You have to choose between the two techniques:

CREATE GENERATOR IMAGECODE;

When checking for referential integrity, your program may raise an exception because you failed to fill in a field specified as NOT NULL before the BeforePost event is fired. In other words, you might need to fill in the field even before the code reaches the BeforePost event. One way to solve this problem is to simply put in a dummy number at the time the user first decides to do an Insert:

void __fastcall TDMod::AlbumTableAfterInsert(TDataSet *DataSet)

The value 0 shown in this code serves as a placeholder. It is replaced during the BeforePost event or during the execution of a trigger. All the value 0 does is satisfy the referential integrity code that insists the field not be null. The actual value, of course, is supplied later by the OnBeforePost event handler.

The Complete Data Definition for MUSIC.GDB

Here is the complete data definition for MUSIC.GDB:

/* Extract Database e:\unleash\data\music.gdb */

If you want to create this database from scratch, you can run this entire statement through WISQL from the File menu. Otherwise, you can create the database and pass the statements through one at a time. A third use for the code is simply to give you one place to look when you need a reference for MUSIC.GDB. If you already have a copy of the database, you can create the output shown here; select Extract | SQL Data from Database from the WISQL menu.

Server-Side Rules Versus Client-Side Rules

A few more words about database design: In many people's minds, the holy grail of contemporary client/server development is to place as many rules as possible on the server side of the equation. This means that no matter how the user accesses the data, and no matter how many front ends are written to access the data, the basic rules of the database are enforced.

This means that you must create referential integrity on the server side using foreign keys or whatever tools are at your disposal. Furthermore, you should use triggers whenever possible to enforce additional rules. For instance, some people view it as an error to insert the Code field of the Album table using a stored procedure rather than a trigger. I do things this way simply because it makes it easier for me to create the kind of front end I want for the Music table.

Even using triggers and referential integrity is not enough for many hard-core adherents of the server-side philosophy. This book, however, is written about a client-side tool, so I generally promote just placing the referential integrity on the server side and maybe adding a few more triggers or stored procedures where necessary.

If you can use QBE, or some other SQL builder, to create powerful SQL statements that you use in your programs, you probably should place those SQL statements in stored procedures, as shown previously and discussed in more depth later in this chapter, in the section called "Asking the Database a Question."

I find that most other database chores are easier to perform on the BCB side. BCB is a powerful language with powerful debuggers to back it up. Most servers have neither a powerful language nor a powerful debugger. As a result, I feel it's often wisest to keep certain kinds of database logic on your client side, as long as it does not exact a huge penalty in terms of performance. Obviously, if you need to fetch a lot of rows and then process them, it's best to do that on the server side where the data itself is stored.

The emergence of Distributed OLE and other tools that support Remote Procedure Calls (RPCs) is likely to have a powerful impact on the future of this issue. If PC-based developers can use Distributed OLE to place rules easily on remote servers, the whole way databases are constructed is likely to change. In other words, if I could use BCB to enforce a bunch of rules and then encapsulate those rules in an object that resides on the same machine as the InterBase server, I would do it. I would put all my database logic out there and just provide a few entry points for my front-end program to call.

An Overview of the Interface for the Music Program

The interface for MUSIC.DPR presents the user with a main screen with three pages embedded inside it. One page (shown in Figure 16.1) is meant only for

performing searches. It gives you a view of both the Artist table and either the Book table or the Album table. You can't alter the Artist table from this screen, but you can change the Album or Book table.

Figure 16.1.The Index page from MUSIC.DPR enables you to view artists and their related productions.

　

---

NOTE: I use the following code to prevent the user from using the Insert key to insert a record into either of the grids on the Index page:

　

void __fastcall TIndexForm::ArtistGridKeyPress(TObject *Sender,

char &Key)

{

if ((Key == VK_INSERT) || (Key == VK_DELETE))

Key = 0;

　

}

This code disarms Insert and Delete keystrokes. Users never know the method is called but find that they can't use the Insert or Delete keys. The code is called in response to OnKeyDown events.

---

　

The second page in the Music program allows you to see one record from the Artist table, as shown in Figure 16.2. Users can browse or search through the data using the Index page shown in Figure 16.1. If they want to look at a particular record, they can switch to the Artist, Album, or Book pages. Figures 16.3 and 16.4 show the latter two of these three pages.

Figure 16.2.The Artist page from the Music program.

Figure 16.3.The Album page from the Music program.

The Book or Album page is always the third page in the program. There is no fourth page. If the current record is an album, then the Album form is shown; if it is a book, then the Book form is shown.

Figure 16.4.The Book page from the Music program.

Working with Paged Forms

BCB provides a number of paged dialogs or notebooks that you can use to present data to the user. In this particular program, I use the TTabControl tool in conjunction with a series of forms. My primary concern is allowing the programmer to place each major object in a separate unit, rather than forcing the combination of various different sets of functionality into one paged notebook. In other words, the Album page is a separate object, not a part of the TNotebook object.

The Music program really has five major forms in it, the first representing the frame for the entire program, and the rest representing the Index, Artist, Album, and Book pages. The next few paragraphs describe how to make a form become a child of a second form, which is what is really happening inside the Music program.

The key point to grasp is that you need to convert the standard BCB pop-up form to a child form that has Form1 as its parent. Here is how to proceed:

#include <vcl\vcl.h>

The logic for the code shown here was based on work done by Pat Ritchey.

In the Music program, all the main forms descend from the ChildForm shown previously. As a result, they inherit the capability to live as a child form pasted on top of another form:

When using this technique, you want the parent form to explicitly create the child forms. To do this, do not autocreate the forms but choose Options | Project | Forms and move the forms from the Auto-Create list box into the Available Forms list box. Then you can create the forms as needed inside of Form1 with code that looks like this:

I describe this process in more depth later in this chapter in the section that examines the FormCreate event for the main module.

To make sure the form adheres to the dimensions of its parent, you can respond to OnResize events:

This code uses the TabControl1->DisplayRect function call to get the size of the window to draw in, excluding the location where the tabs reside. It then resizes the child form so that it fits in this space.

The Code for the Music Program

Now that you understand the basic structure of the Music program, the next step is to take a look at the code and analyze any sections that need explanation. The code appears in Listings 16.1 through 16.12.

Listing 16.1. The Main header form for the Music program.

#endif

Listing 16.2. The Main form for the Music program.

}

Listing 16.3. The Index header for the Music program

#ifndef IndexForm1H

#endif

Listing 16.4. The Index form for the Music program.

}

Listing 16.5. The header from the Artist form for the Music program.

#endif

Listing 16.6. The Artist form for the Music program.

}

Listing 16.7. The header from the Album form from the Music

program.

#endif

Listing 16.8. The Album form from the Music program.

}

Listing 16.9. The header for the Book form from the Music program.

#endif

Listing 16.10. The Book form from the Music program.

}

Listing 16.11. The header for the TDataModule for the Music

program.

#endif

Listing 16.12. The data module for the Music program.

}


Several other utility forms from the Music program are not shown here. I do not include them because they are mostly empty of code and are used primarily as a place to display data or else to retrieve simple input from the user.

Using the Music Program

There are many interrelated tables in the Music program all working together to produce a particular result. The thing to understand when studying this program is how you can make the tables in a relational database work together seamlessly toward a particular end.

Because this program uses InterBase, you can use it in a rigorous multi-user environment without fear that it would collapse under the load. For instance, there is no reason why this program, as is, cannot handle 200-300 simultaneous users.

Suppressing the Password: The TDatabase Object

The TDatabase object on the main form has its AliasName property set to the Music alias. This alias was defined in the Database Explorer, and it points to the tables that make up the music database. The alias is shown in Figure 16.5.

Figure 16.5.The alias for MUSIC.GDB as it appears in the Database Explorer.

The DatabaseName property of the TDatabase object is set to the string MusicData, which is the alias attached to by all the other TTable, TStoredProc, and TQuery objects in the program. Remember: Only

the TDatabase object attaches directly to the Music alias. This allows you to point the entire program at a second database by changing only one variable: the AliasName property. This feature can be handy if you need to experiment without touching your primary data.

The Params property for the TDatabase object contains the following information:

USER NAME=SYSDBA

The LoginPrompt property is then set to False, which makes it possible to launch the program without entering a password. This is pretty much a necessity during development, and it's a useful trait in a program such as this that probably has little fear of hostile attacks on its data.

The FormCreate Event

It's up to the constructor for the main form to create the child windows that hold all the main controls used in the program:

__fastcall TForm1::TForm1(TComponent* Owner)

The first four lines of the routine create the forms. The next four lines assign them to an array of TForm objects. You can then use this array to iterate through all the main forms for the program, as shown in the last two lines of the routine, and in the OnResize response method shown previously.

As you can see, an ordinal value of 0 gives you immediate access to the Index form if you write code that looks like this:

ChildForms[0]->Width = X;

One of the most important methods in the program is the TabControl1Change

event handler:

void __fastcall TForm1::TabControl1Change(TObject *Sender)

The primary burden of this code is to move the appropriate form to the front when requested by the user:

ChildForms[2]->BringToFront();

This code brings the AlbumForm to the front. To the user, this looks as though a hit on the TabControl caused the "page to be turned" inside the control. Of course, what really happens is that you simply push one form down in the Z order and bring another to the top. In short, you create your own page control out of separate forms. The beauty of this arrangement is that it ensures that each page of the TabControl exists as a separate object in its own module. This is much better than the system used in the kdAdd program.

Another key chore of the TabControlOnChange handler is to set the OnClick event for the buttons at the top of the form so that they reflect what happens inside the current page. For instance, if the BookForm is selected, a click on the Post button should call the Post method of the Book table, not the Album or Artist table. To ensure that all works correctly, this method simply sets the OnClick method to the appropriate routine whenever the TabControl is moved:

InsertBtn->OnClick = BookForm->sbInsertClick;

In this case, the methods associated with the InsertBtn and so on are the methods from the BookForm. This technique helps you to see how dynamic the delegation model can be if you need to push the envelope a bit.

I'd mentioned earlier that the Types table holds a series of types that can apply to either musical or written works. For instance, the table might look like this:

TYPES CODE

When the application is in Music mode, then the first half of the table is used; otherwise the second half of the table is used. Here is code from the TabControlOnChange event that ensures that the proper part of the code is operative when the program is in Book mode:

DMod->TypesTable->Filtered = False;

The following lines are executed when the user switches the program into Music mode:

DMod->TypesTable->Filtered = False;

After looking at the code, it should come as no surprise to learn that the Types table has an OnFilterRecord event handler:

void __fastcall TDMod::TypesTableFilterRecord(TDataSet *DataSet,

Once this method is defined, all you have to do is set the TypesTable filtered property to true, and only the selected half of the types table is visible. By the time the Album or Book form gets at this table, it appears that it only contains value pertinent to the relevant form.

Of course, I defined a property so that the code in the main form never directly touches the privates of the TDataModule object:

__property int MaxTypes={read=FMaxTypes, write=FMaxTypes, nodefault};

FMinTypes and FMaxTypes are private data for the program.

The following code from the main module of the program gets executed whenever the user switches between Music and Book mode:

void __fastcall TForm1::FilterOptionsClick(TObject *Sender)

The purpose of this code is to properly set up the two grids on the index page. When the program is in Music mode, you want the right-hand grid on the Music page to show the Music table, and when you are in Book mode, you want it to show the Book table. I could have simply switched the DataSource for one form as needed, but that does not take care of the issue of defining the fields to be shown in the grid. Rather than try to create the columns on-the-fly, I decided instead to use the code shown here.

Lookups

This program uses a large number of lookups. All of them are defined on the TDataModule itself, although some are used in the grids on the Index form, others on the Book and Album pages, and some in both locations.

The primary purpose of the lookups is to ensure that the user always chooses from

preselected lists of values and does not start typing in her own values on-the-fly. For instance, you want the user to choose a type from the Types table and not make up new types at random.

Both the Book and Album tables have lookups into the Types and Medium tables, whereas the Album table also has a lookup into the Loudness table. These lookups are visible to the user both on the grids from the index page and also on the Book and Album forms.

Because these lookups are so readily available, the user rarely has to type anything into a control. Instead, he can quickly select options from a lookup list. It is not currently available, but it is a nice gesture to the user to provide a means of editing the lookup tables, as shown in the Address program from Chapter 13, "Flat-File, Real-World Databases."

Asking the Database a Question

By this time, you know most of what you need to know to construct a reasonably powerful database. This is a good feeling, and it's nice to know that you can get this kind of control over an important domain of information.

Nevertheless, despite this sense of accomplishment, there may still be a nagging feeling that something is missing. After all, it's work to construct the database and work to enter data into it, so where is the fun part? Where is the part that makes you say in the inimitable words of the fearless leader from Redmond, "Hey, that's cool!"

Once you have a database up and running, the way to get joy from it is to ask it questions. At first, you might just want to ask simple questions. For instance, you might remember the beginning of the name of an album, but you can't remember the whole thing.

Suppose that you remember that an album begins with the letter L. If you bring up WISQL, you can ask the following question:

select Album from album where album like "L%"

As you can see, the results returned from this question are a list of the albums and books that start with the letter L.

This is fairly useful, but you really want to know not only the name of the album, but also the artist behind the album. You could, of course, ask the following question:

select Album, GroupCode from album where album like "L%"

This gives you the name of an album plus the GroupCode associated with the album. Then, all you have to do is run one more query to get the answer you need:

select First,Last from Artist where Code = 11

Of course, it would be nice if you didn't have to ask this question in two stages. Instead, you might want to ask the following question, which performs a join between the Album and Artist tables:

select Artist.Last, Album.Album

Now you are starting to get somewhere. This information is fairly valuable to you. When composing the preceding query, you should be careful to include where clauses that both specify the letters you want to search on and the relationship between the Artist and Album tables:

where artist.code = album.groupcode

If you don't qualify the question in this way, then you end up getting a much larger result set than you want. In particular, you indicate that the query shouldn't link the resulting albums to all the names in the Artist table but just link them to the names of the artists that have the same code as the groupcode of a particular album.

Now that you have seen this much, most people also want to get information about the rating for the album, as well as its type. One way to ask that question looks like this:

select Artist.Last, Album.Album, Album.Rating, Album.Types

When reviewing this data, it's hard not to feel that there is something missing from the Types field. After all, what does the number 1 mean? What type is that?

Once again, you can get the question answered by going to the well a second time and querying the Types table. However, it should come as no surprise to learn that there is a second solution:

select Artist.Last, Album.Album, Album.Rating, Types.Types

Here you broadened the question by specifying that you want to bring in the Types table:

from Album, Artist, Types

Include one of its fields in the result set:

select Artist.Last, Album.Album, Album.Rating, Types.Types

Link the Album table and Types table on the primary and foreign keys of the two tables:

where ... Types.Code = Album.Types

You get all the things you need; the only problem is that you can't ask the user to open up WISQL--of all applications!--just to get the answer to a simple question.

You can take a number of courses at this point, but one of the best is to simply wrap your query in a stored procedure:

CREATE PROCEDURE ALBUMSEARCH (ANALBUMNAME VARCHAR(75))

You can break this procedure down into several sections in order to make some sense of it. First, notice the header:

CREATE PROCEDURE ALBUMSEARCH (ANALBUMNAME VARCHAR(75))

This says that you are creating a stored procedure named AlbumSearch that takes a string as a parameter. You supply the name of the album you want to search in this string.

The next part of the procedure declares what is returned to the user:

RETURNS (ARTISTNAME VARCHAR(30),

These rows set up what you want returned from the procedure. At the very bottom of the procedure, you state these names again, saying that you want the query to be returned in these variables:

into :ArtistName, :AlbumName, :RatingValue, :TypeName, MediumName

The query itself sits in between a begin..end pair, which nests around a for...do statement:

as

If you forget to wrap your query in this faintly ridiculous-looking syntactical sugar, InterBase complains about a singleton query not being able to return multiple rows.

Now that you have your stored procedure all set up, the next thing to do is call it from BCB. The syntax for doing this could not be simpler:

select * from AlbumSearch(:SearchValue);

This simple SQL statement should reside inside the SQL property of a BCB TQuery component. You can then call this procedure with code that looks like this:

That's all there is to it. Now you can hook up a TDataSource to the TQuery and a TDBGrid to the TDataSource, and after calling the AlbumSearch function, you see the results of your query inside a BCB application.

You can access a number of interesting stored procedures in this manner from the menus of the BCB program. Some of the most interesting ones involve asking about the ratings you assign to albums. For instance, you can ask to see all the albums that have a rating between five and seven or a rating higher than nine. This is such an important set of queries that I review them in the last section of this chapter, "Viewing a Range of Data."

Asking Questions That Do Not Return Datasets

In the preceding section, you saw how to ask a question that returns a dataset. A different kind of stored procedure asks how to return a particular value such as a single number or string. For instance, you might want to ask the answer man how many albums are in the database:

select Count(*) from album;

To create a stored procedure that returns this kind of information, you should write

CREATE PROCEDURE ALBUMCOUNT

This procedure doesn't take any parameters:

CREATE PROCEDURE ALBUMCOUNT

It does, however, return a value:

RETURNS (NUM INTEGER)

Because you ask for a single answer and not a series of rows, you can use exit instead of suspend:

for // Query goes here

Once you compose the query, you can use a stored procedure on the BCB end to get data from it. To set up the stored procedure, all you have to do is drag it off the Component Palette, set its DataBaseName to the Music alias, and drop down the list from its StoredProcName property so you can choose the appropriate stored procedure.

The following code shows how to call the stored procedure from BCB:

int TDMod::GetTotalAlbumCount(void)

This method returns an integer, which you can display to the user in any manner you think appropriate.

Viewing a Range of Data

Two interesting stored procedures allow you to ask questions such as, "What albums have a particular rating?" and "What albums have a rating of nine or better?"

CREATE PROCEDURE NINEORBETTER

The query at the heart of this procedure asks to see albums that have a rating larger than or equal to nine. To properly qualify the query, the code also asks to see only the entries from the Artist table that are associated with the albums that make it into the result set. The last line of the query asks to order the result set on the album.rating field with the highest ratings first.

The stored procedure shown here lets you ask for data from the table that falls

into a particular range:

CREATE PROCEDURE RATINGRANGE (LOWRATING INTEGER,

This procedure is very much like the last one, but it takes parameters that allow you to specify the range you want to see, and it uses those parameters to customize the result set to your needs:

Album.Rating >= :LowRating andAlbum.Rating <= :HighRating

---

NOTE: Here is another example of a stored procedure that uses SQL:

CREATE PROCEDURE CONTACTBYPRODUCT (PRODNAME VARCHAR(20))

RETURNS (SFIRST VARCHAR(30),

SLAST VARCHAR(30),

STITLE VARCHAR(30))

AS

BEGIN

SELECT DISTINCT C.LAST, C.FIRST, C.TITLE

FROM CONTACTS C, CONT2PROD C1, BORPRODS B

WHERE

(C1.CONTACTNO = C.CONTACTNO)

AND (B.BORPRODID = C1.BORPRODID)

AND (B.PRODUCT = :ProdName)

ORDER BY C.LAST, C.FIRST, C.TITLE

INTO : SLAST, SFIRST, STITLE;

SUSPEND;

END

This code is part of the expression of a many-to-many relationship. The BorProds table contains a list of products that might be associated with the people listed in the Contacts table. This situation calls for a many-to-many relationship because there can be more than one product associated with each of the records listed in the Contacts table, and there can be more than one contact associated with each of the records in the BorProds table. As a result, there is an intermediate table that lists a series of IDs from the Contacts and BorProds tables.

The query shown previously fulfills the request: Show me a list of contacts associated with one particular product. You pass in a product name, and you get back all the Contacts associated with that product. The product name passed in is bound to the query as a parameter:

AND (B.PRODUCT = :ProdName)

　

Notice that you will get back a dataset, not just three variables, when you call this function. In other words, you get back a series of records, each containing the SLAST, SFIRST, and STITLE fields. The results of the query are placed in the dataset via the syntax that reads

INTO : SLAST, SFIRST, STITLE;

　

The SUSPEND statement temporarily delays the execution of the procedure while a Fetch statement is carried out. Imagine the moment after the first row is calculated, while it is returned via the calling procedure. The loop is temporarily put on hold while this is going on; that is, it is SUSPENDed. After the fetch is complete, the next row is calculated, and so on. You use SUSPEND statements in procedures that return rows of values, not in simple procedures that return only one value.

The point here is that you can store a particular query in a stored procedure and then call it from a TQuery or TStoredProc component. This approach can eliminate considerable complexity in programs that need to perform complex operations such as creating many-to-many relationships. This process allows you to keep a SQL statement on the server side rather than in the SQL property of a TQuery component.

---

　

I've shown you several examples of stored procedures so that you might understand how much power there is in a simple query. To me, the most interesting thing about database programming is the ability to ask questions of the data you have collected. The key to that process is to write a query and then place it in a stored procedure so you can call it from your applications. You can also place the query directly in a TQuery component, but then the query takes longer to execute, and you have the bother of managing it on the client side. Everything is easier if you just leave the query on the server where it belongs.

Summary

That's all I'm going to say about the Music program. A few lines of code in the program were never mentioned, but most of the application was reviewed in this chapter.

Once again, this program contains a lot of the basic code that anyone uses when constructing a relational database with BCB. The particular example shown is not

robust enough to use in a professional setting, but it gives you a good feeling for how to proceed if you want to construct such an application.

In particular, you got a good look at the techniques used to create a robust database with referential integrity. You also saw how to use generators, triggers, and stored procedures and how to perform filters and lookups on relational data. In general, this chapter is the one that sums up the core information necessary to produce a

professional database program. If you understand all this material, you are not yet necessarily an expert, but you are ready to start building relational databases in a professional setting.