|home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links|
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.
As 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.
The 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.
|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.|
|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.|
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.|
|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