Paging Index/Database Experts
>This question has yet to be satifactorily answered by anyone.
>I am moderately new to Delphi and am struggling over the database
>index seek/find, and relation master->detail relationships.
>As a dBase/Clipper programmer for years, it has been a practice of my to
>create indexes on several views so my browsing objects view the
>information in a specific order. It isn't enough to view the (for
>example) the part number only, but the part number IN DATE order. I would
>create an index like: part_num + dtos(date). OR part_num + cust_name to
>view in customer order.
What were you using with Clipper? If you were using file based
indexes such as ntx, you have some catching up to do. If you were
using MDX, Comix, or Successware drivers, then you understand tags.
You use the MDX indexes and tags in Delphi the same way.
You can create them with the Database Desktop that comes with Delphi
or another utility such as Broplus. In Database Desktop, with your
table open, select restructure, then under table properties select,
then define. The following screen is pretty self explanatory,
allowing you to index on expressions such as (key1 + key2).
To select the tag in Delphi use the TTable.IndexName property with the
object inspector. Or, programatically, IndexName := 'my_tag_name';
For searches use help to look up SetKey and GotoKey or GotoNearest
procedures/functions. FindKey and FindNearest do not work with dbase
compound index expressions such as (key1 + key2).
>The problem with this is that the Delphi relation (master/detail) will not
>recognize the index name. It needs a KEY field which has to be EXACT
>match to a key field in the detail database. In addition, it is difficult
>to seek on indexes when they are not key fields.
I explained how to perform seeks above. I don't understand regarding
the key field aspects. Master/detail relationships should be based on
the primary key in the master in order to ensure its referential
integrity. Hopefully the above will help you here as well.
>WHY IS THIS? For compatibility with Interbase, paradox or What? How do
>other programmers deal with this issue.
The BDE works for all Borland products. In Delphi, you can actually
write one program that will work on identical dbase or Paradox tables.
All that needs to be changed in the code is the TableType. You can
leave the rest of the code the same. Rather nice, I think.
>PLEASE don't tell me to change database structures to Paradox or
>Interbase. I may change to a client-server someday, but do not have a
>desire to change all my databases to Paradox--and frankly do not see a
>very good reason to do so. Both dbf/db have their advantages and
>disadvantages. That argument is beyond the scope of this question.
Leaving all the theory behind, you might prefer Paradox for purely
pragmatic reasons. Not having index expressions such as LastName +
dtos(date_recvc) + dow(date()) is really a blessing. Dive into it,
it's really easier. For example:
to search with dbase index defined as LastName + FirstName
Table1LastName.Value := 'Riddell';
Table1FirstName.Value := 'W';
if Table1.GotoKey then
to search with Paradox index defined as LastName, FirstName
if FindKey('RiddellW') then
Which do you prefer? Hope I've helped you.