Two apps, with grid updating same table and getting refreshed periodically 1

[Rajesh Karunakaran]

Hi friends,

I have two applications. Both have a grid with the same underlying table in buffered mode. Different users are updating the table records. The grid in both applicaions are being refreshed using timers in them.

Now, suppose the Amount in a particular record was 100. One user is tring to edit that Amount to 200. Now, if the timer triggers at that time, the value gets back to 100, because the timer refreshes the grid.

Another thing, a bit more complex, is that another user may be editing ANOTHER RECORD Amount to some other value and that has to be refreshed correctly in the first user's grid, because he has not trying to edit it and he should see the LATEST value entered by other users.

Ultimately, I think, when the timer refreshes the grid, if I can avoid Refreshing the currenly selected record (the record pointer), then my goal can be achieved.

How I can handle such a situation? Is there any way? Either systematic and know or a tricky one.

Rajesh

 

[Chris Miller]

First of all, there is SET REFRESH, but notice everything said in the help topic of it. It's actually not refreshing grids, mainly browse windows, but then also buffers.

Intuitively you'd set this to some value like 1 second and get updates every second.

It's a good idea to set this to -1, though, which means to always read data from disk, meaning from the dbf file. Think about it, whatever changes are buffered are not yet written to the dbf. The most recent data any client can get is what is committed to the dbf, because there are no peer to peer connections that would fetch what other users began editing and didn't commit yet. So the dbf is the best bet.

This way you technically only use write caching, every time you read something you read it from the dbf, everytime you write something it first only enters the buffer and only tableupdate commits it.

SET REFRESH TO -1 does not mean now the grid will always refresh whenever there is a change in the dbf, the moment it reads is determined mainly by setting focus to a control. A textbox bound to a dbf field will not only read it once when the form activates or when the controlsource is set, but also everytime you click into the textbox and give it the focus.

That means your timer is still necessary to refresh the contents of controls. I'd do a grid.refresh and also grid.setfocus.

Now it depends what you bind, if you used a view or a query to get the grid contents, that'll need a requery, there also is REFRESH() for views, see the help about that. The much more well known REQUERY() function only works for views or cursoradapter managed workareas when the have no buffered changes, so that way once a user starts editing, you can not see other changes, also in other records by requery. But REFRESH() allows you to refresh a specific record or number of records. I never went to such detail, but that means you could actually concentrate on the records currently visiblein the grid and just refresh them. As the grid might not be fully populated a remaining refresh could be about new records and deleted records. The former will be hardly doable with REFRESH(), but a REFRESH() of a deleted row at least should also delete it, which then might make another record visible in the grid.

Users I worked for mostly were satisfied or at least complied to the compromise when they click on something it will at worst update just then, you could always restart a form to get it.

Chriss

 

[Rajesh Karunakaran]

Chriss,

First, it appears I missed to mention one point. After a user's edit, the data is committed through TABLEUPDATE(). When the Grid Refresh happens, the data I want to be shown in the grid is from the disc DBF indeed. But, the point is that, when the user is in the process of editing an amount, suddenly the value changes back to the original. I wanted to avoid this, obviously only for the record on which the user is. All other records have to be refreshed and shown values from the disc DBF.

Otherwise, I can make this particular table un-buffered so that the edits are immediately saved in the disc DBF. Let me see is there any major implication if I do that. But then, if the other user happens to choose the same record, will there be any problem like, locking conflict ?

Rajesh

 

[Chris Miller]

But, the point is that, when the user is in the process of editing an amount, suddenly the value changes back to the original.

Well you said so already:

Now, if the timer triggers at that time, the value gets back to 100, because the timer refreshes the grid.

1. What do you do in the Timer event? An Actual SQL-Select query and Update of a cursor?
2. I pointed out REFRESH(), look into it, first, before you think it wouldn't work. You decide which records to refresh, you can skip the current record, you can refresh each single record in the visible part of the grid, you don't need to do more for a start. Then when this leads to a record disappearing as it was deleted by another user you have one more record in the visible grid rows, so you can refine this to do a second pass of refreshs.

Chriss

 

[TamarGranor]

Don't allow the grid to be updated when this user has made changes to the current record. There's a whole set of functions that let you figure out what's going out. Check out GetFldState(), CurVal(), and OldVal().

When this user wants to save, you can use the same functions to figure out whether there are conflicts.

Tamar

 

[Chris Miller]

Just to be clear: I am talking of the REFRESH() function, not Grid.Refresh().

Chriss

 

[Rajesh Karunakaran]

Dear all, especially Tek Tips Administrators,

Please see above entry by 'james425'. It looks suspicious to me.
Remove it if you find it is problematic.

Thanks

 

[Mike Lewis]

Rajesh,

You're right. It is a blatant bit of spam. I had already reported it, and you can too. Just click the "report" icon (the one with the red flag) in the bottom right corner of the post.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

For sake of demonstrating what happens with SET REFRESH TO 0,-1:

No timer, no use of Grid.Refresh nor the Refresh() function:

First, create a test database with a few records in a test table:

* create temp subfolder for the refreshtest database

Cd Getenv("TEMP")
*Mkdir Refreshtest
Cd Refreshtest

* create database and table
Create Database Refreshtest.Dbc

Create Table Refreshtest (Id Integer AutoInc, Primary Key id Tag id,;
	field1 Char(10),;
	field2 Char(10),;
	lastupdate Datetime,;
	rowversion Integer Default 0)

TEXT TO lcProcedures Noshow
  PROCEDURE Tablechange
      Replace rowversion WITH rowversion+1, lastupdate WITH DATETIME()
      RETURN .t.
  ENDPROC 
ENDTEXT

STRTOFILE(lcProcedures,'refreshtestprocedures.prg')
APPEND PROCEDURES FROM refreshtestprocedures.prg overwrite

ALTER table Refreshtest Set CHECK tablechange()

* Create a few records
Append Blank
Append Blank
Append Blank
Append Blank
Append Blank
Append Blank
Append Blank
Append Blank
Append Blank
Append Blank

CLOSE TABLES All
COMPILE DATABASE Refreshtest 
CLOSE DATABASES All

Then have a form that just displays that table for editing buffered and with a save button that does a TableUpdate() ignoring conflicts (force update).

To be very clear: For now there is no timer that refreshes anything, the only refresh mechanism working is native VFP behavior by the SET REFRESH setting. And remember, at this stage we check what that means, when you SET REFRESH TO 0,-1 (see form.Load() code) and that means don't refresh after a refresh time, only read from DBF.

Is there a refresh at all? See for yourself. The answer is, as long as users edit and don't save, they each have their version of the table only with their own changes. If one user saves, that will not conflict (nevertheless the case is programmed with a Rollback and TableRevert). The second user will then see changes, but only when changing focus to a changed row. That's native VFP behavior about control sources (and row/record sources too, so in general data binding): The control you focus will read the current data. If you don't make the special REFRESH setting to read from DBF, the local buffer will be used for this gotfocus refresh and the DBF is not read. And that's the default behavior and I think it's the reason people think they write a refresh routine at all. This is actual unnecessary this way.

So this is a minimum solution that'll work, as you see the latest row version and field data when you click on a field. So you edit the most current data. When you are in the middle of editing, the other user might also edit (we're using optimistic buffering, no locks, so that can be changed to disallow parallel editing for the rows that are edited by anyone in any session that locks it, but that's not the major concern in this test, we intentionally allow that to see what happens when a user is in editing and the DBF has new data for the field that's edited).

So to say it in short this is the worst case scenario: User A edits the same field of the same record as User B, one of them saves, the situation is symmetrical, so it doesn't matter, say it's user A. User B would only see that change whenever he next time focusses that field. It currently has the focus, but the native automatic refresh only happens with the GotFocus() event (perhaps already in the When() event) that only happens once when the focus moves to the control, initially, not while you edit.

User B now can also click save and will overwrite User A's change without error (as the save routine uses TableUpdate() with the force option, you can change that).

Check it out for yourself (start VFP two times on the same PC, put the windows side to side and start the second PRG in both VFP IDEs). If you already did, I think you'll confirm this is the behavior you also see. And that means your concern of an edit being overwritten will not happen. If you had a timer that refreshes the grid, that will change, sure, but you don't need to have that in this version.

The disadvantage is, a user really just sees the current data when changing to a row. At that moment he might already look to the keyboard clicks and doesn't notice the refresh this causes. That's the risk of it. Also it would of course be nice if you could see changes earlier, in rows you don't (yet) edit, but might edit next. You could see another user already cares for them and don't interfere. That's not happening here and that will need an active refresh method. Which means I call this automatic refresh we have so far a passive refresh. As nothing happens without an action of the user to click on a record.

Before introducing a timer to do something (or modifying your timer code) we could check what a change of SET REFRESH does. So let's next try SET REFRESH TO 1,-1. That means every second the cached table data will be refreshed, but will the grid refresh? Spoiler: No.

Yes, I hate spoilers, too, but that's the reason you still have a legitimation for something on top of this automatic just in time refresh, as it's good enough, but has one aspect confusing users. They'll ask the question why does data all of a sudden change when I want to edit it? Even when they realize that the data may have been that for a while and they just see it by clicking on it. Users by default think what they see is current, especially on a computer display. Paper might have outdated data, but not a display? But a form, even directly bound to a DBF, isn't a file view, it's only a view of the data as it was initially read. That's not only happening with SQL. The just in time refresh is already more than a usual client/server architecture can provide. I think many VFP developers are proud of this already, as it makes working with data so easy and adds to the feeling of having the best database backend. It has it's downsides, too, especially if you want to perfect it to be even more to the point of a viewer of the data as it is right now.

One of the few really live view of data I know works quite well is google docs online multi user sessions editing in parallel you can see multiple text cursors and see live changes. You don't know how complicated that is, you have an idea as you already think know the problem of getting a refreshed field while you edit it.

Okay.

I'll start continuing my experiment, but might only post it tomorrow. This already is enough for now to let it sink in, right? If you encounter any problems with the code so far: I'm here foor the next 1-2 hours for sure and could react quickly.

Chriss

 

[Chris Miller]

I forgot to post the form that uses this test database. Here it is:
When you have done the prg of my previous code to establish test data, run this in two VFP sessions:

CD GETENV("TEMP")

oForm=CREATEOBJECT('Refreshtestform')
oForm.Show()
READ EVENTS
 
DEFINE CLASS refreshtestform As form
   Width = 524
   
   ADD OBJECT grid1 as grid With;
      Left=12, Top = 12, height= 120, width=500,;
      Recordsource="Refreshtest"
      
   ADD OBJECT command1 as commandbutton With;
      Left=12, Top = 144,;
      Caption = 'Save'

   PROCEDURE command1.click()
       Thisform.Savedata()
   ENDPROC
   
   PROCEDURE Load()
      SET EXCLUSIVE OFF
      SET MULTILOCKS ON
      SET REFRESH  TO 0,-1
      OPEN DATABASE (ADDBS(GETENV("TEMP"))+'Refreshtest\Refreshtest.dbc')
      USE Refreshtest!Refreshtest
      CURSORSETPROP("Buffering",5) && optimisitic tble buffering
   ENDPROC
    
   PROCEDURE Savedata()
      BEGIN TRANSACTION
      IF TABLEUPDATE(2,.t.,'Refreshtest')
         END TRANSACTION 
      ELSE
         ROLLBACK
         MESSAGEBOX('Save conflict')
         TABLEREVERT(.T.)
      ENDIF 
   ENDPROC 
   
   PROCEDURE UnLoad()
      TABLEUPDATE(2,.t.,'Refreshtest')
      CLEAR EVENTS
   ENDPROC
ENDDEFINE

Chriss

 

[Chris Miller]

In very short: A timer that does nothing more than Grid.Refresh() will update the viewed records only if they have no buffered changes.

So that's doing what you need already and includes not overwriting users editing. It even works without changing SET REFRESH TO 1,-1, as I thought would be need to already let that update the local cached data.

Try it yourself, edit 3 records as user A, edit 2 of them as user B, save as user A and see the Grid Refresh only updates the 1 record user B didn't touch.

What's imperfect in this is that the decision whether to update or not is based on the overall record buffering state, not the state of each individual field. So if you change field1 as user A and field2 as user B, save as user A, user B will not see the change of user A, even though user B didn't change field1 and so the change is no conflict with the editing of user B.

To make such changes you need to read Curval() and use that data to update the rows that have buffered changes, but no changes in the field you read by Curval(), as Tamar already indicated.

Still the REFRESH() function will be necessary to do this with Views or Cursoradapter as data sources. The cursoradapter integrates with it in the RecordRefresh() method you could use instead, too, wheres CursorRefresh is more like Requery and also only works when there are no buffered changes at all, in any record. The cursoradapter also has some events, which speak for using it as starting point to perfectionate data refreshing to the field by field level, if you want that.

Chriss

 

[Chris Miller]

To highlight it:

You can get very near what you want when binding to a DBF with SET MULTILOCK ON, Buffering set to 5 (optimistic table buffering) SET REFRESH 0,-1 and a timer doing nothing more than Grid.Refresh(). It won't override your editing. It won't even override anything in records that have any buffered changes, so if you'd call that override protection this protection covers all records you changed so far without saving the changes.

So that's a simple solution.

What's not covered this way is seeing changes in the edited records another user also edited, but perhaps in different fields not conflicting with your edit. Which becomes important if you don't want to overwrite changes with old values.

Before I continue, the first question is do you bind grids to the DBFs or do you use views(local or remote) or cursoradapter? Or do you even query data into cursors for the grid and are not bound toteh actual DBF or remote table?

Chriss

 

[Rajesh Karunakaran]

Hi Chriss, Tamar & all others,

Sorry for coming back here after a gap.

Chriss, the REFRFSH() is not really suitable in this situation I think.

Now, I am using those OLDVAL(), CURVAL() combination. Tamar had mentioned these above.

In the VALID event of the grid column text box, I am checking if the OLDVAL and CURVAL are different. If they are, I am alerting the user that someone else has already modified the value and if the user wants to accept that value or force his value to be saved. This should handle the conflict, if any, I hope.

But the main challenge was to get a correlation between what the Timer (periodically) does and what the users do (randomly)

Then modifications are being verified. Will definitely get back here when I get the status.

Thanks

 

[tomk3]

Hello,

just an idea for a mechanism we use with MS SQL :

we use a table locki like pk_locki I, tablename c(100), fk_table I, fk_user I, started T.

When a user tries to edit a record, program looks into that table if there is an entry for that record (fk_table).
If not a new entry is written and user may edit, if there is a locki for another user he gets alerted WHO since WHEN "locks"
and user may only view (and run to the other user who may have forgotten to leave the form when going for a break )

In save/abort the record in locki is deleted, in on error and in appquit() all records for that user are deleted.
There is an admin function to delete all records / all older then, all for user, ...

This mechanism prevents users from entering for edit a record which is edited by someone else and offers info who does it since when.

Maybe this can be adapted to your situation, too.


regards
tom

Instead of fk_user you may use sys(0) or part of it
Sometimes we generate one entry fk_table=0 for all records and query that entry only, this is something like tablelocking , used in base data forms.
Sometimes if the program finds a locki , it not only sets mode from edit to view, but also does other things (requery, recalc,...)
Sometimes we use a dummy fk_table (-3105) to store the latest entering of edit for the table (so edit creates an entry fk_table for a record and updates fk_table=-3105)

 

[Chris Miller]

But the main challenge was to get a correlation between what the Timer (periodically) does and what the users do (randomly)

Well, your main concern as stated so far was the timer would overwrite the field the user currently edits. And that's not the case, unless the timer actively queries the dbf and replaces values in the grid cursor, which would only be necessary when you bind to a view or data queried in any way that's not directly binding to the DBF.

If you bind to a DBF, a grid.refresh() will show current (newest) data from that DBF. You still don't say what you bind to.

REFRESH only applies to remote data, but also cursoradapters, and they can be used to bind to dbfs, too, not just for remote data.

Chriss

 

[Rajesh Karunakaran]

Chriss & all other friends,

In my first post I had mentioned that both the grids (in different forms) are with the same underlying table in buffered mode. Sorry if I confused you all. Let me describe it once again.

* 2 forms. Each has a grid.
* The grids are bound to the same DBF (each column of grid to their respective fields)
* The DBF is in buffer mode 5 in both forms.
* My startup PRG sets SET MULTILOCK ON

In this above way, all usual things are working okay.

* The 2 forms are used by different users.
* Number of records in the DBFs are fixed (some kind of fixed charges)
* Only the AMOUNT column is editable.
* Lets assume, by default all AMOUNTs are 10.
* Users will be modifying the AMOUNT in a random way, i.e., we wouldn't know which record is edited at a given time.

* In the VALID event of the Amount column, I am doing a TABLEUPDATE().

Now, I wanted the modification by a user to be reflected in the grid of the another user, ideally immediately after the edit. For this I have a Timer in both forms which ticks say at every 5 seconds.

In the Timer, I have 'grid.Refresh()'.

Now, suppose user A edits the 3rd record, from amount 10 to 120. But if user stays at the field for some more time and in between if the timer kicks in, the amount gets back to the earlier amount, ie 10 because it's still 10 in the DBF.

Ultimately, what I want to achieve is, if user is editing record 3, the timer SHOULD ONLY UPDATE/REFRESH those records except the 3 so that the user's editing process is not disturbed. Later, if the user confirms the amount, then I will use OLDVAL() and CURVAL() to make a decision as to which amount to be finally saved onto the DBF. This decision I am making in the VALID method of amount column. This is required because while user A is editing the record, user B might have already modified the same record and saved onto the DBF.

END NOTE:
Now, I think, I had the issue when I was issuing a RAISEEVENT for the Refresh() of grid in the Timer. Now, I changed to a simple call to 'grid.Refresh()'. This has a change which I am looking for, it seems.

The OLDVAL(), CURVAL() combination I have already added and it works fine it seems.
Let me check.

If you friends are having some new thoughts after reading these descriptions, please share here.

Thanks.

 

[Chris Miller]

Now, suppose user A edits the 3rd record, from amount 10 to 120. But if user stays at the field for some more time and in between if the timer kicks in, the amount gets back to the earlier amount, ie 10 because it's still 10 in the DBF.

No, that doesn't happen.Since multilocks are on nd the record is changed inthe buffer of that user, it is not refreshed with the DBF value.

I already pointed out a few times this is working fine. Or are you having code that does more than native behavior?

Refresh isn't an event so there is no reason to call it with raiseevent. I doubt it makes a big difference, since the grid will read from the workarea with the dbf alias name and that will prioritize buffer content over dbf and so editing is not disturbed.

The only thing that could be better with this is refreshing unedited fields of records, but when you only edit a single column that isn't a problem.

And what can happen if a user edits but then doesn't save (by moving away from the record) is, that another user also edits it and so you finally see a conflict. Your use of OLDVAL() and CURVAL() and the workarea value (buffer) then is just the usual use of them for checking the reason after a tableupdate conflict. And to cover that, it makes less sense to first check curval(), chances are low but not zero the curval changes after you read curval and before you do tableupdate. So just like locking and exclusive access to things, the only good way is first trying and then reacting.

As long as edits are kept short, you always get the curval right before editing, no matter if you have a timer or not, becuase focusing a field that'snot yet editedand thus not buffered will always read the curval anyway. So the problem only occurs if a user takes longer to edit and leave the record. That points out users might think the edit is saved immediately not only after leaving the record or column. You could solve that by setting focus to the grid column that already has focus. That causes one cycle of going through valid, lostfocus and gotfocus. This way other users see the edit earlier and might even not edit it themselves at all, which would lower the nummber of conflicts.

Chriss

 

[Chris Miller]

By the way, sorry for having kept to ask about what you bind to, but "underlying table" is a term that can also be used when you bind to views. Maybe that's just me.

Chriss

 

[Rajesh Karunakaran]

Chriss,

No, that doesn't happen.Since multilocks are on nd the record is changed inthe buffer of that user, it is not refreshed with the DBF value.

I also was expecting that. But, for some reason, it behaves in some other way. Let me create a small form and only the essential components to test this behavior. Let me test it as per your view points.

Rajesh

 

[Chris Miller]

I posted such an example and that shows refersh of records you edited (even in other fields) isn't done. Have you tried this example? It's sufficient to run two VFP ID sessions on the same computer.
So run the code creating the data once, then run the form code in two ID sessions.

You get refreshed data about what the other user/IDE saves, but only in records you don't have buffered yourself.

Chriss