[Delphi] Findkey infinite loop on Table search - HELP!!
Chris Sexton
I am writing a master-detail database application, and I am having
problems using Findkey searching on a secondary index. It finds the first
occurrence OK, but doesn't ever progress any further, getting stuck in an
infinite loop.
MasterTable has an identifier of PRODUCT_ID, and DetailTable has an
identifier of PART_ID. Each part in the DetailsTable belongs to a product
in the MasterTable, and any one product may have many parts. There is a
secondary index on the DetailTable which consists simply of PRODUCT_ID, in
order for me to retrieve those parts which belong to a particular product.
The relation between Parts and the Product they belong to is therefore
maintained.
I use 2 FindKey procedures so far:
When I delete a product, I also delete all the parts which have their
PRODUCT_ID field with the relevant value. This is done by the following
routine (and works fine):
with DetailTable do
begin
{ get the current PRODUCT_ID in the master table }
product := MasterTable.FieldByName('PRODUCT_ID').AsInteger;
{ search the detail table by the secondary index - just PRODUCT_ID }
IndexName := 'PRODUCT_INDEX';
while FindKey([product]) do { find a part }
Delete; { delete the part record }
{ Then delete the master table current record }
end;
The above works perfectly fine, though maybe it could have been written
better. I cut the code and pasted it onto another form, to adapt it to a
similar routine.
In this second routine, I don't want to _delete_ all the relevant records
in the detail table, I just want to add some text into a listbox for each.
The text is simply the name of the part.
I tried (but got stuck on...):
with MainForm.DetailTable do
begin
{ get the current PRODUCT_ID in the master table }
product := MasterTable.FieldByName('PRODUCT_ID').AsInteger;
{ search detail table by the secondary index - just PRODUCT_ID }
IndexName := 'PRODUCT_INDEX';
while FindKey([product]) do { find a part }
begin
PartName := FieldByName('PART_NAME').AsString; { get the name }
ListBoxParts.Items.Add(PartName); { add name to listbox }
end;
end;
When I step through this in the debugger it finds the first relevant PART,
and I can see the correct name in the variable, but in the while loop it
keeps finding the same record, and therefore goes on forever.
I've tried various combinations of SetKey/GotoKey, but haven't had any
success, and I really can't work out why it works when _deleting_ parts
but not when simply accessing their name to add to a listbox. Is it
something to do with where the table cursor is pointing, and the delete
removing the record being pointed to?
(Please note, I _don't_ want to have a DBListbox, as I will want to remove
items from the listbox as and when appropriate without affecting the
database, and also I will want to later select a subset of the parts)
The first procedure lives on the form where the TTable and TDataSource
components are, and the second lives on another form. The main form unit
is in the uses clause of the child, but I don't feel that it's because
it's not on the same form, as I feel that I've accounted for this - it all
compiles OK, so the relevant components can be 'seen' by the child form.
I'm not sure of anything anymore :-o
I've been pulling my hair out over this one, and don't fancy being bald,
so please can you send some refreshing ideas?
Regards
Chris
--
*********************************************************
Chris Sexton
cse...@partridge.attiaa.attistel.co.uk
I'd rather have a full bottle in front of me than a full frontal labotomy
*************************************************
Sundial Services
>In this second routine, I don't want to _delete_ all the relevant records
>in the detail table, I just want to add some text into a listbox for each.
>The text is simply the name of the part.
>When I step through this in the debugger it finds the first relevant PART,
>and I can see the correct name in the variable, but in the while loop it
>keeps finding the same record, and therefore goes on forever.
As you have seen, "FindKey" only finds the first record with the desired key
value. Presumably it is designed for use with primary indexes wherein the key
values must be unique. Presumably you are using a secondary index or a
portion of a primary one.
What you probably want to do is to assign to the "IndexName" property the name
of a secondary index, which puts the values in logical order by that index.
Now "FindKey" the first value, and scan from there using regular "Next" calls
until you have exhausted the records having a matching value. Your scan
should follow the [secondary] index order.
Steve Koterski
: I am writing a master-detail database application, and I am having
: problems using Findkey searching on a secondary index. It finds the first
: occurrence OK, but doesn't ever progress any further, getting stuck in an
: infinite loop.
The FindKey method of the TTable component is intended to find the first
record in the data set where the field(s) in the index expression contain
the search value(s) specified in the use of the FindKey method. This is
true whether using a primary or a secondary index.
In your deletion example, the FindKey method appeared to be moving the
record pointer to succeeding records, but was actually not doing so. In
your code, you were successively calling FindKey, arriving at a matching
record, and then deleting that found record. Because you were deleting
each found record, subsequent calls to FindKey naturally found a diff-
erent record because what was the preceding record was deleted and the
new record found thus became the first occurrence of the search value(s).
To find succeeding matching records (without having to delete them to
do so), you need to:
1. Use FindKey to locate the first matching record.
2. Save the search value(s) to memory variable(s).
3. Use a While loop based on the index field(s) matching the memory
variable(s) and on the Eof method returning False, using the Next
method to iterate through each succeeding records.
FindKey depends on an index being active, and one with an expression
compatible with the search value(s). If such an index is active, one
side effect is that all records with the same field value(s) as used
in the index expression will be located contiguous in the indexed
ordering of the records. This is how the loop described above is able
to find each succeeding matching record just with the use of the Next
method.
For example, assume a table (Table1) with a LastName field, and an index
active that is based on this LastName field. To find all occurences of
the string "Smith" in this field and put them into a TListBox component:
procedure TForm1.DooDah;
var
CompValue: String;
begin
CompValue := 'Smith';
with Table1 do begin
FindKey([CompValue]);
while (FieldByName('LastName').AsString = CompValue) and (not Eof) do
begin
ListBox1.Items.Add(FieldByName('LastName').AsString);
Next;
end;
end;
end;
If the FindKey method as used above fails to find a single instance of the
string "Smith" in the LastName field, the While loop condition will
evaluate to False and no items will be added to the TListBox. If one or
more such records exist, all will be added.
[...]
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski _/ The opinions expressed here are _/
_/ kote...@borland.com _/ exclusively my own _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Andreas Hammar
On Tue, 11 Jul 1995, Chris Sexton wrote:
<> Hello all,
<>
<> I am writing a master-detail database application, and I am having
<> problems using Findkey searching on a secondary index. It finds the first
<> occurrence OK, but doesn't ever progress any further, getting stuck in an
<> infinite loop.
<>
<> MasterTable has an identifier of PRODUCT_ID, and DetailTable has an
<> identifier of PART_ID. Each part in the DetailsTable belongs to a product
<> in the MasterTable, and any one product may have many parts. There is a
<> secondary index on the DetailTable which consists simply of PRODUCT_ID, in
<> order for me to retrieve those parts which belong to a particular product.
<> The relation between Parts and the Product they belong to is therefore
<> maintained.
<>
<> I use 2 FindKey procedures so far:
<>
<> When I delete a product, I also delete all the parts which have their
<> PRODUCT_ID field with the relevant value. This is done by the following
<> routine (and works fine):
<>
<> with DetailTable do
<> begin
<> { get the current PRODUCT_ID in the master table }
<> product := MasterTable.FieldByName('PRODUCT_ID').AsInteger;
<> { search the detail table by the secondary index - just PRODUCT_ID }
<> IndexName := 'PRODUCT_INDEX';
<> while FindKey([product]) do { find a part }
<> Delete; { delete the part record }
<> { Then delete the master table current record }
<> end;
<>
<> The above works perfectly fine, though maybe it could have been written
<> better. I cut the code and pasted it onto another form, to adapt it to a
<> similar routine.
<>
<> In this second routine, I don't want to _delete_ all the relevant records
<> in the detail table, I just want to add some text into a listbox for each.
<> The text is simply the name of the part.
<>
<> I tried (but got stuck on...):
<>
<> with MainForm.DetailTable do
<> begin
>< { get the current PRODUCT_ID in the master table }
<> product := MasterTable.FieldByName('PRODUCT_ID').AsInteger;
<> { search detail table by the secondary index - just PRODUCT_ID }
<> IndexName := 'PRODUCT_INDEX';
<> while FindKey([product]) do { find a part }
<> begin
<> PartName := FieldByName('PART_NAME').AsString; { get the name }
<> ListBoxParts.Items.Add(PartName); { add name to
<listbox } > end;
<> end;
<>
<> When I step through this in the debugger it finds the first relevant PART,
<> and I can see the correct name in the variable, but in the while loop it
<> keeps finding the same record, and therefore goes on forever.
<>
<> I've tried various combinations of SetKey/GotoKey, but haven't had any
<> success, and I really can't work out why it works when _deleting_ parts
<> but not when simply accessing their name to add to a listbox. Is it
<> something to do with where the table cursor is pointing, and the delete
<> removing the record being pointed to?
<>
<> (Please note, I _don't_ want to have a DBListbox, as I will want to remove
<> items from the listbox as and when appropriate without affecting the
<> database, and also I will want to later select a subset of the parts)
<>
<> The first procedure lives on the form where the TTable and TDataSource
<> components are, and the second lives on another form. The main form unit
<> is in the uses clause of the child, but I don't feel that it's because
<> it's not on the same form, as I feel that I've accounted for this - it all
<> compiles OK, so the relevant components can be 'seen' by the child form.
<> I'm not sure of anything anymore :-o
<>
<> I've been pulling my hair out over this one, and don't fancy being bald,
<> so please can you send some refreshing ideas?
<>
<>
Hi Chris!
I'm not sure that this is going to solve your problem, but my problem was
the same, I couldn't search in the second Index!
Becouse in the DatabaseDesktop you must put the * Key infront of all the
index you whant to search in! Maybe this will help you!
/Ahammar