Locate Command doesn't fetches fresh values of column

[KALP1]

I have a table with column update - c(1)
I have a opened this table in 1 vfp window with the record pointer on record no. 1 with Update = 'Y'.
Now in another VFP window I open same table and manually changed Update to 'N' .
Now in the first window, run command ->
Locate for Code = 1(which is first record.)
If found()

messagebox(Update)​

Endif
>> Still I get Update = 'Y' in messagebox though value of column 'update' has been changed from another window.

What is the reason for such behaviour ? Does it mean that I have to write Go Top command before every locate command ?

 

[Olaf Doschke]

LOCATE does start from top without GO TOP, that's not the problem. LOCATE always starts from TOP, for multiple locates continuing at the last position your code would need CONTINUE, since you use LOCATE you start from top.

You most probably have the new Value 'N' not yet saved and just in the buffer of the other VFP session. Or you locate within the local buffer of your initial VFP session. Either way I stringly assume buffering is in your way to see the DBF.

What would reliably get the DBF value is SQL-Select: SELECT update from table WHERE code=1. If that is unexpected, perhaps even blank and neither 'Y' nor 'N', you most probably have buffering as a default for your VFP IDE for all workareas. That's possible via CURSORSETPROP of Buffering for workarea 0 and then must be taken into account from any code.

Bye, Olaf.

 

[Mike Lewis]

Without prejudice to what Olaf says above, I wonder if the solution is simply to move the record pointer in the second instance. Within a Browse window (which I assume is what you are using), the record does not get updated until you move to another record (or close the table, or explicitly update the buffers).

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

With record buffering a skip 1 could work, sure. But it's also not clear which VFP session buffers. Even if the other session saves, the initial session will read its locally buffered 'Y' and would get a conflict, when trying to tableupdate the buffer.

What's also true is - I think - just like any UI also the browse window will only store your change when you move from the cell, as long as you type in a browse window cell even without buffering, you don't yet edit the dbf file, that is only updated ehen you move to another cell, even of the same record. In the end the browse also has textboxes and those only save back to the underlying dbf after valid is passed, an active cell only changes its own control.Value, not the dbf row/column.

PS: Just checked. Even explcitly setting buffering off before opening any DBF via CURSORSETPROP('Buffering',1,0) I only get the changed value in a secondary IDE session, if I move from the edited cell in the editing session. You only edit a DBF when leaving a cell, even in unbuffered mode. And the changed value only gets visible, if the browse is refreshed, ie clicking on the cell, but that's only a browse refresh problem, LOCATE will already see the changed value, once you leave the cell of the original edit.

Bye, Olaf.

 

[KALP1]

Olaf, Buffering is off and SQL statement is giving correct value i.e Update = 'N' . But when I use just locate command it fetches old value of Update as my record pointer's position is not changed. When I run Go Top before locate command , it gives correct value.

 

[Olaf Doschke]

OK, sql statement giving correct 'N' and locate not, indicates your initial IDE session has the data buffered and so LOCATE sees the buffered 'Y' ,while SQL sees the DBF stored 'N'.

You have to look for buffering states in both IDE sessions. Not only in the one changing the update field to N.

So when you're in the state of having 'Y' in one and 'N' in the other IDE, please report what CURSORGETPROP("Buffering","yourtable") is for both IDEs.

Besides, your solution GO TOP indicates the IDE is buffering and stores it's buffered value by the GO TOP. It's change might get refused as conflict you swallow somehow, because your buffered state is still Y and GO TOP will try to save 'Y' on the changed 'N', but fails to do so. Finally LOCATE then sees the 'N', but only after your GO TOP reverted the buffered state.

Bye, Olaf.

 

[Olaf Doschke]

One way to check my theory would be to USE yourtable AGAIN before locate and locting in there, I bet you'll then see 'N' in that freshly opened workarea and 'Y' in the old one.

Bye, Olaf.

 

[KALP1]

I am not browsing window. Actual scenario goes like this -> 1 user opens 2 instances of an application . In 1 instance a table is opened and record pointer is at the top with update = 'Y'. In another instance user edits some data, so update field becomes 'N' and comes to the first instance. I have 1 program to update different tables based on Update field i.e. each time it checks whether update is 'Y' or 'N'. As soon as update becomes 'N' , some updations takes place and user view correct data. For that I have code as below
********************
Locate for code = codev
If found()

if Update = 'Y'​

return && Nothing to do​

Else​

*- Call your Updation Procedures here​

Do .....​

Endif​

Endif
********************
Now in the first instance Record pointer is at the top and Locate command does not move to next record as Codev = 1(1st Record) in the above code. So in the second line, Value of Update fetched comes 'Y' and no updation takes place . I have buffering off.
I tried Go Top before locate command, now It fetches update = 'N'.
My Problem is just reverse. Browse window is fetching correct values but locate is not fetching correct values.

 

[KALP1]

Yes, olaf
Use Tablename Again is fetching correct result

 

[KALP1]

My purpose of asking is that wherever I use locate will have to use table again in my application before locating?

 

[KALP1]

CursorgetBuffering("Bufffering",tablename) -> is returning value 1 in both the IDEs

 

[Olaf Doschke]

No, you should neither need to GO TOP nor use a table again, but you still have some buffering problem. Interesting both IDEs have Buffering 1 - that means no buffering. I don't know what else to look into causing different states of data. At least we know the new value is stored in the DBF file. Do you LOCATE in the correct workarea, ie do you SELECT table before LOCATE?

Bye, Olaf.

 

[KALP1]

Yes, I have selected correct workarea. To simulate the situation,

*- In 1st IDE
Create Table Update_Table Free (Code n(6),Update c(1))
for i = 1 to 50

Append Blank​

Replace code with i,Update with 'Y'​

ENDFOR
USE DBF() SHARED
LOCATE FOR code = 1
messagebox(Update)
*** Values Comes To 'Y'

*- Now in 2nd IDE
USE Update_Table Shared
LOCATE FOR code = 1
replace update WITH 'N'
messageb( CURSORGETPROP("Buffering","Update_table")) && Returns 1
USE


*- Now Again in 1st IDE

SELECT Update_table
LOCATE FOR code = 1
messagebox(Update)
messageb( CURSORGETPROP("Buffering","Update_table")) && Returns 1
*** Values again Comes To 'Y' although changed in 2nd IDE

 

[Olaf Doschke]

OK, I see. I get the same erroneous behaviour.

I checked, whether FLUSH FORCE helps flushing out the cache, but even applying that on both sides didn't change behaviour. Also SYS(1104), also enforcing memory usage reset via SYS(3050,1,1) and back to a normal size. We already know the data is in the DBF. And as the second IDE closes the table, all caches of IDE2 are flushed out. The problem also is not with write caching, but read caching, it seems.

So it really is a LOCATE flaw, a too good optimization issue. Because code=1 is true, it seems LOCATE does not do anything. The same happens with LOCATE FOR .T., although that is quite the same as GO TOP, normally, it has a difference.

In the light of that behaviour it's indeed a good idea to GO TOP before LOCATE, though it's only necessary in that specific case of having your LOCATE goal in row 1, not if you do the same thing for code=2, even though then also code=2 is true before the second LOCATE, it then restarts from top.

Final test with code=1 and an index on code, you get the same wrong behaviour, the optimization causing unwanted behaviour is not an index optimization, but a read cache optimization.

In the end Foxpro does not move record pointer and takes the update field value from its cache, I think. And that means the read cache is hindering it to see the current dbf value. You don't have that optimization under your control. It also didn't help to SET REFRESH TO 0,-1. I'm afraid you have to cover that specific case of LOCATE finding its result in row 1 via GO TOP before LOCATE. Or introduce code=0 or code=-1 records, something like that in the top row.

I applied a few minor changes to your code:

*Both IDEs:
#Define ccRootDir "D:\Temp\"
CURSORSETPROP("Buffering",1,0) && explicitly turn off any buffering in all workareas used after this code in the current datasession

*- In 1st IDE
CD (ccRootDir)
ERASE Update_Table.*
Create Table Update_Table...

* In 2nd IDE
CD (ccRootDir)
USE Update_Table Shared...

Bye, Olaf.

 

[Olaf Doschke]

That said, what also does NOT help is using buffering at least in IDE1 and after LOCATE look at CURVAL("update"), which should enforce VFP to read from the DBF file. But it still reads 'Y', and that is astonishing.

Bye, Olaf.

 

[Olaf Doschke]

Just to stress it out once more: The problem only seems to be with row=1, code=1. If you can introduce a row with code=0 or -1 as recno 1, you can solve the problem without GO TOP, but then may have more problems suppressing to list that new record elsewhere.

Bye, Olaf.

 

[KALP1]

Ok, Olaf
I will implement your suggestion. Now I will have to analysis my whole application( using locate command) will above perspective to avoid such problem

 

[Olaf Doschke]

I would still consider adding a new top row. You can use GO TOP, then INSERT BLANK BEFORE and have a new top row, which you never want to locate. To hide it, the simplest way of course is to delete it. You only need to repeat adding such a top row after a PACK, but not every time before a LOCATE, so this will mainly be a one time act, unless you often PACK. It works.

#Define ccRootDir "D:\Temp\"
CursorSetProp("Buffering",1,0) && explicitly turn off any buffering in all workareas used after this code in the current datasession
Cd (ccRootDir)

*- In 1st IDE
Create Table Update_Table Free (Code N(6),Update c(1))
For i = 1 To 50
   Append Blank
   Replace Code With i,Update With 'Y'
Endfor
[highlight #8AE234]* create a top row never interesting for locates
Go Top
Insert Blank Before
Delete[/highlight]
Use Dbf() Shared

Locate For Code = 1
Messagebox(Update)
*** Values Comes To 'Y'

* Before closing the Messagebox, execute 2nd IDE code

*- Now Again in 1st IDE
Select Update_Table
Locate For Code = 1
MessageBox(Update)
*** Values comes To 'N' as the deleted top row enforces LOCATE to relocate
Use

#Define ccRootDir "D:\Temp\"
CursorSetProp("Buffering",1,0) && explicitly turn off any buffering in all workareas used after this code in the current datasession
Cd (ccRootDir)

*- Now in 2nd IDE
Use Update_Table Shared
Locate For Code = 1
Replace Update With 'N'
Use

This means no code change, just data changes in any relevant table.

Bye, Olaf.

 

[Olaf Doschke]

And a simple idea for keeping that deleted row even when PACKing the dbf, you could do GO TOP / RECALL / PACK / GO TOP / DELETE. That is better than to redo INSERT BLANK BEFORE after PACK, because inserting a top row just like PACK means rewriting the DBF from the point of the inserted row, which is the whole dbf again, as the insert before is done at top.

I can only make educated guesses, why it works. I have to assume if the FOR condition is true and the position is not RECNO()=1, LOCATE has to look for previous rows with code=1, so that wrong optimization of staying at the recno and reading its cached value is prevented.

Bye, Olaf.