Invalid key length when increasing character type field to near maximum length

[Dan Musicant]

Why does this happen? I have a character field with a simple index on it
and if I try to modify the structure to make the field anywhere near the
maximum of 254 characters for a character type field I get errors :
Invalid key length. I have to reduce the number of characters to not get
the error. I have no idea just how many characters I have to reduce it
from 254 to not get errors, it's a total mystery to me. I'm using VFP
9.0 here. Been having this problem for a while. Anyone know what may be
afoot? The field is named "desc" and the index is achieved thusly:

INDEX on desc TAG desc (i.e. as simple as can be)

Dan


Email: dmusicant at pacbell dot net

[JayB]

it does not serve a purpose to have an index on a long field.
the first 50 or so chars should do it.
try
INDEX on left(desc,50) TAG desc
i also use
INDEX on upper(left(desc,50)) TAG desc

Post by Dan Musicant
Why does this happen? I have a character field with a simple index on it
and if I try to modify the structure to make the field anywhere near the
Invalid key length. I have to reduce the number of characters to not get
the error. I have no idea just how many characters I have to reduce it
from 254 to not get errors, it's a total mystery to me. I'm using VFP
9.0 here. Been having this problem for a while. Anyone know what may be
INDEX on desc TAG desc (i.e. as simple as can be)
Dan
Email: dmusicant at pacbell dot net

[Dan Freeman]

Foxpro has never been particularly graceful when operating at the edges
of its limits.

Index expressions are evaluated before they're stuffed into the Btree,
and the evaluation isn't a 1:1 character length equivalent. Sometimes
an expression can evaluate to longer than the 100-byte limit on index
expressions.

We've all had complex index expressions that work just fine until a
data condition makes the expression evaluate to somethimg the index
engine can't handle. This is the same thing.

The limit isn't on the length of the data field, it's on the evaluation
of the index expression and that evaluation is a bit fluid.

Dan

Post by Dan Musicant
Why does this happen? I have a character field with a simple index on it
and if I try to modify the structure to make the field anywhere near the
Invalid key length. I have to reduce the number of characters to not get
the error. I have no idea just how many characters I have to reduce it
from 254 to not get errors, it's a total mystery to me. I'm using VFP
9.0 here. Been having this problem for a while. Anyone know what may be
INDEX on desc TAG desc (i.e. as simple as can be)
Dan
Email: dmusicant at pacbell dot net

[Gene Wirchenko]

Post by Dan Musicant
Why does this happen? I have a character field with a simple index on it
and if I try to modify the structure to make the field anywhere near the
Invalid key length. I have to reduce the number of characters to not get
the error. I have no idea just how many characters I have to reduce it
from 254 to not get errors, it's a total mystery to me. I'm using VFP
9.0 here. Been having this problem for a while. Anyone know what may be
INDEX on desc TAG desc (i.e. as simple as can be)

^^^^ ^^^^
"desc" is a reserved word.

The limit is not 254. From the docs:

"Maximum # of bytes per index key in a non-compact index3.
100

Maximum # of bytes per index key in a compact index3.
240

...

3 If the collating sequence is set to MACHINE, each character uses one
byte. If the collating sequence is not set to MACHINE, each character
uses two bytes. If the indexed field supports null values, one
additional byte is used in the index key. Note that non-machine
indexes are always compact."

Sincerely,

Gene Wirchenko