|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

| ## Charlie Calvert's C++ Builder Unleashed ## - 9 - ## Using TTable and TDataSet In this chapter, you learn some of the basics about accessing database tables using the `TTable` object. In particular, the chapter covers the fundamental information you need to access tables without using SQL. An examination of using SQL to access tables begins in the next chapter. In the examples given here, you will be explicitly working with local Paradox tables, but nearly everything explained in this chapter applies equally to dBASE files or to files located on a SQL server such as InterBase or Oracle. I decided to run these examples against Paradox tables because I wanted to keep this chapter as simple as possible. Large portions of this book work exclusively with SQL databases, but this chapter sticks to the world of local tables. Looking a little more deeply at the content of this chapter, you can expect to find information on the following: The `TTable` object, which provides the fastest and simplest access to tables. The `TQuery` object, which is the gateway to the flexible and powerful world of SQL. The `TDataSet` object, an ancestor of `TTable` and `TQuery`, which provides the core functionality for accessing tables and the records that lie within them. The `TField` object, which gives you access to the fields in a table or dataset. This object has powerful descendants such as `TStringField` and `TIntegerField`, all of which can be created automatically by a visual tool called the Fields Editor. The `TDataSource` object, which serves as an intermediary between data-aware controls and the `TTable` and `TQuery` objects. The `TDBGrid` object, which provides a simple and easy way to display the contents of tables to the user. The `TDBGrid` object supports editing, deletion, and insertion. It also has support for drop-down lists in lookup fields, and the capability to assign colors to a column, row, or individual field. I visit this control more in Chapter 11, "Working with Field Objects." The `TDBEdit` component, which enables you to display a single field from a single record and to edit or insert the data for that field. Filtering on nonkeyed fields. The `TDatabase` component, which can be handy when you need to optimize the way you connect to a server. Connecting to a database without using any visual tools. BCB supports something called two-way tools, which enable you to do things either visually or in code. Most of this chapter takes the visual route, but you can work entirely in code if you want or if you have some special need that requires that approach. This chapter ends with a lengthy explanation of how to take this rather unusual approach to BCB database programming. The order in which database events occur. This is an important subject, which has to be mastered by serious database programmers. Here is a second way to categorize some of the objects discussed in this chapter: Nonvisual: `TTable`, `TQuery`, `TDataSet`, `TField`, and `TDatabase` Visual: `TDBGrid` and `TDBEdit` Link (also nonvisual):`TDataSource` This latter view of the major database components breaks them down into two major categories. The nonvisual components enable you to open, close, edit, and otherwise manipulate tables, records, and fields. The visual components display the tables to the user so he or she can view or edit them. The powerful `TDataSource` object forms a link between the visual and nonvisual database controls. You might want to think of the nonvisual controls as being the intelligent heart of the BCB database tools, while the visual controls are the less intelligent outward show. The nonvisual controls manipulate the data; the visual controls display it. The overriding purpose of this chapter is to give you a good overview of the basic facts about using a BCB database class called `TDataSet`. `TDataSet` is the driving force behind both the `TTable` and `TQuery` objects. It is the root class from which they are both descended. A third component, called `TStoredProc`, is also descended from `TDataSet`. It will be discussed in more depth in Chapters 15, "Working with the Local InterBase Server," and 16, "Advanced InterBase Concepts," both of which deal with InterBase. Specific information about other database issues will be presented in subsequent chapters. For instance, the `TQuery` object will be treated in depth in the next chapter, and more detailed explanations of `TDBGrid`, `TField`, `TStringField`, and `TIntegerField` are found in Chapter 11. ### Understanding the TDataSet Class In the last chapter you were introduced to the Database Explorer, the SQL Monitor, and `TDataModule`. It is now time to start digging into some of the technical details of the `TTable` object. Learning something about these details will go a long way toward helping you understand the structure of the database tools supplied by BCB. `TTable` and `TQuery` inherit most of their functionality from `TDataSet`. As a result, the `TDataSet` class is one of the most important database objects. To get started working with it, you need to concentrate on the hierarchy shown in Figure 9.1. `TDataSet` contains the abstractions needed to directly manipulate a table. `TDBDataSet` knows how to handle passwords and other tasks directly associated with linking to a specific table. `TTable` knows how to handle indices and the specific chores associated with linking two tables in a one-to-many relationship. As you will see in the next chapter, `TQuery` has a deeply rooted and complete knowledge of how to process SQL statements. The `TStoredProc` object is on the same level of the hierarchy as `TTable` and `TQuery`. It is used to process the stored procedures in a SQL database. FIGURE 9.1. The core hierarchy for `TTable` and `TQuery`. The methods of the `TDataSet` object enable you to open and navigate a table. Of course, you will never directly instantiate an object of type `TDataSet`. Instead, you will usually be working with `TTable`, `TQuery`, or some other descendant of `TDataSet`. The exact way this system works, and the precise significance of `TDataSet`, will become clear as you read through this chapter. On the most fundamental level, a dataset is nothing more than a set of records, each containing x number of fields and a pointer to the current record. On many occasions, a dataset has a direct, one-to-one correspondence with a physical table that exists on disk. However, at other times, you may perform a query or other action that returns a dataset that contains either a subset of one table or a join between multiple tables. The text that follows, however, sometimes uses the terms dataset and table interchangeably if it helps to simplify the explanation of a particular concept. You will normally instantiate an object of type `TTable` or `TQuery` in order to access the functionality of `TDataSet`. Because of this relationship, the code in the next few sections will always assume the existence of an instance of class `TTable`. Remember, however, that the functions under discussion are part of `TDataSet`, unless the text specifically states otherwise. In other words, much of what I say here applies to both `TQuery` and `TTable`, since both of these objects descend from `TDataSet`. It's now time for you to begin a direct exploration of `TDataSet`. As you become familiar with its capabilities, you will begin to understand exactly how BCB accesses the raw data saved to disk as a database. The key point to remember is that nearly every time a BCB programmer opens a table, he or she will be using a class such as `TTable` or `TQuery`, both of which are merely thin wrappers around `TDataSet`. ### Opening and Closing Datasets The simplest thing you can do with a `TDataSet` is open or close it. This is therefore an appropriate starting point for an exploration of datasets. In the sections that follow, you will drill down deeper and learn more about the thorough access to databases provided by BCB. If you are writing code rather than working through the Object Inspector, there are two different ways to open or close a dataset. You can write the following line of code:
    Table1->Open();
Or, if you prefer, you can set the `Active` property equal to `True`:
    Table1->Active = True;
There is no difference between the effect produced by these two statements. The RTL call to `Open`, however, ends up setting `Active` to `True`, so it may be ever so slightly more efficient to use the `Active` property directly. Just as there are two ways to open a table, there are also two ways to close a table. The simplest way is to call `Close`:
    Table1->Close();
Or, if you want, you can write the following:
    Table1->Active = False;
Once again, there is no substantial difference between these two calls. You should note, however, that `Close` and `Open` are functions, while `Active` is a property. In this section, you have learned about two methods:
    void __fastcall Open(void);

    void __fastcall Close(void);
You also learned about one property:
    __property System::Boolean Active;
It is definitely worthwhile opening up `DB.HPP`, finding the class declaration for `TDataSet`, and examining the methods shown here as well as some of the other ones included in this large object. Remember, most of the rest of this chapter is dedicated to an examination of `TDataSet`. ### Navigational Routines After opening a dataset, the next step is to learn how to move about inside it. The following rich set of methods and properties from `TDataSet` provides all the tools you need to access any particular record inside a dataset:
    void __fastcall First(void);

    void __fastcall Last(void);

    void __fastcall Next(void);

    void __fastcall Prior(void);

    property System::Boolean Bof;

    property System::Boolean Eof;

    System::Integer_fastcall MoveBy(System::Integer Distance);
Experienced programmers will find these functions very easy to use. Here is a quick overview of their functionality: Calling `Table1->First()` moves you to the first record in a table. `Table1->Last()` moves you to the last record. `Table1->Next()` moves you one record forward, unless you are at the end of a table. `Table1->Prior()` moves you one record back, unless you are at the beginning of the table. You can check the `Bof` or `Eof` properties in order to see if you are at the beginning or the end of a table. The `MoveBy()` function moves you x number of records forward or backward in a table. There is no functional difference between calling `Table->Next` and calling `Table->MoveBy(1)`. Furthermore, calling `Table->Prior` has the same effect as calling `Table->MoveBy(-1)`. In fact, `Next` and `Prior` are one-line functions that call `MoveBy`, exactly as shown here. Most of these properties and methods are demonstrated in the sample program found on the CD-ROM accompanying this book as `Navy.dpr`. You can open this example directly, or construct it piece by piece by following the description that follows. To get started using these navigational routines, you should perform the following steps: 1. Place a `TTable`, `TDataSource`, and `TDBGrid` on a form. 2. Hook the grid to the data source and the data source to the table. 3. Set the `DatabaseName` property of the table to the `DBDEMOS` alias, or type in the path to the `demos` subdirectory (`..\BCB 2.0\demos\data`). 4. Set the `TableName` property to the `CUSTOMER` table. If you are having trouble completing these steps, refer to `Navy.dpr`. If you run a program that contains a `TDBGrid` control, you will find that you can iterate through the records in a dataset by manipulating the scrollbars on the edges of the grid. You can gain the same functionality by using the `TDBNavigator` component. However, there are times when you want to move through a table programmatically, without the use of the built-in visual tools. The next few paragraphs explain how this process works. Place two buttons on a form and label them Next and Prior, as shown in Figure 9.2. FIGURE 9.2. The Prior and Next buttons in `Navy.dpr` enable you to maneuver through a database. Double-click once on the Next button to create an `OnClick` method, and fill it in like this:
    void __fastcall TForm1::bbNextClick(TObject *Sender)

    {

      BioLife->Next();

    }
Perform the same action with the Prior button, so that the function associated with it looks like this:
    void __fastcall TForm1::bbPriorClick(TObject *Sender)

    {

      BioLife->Prior();

    }
Now run the program and click the two buttons. You will find that they easily let you iterate through the records in a dataset. Now drop down two more buttons and label them First and Last, as shown in Figure 9.3. FIGURE 9.3. The Navy program with all four buttons inserted. Do the same thing for the calls to `Table->First` and `Table->Last` as you did with `Next` and `Prior`:
    void __fastcall TForm1::bbFirstClick(TObject *Sender)

    {

      BioLife->First();

    }

    void __fastcall TForm1::bbLastClick(TObject *Sender)

    {

      BioLife->Last();

    }
Nothing could be more straightforward than these navigational functions. `First` takes you to the beginning of a dataset, `Last` takes you to the end, and the `Next` and `Prior` functions move you one record forward or backward. ### Checking for the End or Beginning of a Dataset `TDataSet`'s `Bof` is a read-only Boolean property used to check whether or not you are at the beginning of a dataset. The `Bof` property returns `True` on three occasions: After you first open a file After you call `TDataSet->First()` After a call to`Prior` fails The first two items listed should be obvious. Specifically, when you open a dataset, BCB places you on the first record, and when you call `First`, BCB again moves you to the beginning of the dataset. The third item, however, requires a little more explanation: After you have called `Prior` enough times to get to the beginning of a file, and have then tried one more time and found that the call failed, `Bof` will return `True`. The following code shows a common method for using `Prior` to get to the beginning of a file:
    void __fastcall TForm1::bLastToFirstClick(TObject *Sender)

    {

      ListBox1->Items->Clear();

      tblAnimals->Last();

      while (!tblAnimals->Bof)

      {

        ListBox1->Items->Add(tblAnimals->Fields[0]->AsString);

        tblAnimals->Prior();

      }

    }
The code shown here is from the BofAndEof program found on the CD-ROM that comes with this book. In this case the `ListBox` `Add` method is called on the current record and then on every other record between the end and beginning rows of the dataset. The loop continues until a call to `Table->Prior` fails to move you back any further in the table. At that point `Bof` returns `True` and the program breaks out of the loop. If your dataset is connected to a data source, you can optimize a loop like the one shown previously by setting `DataSource1.Enabled` to `False` before beginning the loop, and then resetting it to `True` after the loop is finished. These two lines of code enable you to iterate through the table without having to update the visual tools at the same time. Everything said previously about `Bof` also applies to `Eof`. In other words, the code that follows provides a simple means of iterating over all the records in a dataset:
    void __fastcall TForm1::bFirstToLastClick(TObject *Sender)

    {

      ListBox1->Clear();

      tblAnimals->First();

      while (!tblAnimals->Eof)

      {

        ListBox1->Items->Add(tblAnimals->Fields[0]->AsString);

        tblAnimals->Next();

      }

    }
The classic error in cases like this is to enter into a `while` or `repeat` loop but to forget to call `Table->Next`:
    do

    {

      ListBox1->Items->Add(tblAnimals->Fields[0]->AsString);

    } while (!tblAnimals->Eof);
If you accidentally wrote code like this, your machine would appear to lock up. You could break out of the loop only by pressing Ctrl+Alt+Del and asking Windows to kill the current process. Also, this code could cause problems if you opened an empty table. Because the code uses a `do` loop, the `ListBox` `Add` method would still be called once, even though there was nothing to process. As a result, it's better to use `while` loops rather than `do` loops in situations like this. `Eof` returns `True` in the following three cases: If you open an empty dataset If you call `Table->Last` If a call to`Table->Next` fails NOTE: Iterating through tables as described in the last few paragraphs is a common process on local databases, but it is not as popular a technique to use with SQL servers. In particular, most client/server databases expect you to process individual records, or sets of records, but not to treat a table as a series of contiguous rows. This is rather a fine point to put on the discussion at this stage of the game, but it is something to keep in mind if you intend to move directly into processing large amounts of data on a server. If you must perform this kind of operation on a real server, it is probably best to do so from inside a stored procedure. The last navigational routine that I want to cover is called `MoveBy`. `MoveBy` enables you to move x number of records forward or backward in a dataset. If you want to move two records forward, you would write the following:
    tblAnimals->MoveBy(2);
And if you wanted to move two records backward, you would write this:
    tblAnimals->MoveBy(-2);
When using this function, you should always remember that when you are working on a network, datasets are fluid entities, and the record that was five records back a moment ago may now be back four records, or six records, or who knows how many records. In other words, when you are on a network, someone on another machine may delete or add records to your database at any time. If that happens, `MoveBy` might not work exactly as you expect. One solution to this "fluidity" problem is to use the Bookmark functions mentioned later in this chapter. NOTE: `Prior` and `Next` are simple one-line functions that call `MoveBy`. If you have the source, look up `TDataSet.Next` in `DB.PAS`. The `TDataSet` object is a beautifully written wrapper around the core functionality in the BDE. After reading the last two sections, you should have a good feeling for how to move around in a dataset. The navigational commands you have been learning about are very easy to use. Take the few moments necessary to be sure you understand them, because they are likely to be part of your day-to-day BCB programming experience. ### Fields On most occasions when you want to programmatically access the individual fields of a record, you can use one of the following properties or methods, all of which belong to `TDataSet`:
    __property TField *Fields[System::Integer Index];

    __property System::Integer FieldCount;

    __property System::Variant Value;

    TField *__fastcall FieldByName(const System::AnsiString FieldName);
The `Fields` property is one of the most important single syntactical elements of the VCL. It consists of an array of `TField` objects, each of which is automatically assigned to a separate field in a table. For instance, if a table called `Address` had five fields called `Name`, `Address`, `City`, `State` and `Zip`, BCB would automatically create five field objects when you accessed the `Address` table. You don't have to do anything to create these field objects; they are given to you automatically. The `Fields` array for the hypothetical `Address` table mentioned in the last paragraph would have five members. `Fields[0]` would access the `Name` field, `Fields[1]` would access the `Address` field, and so on. The `TField` object is declared in `DB.HPP`. This is an extremely powerful, and fairly complex object, which is worth your while to study in depth. For instance, here are some of the methods of the `TField` object:
`Assign` | Assigns one field to another.
`Clear` | Sets the field to `NULL`.
`FocusControl` | Sets the focus of the form to the first control that hosts the field.
`GetData` | Gets raw data from a field in a buffer. Contrast with `AString`, `AsInteger`.
`SetData` | Sets the field to raw data held in a pointer. Contrast with `AsString` or `AsInteger`.
`IsValidChar` | Tests to see if a character is within the range of valid values for a particular field.
Here are some of the properties associated with a `TField` object:
`AsBoolean` | Conversion property, can be used to read or set the value of a field as a Boolean value.
`AsDateTime` | Conversion property, can be used to read or set the value of a field as a date.
`AsFloat` | Conversion property, can be used to read or set the value of a field as `Float`.
`AsString` | Conversion property, can be used to read or set the value of a field as a string.
`AsInteger` | Conversion property, can be used to read or set the value of a field as an `Integer`.
`Calculated` | Read only Boolean property, tells whether a field is calculated.
`DataSet` | Assign a dataset to a field, or read what dataset is associated with a field.
`EditMask` | Define a mask limiting the valid characters used by a field.
`Value` | The standard means for accessing the value of a field.
FieldName | The name of the underlying field in the database.
`Visible` | Boolean property toggles whether or not a field is visible.
Most of the properties and methods listed previously are discussed in the next few pages of this book. For now you can just review the functionality previously outlined in a general way, so that you can get some sense of what the `TField` class is about. Remember, the previous list is far from exhaustive. It is meant merely to introduce some of the key features of the object.

The `TField` object also has a number of useful descendant classes with names such as `TStringField` and `TIntegerField`. These child objects are discussed in Chapter 17, "Printing: QuickReport and Related Technologies."

The `FieldCount` property returns an integer that specifies the number of fields in the current record structure. If you wanted a programmatic way to read the names of these fields, you could use the `Fields` property:

    {

      AnsiString S(Table->Fields[0]->FieldName);

    }

If a record had a first field called `CustNo`, the preceding code would put the string `"CustNo"` in the variable `S`. If you wanted to access the name of the second field in the example, you could write this:

    S = Table->Fields[1]->FieldName;

In short, the index passed to `Fields` is zero-based, and it specifies the number of the field you want to access, where the first field is number zero, the second is referenced by the number one, and so on.

If you want to find out the current contents of a particular field from a particular record, you can use the `Fields` or `FieldByName` property, or you could access the entire table as an array of fields. To find the value of the first field of a record, index into the first element of the `Fields` array:

    S = Table->Fields[0]->AsString;

Assuming that the first field in a record contains a customer number, the code shown would return a string such as ``1021`', ``1031`', or ``2058`'. If you wanted to access this variable as an integer value, you could use `AsInteger` in place of `AsString`. Similar properties of `Fields` include `AsBoolean`, `AsFloat`, and `AsDate`.

If you want, you can use the `FieldByName` function instead of the `Fields` property:

    S = Table->FieldByName("CustNo")->AsString;

As used in the examples shown here, both `FieldByName` and `Fields` return the same data. The two different syntaxes are used solely to provide programmers with a flexible and convenient set of tools for programmatically accessing the contents of a dataset. When in doubt, use `FieldByName` because it won't be affected if you change the order of the fields in your table.

> NOTE: I'll add a note here for Delphi programmers who may be a bit confused by the tack I am taking on this subject. In Delphi, you can also treat `TTable` as a variant array, which will let you access the fields of a table with the following syntax:

>     S := Table1[`CustNo'];

> This is obviously a considerable improvement over the `FieldByName` method. However, this syntax is not supported in BCB.

It might be helpful to take a few moments to discuss a set of routines from the FieldObject program found on the disk that accompanies this book. These routines illustrate the most common ways to access the value of a field.

The `vValueClick` routine shows the default means for setting or getting the value of a field:

    void __fastcall TForm1::bValueClick(TObject *Sender)

    {

      ShowMessage(tblOrders->FieldByName("TaxRate")->Value);

    }

This is the standard way to get at a field. You use the `AsString` or `AsInteger` properties for conversion, but when you just want to get at the type of a field, you can use the `Value` property. However, the issue of conversion can sometimes raise its head at strange moments, so it is helpful to note that properties such as `AsInteger` and `AsString` exist.

In the `TField` object, the `Value` property is declared as being of type `Variant`, which means it will handle most type conversions for you automatically. This is illustrated in the previous example, where the `TaxRate` field, which is of type `Float`, is converted for you automatically to a string. Note, however, that descendants of `TField`, such as `TStringField`, may explicitly declare the `Value` field as an `AnsiString`:

    __property System::AnsiString Value;

> NOTE: Remember that Object Pascal treats properties and methods with the same name differently than C++. In particular, there is no function or method overloading in Object Pascal.

Here are some other illustrations of how to use the `TField` object:

    void __fastcall TForm1::bAsStringClick(TObject *Sender)

    {

      ShowMessage(tblOrders->FieldByName("TaxRate")->AsString);

    }

    void __fastcall TForm1::bAsIntegerClick(TObject *Sender)

    {

      int i = tblOrders->FieldByName("TaxRate")->AsInteger;

      ShowMessage(static_cast<AnsiString>(i));

    }

    void __fastcall TForm1::bAsFloatClick(TObject *Sender)

    {

      float f = tblOrders->FieldByName("TaxRate")->AsFloat;

      ShowMessage(static_cast<AnsiString>(f));

    }

Three of the four routines shown here from the FieldObject program produce the same output. The odd man out in this group is the `bAsIntegerClick` method, which displays the value as an `Integer`, rather than as a floating-point number. For instance, if the value of the `TaxRate` field in the current record was 4.5, three of the methods shown here would display the string `"4.5"`. The `bAsIntegerClick` method, however, would display the string as simply `"4"`. The difference here is simply due to the fact that the `AsInteger` property automatically converts a floating point number to an int.

### More Information on the Fields Property

The Fielder program that ships on this book's CD-ROM demonstrates some simple ways to use the `Fields` property of `TDataSet`. If you want to construct the program dynamically, place a `TTable`, two buttons, and two list boxes on a form, as shown in Figure 9.4. Hook up the `TTable` object to the `CUSTOMER` table that ships with BCB.
FIGURE 9.4. The Fielder program shows how to use the `Fields` property.

Double-click the `Fields` button and create a method that looks like this:

    void __fastcall TForm1::bbFieldsClick(TObject *Sender)

    {

      int i;

      ListBox1->Clear();

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

        ListBox1->Items->Add(tblCustomer->Fields[i]->FieldName);

    }

This method starts by clearing the current contents of the first list box, and then it iterates through each of the fields, adding their names one by one to the list box. Notice that the `for` loop shown here counts from `0` to one less than `FieldCount`. If you don't remember to stop one short of the value in `FieldCount`, you will get a "List Index Out of Bounds" error, because you will be attempting to read the name of a field that does not exist.

If you enter the code correctly, you will fill the list box with the names of all the fields in the current record structure. BCB provides other means to get at the same information, but this is a simple, programmatic way for you to access these names at runtime.

In the Fielder example, you can associate the following code with the second button you placed on the program's form:

    void __fastcall TForm1::bbCurRecordClick(TObject *Sender)

    {

      int i;

      ListBox2->Clear();

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

        ListBox2->Items->Add(tblCustomer->Fields[i]->AsString);

    }

This code adds the contents of each of the fields to the second list box. Notice that once again, it is necessary to iterate from zero to one less than `FieldCount`. The key point here is that the indices to `Fields` is zero-based.

> NOTE: Much of the functionality of `TField` can be achieved with visual tools. In particular, you can manipulate fields with the Fields Editor, which you can access by clicking once with the right mouse button on the top of a `TTable` or `TQuery` object. This subject is explored in more depth in Chapter 9. However, good programmers know how to use both the methods of `TDataSet` and the Fields Editor. Furthermore, the Fields Editor can be used to best advantage if you understand how to enhance its functionality with some of the code you are learning about in this chapter.

In this section and the one previous to it you learned how to access the fields of a record. In the next section you will see how to use this knowledge when you want to append, insert, or edit records in a dataset.

### Changing Data

The following methods enable you to change the data associated with a table:

    void __fastcall Post(void);

    void __fastcall Insert(void);

    void __fastcall Edit(void);

    void __fastcall Append(void);

    void __fastcall Delete(void);

    void __fastcall Cancel(void);

All these routines are part of `TDataSet`, and they are inherited and used frequently by `TTable` and `TQuery`.

The preceding list is hardly exhaustive. For instance, here are a few more related methods of `TDataSet`:

        void __fastcall AppendRecord(const TVarRec *Values, const System::Integer                                  Values_Size);

    void __fastcall ClearFields(void);

        void __fastcall InsertRecord(const TVarRec *Values, const System::Integer                                 Values_Size);

    void __fastcall FetchAll(void);

    void __fastcall UpdateRecord(void);

    void __fastcall ApplyUpdates(void);

    void __fastcall CommitUpdates(void);

    void __fastcall CancelUpdates(void);

The point here is not that you need to learn all these methods right away. Rather, I would concentrate on the first list, the one that include `Post`, `Insert`, and so on. This first list is meant to be a fairly careful selection of the most commonly used methods. Many programmers will never need to use any other methods than those in the first list. But it is helpful to know that the `TDataSet` object has considerable depth, and if you need customized functionality, you can generally find it in the `TDataSet` object itself.

> NOTE: There are, of course, times when some programmers might need to do something to a database that is not covered in the VCL. In those cases, your next resort after `TDataSet` might well be the BDE itself. In particular, you should use the Borland Database Engine, found in `BDE.HPP`, or in a separate package available from Borland.

Whenever you want to change the data associated with a record, you must first put the dataset you are using into edit mode. As you will see, most of the visual controls do this automatically. However, if you want to change a table programmatically, you need to use the functions listed previously.

Here is a typical sequence you might use to change a field of a given record:

    Table1->Edit();

    Table1->FieldByName("ShipToContact")->Value = "Lyle Mayes";

    Table1->Post();

The first line shown places the database in edit mode. The next line assigns the string `"Lyle Mayes"` to the field labeled `"ShipToContact"`. Finally, the data is written to disk when you call `Post`. See the SimpleEdit program on the CD-ROM accompanying this book for an example of this code in action. When looking at that programming, you might note that I have double-clicked the grid control in order to edit its properties. Working with the grid control will be explained in more depth later in this chapter.

The very act of moving on to the next record automatically posts your data to disk. For instance, the following code has the same effect as the code shown previously, plus it moves you on to the next record:

    Table1->Edit();

    Table1->FieldByName("ShipToContact")->Value = "Lyle Mayes";

    Table->Next();

Calls to `First`, `Next`, `Prior`, and `Last` all perform `Post`s, as long as you are in edit mode. If you are working with server data and transactions, the rules explained here do not apply. However, transactions are a separate matter with their own special rules, as explained in Chapter 15, "Working with the Local InterBase Server." (Both local tables and SQL servers support transactions, but I cover this subject in the chapter on the InterBase server because it is usually considered an intermediate or advanced database topic.)

Even if you are not working with transactions, you can still undo your work at any time, as long as you have not yet either directly or indirectly called `Post`. For instance, if you have put a table into edit mode, and have changed the data in one or more fields, you can always change the record back to its original state by calling `Cancel`. For instance, you can edit every field of a record, and then call the following line to return to the state you were in before you began editing:

    Table->Cancel();

Here is an excerpt from the SimpleEdit program that shows three button response methods that enable you to enter data and then cancel or post your changes. This code is still not robust enough for a shipping programming, but it gives you a good idea of how to proceed if you don't want to include much error checking.

    void __fastcall TForm1::bEditClick(TObject *Sender)

    {

      AnsiString S;

      if (InputQuery("Enter Contact Dialog", "Enter Name", S))

      {

        DMod->tblOrders->Edit();

        DMod->tblOrders->FieldByName("ShipToContact")->Value = S;

      }

    }

    void __fastcall TForm1::bCancelClick(TObject *Sender)

    {

      DMod->tblOrders->Cancel();

    }

    void __fastcall TForm1::bPostClick(TObject *Sender)

    {

      DMod->tblOrders->Post();

    }

I say this code is not really robust. For instance, I do not check to see if the dataset is in edit or insert mode before calling `Post`. This kind of error checking is covered later in this chapter, and several times in the next few chapters. Despite its lack of error checking, the simple methods shown here should give you a good idea of how to proceed in your own programs.

> NOTE: The `InputQuery` method shown in the `bEditClick` method is used to retrieve a string from the user. The first parameter is the title of the dialog used to retrieve the string, the second the prompt asking for a string, and the third the string to be retrieved. `InputQuery` is a Boolean VCL function. It is usually best to zero out the string passed in the third parameter before showing it to the user.

There are also cancel, edit, and insert buttons on the `TDBNavigator` control. You can use this control to work with a dataset without having to write any code.

There are two methods, called `Append` and `Insert`, which you can use whenever you want to add another record to a dataset. It obviously makes more sense to use `Append` on datasets that are not indexed, but BCB won't throw an exception if you use it on an indexed dataset. In fact, it is always safe to use either `Append` or `Insert` whenever you are working with a valid dataset.

On your disk you will find a simple program called Inserts, which shows how to use the `Insert` and `Delete` commands. To create the program by hand, first use a `TTable`, `TDataSource`, and `TDBGrid` to open up the `COUNTRY` table from the demos subdirectory. Then place two buttons on the program's form and call them Insert and Delete. When you are done, you should have a program like the one shown in Figure 9.5.
FIGURE 9.5.The Inserts program knows how to insert and delete a record from the `COUNTRY` table.

> NOTE: To spice up the Inserts program, you can drop a panel on the top of the form and then add the buttons to the panel. Set the panel's `Align` property to `alTop`, and set the `TDBGrid`'s `Align` property to `alClient`. If you run the program, you can then maximize and resize the form without damaging the relationship between the various visual components.

The next step is to associate code with the Insert button:

    void __fastcall TForm1::FInsertClick(TObject *Sender)

    {

      FCountry->Insert();

      FCountry->FieldByName("Name")->Value = "Erehwon";

      FCountry->FieldByName("Capital")->Value = "Nowhere";

      FCountry->FieldByName("Continent")->Value = "Imagination";

      FCountry->FieldByName("Area")->Value = 0;

      FCountry->FieldByName("Population")->Value = 0;

      FCountry->Post();

    }

The function shown here first puts the table into insert mode, which means that a new record filled with blank fields is automatically inserted into the current position of the dataset.

After inserting the record, the next job is to assign strings to one or more of its fields. There are, of course, several different ways to enter this data. Using the current program, you could simply type the information into the current record in the grid. Or, if you wanted, you could place standard edit controls on the form and then set each field equal to the value the user has typed into the edit control:

    Table1->FieldByName(`Name')->Value = Edit1.Text;

If you place a table in edit mode, or if its `TDataSource` object has `AutoEdit` set to `True`, you can use data-aware controls to insert information directly into a record.

The intent of this chapter, however, is to show you how to enter data programmatically. Therefore you are presented with an example in which information is hardwired into the code segment of the program:

    FCountry->FieldByName("Name")->Value = "Erehwon";

> NOTE: One of the interesting (or perhaps "frustrating" would be a more appropriate word) byproducts of this technique is that pressing the Insert button twice in a row automatically triggers a "Key Violation" exception. To remedy this situation, you must either delete the current record or manually change the `Name` and `Capital` fields of the newly created record.
> You should recall that you can pass either a string or a number into a field when using the `Value` property. The `Value` property is of type `Variant`, and so you can, to a considerable degree, ignore type issues in this situation. For instance, there is no need to write code that looks like this:

>     void __fastcall TForm1::FInsertClick(TObject *Sender)

>     {

>       FCountry->Insert();

>       FCountry->FieldByName("Name")->AsString = "Erehwon";

>       FCountry->FieldByName("Capital")->AsString = "Nowhere";

>       FCountry->FieldByName("Continent")->AsString = "Imagination";

>       FCountry->FieldByName("Area")->AsInteger = 0;

>       FCountry->FieldByName("Population")->AsInteger = 0;

>       FCountry->Post();

>     }

> Instead of calls to `AsString` and `AsInteger`, you can just use `Value`.

Looking at the code shown previously, you will see that the mere act of inserting a record and of filling out its fields is not enough to change the physical data that resides on disk. If you want the information to be written to disk, you must call `Post`.

After calling `Insert`, if you change your mind and decide to abandon the current record, you should call `Cancel`. As long as you do this before you call `Post`, everything you have entered is discarded, and the dataset is restored to the condition it was in before you called `Insert`.

One last related property that you should keep in mind is called `CanModify`. A table might be set to `ReadOnly`, in which case `CanModify` would return `False`. Otherwise `CanModify` returns `True` and you can enter edit or insert mode at will. `CanModify` is itself a read-only property. If you want to set a dataset to read-only, you should use the `ReadOnly` property, not `CanModify`.

In this section, you have learned how to use the `Insert`, `Delete`, `Edit`, `Post`, `Cancel`, and `Append` commands. Most of the actions associated with these commands are fairly intuitive, though it can take a little thought to see how they interact with the `Fields` property.

### Using SetKey or FindKey to Search through a File

If you want to search for a value in a dataset, you can call on five `TDataSet` methods, called `FindKey`, `FindNearest`, `SetKey`, `GotoNearest`, and `GotoKey`. These routines assume that the field you are searching on is indexed. You should note that the BCB includes two methods for searching for values in a table. The `SetKey`, `GotoNearest`, and `GotoKey` methods comprise one technique, and `FindKey` and `FindNearest` comprise a second technique. I discuss both techniques in the next few paragraphs.

This book's CD-ROM contains a demonstration program called Search that shows how to use these calls. You should open up this program and run it once to see how it works, or else follow the steps shown here to create the program yourself.

> NOTE: There is actually a third method for searching for values in a table. I demonstrate that technique in the upcoming section from this chapter called "Filtering with the `OnFilterRecord` Event." The technique in question uses a series of routines called `FindFirst`, `FindLast`, `FindNext` and `FindPrior`. Unlike the routines used in this section, `FindFirst` and the like are not tied to the index fields of your table.

To create the Search program, place `TTable` and `TDataSource` objects on a data module, and `TDBGrid`, `TButton`, `TLabel`, and `TEdit` controls on a form. Arrange the visual controls so the result looks like the image shown in Figure 9.6. Be sure to set the caption of the button to Search, and then to wire the database controls so you can view the `Customer` table in the grid control.
FIGURE 9.6. The Search program enables you to enter a customer number and then search for it by pressing a button.

One set of functionality for the Search program is encapsulated in a single method that is attached to the Search button. This function retrieves the string entered in the edit control, searches the `CustNo` column until it finds the value, and finally switches the focus to the record it found.

In this program, I observe more carefully the rules of object-oriented programming. In particular, I create a data module, store the non-visual database tools on it, and then create methods for manipulating the data within the data module itself, rather than inside the object for the main form. This has only theoretical benefits in a simple program like Search. However, in a more complex program, this shows the way to create a single data module--with some built in rules and functionality--that can be reused in multiple programs. It also shows how to create programs that are easy to maintain, and easy to understand.

In particular, the main form contains a method response routine for the Search button that looks like this:

    void __fastcall TForm1::bSearchClick(TObject *Sender)

    {

       DMod->Search(Edit1->Text);

    }

In its turn, the data module has a search method that looks like this:

    void TDMod::Search(AnsiString S)

    {

      tblCustomer->SetKey();

      tblCustomer->Fields[0]->AsString = S;

      tblCustomer->GotoKey();

    }

As you can see, the `TDMod` object protects its data and exposes its functionality to the outside world through a single easy-to-call method. This way, `TDMod` could totally change the internal technique it has for searching through a table without forcing you to modify the `TForm1` object in any way.

The first call in this function sets `Table1` into search mode. BCB needs to be told to switch into search mode simply because you use the `Fields` property in a special way when BCB is in search mode. Specifically, you can index into the `Fields` property and assign it to the value you want to find.

In the example shown here, the `CustNo` field is the first column in the database, so you set the `Fields` index to zero. To actually carry out the search, simply call `Table->GotoKey()`. `GotoKey` is a Boolean function, so you could write code that looks like this:

    if (!Table->GotoKey)

      DatabaseError("Error searching for data!");

The `DatabaseError` routine might raise a database exception of some kind.

If you are not sure of the value you are looking for, call `Table->GotoNearest`. `GotoNearest` will take you to the numerical or string value closest to the one you specify.

The `FindKey` and `FindNearest` routines perform the same function `GotoKey` or `GotoNearest`, but they are much easier to use. Here, for instance, is the technique for using `FindKey`:

    tblCustomer->FindKey(OPENARRAY(TVarRec, (S)));

In this case, `S` is assumed to be a valid `AnsiString` containing a `CustNo` for which you want to search.

Here's how `FindNearest` looks:

    tblCustomer->FindNearest(OPENARRAY(TVarRec, (S)));

When using `FindKey` or `FindNearest` there is no need to first call `SetKey` or to use the `FieldByName` property. (Needless to say, internally `FindKey` and `FindNearest` end up calling `SetKey` and `FieldByName`, before making calls to either `GotoKey` or `GotoNearest`.)

`FindKey` and `FindNearest` take an array of values in their sole parameter. You would pass multiple parameters to `FindKey` or `FindNearest` if you have a table that was indexed on multiple fields. In this case, the `Customer` table has a primary index on one field, called `CustNo`. But if it had multiple fields in the primary index, you could specify one or more of the values for these fields in this parameter:

    tblCustomer->FindNearest(OPENARRAY(TVarRec, (S1, S2, S3)));

This is one of those cases where Object Pascal provides a built in, easy-to-use method for working with a dynamically created array of values, while there is no such native type in C++. C++, of course, is nothing if not flexible, and it is rare that you cannot find a way to make the language conform to your desires. For instance, in this case, one simple way for C++ to accommodate the VCL's request for a dynamically constructed array of values is by using the `OPENARRAY` macro and its associated template class from `Sysdefs.h`.

If you are not searching on the primary index of a file, you must use a secondary index and specify the name of the index you are using in the `IndexName` property for the current table. For instance, the `Customer` table has a secondary index called `ByCompany` on the field labeled `Company`. You would have to set the `IndexName` property to the name of that index if you wanted to search on that field. You could then use the following code when you searched on the `Company` field:

    void TDMod::CompanySearch(AnsiString S)

    {

      tblCustomer->IndexName = "ByCompany";

      tblCustomer->FindNearest(OPENARRAY(TVarRec, (S)));

    }

Remember: This search will fail unless you first assign the correct value to the `IndexName` property. Furthermore, you should note that `IndexName` is a property of `TTable` and would therefore not automatically be included in any direct descendant of `TDataSet` or `TDBDataSet` that you might create yourself. In particular, it's not part of `TQuery`. Indeed, none of the functions discussed in this section are part of `TQuery`.

The previous code is flawed, or at least could be flawed in some cases, in that any attempt to search on the `CustNo` field after a call to `CompanySearch` would fail, because the table would no longer be indexed on the `CustNo` field. As a result, you might want to save the old index in a temporary variable so it can be restored at the end of the function:

    void TDMod::CompanySearch(AnsiString S)

    {

      AnsiString Temp(tblCustomer->IndexName);

      tblCustomer->IndexName = "ByCompany";

      tblCustomer->FindNearest(OPENARRAY(TVarRec, (S)));

      tblCustomer->IndexName = Temp;

    }

A neat trick you can use with the `FindNearest` method involves performing an incremental search across a table. Start a new project and get things rolling quickly by simply dragging the `Country` table off the Database Explorer and onto `Form1`. You will end up with a `TTable`, `TDataSource`, and `TDBGrid` on the form, with the `TTable` hooked up to the `Country` table. Put a panel on the top of the form and set its `Align` property to `alTop`. Set the `Align` property of the `TDBGrid` for the `Country` table to `alClient`.

Place a `TEdit` component on the panel and create an `OnChange` event with the following code attached to it:

    void __fastcall TForm1::Edit1Change(TObject *Sender)

    {

      tblCountry->FindNearest(OPENARRAY(TVarRec, (Edit1->Text)));

    }

Run the program. When you type into the edit control, you will automatically begin incrementally searching through the table. For instance, if you type `C`, you will go to the record for Canada, if you type `Cu`, you will go to the record for Cuba.

The incremental search example is available on disk as the IncrementalSearch program. It's perhaps worth pointing out that this program is interesting in part because it shows how you can use the built-in features of BCB to easily implement additional features that were never planned by the developers. For instance, there is no Incremental Search component in BCB. However, if you need to build one, the tools come readily to hand.

### Filtering the Records in a Dataset with ApplyRange

The next two sections cover two different ways to filter data. Of these two techniques, the second is preferable on several counts; so if you are in a hurry, you can skip this section. The key difference between the two methods is that the one explained in this section uses keyed fields, while the next technique will work on any type of field. The technique used in the next section is very highly optimized, but the conservatives in the crowd might find the technique outlined in this section appealing because it relies on indexes, which are a tried and true database technology, guaranteed to execute in a highly optimized manner.

The `ApplyRange` function lets you set a filter that limits the range of the records you view. For instance, in the `Customers` database, the `CustNo` field ranges from just over 1,000 to a little under 10,000. If you wanted to see only those records that had a customer number between 2000 and 3000, you would use the `ApplyRange` method from `TTable` and two related routines. When using this method, you must work with a field that is indexed. (As explained in the next chapter, you can perform this same type of operation on a nonindexed field by using a `TQuery` object rather than `TTable` object. You can also use the technique explained in the next section of this chapter for searching on non-keyed fields.)

Here are the four methods of `TTable` that make up the suite of routines you will use when setting up filters:

    void __fastcall ApplyRange(void);

    void __fastcall SetRangeEnd(void);

    void __fastcall SetRangeStart(void);

    void __fastcall CancelRange(void);

To use these functions, perform the following steps:

  1. Call `SetRangeStart` and then use the `Fields` property to designate the start of the range.
  2. Call `SetRangeEnd` and use the `Fields` property a second time to designate the end of the range you are specifying.
  3. The first two actions prepare the filter; now all you need to do is call `ApplyRange`, and the new filter you have specified will take effect.
  4. If you want to undo the effects of a call to `ApplyRange` or `SetRange`, you can call the `CancelRange` function.

The Ranger program, which is located on the CD-ROM that comes with this book, shows you explicitly how to use these functions. To create the program, drop a `TTable`, `TDataSource`, and `TDBGrid` onto a form. Wire them up so that you can view the `CUSTOMERS` table from the demos subdirectory. You need to set `Table->Active` to `True`. Next, drop two labels on the form and set their captions to `Start Range` and `End Range`. Place two edit controls next to the labels. Finally, add a single button with the caption `ApplyRange`. When you are done, you should have a form like the one shown in Figure 9.7.
FIGURE 9.7. The Ranger program shows how to limit the number of records from a table that are visible at any one time.

The `SetRangeStart` and `SetRangeEnd` functions enable you to declare the first and last members in the range of records you want to see. To get started using the functions, first double-click the button labeled `SetRange`, and then create a function that looks like this:

    void __fastcall TForm1::bbSetRangeClick(TObject *Sender)

    {

      DMod->SetRange(StrToInt(eStart->Text), eEnd->Text.ToInt());

    }

This code shows two different ways to translate a string value into an integer value. The first technique calls a stand-alone VCL method named `StrToInt`, the second technique uses a method of the `AnsiString` class.

Once again, the `TForm1::bbSetRangeClick` function merely calls a method of `TDMod` that does all the real work:

    void TDMod::SetRange(int AStart, int AnEnd)

    {

      tblCustomer->SetRangeStart();

      tblCustomer->Fields[0]->AsInteger = AStart;

      tblCustomer->SetRangeEnd();

      tblCustomer->Fields[0]->AsInteger = AnEnd;

      tblCustomer->ApplyRange();

    }

The `TDMod::SetRange` function first calls `SetRangeStart`, which puts the table into range mode and blanks out the records seen in the `TDBGrid` control. Once in range mode, the program next expects you to specify the beginning range, which in this case you grabbed from the first edit control. Setting the end range for the program involves following a similar pattern. First you call `SetRangeEnd`, and then you pass it an appropriate value culled from the second `Edit` control back on the main form.

Note that you can use the `Fields` property to specify the actual range you want to use:

    Table->Fields[0]->AsInteger = AStart;

This use of the `Fields` property is obviously a special case, since the syntax shown here is usually used to set the value of a field, not to define a range. This special case comes into effect only after you have put `Table1` into range mode by calling `SetRangeStart`.

The final step in the function just shown is the call to `ApplyRange`. This is the routine that actually puts your request into effect. When the call to `ApplyRange` ends, the table is no longer in range mode, and the `Fields` property returns to its normal functionality.

If you want to undo the results of a call to `ApplyRange`, you can call the `CancelRange` function:

    Table->CancelRange();

A typical run of the program might involve the user typing in the number `4000` in the first edit control and the number `5000` in the next edit control. After entering the data, clicking the `ApplyRange` button would put the request into effect.

So far, you have learned how to filter the data from a table so that you view only a particular range of records. The steps involved are threefold:

  1. Call `SetRangeStart` and specify the beginning value in the range of records you want to see.
  2. Call `SetRangeEnd` and specify the ending value in the range of records you want to see.
  3. Call `ApplyRange` in order to view the results of your request.

BCB also provides a shorthand method calling the `SetRangeStart`, `SetRangeEnd`, and `ApplyRange` methods. This technique is featured in both the Ranger and Ranger2 programs. The conceit behind the Ranger2 program is to place a Windows 95 track bar control at the top of the form, and to enable the user to move the thumb on the track bar in order to select a range of values to view:

    void __fastcall TForm1::TBarChange(TObject *Sender)

    {

      int i = TBar->Position * 1000;

      int j = i + 1000;

      DMod->SetRange(i, j);

    }

`TBarChange` is called whenever the thumb on the track bar moves. The valid range of values on the track bar are 1 to 10. The code multiplies current position on the track bar times 1,000 to get the start of the range, and adds 1,000 to this value to get the end range. The theory here is that the track bar measures the range in increments of 1,000.

The `TBarChange` method calls the `SetRange` method of `TDMod`:

    void TDMod::SetRange(int AStart, int AFinish)

    {

      tblCustomer->SetRange(OPENARRAY(TVarRec, (AStart)), OPENARRAY(TVarRec, (AFinish)));

    }

This `TTable` `SetRange` method takes two `OpenArray` templates, the first to cover the starting range, the second to cover the ending range. The idea here is that the table might be indexed on multiple fields, and you can use an `OpenArray` to specify the values associated with more than one field, if needed.

### Filtering with the OnFilterRecord Event

The `OnFilterRecord` event enables you to set up filters on fields even if they are not keyed. You can use this event in two different ways.

The first technique involves setting the `TTable` `Filtered` property to `True`. When you do this, you will see only the records that are designated by the formula defined in the `OnFilterRecord` event. For instance, if you had a `State` field in your dataset and the `OnFilterRecord` event said to accept only records from New Hampshire, you would see only the records from New Hampshire when `Filtered` was set to `True`.

The second technique enables you to search for records even when `Filtered` is set to `False`. For instance, if you set up an `OnFilterRecord` event that accepted only records from New Hampshire, you could call `Table->FindFirst` to find the first of these records, and `Table->FindNext` to find the next one, and so on. There are also `FindPrior` and `FindLast` properties that you can use with the `OnFilterRecord` event. The key point to remember is that as long as the `OnFilterRecord` event is implemented correctly, you can use `FindFirst`, and so on, even when `Filtered` is not set to `True`.

An example of the `OnFilterRecord` event is shown in the Filter program found on this book's CD-ROM. The rest of this section describes how to create that program from scratch.

To see the `OnFilterRecord` event in a live program, start by dragging the `Country` table off the Database Explorer onto a blank form or data module from a new project. (It sometimes helps to close the Database Explorer after the drop operation, rather than trying to switch between the two tools by changing their focus.) Drop down a panel and set up the `Align` property for the panel and for the `TDBGrid` as explained in the previous examples from this chapter.

Place a `TCheckBox` object on the panel and set its caption to `Filtered`. Associate the following method with the `OnClick` event for the checkbox:

    void __fastcall TForm1::FFilterClick(TObject *Sender)

    {

      FCountry->Filtered = FFilter->Checked;

    }

This code ensures that the table will be filtered whenever the checkbox is checked.

Use the Fields Editor for the `Table1` object to create field objects for all the fields in the database. Drag the `Continent` field off the Fields Editor onto the form, as shown in Figure 9.8.

######

FIGURE 9.8. The main form for the Filter program includes a grid, a panel, a checkbox, a `TDBEdit` control, and a button.

Turn to the Events page for the `TTable` object, and associate the following code with the `OnFilterRecord` event:

    void __fastcall TForm1::FCountryFilterRecord(TDataSet *DataSet, Boolean &Accept)

    {

      Accept = tblCountry->FieldByName("Continent")->AsString == "South America";

    }

This code states that the `OnFilterRecord` event will accept all records where the `Continent` field of the `Country` table contains the word `"South America"`. The `Continent` field will have either the value `North America` or `South America` in it. If you click the checkbox to turn the filter on, you will see only the records from South America. In short, the filter will automatically accept only those records whose `Continent` field matches the value `"South America"`.

If you wanted, you could change the code so that it always filtered on the value currently in the `Continent` field:

    void __fastcall TForm1::FCountryFilterRecord(TDataSet *DataSet, Boolean &Accept)

    {

      Accept = tblCountry->FieldByName("Continent")->AsString == DBEdit1->Text;

    }

For this code to work, you must drop down a `DBEdit` control, and hook it up to the `Continent` field of the `Country` table.

It's important to note that the `Accept` field of the `OnFilterRecord` event is a Boolean value. This means that you can set up any kind of a Boolean statement in order to set the value of this field. For instance, in addition to the `=` operator, you could also use the following operators: `<>`, `>`, or `<``.

The `FindNext`, `FindFirst`, and `FindPrior` functions are extremely easy to use. For instance, if you wanted to find the next record in the database that satisfied the requirements specified in the `OnFilterRecord` event, you could write the following code to be fired in response to clicking a button:

    void __fastcall TForm1::FFindNextClick(TObject *Sender)

    {

      tblCountry->FindNext();

    }

The other functions work exactly the same way. This is a Boolean function that will return `False` if the search fails. Remember that these methods work even if the `Filtered` property of the `TTable` object is set to `False`.

### Using the Refresh Function

As you already know, any table that you open is always subject to change. In short, you should regard a table as a fluid, rather than as a static, entity. Even if you are the only person using a particular table and even if you are not working in a networked environment, there is always the possibility that the program you are running may have two different ways of changing a piece of data. As a result, you should always be aware of the need to update, or refresh, your current view of a table. Furthermore, BCB will not always update data after you perform an action in the background. As a result, you may need to update data by performing a refresh, particularly after certain kinds of delete operations.

The `Refresh` function is related to the `Open` function because it retrieves the data, or some portion of the data, associated with a given table. For instance, when you open a table, BCB retrieves data directly from a database file. Similarly, when you refresh a table, BCB goes out and retrieves data directly from a table. You can therefore use this function to update a table if you think it might have changed. It is faster, and much more efficient, to call `Refresh` than to call `Close` and then `Open`.

> NOTE: In a networked environment, refreshing a table can sometimes lead to unexpected results. For instance, if a user is viewing a record that has been deleted, it will seem to disappear out from under the user the moment the program calls `Refresh`. Similarly, if another user has edited data, a call to `Refresh` can result in data dynamically changing while a user is viewing it. Of course, it is unlikely that one user will change or delete a record while another is viewing it, but it is possible. As a result, you should use calls to `Refresh` with caution.

### Bookmarks

It is often useful to mark a particular location in a table so that you can quickly return to it when desired. BCB provides this functionality through three methods of `TDataSet` that use the metaphor of a bookmark. When you use these functions, it is as if you have left a bookmark in the dataset, and you can therefore turn back to it quickly whenever you want:

    void __fastcall FreeBookmark(System::Pointer Bookmark);

    System::Pointer __fastcall GetBookmark(void);

    void __fastcall GotoBookmark(System::Pointer Bookmark);

As you can see, the `GetBookmark` call returns a variable of type pointer, which is in fact just a pointer to a `Bookmark`. A `Bookmark` pointer contains enough information to enable BCB to find the location to which it refers. Therefore, you can simply pass this bookmark to the `GotoBookmark` function, and you will immediately be returned to the location with which the bookmark is associated.

It's important to note that a call to `GetBookmark` allocates memory for the bookmark, and so you must remember to call `FreeBookmark` before you exit your program and before every attempt to reuse a bookmark. For instance, here is a typical set of calls for freeing a bookmark, setting a bookmark, and moving to a bookmark:

    void __fastcall TForm1::bMarkClick(TObject *Sender)

    {

      if (Bookmark == NULL)

        Bookmark = DataMod->tblCountry->GetBookmark();

    }

    void __fastcall TForm1::bReturnClick(TObject *Sender)

    {

      if (Bookmark != NULL)

      {

        DataMod->tblCountry->GotoBookmark(Bookmark);

        DataMod->tblCountry->FreeBookmark(Bookmark);

        Bookmark = NULL;

      }

    }

    void __fastcall TForm1::FormDestroy(TObject *Sender)

    {

      DataMod->tblCountry->FreeBookmark(Bookmark);

    }

The code shown here is excerpted from a program called `Bookmark`, which comes with the CD-ROM that accompanies this book. In the declaration for `TForm1`, a variable called `Bookmark` is declared in the private section. Every time the `MarkClick` function is called, the first step is to be sure the `Bookmark` is freed. It is never a mistake to call `FreeBookmark`, because the function checks to make sure `Bookmark` is not set to `NULL`. After de-allocating any existing copies of the `Bookmark`, a new one is allocated. You can then call `GotoBookmark` and repeat the cycle.

Bookmarks are powerful features that can be of great benefit under certain circumstances. The developers of BCB, for instance, used bookmarks frequently in order to develop the database components. They often have several different bookmarks open at the same time.

> NOTE: Most of the features surfaced in `TDataSet` are built into the BDE. For instance, filters, searching for keys, and bookmarks are available to anyone who uses the BDE. What the developers of BCB have done is surface these features so that they can be easily accessed using object-oriented programming techniques. The calls from the BDE are available to people who purchase the Borland Database Engine from Borland or to patient BCB spelunkers who spend some time with `BDE.hpp`.

### More on `TDataSet` and `TTable`

`TTable` adds several frequently used properties to `TDataSet`:

    __property System::Boolean ReadOnly;

    // From TTable

    __property System::Boolean Exclusive;

    __property System::AnsiString MasterFields;

    __property DB::TDataSource *MasterSource;

    __property System::AnsiString TableName;

Of the properties shown here, the most common ones are probably `TableName` and `ReadOnly`. You can use the `TableName` property to specify the table you want to open, and you can set the `ReadOnly` property to `True` or `False` depending on whether you want to allow the user to change the data in a dataset. Neither of these properties can be used when a table is active.

The `Exclusive` property lets you open up a table in a mode that guarantees that no other user will be able to access it at the same time. You will not be able to set `Exclusive` to `True` if another user is currently accessing the table.

The `MasterSource` property is used to specify a `TDataSource` from which the current table needs to obtain information. For instance, if you linked two tables in a master/detail relationship, the detail table can track the events occurring in the first table by specifying the first table's data source in this property. This technique is demonstrated in the following section on linked cursors.

### Creating Linked Cursors

Linked cursors enable programmers to easily define a one-to-many relationship. For instance, it is sometimes useful to link the `CUSTOMER` and `ORDERS` tables so that each time the user views a particular customer's name, he or she can also see a list of the orders related to that customer. In short, the user can view one customer's record, and then see only the orders related to that customer.

To understand linked cursors, you first need to see that the `CUSTOMER` table and the `ORDERS` table are related to one another through the `CustNo` field. This relationship exists specifically because there needs to be a way to find out which orders are associated with which customer.

The Links program on the book's CD-ROM demonstrates how to create a program that uses linked cursors. To create the program on your own, place two tables, two data sources, and two grids on a form. Wire the first set of controls to the `CUSTOMER` table and the second set to the `ORDERS` table. If you run the program at this stage, you should be able to scroll through all the records in either table, as shown in Figure 9.9.

######

FIGURE 9.9. The Links program shows how to define a relationship between two tables.

The next step is to link the `ORDERS` table to the `CUSTOMER` table so that you view only those orders associated with the current customer record. To do this, you must take three steps, each of which requires some explanation:

  1. Set the `MasterSource` property of `Table2` to `DataSource1`.
  2. Set the `MasterField` property in `Table2` to `CustNo`.
  3. Set the `IndexName` property of `Table2` to `ByCustNo`.

If you now run the program, you will see that both tables are linked together, and that every time you move to a new record in the `CUSTOMER` table, you can see only those records in the `ORDERS` table that belong to that particular customer.

The `MasterSource` property in `Table2` specifies the `DataSource` from which `Table2` can draw information. Specifically, it allows the `ORDERS` table to know which record currently has the focus in the `CUSTOMER` table.

The question then becomes this: what other information does `Table2` need in order to properly filter the contents of the `ORDERS` table? The answer to this question is twofold:

  1. It needs the name of the field that links the two tables.
  2. It needs the index of the field in the `ORDERS` table that is going to be linked to the `CUSTOMER` table.

In order to correctly supply the information described here, you must first ensure that both the `CUSTOMER` table and the `ORDERS` table have the correct indexes. Specifically, you must ensure that there are indexes on both the `CustNo` field and the `CustNo` field in the `ORDERS` table. If the index in question is a primary index, there is no need to specifically name that index, and therefore you can leave the `IndexName` field blank in both tables. However, if either of the tables is linked to the other through a secondary index, you must explicitly designate that index in the `IndexName` field of the table that has a secondary index.

In the example shown here, the `CUSTOMER` table has a primary index on the `CustNo` field, so there is no need to specify the index name. However, the `ORDERS` table does not have a primary index on the `CustNo` field, and so you must explicitly declare it in the `IndexName` property by typing in or selecting the word `CustNo`.

> NOTE: To simplify the process described previously, the developers put in a dialog that appears when you click the `MasterFields` property. This dialog simplifies the process and helps to automate the task of setting up a link between two tables.
> In particular, to use the MasterFields dialog, start a new project and drag the `Customer` and `Orders` tables off the Explorer onto the main form. Arrange the grids with the `Customer` grid on top and the `Orders` grid beneath it. Set the `DataSource` property of the `Orders` `TTable` object to the `TDataSource` object associated with the `Customer` table.
> Pop up the MasterFields dialog and make sure the Available Indexes is set to Primary Index. Click the `CustNo` field in both Detail Fields and MasterFields list boxes. Click Add. The two fields will appear in the Joined Fields list box. At this stage, you are all done, so you can click the OK button.

Some indexes can contain multiple fields, so you must explicitly state the name of the field you want to use to link the two tables. In this case, you should enter the name `CustNo` in the `MasterFields` property of `Table2`. If you wanted to link two tables on more than one field, you should list all the fields, placing a pipe symbol between each one:

    Table->MasterFields := "CustNo | SaleData | ShipDate";

In this particular case, however, I'm guilty of a rather shady bit of expediency. In particular, the statement shown here makes no sense, because the `SaleData` and `ShipDate` fields are neither indexed nor duplicated in the `CUSTOMER` table. Therefore, you should only enter the field called `CustNo` in the `MasterFields` property. You can specify this syntax directly in a property editor, or else write code that performs the same chore.

It's important to note that this section covered only one of several ways you can create linked cursors using BCB. Chapter 10, "SQL and the `TQuery` Object," describes a second method that will appeal to people who are familiar with SQL. The Database Expert provides a third means of achieving this end. As you have seen, the Database Expert is an easy-to-use visual tool. The Query Builder is yet a fourth way of creating a one-to-many relationship between two tables. Like the Database Expert, the Query Builder is a visual tool that can save you much time. However, it's best not to rely entirely on visual tools, because there are times when you might feel limited by their functionality. In such cases, you will be glad if you understand the underlying technology. That way you can get the job done yourself without being forced to wrestle with the limitations of a particular tool.

### TDataSource Basics

Class `TDataSource` is used as a conduit between `TTable` or `TQuery` and the data-aware controls such as `TDBGrid`, `TDBEdit`, and `TDBComboBox`. Under most circumstances, the only thing you will do with a `TDataSource` object is to set its `DataSet` property to an appropriate `TTable` or `TQuery` object. Then, on the other end, you will also want to set a data-aware control's `DataSource` property to the `TDataSource` object you are currently using.

> NOTE: Visual tools such as `TDBEdit` or `TDBGrid` all have a `DataSource` property that connects to a `TDataSource` object. When reading this chapter, you need to distinguish between a visual control's `DataSource` property and the `TDataSource` object to which it is attached. In other words, the word `DataSource` can be a bit confusing. I try to refer to a `TDataSource` object as a data source, and to refer to the `DataSource` property as a `DataSource`. You should, however watch the context in which these words are used, and be aware of the rather subtle distinctions in meaning surrounding this issue.

A `TDataSource` also has an `Enabled` property, and this can be useful whenever you want to temporarily disconnect a table or query from its visual controls. This functionality might be desirable if you need to programmatically iterate through all the records in a table. For instance, if a `TTable` is connected to a data-aware control, each time you call `TTable.Next`, the visual control needs to be updated. If you are quickly going through two or three thousand records, it can take considerable time to perform the updates to the visual controls. In cases like this, the best thing to do is set the `TDataSource` object's `Enabled` field to `False`, which will enable you to iterate through the records without having to worry about screen updates. This single change can improve the speed of some routines by several thousand percent.

The `AutoEdit` property of `TDataSource` enables you to decide whether or not the data-aware controls attached to it will automatically enter edit mode when you start typing inside them. Many users prefer to keep `AutoEdit` set to `True`, but if you want to give a user more precise control over when the database can be edited, this is the property you need. In short, if you set `AutoEdit` to `False`, you have essentially made the table read-only.

#### Using TDataSource to Check the State of a Database

The events belonging to `TDataSource` can be extremely useful. To help illustrate them, you will find a program on the CD-ROM that comes with this book called Dsevents that responds to all three `TDataSource` events. This program shows an easy way to set up a "poor man's" data-aware edit control that automatically shows and posts data to and from a database at the appropriate time.

This example works with the `COUNTRY` database and it has a `TTable`, `TDataSource`, five edits, six labels, eight buttons, and a panel on it. The actual layout for the program is shown in Figure 9.10. Note that the sixth label appears on the panel located at the bottom of the main form.

######

FIGURE 9.10. The Dsevents program shows how to track the current state of a table.

`TDataSource` has three key events associated with it:

    OnDataChange

    OnStateChange

    OnUpdateData

A `TDataSource` `OnStateChange` event occurs whenever the current state of the dataset changes. A dataset always knows what state it's in. If you call `Edit`, `Append`, or `Insert`, the table knows that it is now in edit mode, and that fact is reflected in the value held in the `TDataSource` `State` property. Similarly, after you `Post` a record, the database knows that it is no longer editing data, and it switches back into browse mode. If you want more control, the next section in this chapter explains that a dataset also sends out messages just before and just after you change states.

The dataset has nine possible states, each of which are captured in the following enumerated type:

    enum TDataSetState { dsInactive, dsBrowse, dsEdit, dsInsert,

                         dsSetKey, dsCalcFields, dsUpdateNew,

                         dsUpdateOld, dsFilter };

During the course of a normal session, the database will frequently move back and forth between browse, edit, insert, or the other modes. If you want to track these changes, you can respond to them by writing code that looks like this:

    void __fastcall TForm1::dsCountryStateChange(TObject *Sender)

    {

      AnsiString S;

      switch (tblCountry->State)

      {

        case dsInactive:

          S = "Inactive";

          break;

        case dsBrowse:

          S = "Browse";

          break;

        case dsEdit:

          S = "Edit";

          break;

        case dsInsert:

          S = "Insert";

          break;

        case dsSetKey:

          S = "SetKey";

          break;

      }

      StatusBar1->Panels->Items[0]->Text = S;

    }

In this code, I am using a `TStatusBar` object to report the state of the dataset to the user. To use a `TStatusBar` object, first drop one on a form and then double-click on the editor for the `TStatusBar` `Panels` property to bring up the Status Bar Panel Editor. Create a new panel in the Status Bar Panel Editor and set its text to `Browse`, or to some other string. You now have one panel the text of which you can change with the following line of code:

    StatusBar1->Panels->Items[0]->Text = S;

It's time now to stop talking about the `OnStateChange` event and to move on to the `OnDataChange` event. `OnDataChange` occurs whenever you move on to a new record. In other words, if you call `Next`, `Previous`, `Insert`, or any other call that is likely to lead to a change in the data associated with the current record, an `OnDataChange` event will get fired. If someone begins editing the data in a data-aware control, an `OnResync` event occurs.

Dsevents has one small conceit that you need to understand if you want to learn how the program works. Because there are five separate edit controls on the main form, you need to have some way to refer to them quickly and easily. One simple method is to declare an array of edit controls:

    TEdit *Edits[5];

To fill out the array, you can respond to the forms `OnCreate` event:

    void __fastcall TForm1::FormCreate(TObject *Sender)

    {

      int i;

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

        Edits[i] = (TEdit*)FindComponent("Edit" + IntToStr(i + 1));

      tblCountry->First();

    }

The code shown here assumes that the first edit control you want to use is called `Edit1`, the second is called `Edit2`, and so on.

Given the existence of this array of controls, it is very simple to use the `OnDataChange` event to keep them in sync with the contents of the current record in a dataset:

    void __fastcall TForm1::dsCountryDataChange(TObject *Sender, TField *Field)

    {

      int i;

      if (FUpdating)

        return;

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

        Edits[i]->Text = tblCountry->Fields[i]->AsString;

    }

This code iterates through each of the fields of the current record and puts its contents in the appropriate edit control. Whenever `Table->Next` is called, or whenever any of the other navigational methods are called, the function shown previously gets a chance to strut onto the stage. Its primary raison d'etre is to ensure that the edit controls always contain the data from the current record.

Whenever `Post` gets called, you will want to perform the opposite action. That is, you will want to snag the information from the edit controls and tuck them away inside the current record. To execute this chore, simply respond to `TDataSource.OnUpdateData` events, which are generated automatically whenever `Post` is called:

    void __fastcall TForm1::dsCountryUpdateData(TObject *Sender)

    {

      int i;

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

        tblCountry->Fields[i]->Value = Edits[i]->Text;

    }

The Dsevents program switches into edit mode whenever you type anything in one of the edit controls. It manages this sleight of hand by responding to `OnKeyDown` events:

    void __fastcall TForm1::Edit1KeyDown(TObject *Sender, Word &Key,

          TShiftState Shift)

    {

      if (dsCountry->State != dsEdit)

        tblCountry->Edit();

    }

This code demonstrates how to use the `State` variable of a `TDataSource` object to find out the current mode of the dataset.

### Tracking the State of a Dataset

In the last section, you learned how to use `TDataSource` to keep tabs on the current state of a `TDataSet` and to respond just before certain events are about to take place.

Using a `TDataSource` object is the simplest way to perform all these functions. However, if you would like to track these events without using `TDataSource`, you can respond to the following rather intimidating list of events from `TDataSet`, all of which are naturally inherited by `TTable` or `TQuery`:

    __property TDataSetNotifyEvent BeforeOpen;

    __property TDataSetNotifyEvent AfterOpen;

    __property TDataSetNotifyEvent BeforeClose;

    __property TDataSetNotifyEvent AfterClose;

    __property TDataSetNotifyEvent BeforeInsert;

    __property TDataSetNotifyEvent AfterInsert;

    __property TDataSetNotifyEvent BeforeEdit;

    __property TDataSetNotifyEvent AfterEdit;

    __property TDataSetNotifyEvent BeforePost;

    __property TDataSetNotifyEvent AfterPost;

    __property TDataSetNotifyEvent BeforeCancel;

    __property TDataSetNotifyEvent AfterCancel;

    __property TDataSetNotifyEvent BeforeDelete;

    __property TDataSetNotifyEvent AfterDelete;

    __property TDataSetNotifyEvent OnNewRecord;

    __property TDataSetNotifyEvent OnCalcFields;

    __property TFilterRecordEvent OnFilterRecord;

    __property TOnServerYieldEvent OnServerYield;

    __property TUpdateErrorEvent OnUpdateError;

    __property TUpdateRecordEvent OnUpdateRecord;

    __property TDataSetErrorEvent OnEditError;

    __property TDataSetErrorEvent OnPostError;

    __property TDataSetErrorEvent OnDeleteError;

Most of these properties are self-explanatory. The `BeforePost` event, for instance, is functionally similar to the `TDataSource->OnUpdateData` event that is explained and demonstrated previously. In other words, the Dsevents program would work the same if you responded to `DataSource1->OnUpdateData` or to `Table->BeforePost`. Of course, in one case you would not need to have a `TDataSource` on the form, while the other requires it.

All of these events are associated with methods that have a particular signature. For instance, most of them are of type `TDataSetNotifyEvent`. A `TDataSetNotifyEvent` is declared like this:

    typedef void _C!astcall (__closure *TDataSetNotifyEvent)(TDataSet *DataSet);

Or, to make the matter more comprehensible, here is the code BCB generates for an `AfterClose` event:

    void __fastcall TForm1::tblCountryAfterClose(TDataSet *DataSet)

    {

    }

To work with these properties, you should see the EventOrd program found on the CD-ROM that accompanies this book, and shown below in Figure 9.11. This program is better experienced in action, rather than explained in words here on the page. The main point of the program is to notify you when any major event associated with a table occurs. If you play with the program for some time, you will begin to get a good feeling for the events associated with a table, and for the order in which they occur.

> NOTE: When I first wrote this program, I learned a number of things about VCL databases that had not been clear to me from prior experience. There is nothing like seeing the events being fired to start to get a sense of how the VCL really works! So I would strongly recommend spending some time with this program.

FIGURE 9.11. The EventOrd program tracks the events that occur in a `TTable`-based database application.

The EventOrd program responds to all the published events that can occur on a `TTable` object. Each method response event does nothing more than report the event by posting a string to list box. Here, for instance, is the response to the `NewRecord` event:

    void __fastcall TDataMod::tblCountryNewRecord(TDataSet *DataSet)

    {

      HandleDataEvent("New Record");

    }

The `HandleDataEvent` pops the string into the list box and makes sure that the most recently added items in the list box are always visible:

    void __fastcall TDataMod::HandleDataEvent(char * S)

    {

      Form1->ListBox1->Items->Add(S);

      Form1->ListBox1->ItemIndex = Form1->ListBox1->Items->Count - 1;

    }

The main form for the application has a simple method that will write the contents of the list box to disk:

    void __fastcall TForm1::WriteListBoxDataToDisk1Click(TObject *Sender)

    {

      if (OpenDialog1->Execute())

        ListBox1->Items->SaveToFile(OpenDialog1->FileName);

    }

Here, for instance, is a short run from the program:

    Before Open

    Filter Record

    dsCountry: State Change

    dsCountry: Data Change

    After Open

    Filter Record

    dsCountry: Data Change

    Navigator click: Next

    Filter Record

    dsCountry: Data Change

    Navigator click: Next

    Before Insert

    dsCountry: State Change

    New Record

    dsCountry: Data Change

    After Insert

    Navigator click: Insert

    Before Cancel

    dsCountry: State Change

    Filter Record

    dsCountry: Data Change

    After Cancel

    Navigator click: Cancel

The preceding list records what happens when

- A table is opened

- The `TDBNavigator` Next button is pressed twice

- The `TDBNavigator` Insert button is pressed

- The `TDBNavigator` Cancel button is pressed

Unfortunately, the `TDBNavigator` `OnClick` method occurs after the changes are recorded in the dataset. For instance, the last six events in the record look like this:

    Before Cancel

    dsCountry: State Change

    Filter Record

    dsCountry: Data Change

    After Cancel

    Navigator click: Cancel

They actually occurred in the following order:

    Navigator click: Cancel

    Before Cancel

    dsCountry: State Change

    Filter Record

    dsCountry: Data Change

    After Cancel

The point is that I clicked the navigator Cancel button, and then the other events occurred, in the order shown. However, BCB gives priority to the database events, and then reports the `OnClick` event.

If you do a lot of database programming, you really need to know the order in which certain events occur. Outlining all that information in these pages is not likely to be particularly helpful to most readers. To learn about this information, it's best just to watch the events occur and to trace their history. So fire up the OrdEvents program and see what you can learn from it.

### Working with TDatabase

While you are learning the basics of connecting to a table, you should also look at the `TDatabase` object, which exists primarily to give you a means of staying connected to a database even if you are continually opening and closing a series of tables. If you use `TDatabase`, you can be connected to Oracle, InterBase, or other servers without ever opening any tables. You can then begin opening and closing tables over and over without having to incur the overhead of connecting to the database each time you call `Open`.

The `TDatabase` object also enables you to start server-based applications without specifying a password, and it gives you access to transactions. Besides the information in this chapter, more information on `TDataBase` appears in the discussion of the Transacts program, which is covered at the end of Chapter 15, "Working with the Local InterBase Server."

To use the `TDatabase` object, first drop it on to a form or data module. Set the `AliasName` property to `DBDEMOS`. Create your own `DatabaseName`, such as `MyDBDemos`. Set `Connected` to `True`. Drop down a `TTable` object and set its `DatabaseName` property to `MyDBDemos`. The issue here is that you created a "new database" when you dropped the `TDatabase` object on the form and filled in its fields as shown previously. Therefore you can select the name of this database from the drop-down list for the `TTable` `DatabaseName` property.

After connecting the table to the `TDatabase` object, you can work with the `TTable` object exactly as you would under normal circumstances. For instance, you could connect it to the `BioLife` table, as shown in the DatabaseObject program found on the CD-ROM that accompanies this book.

If you double-click a `TDatabase` object, you bring up the component editor for this object, shown in Figure 9.12. Inside the component editor you can discover the default values for many of the database parameters passed in the BDE alias for this database.
FIGURE 9.12. The component editor for the `TDataBase` object displaying information about a connection to an InterBase table.

In this section your learned about the `TDatabase` object, which can be used to optimize code that is continually connecting and disconnecting from tables that belong to one database. Throughout the rest of this chapter and the next, the `TDatabase` object will play only a minor role. However, these components come into their own when you are connecting to SQL data as described in Chapter 15.

### Connecting to a Database without Visual Tools

Everything that you do in BCB with the visual tools can also be done in code. The visual tools are just an easy way to write code; they do not do anything special that you can't also do in code. In fact, the code came first, and then the visual tools were added to make it easier for you to create programs. The key to BCB is the way its language conforms to the metaphors of visual programming without causing you any undo overhead.

> NOTE: It would not be true, of course, to say that BCB has no overhead as a result of its concessions to the world of visual programming. For instance, all objects that reside on the Component Palette inherit at least some code that makes them able to be manipulated visually. In other words, they descend not directly from `TObject`, but from `TComponent`.
> The amount of this code is small compared to what you see in a product like PowerBuilder. In particular, it is nearly identical in size and kind to the code associated with an OWL or MFC object. In short, BCB components are really just normal objects with a few extra methods. As a result, they are much smaller than most ActiveX controls or than controls you see in other products. Furthermore, the principles of inheritance allow most of the code to be reused over and over by multiple objects. In other words, you get the hit the first time you bring up a form, but other forms, or subsequent objects dropped on a form, just reuse the same objects used by the `TForm` object.
> To help drive this point home, I should perhaps point out that a typical small BCB application with five or six components on it is about 200KB in size. Many of the ActiveX controls that I have used are at least that large. In other words, it would not be unusual to find that five ActiveX controls added over a megabyte to the size of your application's install image. BCB controls, on the other hand, frequently added no more than five or ten KB to the size of your executable.
> An exception is the presence of the first database control you drop on a form, which usually adds about 200KB to your executable. You should note, however, that because of inheritance, subsequent database objects do not increase the size of your application by nearly so large a percentage. Big database applications with four or five forms and thirty or forty database controls are frequently some 500-700KB in size. That's big by the old standards of DOS, but not large by today's standards.

If you want to write code that is completely independent of the visual tools, you have to add some `include` directives to the header file for your form. For instance, to use a `TTable` object, you should add the following code to your header file:

    #include <vcl\DBTables.hpp>

    #include <vcl\DB.hpp>

Here is an example of how to connect to a `TTable` object without using any visual tools:

    void __fastcall TForm1::Button1Click(TObject *Sender)

    {

      TTable *MyTable = new TTable(this);

      MyTable->DatabaseName = "DBDEMOS";

      MyTable->TableName = "COUNTRY";

      MyTable->Open();

      ShowMessage(MyTable->Fields[0]->AsString);

      MyTable->Free();

    }

Of course, this code would not work if I did not first drop down a `TButton` object and create an `OnClick` event for it with the visual tools. But I am not using any visual tools when working with the database.

In this example, the first line of code creates an instance of a `TTable` object. This is the equivalent in code of dropping a `TTable` object onto a form from the Component Palette. Notice that you pass in `this` to the object's constructor in order to give the object an owner. In particular, `Form1` is being assigned as the owner of the object, and is therefore responsible for freeing the object at shutdown time. If you passed in `NULL` in this parameter, you would have to call `Free` or `Delete` in the last line of code. I chose to do this anyway, in large part because I am declaring `TTable` as a local variable that lives on the stack created for this one function. It therefore makes sense to destroy the object at the end of the function, though the VCL would in fact still clean up the memory for me when the form is destroyed. However, if I declared `MyTable` as a field of `Form1`, it would make sense just to let `Form1` call `Free` on the object at program shutdown, which is what I do in the other examples included in this section.

The next three lines of code do what you would normally do with the Object Inspector. That is, you first fill in the `DatabaseName` and `TableName` properties, and then you call `Open`, which is the equivalent of setting `Active` to `True`.

After opening the table, you are free to access its code in any way you want. You could, for instance, iterate through the records in a table, as explained later in this chapter, or you could even connect the table to a data source, and the data source to a `TDBGrid` or `TDBEdit` control.

An example of this type of program is available on disk as a program called `TABLESWITHOUTVISUALTOOLS.MAK`, found in the `NOVISUALTOOLS` directory. The code for the program is shown in Listings 9.1 and 9.2.
Listing 9.1. The main source file for the TablesWithoutVisualTools program.

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

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "Main.h"

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

    #pragma resource "*.dfm"

    TForm1 *Form1;

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

    __fastcall TForm1::TForm1(TComponent* Owner)

      : TForm(Owner)

    {

    }

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

    void __fastcall TForm1::Button1Click(TObject *Sender)

    {

      TTable *MyTable = new TTable(this);

      MyTable->DatabaseName = "DBDEMOS";

      MyTable->TableName = "COUNTRY";

      MyTable->Open();

      ShowMessage(MyTable->Fields[0]->AsString);

      MyTable->Free();

    }

Listing 9.2. The header file for the TablesWithoutVisualTools program.

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

    #ifndef MainH

    #define MainH

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

    #include <vcl\Classes.hpp>

    #include <vcl\Controls.hpp>

    #include <vcl\StdCtrls.hpp>

    #include <vcl\Forms.hpp>

    #include <vcl\DBTables.hpp>

    #include <vcl\DB.hpp>

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

    class TForm1 : public TForm

    {

    __published:    // IDE-managed Components

      TButton *Button1;

      void __fastcall Button1Click(TObject *Sender);

    private:        // User declarations

    public:         // User declarations

      virtual __fastcall TForm1(TComponent* Owner);

    };

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

    extern TForm1 *Form1;

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

    #endif

If you also wanted to connect to `TDatabase` object without using any visual tools, you can do so as follows:

    void __fastcall TForm1::bOpenTableClick(TObject *Sender)

    {

      FMyDatabase = new TDatabase(this);

      FMyDatabase->AliasName = "DBDEMOS";

      FMyDatabase->DatabaseName = "MyDBDemos";

      FMyDatabase->Connected = True;

      FMyTable = new TTable(this);

      FMyTable->DatabaseName = FMyDatabase->DatabaseName;

      FMyTable->TableName = "COUNTRY";

      FMyTable->Open();

      DataSource1->DataSet = FMyTable;

    }

For this code to work, you need to do several things. In particular, you would need to add `DB.HPP` and `DBTABLES.HPP` to your header file for your main form, and you would need to edit the declaration for your `TForm1` class so that it looked like this:

    class TForm1 : public TForm

    {

    __published:

      TButton *bOpenTable;

      TDBGrid *DBGrid1;

      TDataSource *DataSource1;

      void __fastcall bOpenTableClick(TObject *Sender);

    private:                              // User declarations

      TDatabase *FMyDatabase;             // Add field for database

      TTable *FMyTable;                   // Add field for table

    public:                               // User declarations

        virtual __fastcall TForm1(TComponent* Owner);

    };

Notice the addition of the `TDatabase` and `TTable` data items. These are added so that the objects are available for use in the `bOpenTableClick` function.

I have also added `TDBGrid` and `TDataSource` to the project and hooked up my database to them. I do this in order to show that there is absolutely no difference between creating a `TTable` and `TDatabase` object in code and doing it with the visual tools. As you can see, you can hook up the visual tools to the code based objects with just a single line of code:

    DataSource1->DataSet = FMyTable;

In particular, this line of code defines exactly what happens logically when you connect a data source to a table using the visual tools.

In this next example, absolutely everything is done from scratch. No visual database objects are used, and even the `Alias` is created on the fly. All the visual database controls are created on the fly, assigned locations on the form, attached to the database, and made visible:

    void __fastcall TForm1::bCreateClick(TObject *Sender)

    {

      FMyDatabase = new TDatabase(this);

      FMyDatabase->DatabaseName = "AnyName";

      FMyDatabase->DriverName = "STANDARD";

      FMyDatabase->Params->Add("path=g:\\cs\\examples\\data");

      FMyDatabase->Connected = True;

      FMyTable = new TTable(this);

      FMyTable->DatabaseName = FMyDatabase->DatabaseName;

      FMyTable->TableName = "BioLife";

      FMyTable->Open();

      FMyDataSource = new TDataSource(this);

      FMyDataSource->DataSet = FMyTable;

      FMyEdit = new TDBEdit(this);

      FMyEdit->DataSource = FMyDataSource;

      FMyEdit->Parent = this;

      FMyEdit->Visible = True;

      FMyEdit->DataField = "Common_Name";

      FMyImage = new TDBImage(this);

      FMyImage->Parent = this;

      FMyImage->BoundsRect = Bounds(0, 100, 300, 150);

      FMyImage->DataSource = FMyDataSource;

      FMyImage->DataField = "Graphic";

      FMyImage->Stretch = True;

      FMyImage->Visible = True;

      FMyNavigator = new TDBNavigator(this);

      FMyNavigator->Parent = this;

      FMyNavigator->BoundsRect = Bounds(0, 50, 100, 25);

      FMyNavigator->VisibleButtons =

        TButtonSet() << nbFirst << nbLast << nbNext << nbPrior;

      FMyNavigator->DataSource = FMyDataSource;

      FMyNavigator->Visible = True;

    }

Needless to say, this code would not work unless the database objects were declared in the class declaration for `TForm` or in some other legal location:

    class TForm1 : public TForm

    {

    __published:    // IDE-managed Components

        TButton *bCreate;

        void __fastcall bCreateClick(TObject *Sender);

    private:        // User declarations

      TDatabase *FMyDatabase;

      TTable *FMyTable;

      TDataSource *FMyDataSource;

      TDBEdit *FMyEdit;

      TDBImage *FMyImage;

      TDBNavigator *FMyNavigator;

    public:         // User declarations

        virtual __fastcall TForm1(TComponent* Owner);

    };

In this case all variables declared in the private section are database objects used in the `bCreateClick` method shown previously.

The key lines to focus on are the ones that define the `Alias` for the `TDatabase` object:

    FMyDatabase->DatabaseName = "AnyName";

    FMyDatabase->DriverName = "STANDARD";

    FMyDatabase->Params->Add("path=g:\\bcb\\examples\\data");

The first thing to notice about this code is that it hard-codes the path to my version of BCB into the application. You will, of course, probably have to edit this path before running the application.

You can see that the database object is given a `DatabaseName`, but no `AliasName` is declared. Instead, a `DriverName` is specified. This is an either/or proposition. You declare the `AliasName`, in which case the driver is specified inside the `Alias`, or else you explicitly declare the `DriverName`, which means that no externally defined `Alias` will be used.

Selecting the `STANDARD` driver is another way of saying you want to use a Paradox table. If you resort to the visual tools, you can see that this is a drop-down list of items, so you can see what other drivers are available on your system. As a rule, you will have available `STANDARD` drivers, dBASE drivers, and a list of SQL Links drivers such as Oracle, Sybase, and so on.

If no externally defined `Alias` will be used, how does the `TDatabase` object perform its function? How does it know which database you want to reference? How does it know the details about the database such as server name, user name, and database name? The solution to this quandary is supplied by the `Params` property, which can be used to define all the data that normally appears in an `Alias`. (This is the same property you explored earlier in this chapter when you double-clicked a `TDataBase` object to bring up the `TDatabase` component editor.)

In the case of Paradox tables, the only information that you need to fill concerns the path to the database. However, there are additional fields that will need to be covered if you want to connect to a client/server database. The `Params` property is of type `TStrings`, which means you can treat it more or less as you would any `TStringList` object, or as you would the `Items` property for a `TListBox`.

Notice that when you create visual database controls, you need to specify both the owner and the parent of the control:

    FMyEdit = new TDBEdit(this);

    FMyEdit->Parent = this;

In this case, both the owner and the parent are the main form. The `Owner` is responsible for deallocating the memory associated with the object, and the `Parent` is a field used by Windows so it will know where to draw the control.

If you are working in code rather than using the visual tools, you still have complete control over the object. For instance, I can define how many buttons will be visible on a `TDBNavigator` control:

    FMyNavigator->VisibleButtons =

        TButtonSet() << nbFirst << nbLast << nbNext << nbPrior;

In this case, the `VisibleButtons` property is of type `TButtonSet`. In C++, sets are emulated through objects. If you want to add a member to the set, you use the overloaded `<<` operator. In this case I am turning on the First, Last, Next, and Prior buttons, and leaving the other buttons uninitialized.

Before closing this section, I should make one final point. In all these examples, I am assuming that a `TSession` object has been created. This will indeed happen automatically, so long as you have a `TApplication` object initialized in your application. The `DatabaseAlias` application and all standard BCB applications initialize the `TApplication` object in `WinMain`:

    WINAPI WinMain(HINSTANCE, HINSTANCE, LPSTR, int)

    {

        Application->Initialize();

        Application->CreateForm(__classid(TForm1), &Form1);

        Application->Run();

        return 0;

    }

If you don't have this kind of code in your application (for instance, if you have created a console application), you will have to explicitly create the `TSession` object yourself. Code for doing this is shown later in the book in the chapter on ISAPI programming, "Extending an Internet Server with ISAPI."

In this section you have learned something about creating database objects in code rather than with the visual tools. One drawback to this section is that it makes BCB database programming appear fairly difficult. The other side of the coin, of course, is that you don't have to manipulate the database tools this way. In fact, you will usually use the speedy visual tools to do in a few seconds what is done previously in code. It is, however, important to know that you can do all forms of database programming in code if you so desire.

### Summary

In this chapter, you have learned how to use the `TDataSet`, `TField`, `TDBDataSet`, `TTable`, `TDatabase`, and `TDataSource` classes. This material is very much at the heart of the BCB database machinery, so you should be sure you understand how it works.

The key points to remember are as follows:

- `TDataSet` encapsulates the basic actions you will perform on a table.

- `TField` is a property of `TDataSet` that enables you to access the contents or name of each field in a record.

- `TDBDataSet` gives you the capability to associate a dataset with a given table.

- `TTable` encapsulates all the functionality of a dataset, but it also gives you access to table-specific chores such as setting indexes or creating linked cursors.

- `TDataSource` forms a link between `TTable` or `TQuery` and any of the data-aware components such as `TDBEdit` or `TDBGrid`. `TDataSource` also contains three useful events that keep you informed about the current state of the database.

In the next chapter, you will learn about the `TQuery` object and SQL. SQL is especially useful when you want to access the advanced capabilities associated with servers and server data.

©Copyright, Macmillan Computer Publishing. All rights reserved.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.