vfp5 index recreated in v6 or v7 does not work

[bcraig]

I have a vfp5 project that I am trying to port to v7. My problem is with a simple index. When I reindex my file in v7, my search results in a "no find". If I go back into v5 and reindex, my searches work. The searches work fine in v7 as long as the index is not rebuilt.

details:

database structure

fieldname: text
type: character
length: 50

record contents like:

1) Support
1.1) Email
1.2) News
2) Billing
2.1) Hardware
2.1.1) NIC

index on SUBSTR(text,1,AT(&quot",text)-1) to item

created in vfp5
seek "2.1.1" && recno 6

in vfp7
seek "2.1.1" && recno 6
reindex
seek "2.1.1" && no find

Any suggestions?

 

[rgbean]

When you say you "reindex", do you mean
REINDEX
or
DELETE TAG ITEM
index on SUBSTR(text,1,AT(&quot",text)-1) to item

Rick

 

[DSummZZZ]

O.k., I was able to duplicate your scenario, well not exactly as I am not running VFP7, I am running VFP6. However, this is what I did with a table containing a text field named text, 20 characters long having some fields with 2.1.1 as the first 5 characters:

go top
index on SUBSTR(text,1,whr()) to item
?seek ('2.1.1') &&... reports .T.
go bott
index on SUBSTR(text,1,whr()) to item
?seek ('2.1.1') &&... reports .F.
go top
reindex
?seek ('2.1.1') &&... reports .T.
go bott
reindex
?seek ('2.1.1') &&... reports .F.

There is something sticking out at me here but I don't quite have it pinned down yet.

Dave S.

 

[DSummZZZ]

By the way, I made a function called whr() just to eliminte the AT(&quot",text)-1) function from the index command:

FUNCTION whr

RETURN AT(&quot", text)-1

Dave S.

 

[bcraig]

rgbean - I am not deleting the tag item and physically recreating the index...I am simply issuing the REINDEX command.

 

[rgbean]

You always run a risk when using a UDF() as an index expression - especially those that are data dependent. (e.g. Your index will fail if any of the text fields don't have a &quot" in it.) Also, when an index goes bad then the header (where the index expression is stored) is just as likely to be bad as the other data and then a REINDEX can't possibly get it right. While actually recreating an index in VFP when using databases with presistant relationships and RI code makes it really non-trivial, using the information in a GENDBC output or using the Stonefield Database Toolkit can make it easier.

Note: While I haven't spent enough time to actually discover the cause of your problem, perhaps you can consider alternate solutions. (e.g. parsing the "section" data at entry time and store it in one or more fields that can be unconditionally indexed.)

Rick

 

[chpicker]

The INDEX expression operates using the current record to determine the length of the result. Depending on WHICH RECORD is the current record, the result of the index file varies.

For example, if you INDEX while at EOF(), the INDEX command returns an error message. If you issue in INDEX command while on the first record (such as when the table is first opened) you end up with 6 index keys that look like this:

"1"
"1"
"1"
"2"
"2"
"2"

If you issue the INDEX command while on the 3rd record, you end up with the following index keys:

"1  "
"1.1"
"1.2"
"2  "
"2.1"
"2.1"

Finally, if you happen to be on the final record, then your index keys will look like this:

"1    "
"1.1  "
"1.2  "
"2    "
"2.1  "
"2.1.1"

Indexes are not variable-length. They are determined immediately when you execute the INDEX command. Any expression longer than that determined length is truncated.

 

[DSummZZZ]

Rick,

You are absolutely right, I don't use UDF's unless I have to. I was just trying to eliminate the (&quot"... scenario for the AT() function from getting into the index header. It obviously didn't make a difference. It mattered more as to what the current record was.

Dave S.

 

[rgbean]

Good catch on the variable length index keys. While I'm always aware of a similar limitation in UDF()s (or any FP function that can return a variable length) in SQL SELECTs, I'd forgotten that the same logic applied to indexes.

Rick

 

[bcraig]

Thank-you

Positioning my record pointer on the longest occurence of characters before the ) before issuing the reindex command worked like a charm.

 

[rgbean]

Note, you could also add a PADR() function to make things easier, just make it as long as you expect the expression to ever be. e.g.
index on PADR(SUBSTR(text,1,AT(&quot",text)-1),7) to item

Rick