Posted 53 months ago on 12/12/2008 and updated 2/12/2011
With the introduction of support for ADO Database access, Borland gave us TADOTable, a quick, easy-to-use component to begin the job of outfitting an existing application with a newer database. But TADOTable is no TTable! It comes with some baggage you can do without.
It was a great day when Borland added ADO support to Delphi. Now we could work effectively with any database that supported an ADO provider, and without the need for the old, BDE-dependent, SQL-Links Technology.
Naturally, many existing BDE-based applications became candidates for database replacement. Sure, dBASE, Paradox, and several "BDE replacement" databases gave us great data stores - for their day - but SQL, client-server databases offer so much more in capacity, security, and robustness.
Additionally, many developers were finding it increasingly difficult to market their old BDE applications to organizations with opinionated IT staff.
Recognizing this, Borland gave us a little something extra with Delphi's new ADO components, TADOTable. TADOTable was intended as a way for developers to get a quick jump on migrating to newer, better databases.
But Borland never intended TADOTable to be the final solution. It's viable in some limited instances, but comes at a huge cost in other places.
Before we explore TADOTable's limitations and the reasons for them, let's review some of the key features of the "old way" of doing things:
The Navigational Model
With plain old TTable, and databases like dBASE, Paradox, and DBISAM, we had direct access to what I'll call the "navigational" model of database programming.
Additionally, the VCL data-aware components were very smart about optimizing access to TTable-backed stores. If you "opened" a million-row table and displayed its records in, say, a TDBGrid, the VCL would ensure that only as many records as it took to populate the visible rows of the grid were actually fetched into memory.
In other words, you could easily open a huge table, and get away with it, performance-wise.
We could specify the index we wanted a TTable to use. Then we could take immediate advantage of the database's fast indexing support with methods like FindKey, FindNearest, and SetRange. Used properly, we could often avoid the use of Filters altogether.
Even the Locate method was pretty smart. It would try to use an index if a suitable one was available. Locate, in fact, taught a lot of us the importance of providing suitable indexes everywhere they're needed; because, if Locate couldn't use an index, it had to resort to an expensive, brute-force table scan.
I wanted to remind you of all these things because, as soon as you use TADOTable, many (or all) of the above, super-fast ways of accessing data disappear.
The Relational Model
That's right: Disappear. All your smart, fast navigational code becomes useless.
To understand why, we need to understand a bit about both TADOTable, and relational, client-server databases.
First, get ready for a shock: Most client server databases do not give you direct access to your data tables. Why? Because, according to relational theory, they're not supposed to. Thinking about that a moment, something else makes sense: They don't let you specify which index to use, either. Index specification is meaningless without direct table access.
You access your client server data via SQL Queries, instead. Sure, under the covers, the database must eventually get down to the table/index level, but SQL is supposed to shield you from those details.
So, when designing a client-server database, you design your tables pretty much like you used to, and, if you're diligent, you anticipate the indexes that'll be needed by queries that include "ORDER BY" clauses.
When you execute a SQL Query, the database returns a "result set:" Every record and column included by your query. It can do nothing but return that result set.
In programming parlance, this result set is often called a "cursor," and your database may allow you to specify where that cursor will live: locally, or on the server. That choice can be important, but the crucial point to understand, here, is that that cursor will be created - somewhere.
Knowing that, how often would you write this SQL statement against a huge table: "SELECT * FROM TableName"?
Not very often at all, I'll bet, because you know in advance that the resulting "cursor" (result set) is going to be huge. It's going to take time to generate, it's going to hog resources, and it's going to pump a monstrous stream of data over your network.
But that really dumb query is exactly what's "under the covers" with TADOTable: SELECT * FROM TableName.
Yes, your VCL TDBGrid will still faithfully pull only as many records as needed to populate the visible grid rows, but that no longer buys you anything. By the time TDBGrid gets a chance to work that bit of magic, it's already too late: The monster cursor has already been created.
And it gets worse: If your particular database's ADO provider does not provide Delphi with meta-data about indexes (few do), you cannot specify and index, or use FindKey, FindNearest, or SetRange. You're stuck with Locate, and even Locate is now severely crippled.
As an exercise, try this:
Start a simple Delphi project (or you can download the project source; see attached files below).
Drop a TADOConnection and a TADOTable on the main form and name the TADOTable as "MyAdoTable;"
Specify the connection string for the TADOConnection to use one of your SQL Server databases (SQL Server Express will do just fine). Leave all other properties at their defaults.
Specify the name of one of your existing SQL Server tables as the TableName property of MyAdoTable. Set its Connection property to your TADOConnection. Leave all other properties at their defaults.
Add two buttons to your form. Caption one Button "Open Table" Caption the other "Set Index:"
In the first button's OnClick handler write this simple code:
procedure TForm1.OpenTableButtonClick(Sender: TObject); begin ADOConnection1.Open; MyAdoTable.Open; end;
In the second button's OnClick handler, write this:
procedure TForm1.SetIndexButtonClick(Sender: TObject); begin MyAdoTable.IndexName := ''; end;
If you want to see things happening, you could add a TDataSource and TDBGrid - but they aren't strictly necessary.
Run your program and
Click the "Open Table" button. So far, so good, right?
Now click the "Set Index" button. Notice that we're only asking it to use its primary key - nothing fancy, right?
This little explosion should satisfy you that TADOTable is not going to observe any of our customary navigational programming techniques. And, since the VCL can't even "see" index information, good old Locate() will be severely crippled (all it can is brute-force scans).
The solution, of course, is to replace that TADOTable with a TADOQuery. That still doesn't get us back our navigational conveniences, but we can usually achieve all the same benefits by writing appropriate SQL for our TADOQueries.
The first advantage of TADOQuery is that it allows us to control the size of that potentially monstrous cursor. Instead of "SELECT *" we can SELECT a limited number of columns. A WHERE clause can limit the number of rows involved. And, of course, and ORDER BY clause replaces our old "IndexName := 'something'" business.
Does that mean that TADOTable is useless?
No, TADOTable is not useless.
First of all, TADOTable is still appropriate for small data tables. Just be aware that they won't come back sorted the way you might wish. (Under relational theory, there is no guarantee that your records will be physically stored in any particular order.)
But TADOTable is also useful as a tool for learning some Delphi ADO "isms" that'll be important with TADOQuery and a couple other components we'll mention.
Let's start by considering how TADOQuery, which is based on a SELECT * query, can possibly be used to update records. It can, you know.
The first thing you have to do is specify a CursorType property value that gets you an updatable cursor. The default ctStatic won't do at all. Try ctDynamic.
Next, understand that, without any further help from you, TADOTable can post an updated record back to the database. But how?
There's a little "under the covers" magic that happens when you update a record with TADOTable. VCL code will generate an update query. That query, of course, has to specify which record to update.
By default (that is, without your help) TADOTable will generate an "UPDATE WHERE ALL" query. That means that it will specify the record to update by building a WHERE clause that includes all columns, and populates them with the values from the buffered copy of your unchanged record.
Sounds kind of expensive, doesn't it?
Help is on the way, but be forewarned that we're not going to complete the story in this article.
The way to "smarten up" TADOTable is via persistent fields. If you haven't already, add a TDataSource, TDBNavigator, and TDBGrid to your test project. While you're at it, you might as well delete the "Set Index" button and its OnClick handler. We already know they're useless.
Now right-click on MyAdoTable, select "Fields Editor" and add all fields. I know, some developers hate persistent fields. In a moment, you'll see why they're important in ADO.
Click on a persistent fields that is a member of your table's primary key, then look at the Object Inspector. There's something new here! You'll see a "ProviderFlags" property collection. Go ahead and expand it.
Interesting-looking property names, right? These are the values you can set to "inform" Delphi how to generate smarter SQL statements than UPDATE WHERE ALL. Actually, more than updates.
pfInUpdate (defaults to true for all persistent fields) The field can be included in UPDATE statements. (That is, it can be modified)
pfInWhere (defaults to true for all persistent fields) The field is included in the WHERE clause when the provider's UpdateMode is upWhereAll or upWhereChanged.
pfInKey (defaults to false for all persistent fields) The field is included in the WHERE clause when the provider's UpdateMode is upWhereKeyOnly.
pfHidden (defaults to false for all persistent fields) The field is included in data packets to ensure the uniqueness of records so that they can be correctly updated. It can't be seen or used by the receiving client dataset.
Ah, but did you notice the new term introduced above? "Provider." It refers to TDataSetProvider which, in turn, is related to TClientDataSet. To take advantage of the ProviderFlags collection, we'd need to add these to our little project, and say quite a bit about them.
And that's another article for another day.
As you've seen, TADOTable has limited usefulness. It was introduced as a stop-gap; a quick and dirty way to help developers begin the job of outfitting existing navigational style applications with a client-server database.
But it was never intended to be the end of the job.
You can see that Delphi's ADO support includes components that let us overcome the liabilities and limitations of TADOTable. These begin with TADOQuery, and include TADOCommand. In addition, TDataSetProvider and TClientDataSet flesh out ADO support with powerful capabilities for taking total control over the ways that Delhi deals with inserting, updating and deleting rows in client-server databases while retaining the usefulness of data-aware controls.
Use TADOTable sparingly. Its use should be limited to small tables, and in situations where you can either live with unsorted records, or load them into lists or components that support in-memory sorting. Other than those situations, it's time to move on.