Paging Index/Database Experts 
Author Message
 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.

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.

WHY IS THIS?  For compatibility with Interbase, paradox or What?  How do
other programmers deal with this issue.  

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.

Sincerely,

WRiddell
Developer/Christian Life College/Cntr



Tue, 19 May 1998 03:00:00 GMT  
 Paging Index/Database Experts

Quote:

>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).

Quote:
>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.

Quote:
>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.

Quote:
>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

SetKey;
Table1LastName.Value := 'Riddell';
Table1FirstName.Value := 'W';
if Table1.GotoKey then
   blah, blah

-------------------

to search with Paradox index defined as LastName, FirstName

if FindKey('RiddellW') then
  blah, blah

Which do you prefer?  Hope I've helped you.




Sat, 23 May 1998 03:00:00 GMT  
 Paging Index/Database Experts

Quote:

>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.

I did Clipper programming for years but like Delphi much better 'cause
Pascal is the greatest language there is (IMHO) :).

The way I deal with the above situation is to go ahead and define the
expression index, such as CUSTNO+DTOS(DATE), and then do the
master/detail stuff myself.  It isn't a big deal at all.  In the
DataChanged event for the master table, select the index you want to
use and set a range for the detail table by setting the detail table's
fields to the start range end range.  This is a very elegant way to
handle this situation.  The later versions of Clipper did something
like this with the TBrowse class.  

Email me if you want examples, I would hate to post incorrect code and
I am not at work so I can't look at my own examples!



Sat, 23 May 1998 03:00:00 GMT  
 Paging Index/Database Experts

Quote:
>to search with dbase index defined as LastName + FirstName
>SetKey;
>Table1LastName.Value := 'Riddell';
>Table1FirstName.Value := 'W';
>if Table1.GotoKey then

   blah, blah

Quote:
>-------------------
>to search with Paradox index defined as LastName, FirstName
>if FindKey('RiddellW') then

 > blah, blah

Quote:
>Which do you prefer?  Hope I've helped you.


I see your point.  Actually when I recently got copy of Visual dBase,
I discovered that Borland has added complete paradox support for
dbase style commands. Browse for, INdex on, etc.  This adds a bit
of comfort and  yet another reason to consider changing to Paradox.

However, the DBF standard is supported in so many other
areas (word, excel, etc) that I am still hesitant to change!



Tue, 26 May 1998 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. MemDB - Memory Database Expert enters public beta

2. Delphi 2.0 database form expert error

3. Discussion : Datawarehousing Case - Need Advice from Database Expert

4. MIT software guru, database expert, seeks offsite work

5. TAPI / Databases and telephone Access - Experts?

6. Delphi 2.0 Database Form Expert not working

7. Is there any experts like database form expert out there ? But cool - er ?

8. Database Page Size

9. Generate Static Web Pages from a DBF Database

10. Print Rich Edit from a Database in several pages

11. page up/page down

12. Visual page / Active page

 

 
Powered by phpBB® Forum Software