Problems wilth filtering records and/or GotoNearest

[Mark]

Using Delphi 2006, MySQL 5 and Corelab DBExpress drivers.

My keyfield has the following data:

99999
B 99999
B-99999
B99999

This is in the correct sorting order. My "search value" is B21600, which
means I need the B99999 record.

My first attempt was using GotoNearest, which should give me the correct
record. To my surprise it gives B-99999 as found record.

Ok, next try. Let's use a filter: "search value" <= "keyvalue", so B21600 <
keyfield. This should give a record count of 1 based on my list, because
only the last record is true. But no, recordcount is 2 and the first record
in the set is again the B-99999 record.

I tried this with both a TSimpleDataset and a
TSQLTable-TDataSetProvider-TClientDataSet combination. With the
poRetainServerOrder property set to true and to false. But all give the same
result.

What am I missing here ? It all works correct if I leave out the B-99999
record.

The work around is of course looping through the table and search for the
record myself, but I rather would use something like GotoNearest...

Is this Delphi related or driver related ?

[yannis]

Post by Mark
Using Delphi 2006, MySQL 5 and Corelab DBExpress drivers.
99999
B 99999
B-99999
B99999
This is in the correct sorting order. My "search value" is B21600,
which means I need the B99999 record.
My first attempt was using GotoNearest, which should give me the
correct record. To my surprise it gives B-99999 as found record.

corectly so what makes you thing that the character '9' is closer to
the character '2' than the character '-' ?
ord('-') = 45, ord('2') = 50 and ord('9') = 57 why would GotoNearest
select 57 as closer to 50 than 45?

Post by Mark
Ok, next try. Let's use a filter: "search value" <= "keyvalue", so
B21600 < keyfield. This should give a record count of 1 based on my
list, because only the last record is true. But no, recordcount is 2
and the first record in the set is again the B-99999 record.

the filter should be 'B21600' < keyfield are you sure you have included
the quotes?

Post by Mark
I tried this with both a TSimpleDataset and a
TSQLTable-TDataSetProvider-TClientDataSet combination. With the
poRetainServerOrder property set to true and to false. But all give
the same result.
What am I missing here ? It all works correct if I leave out the
B-99999 record.
The work around is of course looping through the table and search for
the record myself, but I rather would use something like
GotoNearest...

No just use the gotoNearest and if the found keyValue is smaller than
the one you are searching for then goto to the next record.

[Mark]

Post by yannis

Post by Mark
My first attempt was using GotoNearest, which should give me the
correct record. To my surprise it gives B-99999 as found record.

corectly so what makes you thing that the character '9' is closer to
the character '2' than the character '-' ?
ord('-') = 45, ord('2') = 50 and ord('9') = 57 why would GotoNearest
select 57 as closer to 50 than 45?

That is not what the help says:

Call GotoNearest to position the cursor on the record that is either the
exact record specified by the current key values in the key buffer, or on
the first record whose values exceed those specified. If there is no record
that matches or exceeds the specified criteria, GotoNearest positions the
cursor on the last record in the dataset.

And it works perfectly just as the helps says as long as the B-99999 value
is not in the table. Every GotoNearest works as the help describes. It finds
the exact value or positions on the first exceeding record. But as soon as I
add B-99999 in there, it fails.

But to make it even more interesting. I wanted to test your assumption on
how GotoNearest would work. I added a value B19999 into the table. So
according to your explanation I should get B19999 instead of B99999. But
what do I get ? B-99999 again !!!

Sorting order should be

99999
B 99999
B-99999
B19999
B99999

Well, when I check the database with a querybrowser, I see this exact
ordering.

My TSimpleDataSet has IndexFieldNames equal to the fieldname. So I would
expect the same ordering. But when displaying the data in a grid or when
looping through the dataset by code and outputting the value, I get the
following order !!!

99999
B 99999
B19999
B-99999
B99999

Post by yannis

Post by Mark
Ok, next try. Let's use a filter: "search value" <= "keyvalue", so
B21600 < keyfield. This should give a record count of 1 based on my
list, because only the last record is true. But no, recordcount is 2
and the first record in the set is again the B-99999 record.

the filter should be 'B21600' < keyfield are you sure you have included
the quotes?

Yes, the quotes were there. My actually code is:

Filter := QuotedStr('B21600') + ' <= RDQPCODE'

where RDQPCODE is the name of my field (obviously).

[Mark]

Just to see what is going on with ordering I entered some more values in two
different steps. It gets really strange.

I addes some more values. B29999, B39999 and B89999

So actual sort order would be

99999
B 99999
B-99999
B19999
B29999
B39999
B89999
B99999

The order I get in my DataSet (being it TSimpleDataSet, TSQLTable or
TClientDataSet) is:

99999
B 99999
B19999
B29999
B39999
B89999
B-99999
B99999

Now adding B1-9999, B3-9999 and B-9999 (notice one 9 less then I already
had) gives in my Dataset:

99999
B 99999
B19999
B1-9999
B29999
B39999
B3-9999
B89999
B-9999
B99999
B-99999

as sorting order in my DataSet. I am completely lost now...

[Mark]

Using Delphi 2006, MySQL 5 and Corelab DBExpress drivers.

I have been experimenting with my sorting order problem this weekend. The
only way I can get a correct order is to use TSQLQuery together with a
TDataSetProvider, with option RetainServerOrder, and a ClientDataSet.

But as soon as Delphi tries to interfere with the data (one way or the
other), things get messed up.

For example: I need to use GotoNearest. This means I need to set an index on
my ClientDataSet.

As soon as I add an index, as a result Delphi orders on the clientside (is
this assumption correct ?) and this ordering messes up the sortorder.

Right sort order:

B-9999
B-99999
B89999
B99999

Sort order when specifying index:

B89999
B-9999
B99999
B-99999

It looks like the "-" is being ignored. Because that would lead to:

B89999
B9999
B99999
B99999

And that is once again correct.

So what could cause Delphi to ignore (?) the "-" sign ?