Viewing data in different data sessions

[AndrewMozley]

In some circumstances data which is being written to two tables in one data session is not being seen in the same way in another data session

Session 1 : Purchase invoice

When an invoice is entered, several tables are updated. In particular, a record in added to the invoice table and the ‘Balance’ field is updated in the supplier table.

Session 2 : Enquiry

This session (a different form within the application) uses both these tables.

Initially an enquiry (Session 2) is made on a particular supplier’s account. This shows the balance and the transactions. Meanwhile an invoice is entered and posted. (Session 1)

When the invoice has been posted. an enquiry on that supplier is made, back in session 2.

On my own development machine all is fine : the enquiry shows the updated balance and details of the new invoice. But on one client’s machine (on a network) he notices that while the updated balance (from the supplier table) is shown correctly, the new transactions (from the invoice table) do not always show.

But if he exits the system, restarts and examines the supplier’s account, all is well.

What am I doing wrong! what commands should I include as well as the code for appending and editing? a skeletal example of code would be most helpful.

Thanks in advance

 

[Mike Lewis]

Andrew, my first thought is that this is an issue with buffering rather than with data sessions. Are any or all of the tables buffered? And if so, what buffer mode?

In general, no "process" will see updates or insertions in a buffered table unless and until those changes are committed. I use the term "process" to mean another form in the same application (regardless of the data session) or another user on the network.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thanks Mike

I may have been inconsistent! The invoice table is not buffered - I probably felt that appending records could never fail, whereas the supplier table has buffer mode 5 (Optimistic table) - I must have been thinking that that could fail if someone is editing the supplier record.

And I notice that for the supplier table I call TableUpdate() - to check whether it worked, whereas I had not bothered in the case of Appending the invoice records - perhaps that is my mistake.

The whole posting process is bracketed with BEGIN . . END TRANSACTION

 

[Mike Lewis]

Andrew, I can't comment on whether the tables need to be buffered. I can only say that, if they are buffered, you must at some point do either a TABLEUPDATE() or a TABLEREVERT(). If you don't do a TABLEUPDATE(), then the inserts or changes won't be visible to other processes.

Yes, you should always check the reply from TABLEUPDATE(). But the fact that you did not do so in connection with your append does itself explain the behaviour you are seeing. Unless, that it, the append is consistently failing some reason.

Using BEGIN / END TRANSACTION could also be an issue. In general, the updates or inserts won't be visible until the transaction is completed. If the transaction is still in progress when the other "process" is viewing the table, it won't see the changes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thanks again Mike

In the matter of TableUpdate(), if I call it, I do check the result : this was because for the supplier record (optimistic row buffering), it might have failed if someone had locked or was working on the record.

But in the case of the invoice record - which is being slow to show as updated, even on the same workstation (but in a different data session) - there is no buffering and at present I do not call Tableupdate().

I am slightly inclined to call TableUpdate(), even for the unbuffered tables - do you think that will make any difference? or I suppose that I could change the whole lot to optimistic row buffering?

The problem seems only to have arisen recently when my customer changed his server; he is aware of that and is a reasonable man! But my slight problem is that I cannot get the error to occur on my machine, nor on the customer’s particular machine which I log into. But I have screen shots of the error when it did occur.

If any other user has experienced this problem it would be good to hear.

Andrew M.

 

[TamarGranor]

Pretty sure TableUpdate() will give you an error, if you call it on an unbuffered table.

Tamar

 

[AndrewMozley]

Tamar, you are quite right. Calling TABLEUPDATE() on an unbuffered table gives a run-time error.

I am wondering whether the VFP FLUSH command might be the answer. I have not used this instruction before, but perhaps I should try using it after doing all the appends / updates, calling TABLEUPDATE() where relevant, and ENDing the TRANSACTION.

As I mentioned, I have not been able to reproduce the problem remotely (might have to visit), but I know it is happenning - have screen shots showing that the buffered updates have ‘taken’ whereas the unbuffered APPENDs have not, until my application is closed. I believe that it is a network-related problem - my user has recently upgraded his network; in general my VFP application seems to be working as before, apart from this matter.

Has anyone experience of using the FLUSH command - it is a gap in my education! I may have to install this as an update and see how it goes . . .

Thanks again. Andrew

 

[Olaf Doschke]

FLUSH - Saves table and index changes to disk.

I tried it once when later it turned out to be an opportunistic locks SMB v2 or 3 protocol problem and nothing helped.
It does not more than when you finally USE = close a workarea or end the form and datasession when VFP also flushes anything in cache.

The point is, VFP actually only can directly affect local DBFs. It has not all hands on anything that's on a share - and DBC/DBFs in multi-user environments, of course, are - these files also depend on the OS file system and its caching mechanisms, plus network and it's behavior regarding caching, especially write caching and invalidating write caches. Since they are on different levels even just the information missing about which node in the network has the currently valid cached data not yet stored to disk, well, causes the assumption the disk still has the latest file block content.

Before you dive into network analysis and network settings on the caching subject, both VFP and the OS do something on that level and I can't give a definitive guide on that. A customer back in about 2005 had network specialists not finding the reason when only an older Windows 2003 Server used as file share helped for a VFP7 app.

I'd rather try to end the datasession, ie close and restart the form than trying to get VFP and the OS and network and drivers and hardware, etc. to react to FLLUSHing. At minimum close workareas. And I know that can be a bigger effort not only for grids to keep the binding correct but relations may have navigated to certain records. All that is lost when you close DBFs. It's the best you can do besides also ending the process.

When you don't have a grid bound to a backend DBF and use - for example - the safe select technique or views or cursoradapters queried workareas, not the data persisting DBFs, you can cause the simplest flushing by closing and reopening in a single USE:

SELECT sometablealias
USE (DBF()) AGAIN ALIAS (ALIAS())

Grids still go blank, when they are bound to that workarea, other bindings are not that sensitive, but relations are also lost and RECNO() - the record pointer - will reset to the top record. SET FILTER also os lost and some other workarea related properties, so you also need to reestablish buffering, for example, if you haven't set a default for workarea 0 that then applies to all newly opened and also reused ones.

For some forms I went as far as establishing a routine with several form methods to a) unbind controls, b) refresh data (reopen DBFs) c) bind controls to be programmed individually for any form class but the logic being a base class call of these three methods from a central reinit() method, which also init() called. Then you can reinit all that without closing and restarting the form and datasession. But in the end that also didn't help in our case.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name