Index on Buffered table

[samoody]

I'm having a problem with a SEEK in a buffered table. (VFP 8.0)

The SEEK is returning a .T., but the record that is found, isn't matching the expression being seeked. The SEEK command is being performed hundreds of times (it is in a loop), but this problem only occurs once.

The table in which I'm SEEKing is buffered (optimistic row), but none of the buffered changes are to any fields in the indexed expression. When I don't buffer the table, the SEEK performs without a problem.

I have checked all my SET settings (COLLATE, EXACT, NEAR, etc.) and I can't find anything that helps me.

Does anyone have any insight into this problem?

 

[rgbean]

Since SEEK() (and most table functions) work with the data actually on disk, the uncommitted changes in a buffered table aren't seen. You'll need to "save" the changes before checking for them.

Rick

 

[samoody]

This is so, even if the buffered changes are not to any of the fields in the index expression?

 

[Mike Lewis]

samoody,

Not sure if this helps, but bear in mind that SEEK moves the record pointer, and moving the record pointer will commit any unsaved edits in the row buffer. Maybe that's not relevant in this case, given that the field you are seeking is not one that is edited, but it might be worth keeping in mind.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[BPeisch]

Rick, SEEK is NOT one of the functions that work with data on disk rather than in the buffer. SQL SELECT is the only command I know of that does (besides ones that work directly with disk files like APPEND FROM).

Samoody, I'm not aware of any issues between SEEK and buffering, unless, as Mike points out, moving the record pointer is causing your changes to be committed. If this is not the case, you can check what's stored in the actual index expression by doing this:

* Start by positioning the record pointer to the record chosen by the bad SEEK
x = order()
? &x

Compare the results show with the expression you're using in your SEEK? Do they match?



-BP

http://www.peisch.com

 

[longda]

Maybe you should try recreating the index for the table. May be this would help you out.

Dave L.

 

[rgbean]

Barb,
You are right - I really shouldn't be answering questions when I'm busy - I tend to get the "easy" ones wrong when I'm distracted! :-(

Samoody,
You said you checked those settings, but is this a private datasession, and did you set them again? Many SETs get reset - see the Help topic "SET DATASESSION Command" for a complete list.

Rick

 

[samoody]

Thanks for all your posts... but I'm still not able to solve my problem. I have narrowed it down some....

Below is a paraphrase of my code.


SCAN && table1
SELECT table2
LOCATE
IF SEEK(table1.field1 + table1.field2)
REPLACE field3 WITH table1.field3 + field3,;
field4 WITH table1.fiedl4 + field4
ENDIF
SELECT table1
ENDSCAN


Both TABLE1 and TABLE2 are buffered (5) and contain pending changes but not to fields in the index expressions. About halfway through the SCAN, the REPLACE is altering the index somehow.

Through the debugger, I have stopped my code after the SEEK at the 'problem' record. At this record, if I do a SEEK of a certain expression (not related to the current record in TABLE1 but another record in TABLE1) before the REPLACE, it will find the correct record.

In my test, I move the record pointer back and then run the REPLACE. After executing the REPLACE, if I try and SEEK this same expression again, it will not find the same record.

The problem doesn't surface until approximately half the records have been scanned. Also replaced data for one record is somehow screwing up the index for another record.

I am baffled.

 

[Mike Lewis]

samoody,

You said earlier that the problem goes away if you switch off buffering. Is there any reason why you need buffering in this instance? If not, turning it off would let you avoid the problem, although it would be better if you can find the underlying cause and eliminate it.

Apart from that, I can only echo Dave L's suggestion that you try deleting and recreating the index.

I can see why you're baffled.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk