Hello,

I have a loop where I am comparing items in a StringList to records in a
filtered dataset(using SetRange).

The loop has two purposes.  Delete anything from the dataset that is not
in the string list, and remove everything from the string list that is
already in the dataset.  I need the string list to have all of the items
not in the dataset by the end of the loop.

Here is the basic loop:

  while Table.Eof = false do
  begin

    if StringList.Find(Field.AsString, FoundIndex) = true then
    begin
      StringList.Delete(FoundIndex);
      Table.Next;
    end
    else
    begin
      Table.Delete;
    end;
  end;


The problem I have found is that Eof is not set after deleting the last
record in the range.  This is really bad in my case since when the loop
repeats one more time it will start deleting all of the records in the
range until it empties the dataset.

I think I need to check if the current record is the last record after
calling Delete.  But how do I check that?  I saw a similar question that
Clive posted back in May 2006.  But in that case it looks like the
solution was to just move last directly instead of checking.  I don't
see how that would help here.

The only idea I have is to move Last before the loop and get the
bookmark of the last record.  Then I can check the bookmarks before the
call to delete.  That or get the number of records in the range and
change my outer loop from a while loop to a for loop.

Thanks,

Mark Elder

"Mark Elder" <Mark.Elder@uaar.net> wrote in message
news:CB301A1F-FF8B-4824-88EC-773647E98221@news.elevatesoft.com...
> Hello,
>
> I have a loop where I am comparing items in a StringList to records in a
> filtered dataset(using SetRange).
>
> The loop has two purposes.  Delete anything from the dataset that is not
> in the string list, and remove everything from the string list that is
> already in the dataset.  I need the string list to have all of the items
> not in the dataset by the end of the loop.
>
> Here is the basic loop:
>
>   while Table.Eof = false do
>   begin
>
>     if StringList.Find(Field.AsString, FoundIndex) = true then
>     begin
>       StringList.Delete(FoundIndex);
>       Table.Next;
>     end
>     else
>     begin
>       Table.Delete;
>     end;
>   end;
>

Turn it arround. Loop thru the stringlist and on a table.locate = true
delete the stringlist entry. At the end only the records that are not in the
stringlist are left and you can delete all of them.

I could ofcourse ask if the result in the stringlist itself came from a
query

Frans van Daalen wrote:
> Turn it arround. Loop thru the stringlist and on a table.locate = true
> delete the stringlist entry. At the end only the records that are not in the
> stringlist are left and you can delete all of them.
>
> I could ofcourse ask if the result in the stringlist itself came from a
> query


Thanks for the suggestion.  Unfortunately I am doing the lookups this
way for performance reasons.  I was originally doing the lookup in the
dataset.  However the StringList has fewer items compared to the dataset
and the lookup is much faster.  Also the normal case is that everything
is already in the dataset and everything will be deleted from the string
list.  So as the loop progresses the StringList lookups get faster and
faster.

The string list is actually filled with a list of files from iterating a
directory.  The files are parsed and have some information loaded into a
table.  This code is checking timestamps on the files to see if anything
needs to be updated.  It also checks for new files that have not been
parsed yet and checks to see if anything is in the table that does not
exist on disk anymore.

Thanks,

Mark

Mark,

<< The problem I have found is that Eof is not set after deleting the last
record in the range.  This is really bad in my case since when the loop
repeats one more time it will start deleting all of the records in the range
until it empties the dataset. >>

Your title is a bit of a misnomer - when you say "last record" what you mean
is the last positional record, not the actual last record in the range.
That is, there are still records present in the range due to the Next.

<< However the StringList has fewer items compared to the dataset and the
lookup is much faster >>

It shouldn't be as long as there is an index available for a FindKey or
Locate call.  These are binary searches in such a case, whereas the string
list Find call is a brute-force search.  My suggestion would be to do as
Frans recommends and flip things around.  There's really no other accurate
way of dealing with the issue that I can think of.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Mark,
>
> << The problem I have found is that Eof is not set after deleting the last
> record in the range.  This is really bad in my case since when the loop
> repeats one more time it will start deleting all of the records in the range
> until it empties the dataset. >>
>
> Your title is a bit of a misnomer - when you say "last record" what you mean
> is the last positional record, not the actual last record in the range.
> That is, there are still records present in the range due to the Next.

I don't think that is the case.  This happens when I try and delete the
last record in the range.  My understanding is that the equivalent of
"Next" happens after Delete.  Thus in a delete loop you do not need to
call next explicitly, and if you do call next you will skip records.

However, if you call delete when you are on the last record then the
previous record will be the current record and EOF will not be set.

Take this code for example.  You may expect it to clear out everything
except the first record.  When actually it will delete all records from
the table.


  MyTable.First;
  MyTable.Next;  // Move to the second record
                 // so that the first will not be deleted.

  while not MyTable.EOF do
  begin
    MyTable.Delete;
  end;

  // MyTable.RecordCount is now zero.

At any rate this appears to be standard Dataset behavior, or at least
posts on Borland's newsgroups seem to indicate this is the case.  DBISAM
has so many nice features that the standard TDataset does not.  I was
hoping that maybe there was a way to check for the last record before
EOF was actually set.

At any rate it's not that big of a deal.  I have replaced the while loop
with a straight forward For loop using the RecordCount of the range.  It
looks a little strange but it seems to work.  If you can think of a
reason not to take this approach please let me know.


> << However the StringList has fewer items compared to the dataset and the
> lookup is much faster >>
>
> It shouldn't be as long as there is an index available for a FindKey or
> Locate call.  These are binary searches in such a case, whereas the string
> list Find call is a brute-force search.  My suggestion would be to do as
> Frans recommends and flip things around.  There's really no other accurate
> way of dealing with the issue that I can think of.
>

Well actually the StringList is sorted in the same order as my Dataset.
 The collation order does not match exactly but it is close enough that
most of the time the record being searched for is at the top of the
list.  So the find operation ends up being very fast - basically
constant time.

Also this allows for just one pass through the list.  If I flip it
around I end up needing to run another pass on the string list to find
the rows that really need to be deleted.

It's been a year since I worked on cleaning this up. But as I remember
the difference in speed was significant.  Nothing wrong with DBISAM -
it's very fast.  It's just hard for any type of index search in the
middle of a loop to compare with a single pass constant time loop.

Thanks for your input.

Also thanks to everyone who answers posts in these groups.  I have not
needed to post here before because I can always find answers to my
questions in other posts.

Since making the decision to use DBISAM I have been a very satisfied
customer.

Thanks,

Mark

"Mark Elder" <Mark.Elder@uaar.net> wrote in message
news:31E148E3-D6EB-4566-9AA8-55BA282C500A@news.elevatesoft.com...

> Also this allows for just one pass through the list.  If I flip it around
> I end up needing to run another pass on the string list to find the rows
> that really need to be deleted.
>

What you can do is construct a delete with a IN clause. That way you while
running try the thru the stringlist you construct this IN clause, if however
there are more then few 100 items then you need to split it.

somethink like

// warning bad code habits ahead

s_del : Array[0..300] of String;

// to construct the delete in clause

For I:=0 to Stringlist.count-1 do
Begin
  s_del[del_i div 250] := s_del[del_i div 250]+',"'+stringlist[i]+'"';
  del_I := del_i + 1;
end;

// to delete from database

For I:=0 to (del_i div 250) do
Try
  s_del[i][1] :=char(32); // remove the first ,
  cmdIm.CommandText := 'DELETE <table> where <field> IN ('+s_del[i]+')';
  CmdIm.Execute;
except
end;

//----------------------------------

Mind you that if you would save the results from the filescan in a temp
datafile all of this could be done much easier. Both the removal from the
temp file and the removal from the "real file" could then be done with 2 sql
statements.

>
> Since making the decision to use DBISAM I have been a very satisfied
> customer.
>
left it here for anyone to read

Mark,

<< I don't think that is the case.  This happens when I try and delete the
last record in the range. >>

It can't be - if there's no records left in the range after a delete then
both BOF and EOF will be set, otherwise neither will be set.

<< My understanding is that the equivalent of Next" happens after Delete. >>

Not exactly.  DBISAM just positions itself on the nearest matching record,
which may be the prior record in some cases.

<< However, if you call delete when you are on the last record then the
previous record will be the current record and EOF will not be set. >>

Correct, the only two actions that set EOF are trying to navigate past EOF
using Next or calling Last.

<< At any rate it's not that big of a deal.  I have replaced the while loop
with a straight forward For loop using the RecordCount of the range.  It
looks a little strange but it seems to work.  If you can think of a reason
not to take this approach please let me know. >>

The only downside would be the possibility that records are added while
processing that change the RecordCount.  But, if this isn't possible
(single-user, etc.) then there isn't really any issue.


<< Well actually the StringList is sorted in the same order as my Dataset.
The collation order does not match exactly but it is close enough that
most of the time the record being searched for is at the top of the list.
So the find operation ends up being very fast - basically constant time. >>

Well, if you can be assured that the item being searched for is always at
the top of the list, then yes, that would be fairly quick.

<< Since making the decision to use DBISAM I have been a very satisfied
customer. >>

I'm glad to hear that.


--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Not exactly. DBISAM just positions itself on the nearest matching record,
>which may be the prior record in some cases.

Is this just when there's a range set, or always?

I, like many others, use constructs like

while not xx.eof do begin
 if condition then xx.delete else xx.next;
end;

and you seem to be saying this is unsafe.

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:E7CD99F0-3298-46D9-B4EA-B81E201549D6@news.elevatesoft.com...
> Tim
>
>>Not exactly. DBISAM just positions itself on the nearest matching record,
>>which may be the prior record in some cases.
>
> Is this just when there's a range set, or always?
>
> I, like many others, use constructs like
>
> while not xx.eof do begin
> if condition then xx.delete else xx.next;
> end;
>

I think that will always work

1
2
3  << last record, delete

when you are on 3, you delete it, which puts you back on 2, which does not
meet the delete criteria so you do a next and get eof.

Robert

Robert


Of course just an extra .Next needed - brain in Xmas mode obviously

Roy Lambert