TABLEUPDATE ALWAYS RETURN .T. 1

[Jailendra K]

Dear sir,
I am opening a grid using cursorsetprop("Buffering",5) in one PC and open the same table in another grid in another PC. Whenever i save the data changes with tableupdate in PC2 and after that edit the data in PC1, TABLEUPDATE(.T.,.F.) does'nt detect conflict and always return .T.., why? As soon as i edit data grid in PC2 it appears in data grid in PC1 thats why OLDVAL and CURVAL become same and tableupdate return .T.. Anyone please resolve my issue.

My codes are placed in a form as mentioned below:

In Load Pro................

CLOSE all
CLEAR
set exclusive off
SET MULTILOCKS ON
sele a
use \\jai\milk\mytable shared 
&& Allow optimistic table buffering
p=CURSORSETPROP("Buffering",5)   && Optimistic table buffering on

In Save Command button:-----------

BEGIN TRANSACTION
iSuccess=TABLEUPDATE(.T.,.F.)       && Commit changes made to table
*? "Table changes committed"
ncurRec=0
if iSuccess=.F.
	ROLLBACK
 	AERROR(aErrors)
 	if aErrors[1,1]=1585                                                                                                     
 		ncurRec=getnextmodified(ncurRec)
		do while ncurRec<>0
			go ncurRec
			RLOCK()
			for nField= 1 to FCOUNT()
				cField=FIELD(nField)
				if OLDVAL(cField)<>CURVAL(cField)
					nResult=MESSAGEBOX("Record has been changed. Save?",4+48+0,"Data Changed")
					if nResult=7
						TABLEREVERT(.F.)
						UNLOCK RECORD nCurRec
					ENDIF
					EXIT
				ENDIF
			ENDFOR
		ncurRec=getnextmodified(ncurRec)
		ENDDO
		BEGIN TRANSACTION
			TABLEUPDATE(.T.,.T.)
		END TRANSACTION
	ENDIF
ELSE
	END TRANSACTION
ENDIF
use
CLOSE ALL
thisform.refresh
thisform.release

 

[Olaf Doschke]

You use the same table, so you see data changes made by the other session. As long as changes are made one after the other you don't get conflicts. That's normal.

To get conflicts you would need to change the same record in both grids and after both sessions changed commit, i.e. do a save with Tableupdate. The first session committing will have no conflict, only the second one. But if you start to change in a second session after you already saved the first session, the two edits are sequential and before you edit in the second session your displayed data already is updated, you make the next edit. That's no conflict.

So the essential test to do is making a change in the same record in both grids before doing the tableupdate in any one.

To see that without creating a form, just emulating the user actions, this shows the two cases without and with conflict:

Cd GetEnv("TEMP")

Create Table conflicttest (id int autoinc, cData char(20))
Insert into conflicttest (cData) values ('Hello')
Insert into conflicttest (cData) values ('World')
Use
Doevents Force

Set Multilocks On && necessary for any table buffering

* Session 1 opens table alias session1:
Use conflicttest in 0 Alias session1
CursorSetProp("Buffering",5,"session1")

* Session 2 opens table alias session2:
Use conflicttest in 0 Again Alias session2
CursorSetProp("Buffering",5,"session2")

* Edits in both session are made in recno 2, let's just go there once:
Goto 2 in session1
Goto 2 in session2

Clear && fresh empty screen
* User1 in Session1 changes "World" to "Olaf"
? "session1: now replacing '"+ Alltrim(session1.cData)+;
  "' with 'Olaf' (while oldval is '"+Alltrim(Oldval("cData","session1"))+"')."
replace cData with "Olaf" in session1
* session1 saveing
savedata("session1")

* User2 in Session2 changes "Olaf" to "Tek-Tips"
? "session2: now replacing '"+ Alltrim(session2.cData)+ ;
  "' with 'Tek-Tips' (while oldval is '"+Alltrim(Oldval("cData","session2"))+"')."
replace cData with "Tek-Tips" in session2
* session2 saveing
savedata("session2")
* No conflicts, edits were sequential

* Now establish a conflict:
* User1 in Session1 changes "Tek-Tips" to "Olaf"
? "session1: now replacing '"+ Alltrim(session1.cData)+ ;
  "' with 'Olaf' (while oldval is '"+Alltrim(Oldval("cData","session1"))+"')."
replace cData with "Olaf" in session1

* User2 in Session2 changes "Tek-Tips" to "Jailendra"
? "session2: now replacing '"+ Alltrim(session2.cData)+ ;
  "' with 'Jailendra' (while oldval is '"+Alltrim(Oldval("cData","session2"))+"')."
replace cData with "Jailendra" in session2

savedata("session1") && no conflict
savedata("session2") && conflict

Function savedata()
   Lparameters tcAlias
   Local Array laErrors[1]
   
   ? "saving", tcAlias
   
   Begin Transaction
   llSuccess = TableUpdate(.T.,.F.,tcAlias,laErrors)
   If llSuccess
      End Transaction
      ? tcAlias,"saved without conflict"
   Else
      If laErrors[1]=-1 && possibly other than a simple conflict error
         AError(laError)
         If laError[1,1]=1585 && conflict error
            ? laError[1,2]
            Goto (laError[1,4]) in (laError[1,3])
         EndIf 
      Else
         ? "Conflict in Record ",laErrors[1]
         Goto (laErrors[1]) in (tcAlias)
      EndIf 

      ? "you changed '"+Alltrim( Oldval("cData",tcAlias))+ "' to '"+ ;
        Alltrim(Evaluate(tcAlias+".cData"))+ "' while that value already was changed to '"+;
        Alltrim(Curval("cData",tcAlias))+"'"
      RollBack
      ? tcAlias,"did not save"
   EndIf 

   Return llSuccess
EndFunc

To conclude: you have no problem with TableUpdate, you have a problem with how you test and not establish a conflict. It's totally normal when a grid already laods changed data before you begin editing you get no TableUpdate conflict because your edit starts on the known dbf data.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Hi,
a 'safeselect' after tableupdate() should show in both grids working with same date the, acutual new data in my opinion.
Koen

 

[Olaf Doschke]

Koen, safeselect is a fine method, but that's not the problem here, as this is neither about Views nor Cursors created with SQL queries. It's also not about grid reconstruction problems.
This is leading away off topic.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

With the following, I'm addressing Koens' aspect of being able to see other users changes asap. It does happen automatically even in buffered cursors, it just doesn't happen all on its own when you just let a form sit there and wait, that only works with BROWSE windows and MEMO edit windows and the reasons for that are indirectly explained with the help topic of SET REFRESH.

1. In a BROWSE window, you get updates according to SET REFRESH settings, this setting also affects your memory buffers of records you loaded.
2. You always see what's in your buffer with the highest priority. If you have made changes not yet committed you, of course, don't want them to be overridden by what another user did and that also doesn't happen. Updating memory buffers is not talking about the values your cursor displays in a grid, in case of table buffering that is about the caching of data and client PCs knowledge about current values. It's quite unknown to me though, whether you really get pushed new data just because you have a DBF open, I don't think so, it would cause much network traffic. I'd say you only read CurVal()s when you really make use of that function or when you TABLEUPDATE(). A mismatch of that and OLDVAL is causing the conflict and that's, of course, good and wanted to be able to decide and not force your change in - I have worked with the forced update, too, though. In some situations it is easiest the last edit wins, even if you override what someone else just changed, you only override what you also edited, you don't override any unchanged fields with the old value you see.
3. The way TABLEUPDATE works you won't see conflicts if the two users change different fields, conflicts are not already happening because you change the same record, you also have to change the same field. But that's also a reason the optimistic buffering is a good use case, you might never edit really the same fields, how often would that occur?
4. The only thing that could be more ideal is seeing other edits come through to you, especially in cases your application mainly shows one overview of data all day long, the current orders in a kitchen, the current deadlines of a doctor, the current seat reservations of a restaurant or cinema or whatever. You do see changes of other users the moment you click into a TextBox or grid cell other control unless you already made an edit since you moved to that record and did not yet commit the change. And all that is good as it is, so it scales with very many clients. If you had 100 clients and each would poll for new data once a second that's still 100 reads of a record in the DBF per second and if every client has a different row active, that means your server is busy reading through the DBF, so that's not really done.

I see the way the BROWSE was often used as user interface in legacy VFP applications, but its nature to poll for data is not just a nice feature of giving you a "live view" of data and it's not only nice relations even let you do "live joins", it's also a resource hog.

Indeed the use of buffering means you do without that comfort of seeing current data for the benefit of everyone's client still working fast. And buffer mode 5 is what I'd recommend, too. It's fine you're doing it this way. And conflicting record numbers coming back from TabelUpdate() are a fine way to be able to react to that at the time it matters - when you want to save your changes.

Pitfalls are, users already assume their changes are saved the moment they do them, the user should know and edit session is something which does not save immediately to the database and not even autosaves every N minutes. You can, of course, program an automatic table update, but that's not something I'd recommend for the general case, it makes sense in Word, for example. But most of the time you have a user beginning and ending a process within a few seconds and not having a lengthy editing session and then you don't autosave and instead let the user have full control of the time of commits.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
since you restarted after you firstly mentioned my remark was leading off topic.
What I was trying to say that, unless you don't update Grid2 with the new data, it will remain to show the old - unchanged data. Therefore the oldval and newval will not change. SafeSelect is a fine method to update a Grid which I recommend for a Gridrefesh.
After updating you can see if there are changes and also see if these changes have an effect on earlier changes in your data for Grid2.
I Think already in 1001 Things Andy explained how to solve such update conflicts gallantly.
Regards,
Koen

 

[Olaf Doschke]

Well, but safeselect doesn't apply to grids you bind to a DBF directly.

When you bind to a DBF directly and two users have a table open ahred, one does a change and commit, you'll see a chnge done in session 1 the moment you click on the changed row in session2, before you even begin editing.

Only when you already began editing in that row before session1 commited, you'll not see the change before you try to commit. But that's fine, because you don't need to override with another users changes before you're done with your own edit. Also pushing changes to every client would just cost network bandwidth, that's not the way the binding works.

Besides you don't knw when to do a safeselect in session2, unless you would do something that pushes the news about data changes from any session to any other session. I don't see that as scaling well.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,

Safeselect doesn't apply to grids

Huuh? Safeselect IS made specificaly for grids. I suppose we are talking about two different things here.

You do a safeselect in grid 2 right after you made a tableupdate in grid1 to ensure grid2 has fresh data. And ofcourse also the other way around.

Koen

 

[Olaf Doschke]

We are talking ab0out grids, but bound to a DBFs directly, without a view, without a query. The topic is not the control, the topic is TableUpdate. and refreshing to see current data also is just a side topic.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Koen,

IF you want to make this a topic of preventing update conflicts all together with avoiding the quirks of the grid, safeselect is a nice way.
But the way you avoid update conflicts with safeselect is not being able to detect them, you create a query result not bound to the DBF directly, not even the way a view is (if I have the right understanding of safeselect as a query into a cursor you zap for showing updated data). That way you decouple your own changes from other user changes, you don't have CurVal() until you actively bring it in.

In that aspect you suggest to mend that by doing the safeselect to pull in other user changes, which is fine, you can do this right before editing, but then do you also do this right before saving changes to test for conflicts? On the aspect of updates, writing back changes you do in a READWRITE cursor you have to write code copying back the changes in a READWRITE cursor to the underlying DBFs. That's unnecessary when using views or cursoradapters and in the end, what you do compares to using TableUpdate with lForce=.t., any last change simply is stored without seeing other users concurrent changes.

Jailendra K was observing to NOT detect conflicts where there should have been. This question is not about finding a strategy to avoid conflicts but to get them, to see them and to be able to inform users they made concurrent conflicting changes. In some situation, a "last change wins" strategy is fine. Even if it's just because users don't grasp the concept of concurrency not meaning to do changes in the same split second, sometimes it's easier to make that switch instead of arguing you would reveal a discrepancy which may be even worth the two users to have a phone call about, as unimportant the value of a single field of a single record of a single table may look on the abstract level. If it's your bank account balance it would matter much to you.

The downside of using a DBF directly as controlsource (or recordsource) is, you design your database in a way that's not normalized to fit the UI. Therefore Views are a better alternative to make use of the buffering and conflict detection. If you think it's a good job to gracefully get around conflicts, it's even harder and more admirable to find a way of showing them and offering a good interface to deal with them. That's the highest level of a UI design, which even takes that into account. The downside of it is, users will then perhaps tend to prioritize their own changes and you get into the "last change wins" situation again, but at least the user claiming the win knows what another user entered.

In the end it's also a question of users organizing their work on data in a way not causing any conflicts and the usual solution is partitioning data, not technically but in areas of responsibility, eg by the first letter of the last name of customers or by product category or whatever another logical way of partitioning.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
OK you get it.
Since OP apparently, according to his coding he showed, is not using a view we dont have to discuss this.
Grids normally, or better said, ideally should have a cursor as datasource.
If you want to refresh your cursor, grids datasource, safeselect is a fine way to get fresh data.
If you want to check, before getting the fresh data, that data has been changed, check for update conflicts than I would check first before you zap the cursor in your safeselect.
If you check for update conflicts after a safeselect you wont see any conflict, I believe.
Regards,
Koen

 

[Olaf Doschke]

Well, but the way to do safeselect wouldn't cause any conflicts, it's just filling a grid cursor that even if buffered, isn't seeing changes of other users. You have the need to program something, that's native to a DBF when used itself.

In a safeselect cursor the Oldval() always is equal to the Curval(), only when you bind to the underlying DBF you get the Curval() in it. It's a reason against safeselect or against using the grid as edit control, which would be another topic. You could of course do the editing in another portion or page of a pageframe where you put single controls bound to a single record only. It's quite a norm to judge that way, but I managed grids as edit controls quite well, too.

To me it boils down to using the grid as edit control? Yes, but neither with safeselect cursor nor with a DBF directly, use cursoradapters. They act like views, they also see Oldval and Curval and allow conflict detection and management.

Using the grid for readonly display? Ok, use safeselect, that's easier to manage and offers more flexibility than views or even curosradapters, in the wiki example you point to a specifc query is use, that could be exchanged with any amount of code, a qpr or whatever you like, even sqlexec. In the end all that concerns the grid is you have a grid cursor you zap and refill. Downside is, this is decoupled from the concurrent changes of the source data.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name