Page 1 of 1
unsorting TZQuery breaks Bookmarks
Posted: 17.01.2011, 14:35
by marsupilami
Hello Zeospeople,
first thanks for this library. It helps me a great deal in a lot of projects.
For the Problem:
The following code fails with the Exception "Bookmark was not found." Is that a bug or are bookmarks not meant to survive sorting and unsorting?
Code: Select all
List := TStringList.Create;
try
ZQuery1.Open;
ZQuery1.First;
ZQuery1.SortedFields := 'ID';
while not ZQuery1.Eof do begin
List.Add(ZQuery1.Bookmark);
ZQuery1.Next;
end;
ZQuery1.SortedFields := '';
for x := 0 to List.Count - 1 do
ZQuery1.Bookmark := List.Strings[x];
finally
FreeAndNil(List);
end;
The Zeos version used is 6.6.6. Note that this is just test code. In the real application I want to do a binary search, after unsorting, using the bookmarks.
Thanks for helping,
Jan
Posted: 17.01.2011, 20:53
by josimarz
Hello marsupilami!
Use TList structure to store Pointer types and use
GotoBookmark method:
Code: Select all
List := TList.Create;
try
ZQuery1.Open;
ZQuery1.First;
ZQuery1.SortedFields := 'ID';
while not ZQuery1.Eof do begin
List.Add(ZQuery1.Bookmark);
ZQuery1.Next;
end;
ZQuery1.SortedFields := '';
for x := 0 to List.Count - 1 do
ZQuery1.GotoBookmark(List.Items[x]);
finally
FreeAndNil(List);
end;
Bye!
Posted: 18.01.2011, 15:14
by marsupilami
Hello josimarz,
thank you for your help. Unfortunately this does not work too. I get the same error at the same element. When I remove the line
[font=Courier New]ZQuery1.SortedFields := '';[/font]
everything works fine. So unsorting ZQuery1 must be changing the bookmarks for some records.
Best regards,
Jan
Posted: 18.01.2011, 19:13
by josimarz
Hello marsupilami!
I understand that when you order the result, the
Query lose your references and create new references.
Once I have problems to compare the bookmarks of a query with a structure previously stored pointers. I got satisfactory result when I tried the the pointer as a pointer to integers.
I would like you to perform the following test:
Code: Select all
uses
DB, Types;
{...}
List := TList.Create;
try
ZQuery1.Open;
ZQuery1.First;
ZQuery1.SortedFields := 'ID';
while not ZQuery1.Eof do begin
List.Add(ZQuery1.Bookmark);
ZQuery1.Next;
end;
ZQuery1.SortedFields := '';
for x := 0 to List.Count - 1 do
begin
ZQuery1.First;
while not ZQuery1.Eof do
begin
if PInteger(List.Items[x])^ = PInteger(ZQuery1.Bookmark)^
ShowMessage('EQUALS')
else
ShowMessage('NOT EQUALS')
ZQuery1.Next;
end;
end;
finally
FreeAndNil(List);
end;
First we must find out if this really is a failure to Zeos.
Let me informed of the news.
Josimar
Posted: 25.01.2011, 09:31
by marsupilami
Hello Josimar,
I am sorry for the long delay it took me to answer. I had to modify your code because my test data consists of 65000 rows. I also added a check for comparing the ID-Field to make sure that the bookmark still belongs to the same row. So this is the code that I tested:
Code: Select all
var
BookMark: String;
BookmarkList: TList;
x: Integer;
Found: boolean;
FoundCount, NotFoundCount, EqualPKs: integer;
PKList: TStringList;
Ptr1, Ptr2: Pointer;
a, b: Integer;
begin
FoundCount := 0;
NotFoundCount := 0;
EqualPKs := 0;
BookmarkList := TList.Create;
PKList := TStringList.Create;
try
ZQuery1.Open;
ZQuery1.SortedFields := 'ID';
ZQuery1.First;
while not ZQuery1.Eof do begin
BookmarkList.Add(ZQuery1.GetBookmark);
PKList.Add(ZQuery1.FieldByName('ID').AsString);
ZQuery1.Next;
end;
ZQuery1.SortedFields := '';
Memo1.Lines.Add('finished building the list');
Application.ProcessMessages;
for x := 0 to BookmarkList.Count - 1 do
begin
Found := false;
ZQuery1.First;
while ((not ZQuery1.Eof) or (not Found)) do
begin
Ptr1 := PInteger(BookmarkList.Items[x]);
Ptr2 := ZQuery1.GetBookmark;
a := PInteger(Ptr1)^;
b := PInteger(Ptr2)^;
if a = b then begin
Found := True;
if PKList.Strings[x] = ZQuery1.FieldByName('ID').AsString then inc(EqualPKs);
end;
ZQuery1.FreeBookmark(Ptr2);
ZQuery1.Next;
end;
if not Found then begin
Memo1.Lines.Append(IntToStr(x) + ': not found');
inc(NotFoundCount);
end else begin
Memo1.Lines.Append(IntToStr(x) + ': found');
inc(FoundCount);
end;
Application.ProcessMessages;
end;
Memo1.Lines.Append('Summary: ' + IntToStr(FoundCount) + ' found, ' + IntToStr(NotFoundCount) + ' not found, ' + IntToStr(EqualPKs) + ' equal primary keys');
finally
FreeAndNil(BookmarkList);
end;
end;
The result is: Summary: 65028 found, 0 not found, 65028 equal primary keys
So all the bookmarks seem to be there and they still belong to the same rows. It seems like ZQuery just can not find them...
Posted: 25.01.2011, 11:19
by josimarz
Hello marsupulami!
Exactly! For you to compare Bookmarks of sorted Query's you need to do the type casting of pointer to PInteger to scroll through the records to find, as you did in the previous example.
This technique may consume too much processing on queries with a large amount of results.
I do not know to judge whether this limitation is a failure of Zeos.
An alternative is to use the Locate method of TZQuery to select the desired record based on primary key or unique key.
Greetings,
josimarz
Posted: 01.02.2011, 16:53
by marsupilami
Hello josimarz,
this is the main problem: locate is very slow when there are large datasets (20000+ rows). It makes no difference if the dataset is sorted and i do the locate for the sorted columns. I assume that TZQuery is doing a linear search in any case.
Using a binary search algorithm is fast. But editing is very slow when the TZQuery is sorted. I assume that TZQuery resorts all the rows when I call post.
So my next Idea was to sort the TZQuery, acquire the bookmarks in a sortet order and unsort the TZQuery again to use the bookmarks for doing the binary search. But that does not work too as TZQuery is not able to find the Bookmarks although they are still valid. In my book this is a bug in TZQuery but I am not qualified to correct this as I have no clue about the inner workings of TZQuery.
So my last chance is to hope that bookmarks never break when TZQuery stays unsorted and do the sorting of the bookmarks on my own using TStringlist.CustomSort to compare the rows on my own.
Best wishes and thanks for your support,
Jan
Posted: 02.04.2011, 21:21
by mdaems
marsupilami (Jan),
Zeoslib ZQuery and Bookmark related code can be found in ZAbstractRODataset.pas and ZAbstractDataset.pas. I hope you can find out why the pointers just don't work anymore after sorting.
Please let us know if you were able to fix the code or if you found what's going wrong.