Using Pseudo-Indexes
by Dave Hernandez
Traditionally, Delphi & C++ Builder have built their database support on top of the services offered by the Borland Database Engine. As new features are added to the BDE, the Delphi and C++ Builder functionalities are expanded or simply re-implemented in the light of the new possibilities.
Here we comment on pseudo-indexes, a new feature added to BDE 5.0. Pseudo-indexes most of all should interest programmers using direct BDE calls in their applications (are there any of these still left?), but developers using Delphi and C++ Builder can also put into good use the new possibility. The fact that the TTable class in Delphi 4 and C++ Builder 4 has been modified in order to make use of this new feature (as can be seen by examining the DBTables unit in the VCL source), makes the acquaintance with pseudo-indexes relatively less 'vital' for those programmers.
Under BDE 5, when opening a database table handled through a driver supporting pseudo-indexes, you can specify not only the name of a physical index, but also a pseudo-index: an expression comprising the list of fields you want your table to be sorted on. All the SQL Links support pseudo-indexes, and neither dBase, Paradox or Access drivers do, so this purely a client-server oriented feature. If you want to access the records in the EMPLOYEE table of IBDEMOS ordered by fields JOB_COUNTRY and FULL_NAME (no physical index for the combination of those fields), you can use the pseudo-index expression '@JOB_COUNTRY@FULL_NAME'. Delphi programmers can use the IndexName property:
TblEmployee.IndexName := '@JOB_COUNTRY@FULL_NAME';
But before BDE 5 we could accomplish the same result using:
TblEmployee.IndexFieldNames := 'JOB_COUNTRY;FULL_NAME';
Using a pseudo-index does not imply the creation of an index at the server; instead, the existence of the index is simulated (hence the prefix 'pseudo') by means of the corresponding order by clauses in the select sentences sent by the BDE to the backend. You can verify this behavior using the SQL Monitor. For any further details, I refer the reader to the excellent article of my friend Ian, "Religion Wars in Lilliput".
As an additional comment, I'll mention the fact that you could also have used the pseudo-index '@9@11' to achieve the same result as before (JOB_COUNTRY is the 9th field of EMPLOYEE, FULL_NAME - the 11th). The sample application included with this article builds that kind of pseudo-indexes in order to change the sorting of data in a grid when the user clicks on any title column.
