Today you begin to learn about database programming in Delphi. If you are new to database programming, at first glance it might appear overwhelming. Today I'll try to eliminate any confusion by presenting a clear picture of the labyrinth known as database programming. First, I'll give you an overview of the Delphi database architecture. After that I'll go over some of the data access components.
Make no mistake: Database programming is complicated. I'll give you a high-level view of database programming in Delphi, but I won't attempt to cover every detail.
NOTE: Not all the concepts and components discussed in this chapter pertain to every version of Delphi. The Professional version of Delphi has more database capabilities than the Standard version. The Client/Server version of Delphi has many more database capabilities than either the Standard or Professional version.
Database programming comes with a whole gaggle of buzzwords: BDE, client, server, ODBC, alias, SQL, query, stored procedure, and so on. The good news is that it isn't all that bad after you learn some basics. First, let's take a moment to talk about databases. When you hear the word database, you probably imagine data stored in table format. The table probably contains fields such as FirstName, LastName, and PhoneNumber. These fields are filled with data to create individual records in a database file.
If that's what you envision when you think of a database, you're not too far off, but you aren't exactly correct, either. The term database is used to describe an all-encompassing data creation and maintenance system. It is true that a database can be as simple as one table. On the other hand, a real-world database can include dozens or even hundreds of tables with thousands or millions of records. These tables can contain one or more indexes. A complete client/server SQL database solution can also contain numerous queries and stored procedures. (Don't worry; I'll explain some of these terms later in the chapter.) So as you can see, a database is more than just a table with data.
Speaking of tables, let's quickly cover some table basics. A table consists of at least two parts: fields and records. Fields are the individual categories of data in a table. For example, a table containing an address book would have a field called FirstName, a field called LastName, one called Address, PhoneNumber, and so on. Fields are also referred to as columns. A record, then, is one person's complete address: first name, last name, address, and so on. Records are also called rows.
A database is just a collection of data, of course, but database tables are often displayed in spreadsheet format. The column headers across the top indicate the field names. Each row in the table contains a complete record. Figure 16.1 shows just such a database table displayed in grid (or table) format.
FIGURE 16.1. A typical database table.
New Term: The pointer to the current record within a database is called the cursor.
The cursor points to the record that will be read if data is requested and the record that will be updated if any edits are made. The cursor is moved when a user browses the database, inserts records, deletes records, and so on.
NOTE: When I say the cursor is a pointer, I don't mean it's a pointer in the Object Pascal sense. I merely mean it is an indicator of the current record's position.
New Term: A collection of data returned by a database is called a dataset.
A dataset can be more than just the data contained in a table. A dataset can be the results of a query containing data acquired from many tables. For example, let's say you have a database containing names and addresses of your customers, their orders, and the details of each order. This data might be contained in tables named Clients, Orders, and Order Details. Now let's say you request the details of the last 10 orders placed by Company X. You might receive a dataset containing information from the Clients table, the Orders table, and the Order Details table. Although the data comes from several different sources, it is presented to you as a single dataset.
The simplest type of database is the local database. A local database is a database that resides on a single machine. Imagine that you have a program that needs to store a list of names and addresses. You could create a local database to store the data. This database would probably consist of a single table. The table is accessed only by your program; no one else has access to it. Any edits made to the database are written directly to the database. Paradox, dBASE, and Access databases are usually local databases.
Another way a database can be implemented is as a client/server database. The database itself is stored and maintained on a file server (the server part of the equation). One or more users (the clients) have access to the database. The users of this type of database are likely to be spread across a network. Because the users are oblivious to one another, more than one might attempt to access the database at the same time. This isn't a problem with client/server databases because the server knows how to handle all the problems of simultaneous database access.
The users of a client/server database almost never work with the database directly. Instead, they access the database through applications on their local computer. These applications, called client applications, ensure that the users are following the rules and not doing things to the database that they shouldn't be. It's up to the client application to prevent the user from doing something that would damage the database.
DATABASE SERVERSAs long as I am talking about client/server databases, let's take a moment to talk about database servers. Database servers come in several flavors. Some of the most popular include offerings from InterBase (a Borland-owned company), Oracle, Sybase, Informix, and Microsoft. When a company purchases one of these database servers, it also purchases a license that enables a maximum number of users to access the database server. These licensed users are often referred to as seats. Let's say a company buys InterBase and purchases licenses for 50 seats. If that company grows to the point that 75 users require access to the database, that company will have to buy an additional 25 seats to be in compliance with the license. Another way that client/server databases are sold is on a per connection basis. A company can buy a license for 50 simultaneous connections. That company can have 1,000 users of the database, but only 50 can be connected to the database at any one time. The database server market is big business, no question about it.
Local databases are often called single-tier databases. A single-tier database is a database in which any changes--such as editing the data, inserting records, or deleting records--happen immediately. The program has a more direct connection to the database.
In a two-tier database, the client application talks to the database server through database drivers. The database server takes the responsibility for managing connections, and the client application is largely responsible for ensuring that the correct information is being written to the database. A fair amount of burden is put on the client application to make sure the database's integrity is maintained.
In a multitier client/server architecture, the client application talks to one or more application servers that, in turn, talk to the database server. These middle-level programs are called application servers because they service the needs of the client applications. One application server might act as a data broker, responding to and handling data requests from the client and passing them on to the database. Another application server might only handle security issues.
Client applications run on local machines; the application server is typically on a server, and the database itself might be on another server. The idea behind the multitier architecture is that client applications can be very small because the application servers do most of the work. This enables you to write what are called thin-client applications.
Another reason to use a multitier architecture is management of programming resources. The client applications can be written by less experienced programmers because the client applications interact with the application server that controls access to the database itself. The application server can be written by more experienced programmers who know the rules by which the database must operate. Put another way, the application server is written by programmers whose job is to protect the data from possible corruption by errant client applications.
Although there are always exceptions, most local databases make use of the single-tier architecture. Client/server databases use either a two-tier or a multitier architecture.
So how does this affect you? Most applications you write with Delphi for use with a client/server database will be client applications. Although you might be one of the few programmers given the task of writing server-side or middle-tier applications, it's a good bet that you will write primarily client applications. As an application developer, you can't talk directly to these database servers. Let's look next at how a Delphi application talks to a database.
To enable access to local databases and to client/server databases, Delphi provides the Borland Database Engine (BDE). The BDE is a collection of DLLs and utilities that enables access to a variety of databases.
To talk to client/server databases, you must have the Client/Server version of Delphi. This version ships with SQL Links drivers used by the BDE to talk to client/server databases. Figure 16.2 shows the relationship between your application, the BDE, and the database.
FIGURE 16.2. Your application,
the BDE, and the database.
Naturally, database formats and APIs vary widely. For this reason the BDE comes with a set of drivers that enables your application to talk to several different types of databases. These drivers translate high-level database commands (such as open or post) into commands specific to a particular database type. This permits your application to connect to a database without needing to know the specifics of how that database works.
The drivers that are on your system depend on the version of Delphi you own. All versions of Delphi come with a driver to enable you to connect to Paradox and dBASE databases. This driver, called STANDARD, provides everything you need to work with these local databases.
The Client/Server version of Delphi includes drivers to connect to databases by Sybase, Oracle, Informix, InterBase, and others.
The BDE uses an alias to access a particular database. This is one of those terms that might confuse you at first. The terms alias and database are often used interchangeably when talking about the BDE.
New Term: A BDE alias is a set of parameters that describes a database connection.
When it comes right down to it, there isn't much to an alias. In its simplest form, an alias tells the BDE which type of driver to use and the location of the database files on disk. This is the case with aliases you will set up for a local database. In other cases, such as aliases for client/server databases, the alias contains other information as well, such as the maximum size of BLOB data, the maximum number of rows, the open mode, or the user's username. After you create an alias for your database, you can use that alias to select the database in your Delphi programs. Later today, in the section "Creating a BDE Alias," I'll tell you how to go about creating a BDE alias for your own databases.
As long as I am on the subject of aliases, let's take a quick look at the aliases already set up on your system. To view existing aliases, perform these steps:
After performing these steps, you'll see a list of available databases. At least one of these should be the DBDEMOS alias. This database alias is set up when Delphi is installed. Select the DBDEMOS database from the list.
NOTE: The list of databases you see depends on several factors. First, it depends on whether you have the Standard, Professional, or Client/Server version of Delphi. It also depends on whether you elected to install Local InterBase. Finally, if you happen to have C++Builder or another Borland product installed (such as Visual dBASE or IntraBuilder), you might see additional databases.
As long as you are here, move to the TableName property and take a look at the available tables. The tables you see are those available for this database (this alias). Select another alias for the DatabaseName property. Now look at the table names again. You will see a different list of tables.
The Client/Server version of Delphi comes with SQL Links in addition to the BDE. SQL Links is a collection of additional drivers for the BDE. These drivers enable Delphi applications to connect to client/server databases such as those provided by Oracle, InterBase, Informix, Sybase, and Microsoft. Details regarding deployment of SQL Links drivers are also available in DEPLOY.TXT.
LOCAL INTERBASEThe Standard and Professional versions of Delphi come with a single-user copy of Local InterBase. Local InterBase is just what its name implies: a version of InterBase that operates on local databases. The Client/Server version of InterBase, on the other hand, is a full-featured client/server database. The main reason that Delphi ships with Local InterBase is so that you can write an application that operates on local databases and then later change to a client/server database with no programming changes. This gives you an opportunity to hone your client/server programming skills without spending the money for a client/server database.
If you attempt to access a Local InterBase table at either design time or runtime, you will be prompted for a username and password. The Local InterBase administrator is set up with a username of SYSDBA and a password of masterkey. You can use these for login, or you can go to the InterBase Server Manager utility and add yourself as a new user to the InterBase system.
Okay, so the preceding section isn't exactly the type of reading that keeps you up all night turning pages. Still, it's important to understand how all the database pieces fit together. With that background, you can now turn your attention to the database components provided by VCL and how those components work together to create a database application. First, I'll give you a quick overview of the VCL database components, and then you'll look at individual classes and components in more detail.
The VCL database components fall into two categories: nonvisual data access components and visual data-aware components. Simply put, the nonvisual data access components provide the mechanism that enables you to get at the data, and the visual data-aware components enable you to view and edit the data. The data access components are derived from the TDataSet class and include TTable, TQuery, and TStoredProc. The visual data-aware components include TDBEdit, TDBListBox, TDBGrid, TDBNavigator, and more. These components work much like the standard edit, list box, and grid components except that they are tied to a particular table or field in a table. By editing one of the data-aware components, you are actually editing the underlying database as well.
NOTE: All the VCL database components can be termed data components. I use the term data access components for the nonvisual database components on the Data Access tab of the Component palette and the term data-aware components for the visual database components from the Data Controls tab.
Interestingly, these two component groups cannot talk directly to each other. Instead, the TDataSource component acts as an intermediary between the TDataSet components and the visual data-aware components. This relationship is illustrated in Figure 16.3.
FIGURE 16.3. The architecture of the VCL database components.
You'll look at these components in more detail, but first I'll walk you through a quick exercise to illustrate the relationship described in this section. Start Delphi or create a new application if Delphi is already running. Now do the following:
That was easy, but you're not done yet. Notice, by the way, that you can use the scrollbars on the grid even at design time. Okay, just a couple more steps:
Now run the program. Click any of the DBNavigator buttons. When you click on the Next Record button, the record pointer changes in the DBTable and the picture changes in the DBImage component. All that without writing a line of code!
The data access components are used to connect to a database and to a particular table in a database. The Table component is used to access a database table. This is the simplest way of accessing the data in a table.
The Query component is a way of accessing a database table using Structured Query Language (SQL) statements. SQL is a more powerful way of accessing tables, but it is also more complex. You will use either a Table or Query component to access a database, but not both. Another component is the StoredProc component that enables you access to a database via stored procedures. A stored procedure is a collection of database statements that performs one or more actions on a database. Stored procedures are usually used for a series of database commands that is repeated often.
TDataSet is the ancestor class for TTable, TQuery, and TStoredProc. As such, most properties, methods, and events that these classes use are actually defined by TDataSet. Because so many characteristics of the derived classes come from TDataSet, I'll list the primary properties, methods, and events of TDataSet here, and later I'll list the properties, methods, and events particular to each derived class.
Table 16.1 lists the most commonly used properties of the TDataSet class, Table 16.2 lists the primary methods, and Table 16.3 lists the primary events.
| Property | Description |
| Active | Opens the dataset when set to True and closes it when set to False. |
| AutoCalcFields | Determines when calculated fields are calculated. |
| Bof | Returns True if the cursor is on the first record in the dataset and False if it isn't. |
| CachedUpdates | When True, updates are held in a cache on the client machine until an entire transaction is complete. When False, all changes to the database are made on a record-by-record basis. |
| CanModify | Determines whether the user can edit the data in the dataset. |
| DataSource | The DataSource component associated with this dataset. |
| DatabaseName | The name of the database that is currently being used. |
| Eof | Returns True if the cursor is at the end of the file and False if it isn't. |
| FieldCount | The number of fields in the dataset. Because a dataset might be dynamic (the results of a query, for example), the number of fields can vary from one dataset request to the next. |
| Fields | An array of TFields objects that contains information about the fields in the dataset. |
| FieldValues | Returns the value of the specified field for the current record. The value is represented as a Variant. |
| Filter | An expression that determines which records a dataset contains. |
| Filtered | When True, the dataset is filtered based on either the Filter property or the OnFilterRecord event. When False, the entire dataset is returned. |
| FilterOptions | Determines how filters are applied. |
| Found | Indicates whether a find operation is successful. |
| Handle | A BDE cursor handle to the dataset. This is used only when making direct calls to the BDE. |
| Modified | Indicates whether the current record has been modified. |
| RecNo | The current record number in the dataset. |
| RecordCount | Returns the number of records in the dataset. |
| State | Returns the current state of the dataset (dsEdit, dsBrowse, dsInsert, and so on). |
| UpdateObject | Specifies the TUpdateObject component to use for cached updates. |
| UpdatesPending | When True, the cached update buffer contains edits not yet applied to the dataset. |
| Method | Description |
| Append | Creates an empty record and adds it to the end of the dataset. |
| AppendRecord | Appends a record to the end of the dataset with the given field data and posts the edit. |
| ApplyUpdates | Instructs the database to apply any pending cached updates. Updates are not actually written until the CommitUpdates method is called. |
| Cancel | Cancels any edits to the current record if the edits have not yet been posted. |
| CancelUpdates | Cancels any pending cached updates. |
| ClearFields | Clears the contents of all fields in the current record. |
| CommitUpdates | Instructs the database to apply updates and clear the cached updates buffer. |
| Close | Closes the dataset. |
| Delete | Deletes the current record. |
| DisableControls | Disables input for all data controls associated with the dataset. |
| Edit | Enables editing of the current record. |
| EnableControls | Enables input for all data controls associated with the dataset. |
| FetchAll | Gets all records from the cursor to the end of the dataset and stores them locally. |
| FieldByName | Returns the TField pointer for a field name. |
FindFirst |
Finds the first record that matches the current filter criteria. |
| FindNext | Finds the next record that matches the current filter criteria. |
| FindLast | Finds the last record that matches the current filter criteria. |
| FindPrior | Finds the previous record that matches the current filter criteria. |
| First | Moves the cursor to the first record in the dataset. |
| FreeBookmark | Erases a bookmark set previously with GetBookmark and frees the memory allocated for the bookmark. |
| GetBookmark | Sets a bookmark at the current record. |
| GetFieldNames | Retrieves a list of the field names in the dataset. |
| GotoBookmark | Places the cursor at the record indicated by the specified bookmark. |
| Insert | Inserts a record and puts the dataset in edit mode. |
| InsertRecord | Inserts a record in the dataset with the given field data and posts the edit. |
| Last | Positions the cursor on the last record in the dataset. |
| Locate | Searches the dataset for a particular record. |
| Lookup | Locates a record by the fastest possible means and returns the data contained in the record. |
| MoveBy | Moves the cursor by the specified number of rows. |
| Next | Moves the cursor to the next record. |
| Open | Opens the dataset. |
| Post | Writes the edited record data to the database or to the cached update buffer. |
| Prior | Moves the cursor to the previous record. |
| Refresh | Updates the data in the dataset from the database. |
| RevertRecord | When cached updates are used, this method discards changes previously made to the record but not yet written to the database. |
| SetFields | Sets the values for all fields in a record. |
| UpdateStatus | Returns the current update status when cached updates are enabled. |
| Event | Description |
| AfterCancel | Generated after edits to a record are canceled. |
| AfterClose | Generated when a dataset is closed. |
| AfterDelete | Generated after a record is deleted from the dataset. |
| AfterEdit | Generated after a record is edited. |
| AfterInsert | Generated after a record is inserted. |
| AfterOpen | Generated after the dataset is opened. |
| AfterPost | Generated after the changes to a record are posted. |
| BeforeCancel | Generated before edits are canceled. |
| BeforeClose | Generated before a dataset is closed. |
| BeforeDelete | Generated before a record is deleted. |
| BeforeEdit | Generated before the dataset goes into edit mode. |
| BeforeInsert | Generated before a record is inserted. |
| BeforeOpen | Generated just before a dataset is opened (between the time Active is set to True and the time the dataset is actually opened). |
| BeforePost | Generated before edits are posted to the database (or the update cache). |
| OnCalcFields | Generated when calculations are performed on calculated fields. |
| OnDeleteError | Generated if an error occurs in deleting a record. |
| OnEditError | Generated if an error occurs while editing a record. |
| OnFilterRecord | Generated whenever a new row is accessed and Filter is set to True. |
| OnNewRecord | Generated when a new record is added to the dataset. |
| OnPostError | Generated when an error occurs while posting the edits to a record. |
| OnUpdateError | Generated when an error occurs while cached updates are being written to the database. |
| OnUpdateRecord | Generated when cached updates are applied to a record. |
Any TDataSet descendant (TTable, TQuery, or TStoredProc) gives access to the Fields Editor at design time. The Fields Editor enables you to select the fields that you want to include in the dataset.
To invoke the Fields Editor, right-click on a Table, Query, or StoredProc component on your form and choose Fields Editor from the context menu. The Fields Editor is displayed. At first the Fields Editor is blank, enabling all fields to be included in the dataset. You can add as many fields as you want to the dataset by selecting Add fields from the Fields Editor context menu. You can also create new fields for the table by choosing New field from the context menu. Figure 16.4 shows the Fields Editor as it appears after adding fields.
FIGURE 16.4. The Fields Editor.
After you add fields to the dataset, you can click on any field and modify its properties. The properties show up in the Object Inspector, enabling you to change the display format, constraints, display label, or other field characteristics.
Cached updates enable you to control when edits are applied to a database, and they are controlled by the CachedUpdates property. When cached updates are allowed, changes to records aren't written directly to the database. Instead, the changes are written to an update cache on the local machine. Records are held in the cache until you call the ApplyUpdates method. To abandon any changes in the update cache, you call the CancelUpdates method. You cancel the edits made to the current record by calling the RevertRecord method.
When cached edits are disabled (CachedUpdates is False), any changes made to a record are written to the database when the cursor leaves the record. This is fine for local databases, but it is not a good solution for client/server databases for a variety of reasons. Most often you hear people talk about network traffic being the primary reason for using cached updates. Although it is certainly true that cached updates help reduce network traffic, the value of cached updates goes far beyond the issue of network traffic. Let me explain further.
Many client/server databases return a read-only result set as the result of a query. One advantage of cached updates is that the client can work with a local copy of a dataset, modify it as needed, and then write the edits to the database all at one time. This is possible because the database server handles updates, insertions, and deletions of records from a read-only dataset. A local database has to lock records when they are being actively edited. When a record is locked, other database users cannot access the record. Using cached updates reduces the time a record is locked to a very short period of time.
Another advantage to cached updates is that a user can make several changes to a dataset and then either commit (apply) all changes or rollback (cancel) all changes. This a two-edged sword, however, because if something happens to go wrong on the server when changes are being written to the database, all changes are lost.
One drawback of cached updates is that several users might be working with the same record at the same time. It then becomes a race to see who gets the record updated first. In reality, this problem is reduced somewhat by implementing techniques in the client application that check whether multiple edits have taken place on a record. For example, if Joe tries to post an update to a record, the database and/or client application will notify Joe that Mary has changed the record since Joe initially retrieved it from the database. Joe will have to refresh his copy of the dataset to see whether he still needs to modify the record.
The Table component, represented by the TTable class, provides the quickest and simplest access to a table. Tables are more than adequate for most single-tier database applications. Usually, you will use the Table component when dealing with local databases and the Query component when dealing with SQL database servers.
The TTable class has many properties and methods in addition to those in its ancestor class, TDataSet. Table 16.4 lists the primary properties of the TTable component and Table 16.5 lists the primary methods. Remember, these are properties and methods specific to TTable and do not include those of TTable's ancestor, TDataSet.
For the most part, the properties and methods are very intuitive. By that I mean that you can usually figure out what a property or method does by just looking at its name. It doesn't take a lot to figure out that the LockTable method locks a table for an application's specific use and that the UnlockTable method unlocks the table again. Likewise, you don't have to have an IQ of 150 to guess what the CreateTable, DeleteTable, and RenameTable methods do. With that in mind, I'm not going to cover every aspect of every property and method listed here. Instead, let's get on to some of the more interesting aspects of the Table component.
| Property | Description |
| Exclusive | Locks a local table so that only this application can use it. |
| IndexDefs | Contains information about the table's indexes. |
| IndexFieldCount | The number of fields that make up the current key. |
| IndexFieldNames | Used to set the current key by specifying the names of the fields to use for the index. |
| IndexFields | Used to retrieve information about a specific field in an index. |
| IndexName | Used to specify a secondary index for a table. |
| KeyFieldCount | The number of fields to use when searching on partial keys. |
| MasterFields | The field or fields that should join the master and detail tables. |
| MasterSource | The table to be used as a master table when this table is used as a detail table. |
| ReadOnly | Specifies whether this table is read-only. |
| TableName | The name of the database table. |
| TableType | The table's type (Paradox, dBASE, or ASCII). |
| Method | Description |
| AddIndex | Creates a new index for the table. |
| ApplyRange | Applies a range to the dataset. Only records within that the range (determined by SetRangeStart and SetRangeEnd) are available for viewing or editing. |
| BatchMove | Moves records from a dataset into the table. |
| CancelRange | Removes any ranges currently in effect for the table. |
| CreateTable | Re-creates the table using new information. |
| DeleteIndex | Deletes a secondary index. |
| DeleteTable | Deletes a table. |
| EmptyTable | Deletes all records from the table. |
| GetIndexNames | Retrieves a list of all indexes for the table. |
| GotoKey | Moves the cursor to the record indicated by the current key. |
| GotoNearest | Moves the cursor to the record that most closely matches the current key. |
| LockTable | Locks a table so that other applications cannot access it. |
| RenameTable | Renames the table. |
| SetKey | Enables you to set keys for the dataset. |
| SetRange | Sets the start and end range for a dataset and applies the range. This method performs the same action as calling the SetRangeStart, SetRangeEnd, and ApplyRange methods. |
| SetRangeEnd | Sets the end of the range. |
| SetRangeStart | Sets the beginning of the range. |
| UnlockTable | Unlocks a table that was previously locked with LockTable. |
NOTE: As you have already seen, the DatabaseName property is used to select a BDE alias. For local databases, rather than select an alias from the list, you can enter a directory where database files are located. The TableName property will then contain a list of database tables in that directory.
A common need of a database application is to filter a table. Before I discuss filters in detail, I want to point out that filters are primarily used on local databases. Filters are rarely used with client/server databases; instead, a SQL query is used to achieve the same effect that filters have on local databases.
So why filter? Consider that you might have a table with thousands of records, but you are interested in displaying or working on only a small subset of the table. Let's say you have a database that contains names and addresses of computer users all over the world. Your company sells these names and addresses to other companies that want to do bulk mailings.
I call and want to order a mailing list from your company, but I want the list to contain only those computer users who live in Colorado. You could filter your table by postal code and generate a list of names with only Colorado addresses. Or, maybe Borland calls you and wants a list of computer users in Great Britain who are programmers by occupation. In that case, you could filter by occupation and country, thereby giving only the names and addresses the customer is interested in.
Using Filters in the Table Component Filters in the Table component are handled in one of two ways: through the Filter property or the OnFilterRecord event. Before I discuss these, let me talk about the Filtered property. This property determines whether the table is filtered. If Filtered is True, the table will apply the filter currently in force (either the contents of the Filter property or the results of the OnFilterRecord event). If Filtered is False, the contents of the Filter property are ignored and the OnFilterRecord event is never generated.
For the Filter property, you implement a field name, a logical operator, and a value. A filter might look like this:
FirstName = `Bob'
This statement, in effect, says, "Show me all records in which the first name is Bob." Filters can also use the keywords AND, OR, or NOT:
CustNo = 1384 AND ShipDate < `1/1/94'
NOTE: The field name and the logical operators (AND, OR, or NOT) are not case sensitive. The following two filter statements are identical:
CustName = `TurboPower' and ShipDate < `1/1/94' CUSTNAME = `TurboPower' AND SHIPDATE < `1/1/94'
In the case of searching for text, the FilterOptions property determines whether the search string is interpreted as case sensitive.
The following operators can be used in filter statements:
| Operator | Use |
| < | Less than |
| > | Greater than |
| = | Equal to |
| <> | Not equal to |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| () | Used to specify the evaluation order of compound |
| expressions | |
| [] | Used around field names containing spaces |
| AND, OR, NOT | Logical operators |
Filtering with the Filter Property Earlier I said there are two ways of filtering a table. One way is by using the Filter property. To use this, all you have to do is type the filter statement directly into the Filter property in the Object Inspector at design time or assign a string value to this property at runtime. Naturally, you have to set the Filtered property to True as well.
To see what I mean, perform the following exercise. First, set up the core components:
CustNo = 1384
Now the table should be showing only the orders for customer 1384. Spend some time experimenting with the filter statement and observe the changes to the table each time a different statement is used. Try the following:
CustNo = 1510 CustNo = 1384 and ShipDate < `1/1/94' CustNo = 1384 and ShipDate > `1/1/94' OrderNo > 1100 and OrderNo < 1125
Here you are making changes to the filter at design time, but it's more likely that you will change the filter dynamically at runtime. In that case, it's as simple as
Table1.Filter := `CustNo = 1510';
NOTE: If Filtered is set to True but the Filter property is blank, the entire dataset is returned just as if the table were not filtered.
Filtering with the OnFilterRecord Event The other way you can filter a table is with the OnFilterRecord event. To generate an event handler for this event, double-click in the Value column next to the OnFilterRecord event in the Object Inspector. Delphi will create an event handler. You can then write code to filter the table. Let's take the first filter example from earlier (CustNo = 1384) and filter using the OnFilterRecord event instead of the Filter property:
procedure TForm1.Table1FilterRecord(DataSet: TDataSet; var Accept: Boolean); var Value : Integer; begin Value := Table1.FieldByName(`CustNo').Value; Accept := (Value = 1384); end;
I've broken the actual code into two lines to make it more readable. The key element here is the Accept parameter. The OnFilterRecord event is called once for every row in the table. Set the Accept parameter to True for any rows that you want to show. The preceding code sets Accept to True for any rows in which the CustNo field contains a value of 1384. Earlier I gave you four sample filters to try. The first two filters would look like this if you were to use the OnFilterRecord event instead of the Filter property:
Accept := Table1.FieldByName(`CustNo').Value = 1510; Accept := (Table1.FieldByName(`CustNo').Value = 1384) and (Table1.FieldByName(`ShipDate').AsDateTime < StrToDate(`1/1/94'));
I'm sure you are thinking, "That's sort of messy." You're right. Using OnFilterRecord means more work, but it's also much more powerful than filtering with just the Filter property.
You can search a table for certain records by several different methods. In fact, this section applies to all TDataSet descendants, not just TTable.
NOTE: As with filters, finding records in a client/server database is almost always carried out via SQL queries. Finding records using the TTable methods is primarily a local database operation.
To search a filtered dataset, you can use the FindFirst, FindNext, FindPrior, and FindLast methods. These methods are the best way to search a filtered dataset because the filter is reapplied each time one of these methods is called. Therefore, if records that previously did not match the filter have been modified so that they now match the filter, they will be included in the dataset before the search is performed.
Another way to search a table is using the FindKey and GotoKey methods. These methods require an index. The FindKey method searches the primary key field or fields for a particular value. If a secondary key is in place, the secondary key field is used to perform the search. The following example sets a secondary key and then searches for a customer number of 1384:
Table1.IndexName := `CustNo'; if not Table1.FindKey([1384]) then MessageBox(Handle, `Record Not Found', `Message', MB_OK);
A third way of searching a table includes using the Locate and Lookup methods. One advantage to these methods is that they don't require the table to be indexed. These methods differ in two ways. First, Locate will use the fastest method available to search the table; if a table is indexed, Locate will use the index.
The second way these two methods differ is that the Lookup method will also return the values of the fields you have specified in the ResultFields parameter before calling Lookup. Both of these methods enable you to specify a field or fields to search and the search value. The following example illustrates the use of the Locate method:
){kind=link}
){kind=link}
){kind=link}
){kind=link}
var
Options : TLocateOptions;
begin
Options := [loPartialKey];
if not Table1.Locate(`CustNo', `1384', Options) then
MessageBox(Handle, `Record Not Found', `Message', MB_OK);
end;
If the record is found, Locate returns True, and the cursor is updated to reflect the record where the match was found.
Setting up a master/detail relationship with the Delphi Table component is easy. Let me explain a master/detail relationship and then I'll show you how to set up one. Let's say you have a table called CUSTOMER that contains information on your customers. That table will likely be indexed on a field called CustNo.
Let's further assume that you have a table called ORDERS that contains a list of all orders placed by your customers. Naturally, this table would also have a CustNo field. Now let's say you want to browse the table containing all your customers. Wouldn't it be nice if you could see each customer's orders while you browse? A master/detail table enables you to do that. Perform the following steps to get a good understanding of master/detail tables:
| Name | Master |
| DatabaseName | DBDEMOS |
| TableName | customer.db |
| DatabaseName | DBDEMOS |
| TableName | orders.db |
| MasterSource | DataSource1 |
11. Change the Active property of both tables to True. The Master table will show all customers, and the Details table will show the orders for each customer.
What you just did was create a relationship between the master table and the detail table. This relationship joined these two tables through a common field: CustNo. To fully understand what this means, run the program and move from record to record in the master table. As you select a customer name in the master table, you will see only that customer's orders in the detail table.
The Query component is the preferred method of accessing data in client/server databases. The following sections describe the primary properties and methods of the TQuery class.
TIP: The Query component doesn't have a TableName property as the Table does. This means that at design time, you can't immediately see a list of tables for the current database. To see a list of tables, you can perform one of two tasks. First, you can temporarily drop a Table component on the form, set the DatabaseName property, and then view the list of tables in the TableName property. You also can select the Query component on the form, right-click on it, and then choose Explore from the context menu. This will take you to either the SQL Explorer (Client/Server version) or the BDE Administrator (Standard and Professional versions). You can use either tool to view the tables in a database.
The SQL property is a TStringList that contains the SQL statements to execute. You can set the SQL property's value via the Object Inspector at design time or through code at runtime.
To set the value at design time, click the ellipsis button next to the SQL property in the Object Inspector. The String List Editor dialog box is displayed, and you can type in one or more lines of SQL statements.
TIP: Remember that the String List Editor dialog box has a feature that enables you to edit string lists in the Delphi Code Editor.
When adding lines to the SQL property at runtime, make sure that you clear the previous contents--for example,
Query1.SQL.Clear; Query1.SQL.Add(`select * from country');
It's easy to think of the SQL property as a string instead of a string list. If you don't clear the SQL property before adding a string, previous SQL statements will still be in the string list. Errors will almost certainly occur when you try to execute the SQL statement.
The statements in the SQL property will be executed when either the Open method or the ExecSQL method is called. If you are using SQL statements that include SELECT, use the Open method to execute the SQL query. If you are using INSERT, UPDATE, or DELETE statements, you need to use the ExecSQL method to execute the query. The following example sets the SQL property and then calls the Open method:
Query1.SQL.Clear; Query1.SQL.Add(`select * from country'); Query1.Open;
The SQL SELECT statement retrieves certain columns from a database. The asterisk tells the database server to return all the columns in a table. The preceding example, then, returns the entire table called country from the current database. To return specific columns, use code such as the following:
Query1.SQL.Clear;
Query1.SQL.Add(`select Name, Capital from country'); Query1.Open;
NOTE: Setting the Active property to True is the same as calling the Open method.
The SQL DELETE statement deletes records from a dataset. To delete a record from a dataset, you can use code like this:
Query1.SQL.Clear; Query1.SQL.Add(`delete from country where name = `Royland'); Query1.ExecSQL;
Notice that the ExecSQL method is used instead of the Open method. As I said earlier, you need to use the ExecSQL method to execute a query containing INSERT, UPDATE, or DELETE statements.
The INSERT command inserts a record into a dataset:
Query1.SQL.Add(`insert into country');
Query1.SQL.Add(`(Name, Capital)');
Query1.SQL.Add(`values ("Royland", "Royville")');
Query1.ExecSQL;
NOTE: Notice the use of double quotes in the preceding example. SQL syntax should not be confused with Object Pascal syntax. SQL enables the use of either double quotes or single quotes around value names. You can use either, but if you use single quotes within a string, you need to be sure to double them. Either of the following is valid:
Query1.SQL.Add(`values ("Royland", "Royville")');
Query1.SQL.Add(`values (`'Royland'', `'Royville'')');
Updating a dataset using the UPDATE command looks like this:
Query1.SQL.Clear; Query1.SQL.Add(`update country'); Query1.SQL.Add(`set Capital = `'Royburg'''); Query1.SQL.Add(`where Name = "Royland"'); Query1.ExecSQL;
Although it isn't my intention to teach SQL, I thought a few examples would help get you started.
SQL statements use parameters to add flexibility. A parameter in a SQL statement is much like an Object Pascal variable. A parameter in a SQL statement is preceded by a colon. Take the following SQL statement, for example:
select * from country where name = :Param1
The parameter in the preceding statement is named Param1. When this SQL statement is executed, the value of Param1 in the Params property is substituted for the parameter name:
Query1.SQL.Add(`select * from country where Name = :Param1'); Query1.ParamByName(`Param1').AsString := `Brazil'; Query1.Open;
You can set the parameter values of the Params property at design time via the Parameters dialog box, but most of the time you will be changing the parameters at runtime (which is the point of using parameters, of course). Notice in the preceding code that the ParamByName method is used to set the value of Param1. This is probably the easiest way to set a parameter's value. There is another way, however:
Query1.Params[0].AsString := `Brazil';
Here the Items property of the TParam class is used to set the value of the parameter. Accessing a parameter by index is more error-prone than accessing the parameter by name because you have to remember the orders of your parameters. Most of the time you will just use ParamByName.
NOTE: Not all aspects of a SQL statement can be parameterized. For example, most SQL servers don't allow a parameter for the table name. Take the following SQL statement:select * from :TableName
This statement results in a SQL error because you can't use a parameter for the table name.
The StoredProc component represents a stored procedure on a database server. A stored procedure is a set of SQL statements that executes as a single program. Stored procedures are individual programs that run against a database and can encapsulate often- performed database tasks. This makes it easier for programmers to do their work because they don't have to write line after line of code each time they want to perform a certain action. All they have to do is call the stored procedure on the server.
This also results in smaller client applications because they don't have to contain unnecessary code. Another purpose of stored procedures is to maintain data integrity. A stored procedure can validate data and either allow or disallow changes to the database based on whether the data validation passes.
As with SQL queries, some stored procedures make use of parameters and some do not. For stored procedures that don't take parameters, all you have to do is set the procedure name and execute the procedure:
StoredProc1.StoredProcName := `DO_IT'; StoredProc1.Prepare; StoredProc1.ExecProc;
Notice that the Prepare method is called first to prepare the stored procedure. After that, the ExecProc method is called to execute the stored procedure.
For stored procedures that take parameters, you have to set the parameters before executing the stored procedure:
StoredProc1.StoredProcName := `ADD_EMP_PROJ'; StoredProc1.ParamByName(`EMP_NO').Value := 12; StoredProc1.ParamByName(`PROJ_ID').Value := `VBASE'; StoredProc1.Prepare; StoredProc1.ExecProc;
By the way, if you have Delphi Professional or Client/Server, you can test the preceding code yourself by following these steps:
Table1.Refresh;
Now run the program. When you click the button, a new record is added to the table with an employee ID number of 12 and a project ID of VBASE. Close the program. Now change the code so that the employee ID number is 10 and rerun the program. This time you will get an error message from the stored procedure stating that the employee number is invalid. You will get the error because the ADD_EMP_PROJ stored procedure validates input, and a value of 10 is invalid for this database.
NOTE: You can view a stored procedure using the Explore feature found on the context menu. The stored procedure called ADD_EMP_PROJ looks like this:CREATE PROCEDURE ADD_EMP_PROJ ( EMP_NO SMALLINT, PROJ_ID CHAR(5) ) AS BEGIN BEGIN INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id); WHEN SQLCODE -530 DO EXCEPTION unknown_emp_id; END SUSPEND; END
Naturally, you shouldn't change a stored procedure unless you know what you are doing.
The UpdateSQL component provides a way of applying edits to a read-only dataset when cached updates are enabled. Ordinarily, a read-only dataset is just that--read only. When cached updates are enabled, however, a read-only database can be modified and the results of those modifications written to the database.
Most client/server databases have default actions that they perform when the changes in the update cache are applied. The UpdateSQL component enables you to provide your own SQL statements when a record in a read-only dataset needs to be updated, inserted, or deleted. For example, you can specify default values for certain fields in a dataset by using an UpdateSQL component.
The DeleteSQL property enables you to define a SQL query that will be executed when cached updates are applied and the update cache contains deleted records. Likewise, InsertSQL enables you to define a SQL query that will be executed when records have been inserted in a dataset and cached updates are applied. The ModifySQL property is used to define a SQL query that will be called when a record has been modified and cached updates are applied.
The DataSource component provides a mechanism to hook dataset components (Table, Query, or StoredProc) to the visual components that display the data (DBGrid, DBEdit, DBListBox, and so on). The primary purpose of DataSource is to enable making changes to your applications easier. All the data components on a form are hooked up to the DataSource, which is then hooked up to the dataset.
Because the data components are not hooked directly to the dataset, you can easily change datasets and not have to hook up each and every data component on the form each time you change the dataset. To change your dataset from a Table to a Query, for example, all you have to do is change the DataSet property of the DataSource component. There's no need to change anything in each of the data components.
TDataSource has very few properties. As you have already seen, the DataSet property is used to hook the DataSource to an underlying dataset. The Enabled property determines whether the data components hooked up to this data source display data. When Enabled is True, data is displayed. When Enabled is False, the data components are blank.
The methods of TDataSource are mostly insignificant, and I won't go over them here. The OnDataChange event is generated when the current record has been edited and the cursor moves to a different record. The OnStateChange event occurs when the state of the dataset changes (when the user moves from edit mode to browse mode, for example).
The Session component manages a database session. Each time you start a database application, the BDE sets up a global TSession object called Session. You can use Session to access the current database session. You don't have to create your own TSession objects unless you are writing a multithreaded application. Most of the time this isn't the case, so the default TSession object is usually all you need.
TSession has a couple methods of particular interest. The AddAlias and AddStandardAlias methods can be used to create a BDE alias at runtime. You will probably need to create aliases at runtime when you deploy your applications. Creating a BDE alias is discussed in the section titled "Creating a BDE Alias."
The GetAliasNames and GetDatabaseNames methods can be used to get a list of databases. This is handy when you want to enable your users to choose a database from a list. You could put the database names in a combo box, for example:
Session.GetDatabaseNames(DBNamesComboBox.Items);
In this case, the Items property of a combo box called DBNamesComboBox is filled with the list of database names. The GetTableNames and GetStoredProcNames methods can be used in the same way.
The Database component gives you access to specific database operations. You don't need a Database component for some applications. There are certain operations, though, that require a Database component. These operations are discussed in the following sections.
The KeepConnections property is used to control how database connections are handled when a dataset is closed. If KeepConnections is False, the database connection will be dropped when the last dataset is closed. This requires a login the next time a dataset is opened. It's not so much that logins are an annoyance (which they are), but more importantly that logins take time. I don't mean that they take time in the sense that you have to type a username and password in a login dialog box. I mean that they take a lot of processing and network time to open a database connection and log in, even if that login process is automated. If you don't want to worry about logging in every time a dataset is opened, set KeepConnections to True.
One reason to use a Database component is to control login operations. There are two ways you can control a login. One is by setting the LoginPrompt property to False and explicitly setting the login parameters. You can do this before opening a dataset:
Database1.Params.Values[`user name'] := `SYSDBA'; Database1.Params.Values[`password'] := `masterkey';
The preceding code sets the username and password for a Local InterBase database connection.
NOTE: You should be very careful about hard-coding password information in your applications for security reasons. Login prompts are used for a reason. Don't bypass login requirements unless you have a very good reason to do so.
Taking this example a little further, let's assume that you have a form with a Database component and a Table component. Let's say you want to create a database connection and open a table without any login prompt. Here's the code:
Database1.AliasName := `IBLOCAL'; Database1.DatabaseName := `MyDatabase'; Database1.Params.Values[`user name'] := `SYSDBA'; Database1.Params.Values[`password'] := `masterkey'; Table1.DatabaseName := Database1.DatabaseName; Table1.TableName := `CUSTOMER'; Table1.Open;
This code first sets the Database component's Alias property to IBLOCAL to connect to Local InterBase. Then the DatabaseName property is set to an arbitrary name. You can use any name you like for the database name. Next, the database connection parameters (username and password) are set. After that, the Table component's DatabaseName property is set to the value of the Database's DatabaseName property, which hooks the table to the database. Finally, the TableName property is set for the table and the table is opened.
The other way to perform a login is with the OnLogin event. This is generated whenever login information is required. In order to generate this event, you need to make sure that you have the LoginPrompt property set to True. After that, you can provide an event handler for the OnLogin event. It will look like this:
procedure TForm1.Database1Login(Database: TDatabase; LoginParams: TStrings); begin LoginParams.Values[`user name'] := `SYSDBA'; LoginParams.Values[`password'] := `masterkey'; end;
Does this code look familiar? It's essentially the same code used earlier when directly setting the connection parameters of the database. Usually you would not hard-code the username and password (or, at least, not the password) but would probably pull that information from an outside source, such as an edit component, a configuration file, or the Windows Registry.
Another reason to use a Database component is for transaction control. Normally, the BDE handles transaction control for you. There might be times, however, when you require complete control over transaction processing. In that case you can use the Database component's transaction control methods.
A transaction is a collection of updates to a dataset. Updates can include changes made to records, deleting records, inserting records, and more. You begin a transaction by calling the StartTransaction method. Any changes made to the dataset are held until you call the Commit method. When you call Commit, all updates in the transaction are written to the database. If you want to abandon changes to all updates in the current transaction, you call the Rollback method. The transaction isolation level is controlled by the TransIsolation property's value. (See the TransIsolation topic in the Delphi help for more information on transaction isolation levels.)
NOTE: All transaction updates are treated as a single unit, which means that when you call Commit, all updates are committed. When you call Rollback, all updates are canceled. It also means that if something goes wrong during a transaction commit, none of the updates in the current transaction are written to the database.
The BatchMove component is used to copy records from one dataset to another. The Source property specifies the source dataset, and the Destination property specifies the destination dataset for the batch move operation.
The Mapping property is required if your source and destination datasets don't have identical columns. Mapping is a TStringList property. To specify mappings, edit the string list and add mappings like this:
FirstName = FName LastName = LName Notes = Comments
NOTE: The mapping strings use the equal sign and not the Pascal assignment operator (:=).
The column name on the left side of the equal sign is the destination column; the column name on the right side of the equal sign is the source column. Setting the mappings like this tells TBatchMove, "These two datasets don't match, so copy the data from the FName column in the source dataset to the FirstName column in the destination dataset." If your source and destination datasets are not identical and you fail to set column mappings, the batch move will fail.
The Execute method performs the batch move. To use TBatchMove, all you have to do is set the Source, Destination, and Mode properties and call the Execute method. You can set the Source and Destination properties at design time or at runtime. The following code creates a copy of a table:
DestTable.TableName := `copy.db'; BatchMove1.Destination := DestTable; BatchMove1.Source := SourceTable; BatchMove1.Mode := batCopy; BatchMove1.Execute;
The Mode property specifies how records are applied to the destination dataset. Table 16.6 lists the possible values of the Mode property and their meanings.
| Value | Description |
| batAppend | Appends records from the source dataset to the end of the destination dataset. |
| batAppendUpdate | Combination of batAppend and batUpdate. If a matching record already exists, it is updated. If no matching record exists, a new record is added. |
| batCopy | Creates a new table and copies all records from the source table to the new table. |
| batDelete | Deletes records in the destination dataset that match the source dataset. The destination dataset must have an index. |
| batUpdate | Replaces records in the destination dataset with records from the source dataset that have the same key values. |
CAUTION: Be careful with the batCopy mode. Calling Execute in this mode will overwrite any existing tables and replace the contents with the contents of the source table.
The TField class represents a field (column) in a database. Through the TField class, you can set a field's attributes. These attributes include the data type (string, integer, float, and so on), the size of the field, the index, whether the field is a calculated field, whether it is required, and so on. You can also access or set a field's value through properties such as AsString, AsVariant, and AsInteger.
TField is a base class for more specific field classes. The descendants of TField include TStringField, TIntegerField, TSmallIntField, TWordField, TFloatField, TCurrencyField, TBCDField, TBooleanField, TDateTimeField, TDateField, TTimeField, TBlobField, TBytesField, TVarBytesField, TMemoField, and TGraphicField.
These derived classes extend the base class in small ways to add functionality. For example, numerical field classes have a DisplayFormat property that determines how the number is displayed and an EditFormat property that determines how the value appears while being edited. Each TField descendant corresponds to a specific database field type. The TIntegerField class is used when the field type is an integer, the TTimeField class is used when the field type is a date or time (or date/time), the TBlobField class is used when the field type is binary large object, and so on.
You can access the properties of TField at design time through the Fields Editor. After you add fields, you can click on a field in the Fields Editor and the properties for that field will be displayed in the Object Inspector. Figure 16.5 shows the Fields Editor and Object Inspector while editing fields.
FIGURE 16.5. The Fields Editor and Object Inspector.
The TField properties and methods are so numerous that I'm not going to list them all here. Instead, I'll walk you through some ways you are most likely to use TField and its descendant classes.
Before you can get or set the field value, you need some way of locating a field. There are at least three ways to do this:
Accessing a field by its pointer name is probably the least used method. It works only if you have previously added fields to your project using the Fields Editor. When you add fields via the Fields Editor, Delphi creates a pointer for each field by combining the table name with the field name. If you have a table called Table1 and a string field called FirstName, Delphi would create a TStringField pointer called Table1FirstName. You could use this pointer to access a field:
Table1FirstName.Value := `Per';
The problem with this approach is that you don't always need to add fields using the Fields Editor.
The Fields property offers another way of accessing a field--by position. If you know that the LastName field is the first field in the table, you can use something like this:
Edit1.Text := Table1.Fields[0].Value;
The problem with this approach, of course, is that you have to know the exact order of fields.
Of the three ways of accessing fields, the most commonly used and reliable is the FieldByName method. Using FieldByName, you have to know only the name of the field to access the field:
Table1.FieldByName(`LastName').AsString := Edit1.Text;
FieldByName returns a TField pointer. To make it more understandable, let me break down the preceding line of code:
var Field : TField; begin Field := Table1.FieldByName(`LastName'); Field.AsString := Edit1.Text; end;
In most cases, FieldByName is the way to go. Oh, you might be wondering which record is modified when you execute the preceding code. All these techniques retrieve the field from the current record.
After you obtain a pointer to a particular field, you can change its value by using the Value property or any of the As properties (by As properties I mean AsString, AsInteger, AsDateTime, AsBoolean, and so on). These properties perform conversions from one data type to another. Naturally, you can't always be assured that a conversion can be made. For example, if you try to convert a string field containing Smith to an integer, an exception will be thrown.
Setting a field's value is simple when you know the secret of FieldByName:
Table1.Edit; Table1.FieldByName(`LastName').AsString := Edit1.Text; Table1.Post;
First, the Edit method is called to put the table in edit mode. If you fail to call Edit, you will get an exception when you try to modify a field's value. After the table is put in edit mode, the field's value is set. In this case I used AsString instead of the Value property. For a string field, it's the same thing in either case. Finally, the Post method is called to post the edit to the database (or the update cache if CachedUpdates is on). That's all there is to it. Retrieving a field's value is just as easy:
){kind=link}
var
AcctNo : Integer;
begin
AcctNo := Table1.FieldByName(`ACCT_NBR').Value;
{ More code here. }
end;
The TField events of note are OnChange and OnValidate. The OnChange event is generated each time a field's value changes. This occurs after the data has been posted. You can use this event if you need to be notified of changes to a field.
The OnValidate event, on the other hand, occurs just before data is posted. If you have a data control on a form associated with a field, that control can usually do validation of data. If, however, you are setting a field's value through code, you might want to do your own validation in the OnValidate event handler. This event is somewhat strange in that it doesn't pass you a parameter that you can use to reject an edit. Instead, you should throw an exception if the validation fails:
procedure TForm1.Table1ACCT_NBRValidate(Sender: TField);
begin
if Sender.AsInteger < 3000 then
raise EDBEditError.Create(`Bad Account Number.');
end;
When you throw an exception, the act of posting the data to the database is aborted.
To create an event handler at design time, you have to use the Fields Editor to add fields to the dataset. After you have done that, you can select a field in the Fields Editor and then double-click next to the event name in the Object Inspector, as you would for any other event.
The Client/Server version of Delphi comes with three additional data access components that enable the creation of multitiered database systems. (To recap, a multitiered database system is one in which client applications talk to one or more application servers [the middle tier] that in turn talk to the database server.) The multitier database components are TRemoteServer, TProvider, and TClientDataSet.
The TRemoteServer component is used in a client application to establish a connection to one or more application servers. The TProvider component, used in a middle-tier application server, acts as a conduit between the database server and the client application. The TClientDataSet component is used in a client application to gain access to the provider on an application server. A detailed account of the use of these components is beyond the scope of this book.
You can go only so far in database programming without eventually creating a BDE alias. The sample databases are fine, but sooner or later you will need to create an alias for your own databases. When you deploy your Delphi database application, you will also need to create one or more aliases on your users' machines as well. There are many ways to create an alias:
To create an alias, either you must have your users run the BDE Administrator, or you must create any needed aliases through code. Obviously, creating the alias yourself through code is preferable (never underestimate the ability of your users to botch even the most simple tasks). First I'll show you how to use the BDE Administrator to create an alias. Then I'll show you how to create an alias through code.
While you are developing your applications, you need to create one or more BDE aliases. This is most easily done using one of the BDE utility programs provided with Delphi. The steps for creating an alias using the BDE Administrator and the SQL Explorer are identical, so for simplicity's sake I'll show you how to create an alias with the BDE Administrator.
Let's assume for a minute that you are going to create a mailing list application. The first step you need to take is to create an alias for your database. You can create an alias in several ways, but the easiest is probably with the BDE Administrator utility. Perform these steps:
FIGURE 16.6. The BDE Administrator creating a new database alias.
At this point, you need to provide a few items of information in the Definition window. The Type is already set to STANDARD, so there's nothing to be done there. The DEFAULT DRIVER field is set to PARADOX, which is the type you want, so there's nothing to be done there, either (other choices include dBASE, FOXPRO, and ASCIIDRV). You can also leave the default value for the ENABLE BCD field. The only information you need to supply is the path on disk where the database files will be stored:
Switch back to Delphi and drop a Table component on a form. Check the DatabaseName property in the Object Inspector to see whether your database alias shows up. If you did everything right, you will see it listed there with the other database names. Your database doesn't have any tables yet, but that's okay. You can take care of that later.
To avoid confusion with your users, you will probably want to create any aliases your program needs the first time your program runs. Thankfully, creating an alias at runtime is simple. Here's the code to create a local Paradox alias called WayCool:
CreateDirectory(`C:', nil); Session.AddStandardAlias(`WayCool', `C:', `');
That's it? Yes, that's all there is to it. Naturally, you should perform some checks to ensure that the directory and alias were properly created, but that's about all there is to it.
NOTE: This example uses the AddStandardAlias method to create a STANDARD type alias. To create aliases for database servers of other types, use the AddAlias method.
That's a lot to absorb. The best way to solidify the material presented in this chapter is to spend a lot of time experimenting. Take some sample databases and perform filters on the tables, try out some SQL statements, and browse the databases with the BDE Administrator or the SQL Explorer. At this point you don't have to worry about writing complete database programs. Just spend some time with the various components and get a feel for how the BDE and the VCL database components work together.
The Workshop contains quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you have learned. You can find answers to the quiz questions in Appendix A, "Answers to the Quiz Questions."
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}