I have a table with a string column and I want to do a range on all
words starting with "abc". So I have a range like:

SetRangeStart;
FieldByName('Company').AsString := StartsWith;
SetRangeEnd;
FieldByName('Company').AsString := StartsWith+'zzzz';
ApplyRange;

For some reason it is never finding any rows. The index is case
insenstive and is a compound key. Company is the first field of the index.

Also I noticed I can't assigned

Table1.IndexFieldNames := 'Company';

even though there is an index that starts with the Company field. I have
to specify the index name instead of the index field names.

Has anyone else noticed this?

Dave

v1.02

Dave


>Also I noticed I can't assigned
>
>Table1.IndexFieldNames := 'Company';
>
>even though there is an index that starts with the Company field. I have
>to specify the index name instead of the index field names.
>
>Has anyone else noticed this?

If there are any descending columns in the table that will do it.

Roy Lambert

Roy Lambert wrote:

> Dave
>
>
>
>>Also I noticed I can't assigned
>>
>>Table1.IndexFieldNames := 'Company';
>>
>>even though there is an index that starts with the Company field. I have
>>to specify the index name instead of the index field names.
>>
>>Has anyone else noticed this?
>
>
> If there are any descending columns in the table that will do it.
>

Roy,
    Yes there is a descending column at the end of the compound index.
But this same index structure works fine on other databases and their
ranges. If ElevateDb can't do a range with a descending field in the
index, I guess I can scratch ElevateDb off my list of databases.

    I also created another index with only the one string field in it
and it too won't work properly in the Range, the range is always empty.
The field is case insensitive.

    I'm going to create another index but this time case sensitive and
see if I can get ranges to work.

Dave

Dave


What I was told was that you need to use IndexName not IndexFieldNames. Apparently its a problem with TDataSet.

Roy Lambert

Roy Lambert wrote:

> Dave
>
>
> What I was told was that you need to use IndexName not IndexFieldNames. Apparently its a problem with TDataSet.
>
> Roy Lambert
>

Roy,
   I had tried setting the index with the index name instead of index
field names. I just don't think SetRangeStart.., SetRangeEnd, and
ApplyRange are working in 1.02.

Dave

Dave


OUCH!

Roy Lambert

Dave,

<< I have a table with a string column and I want to do a range on all words
starting with "abc". So I have a range like:

SetRangeStart;
FieldByName('Company').AsString := StartsWith;
SetRangeEnd;
FieldByName('Company').AsString := StartsWith+'zzzz';
ApplyRange; >>

You're not setting the KeyFieldCount, so EDB thinks that the number of
columns being used in the range is 0.  You need to set the KeyFieldCount
property for the beginning and ending of the range.  I'm looking into
changing this for the ApplyRange and Goto* methods, but for now they require
the KeyFieldCount setting.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Dave,
>
> << I have a table with a string column and I want to do a range on all words
> starting with "abc". So I have a range like:
>
>  SetRangeStart;
>  FieldByName('Company').AsString := StartsWith;
>  SetRangeEnd;
>  FieldByName('Company').AsString := StartsWith+'zzzz';
>  ApplyRange; >>
>
> You're not setting the KeyFieldCount, so EDB thinks that the number of
> columns being used in the range is 0.  You need to set the KeyFieldCount
> property for the beginning and ending of the range.  I'm looking into
> changing this for the ApplyRange and Goto* methods, but for now they require
> the KeyFieldCount setting.
>

I tried your suggestion and it worked, sort of. A range of "x" to "xz"
will find the rows starting with "x" but will also include those
starting with "y" and "z". So it starts from the correct row but returns
all rows to the end of the table. The column is case-insensitive. Also
it takes 6 to 7 seconds to execute the range on a million row table.

Dave

Dave,

<< I tried your suggestion and it worked, sort of. A range of "x" to "xz"
will find the rows starting with "x" but will also include those starting
with "y" and "z". So it starts from the correct row but returns all rows to
the end of the table. The column is case-insensitive. Also it takes 6 to 7
seconds to execute the range on a million row table. >>

And you're sure that you're running 1.02 ?

I tried the following here with the DBDEMOS Customer table (ByCompany active
index) and it works fine:

  with Table do
     begin
     SetRangeStart;
     FieldByName('Company').AsString := 'diver';
     KeyFieldCount:=1;
     SetRangeEnd;
     FieldByName('Company').AsString := 'diverzzzz';
     KeyFieldCount:=1;
     ApplyRange;
     end;

Could you send me the table that you're using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Dave,
>
> << I tried your suggestion and it worked, sort of. A range of "x" to "xz"
> will find the rows starting with "x" but will also include those starting
> with "y" and "z". So it starts from the correct row but returns all rows to
> the end of the table. The column is case-insensitive. Also it takes 6 to 7
> seconds to execute the range on a million row table. >>
>
> And you're sure that you're running 1.02 ?

The table's engine version says "1.02 build 1".

>
> I tried the following here with the DBDEMOS Customer table (ByCompany active
> index) and it works fine:
>
>    with Table do
>       begin
>       SetRangeStart;
>       FieldByName('Company').AsString := 'diver';
>       KeyFieldCount:=1;
>       SetRangeEnd;
>       FieldByName('Company').AsString := 'diverzzzz';
>       KeyFieldCount:=1;
>       ApplyRange;
>       end;
>
> Could you send me the table that you're using ?

Arggh. Operator error. I used KeyFieldCount:=1 just once, before the
SetRangeEnd but not before the ApplyRange. I thought the ApplyRange
would use the existing KeyFieldCount value. I added the extra
KeyFieldCount and its working find now. Oh well. Stand down from red
alert.

Dave