How do I verify that my update conflict system will work properly in a multiple child-record update scenario? It seems to me that optimistic table buffering isn't working as advertised. I know that can't be true, that I must be misunderstanding something, but I can't get a clue here.
I'm creating a n-tier system that strives to completely decouple data from user interface. There are no controls bound to fields, no use of form data environments, and no local views. Its "back end" uses native VFP tables, and I use objects containing properties for parent data, and member arrays for child data, to transport the data back and forth.
I realize that I need to use optimistic table buffering for the child tables. So far, though, I've been unable to generate an update conflict in that buffering mode to test my system. I'm sure I'm doing something wrong. Can anyone please help?
My demo code is below; it's a very simplified abstraction of my real system. It starts out with optimistic row buffering first simply to demonstrate the only conditions under which I've been able to generate an update conflict to test the system. The demo system can't generate an update conflict using optimistic table buffering under any circumstances, and I'm pretty sure that in real life it must be possible for such conflicts to occur.
If you can kindly help me, please follow the instructions below to run the demo, and then let me know what you think. I'd even appreciate a "You're nuts, you can't do that!" at this point. Thank you very, very much for your help.
Demo Instructions
1. Please run the code below simultaneously in two different VFP sessions.
2. In both sessions, click on the last record in the combobox to put it into the textboxes. Change the first name value to something different in each session.
3. Press the ENTER button to update the combo with your changes in both sessions.
4. In the SECOND session, press SAVE to commit the changes to the table. Press the RESUME button in the debugger and close the debugger frame.
5. In the FIRST session, press SAVE. When the debugger opens, set watches on the following values:
CURVAL("firstname")
OLDVAL("firstname")
EVAL("firstname")
6. Step through the code and watch those values. In the first record to be updated, all three of those values will be different and TABLEUPDATE() will throw an update conflict error, trapped by the MESSAGEBOX().
7. Close both instances and start over. This time, modify the first name values in the second row of the combobox and watch what happens. You can't get a situation where CURVAL() doesn't equal OLDVAL(), because as soon as the record pointer is moved by SKIP, OLDVAL() changes for ALL the records in the table.
8. Comment the CURSORSETPROP("Buffering",3,"multitest") line in the form class's FillCombo() method and uncomment the CURSORSETPROP("Buffering",5,"multitest") line. Run the demo in two instances again. No matter which array row you modify, or what order you do the updates in, you can't get an update conflict using my steps above.
My steps to modify the third record in the array--which require no movement of the record pointer--should create an instant update conflict in table buffering, with CURVAL(), OLDVAL() and EVAL() all holding different values, just as they do in row buffering, but they just don't. The problem is already in existence at that point, so, it seems to me, neither a DO WHILE GETNEXTMODIFIED <> 0 ... ENDDO loop nor the issuance of TABLEUPDATE() can have anything to do with the problem. However, I will note that when I was testing my parent record updating system, I found that issuing either a GO TOP, a SEEK, or a SEEK() with table buffering would make it impossible to detect any conflicts, because all of those commands instantly set OLDVAL() equal to CURVAL() for all fields in all records--contrary to what the VFP documentation says should happen. (That's not a problem with parent records because the pointer isn't going to move between when I retrieve the data and when I put it back.) I've found that I can do an UPDATE SQL without moving the record pointer, but that doesn't change the results I get from CURVAL() and OLDVAL(). So I'm at a loss to understand what I've done wrong, or what I should expect to happen.
IF NOT FILE("multitest.dbf")
CREATE TABLE multitest ( ;
firstname C(30), ;
lastname C(30) )
INSERT INTO multitest (firstname, lastname) ;
VALUES ("Jim", "Jones")
INSERT INTO multitest (firstname, lastname) ;
VALUES ("Tom", "Adams")
INSERT INTO multitest (firstname, lastname) ;
VALUES ("Jane", "Doe")
ENDIF
SET EXCLUSIVE OFF
SET MULTILOCKS ON
oForm = CREATEOBJECT("MyForm")
oForm.Show()
READ EVENTS
DEFINE CLASS MyForm AS Form
ShowWindow = 2
ADD OBJECT Combo1 AS MyCombo WITH ;
Top = 10, ;
Left = 10, ;
Width = 100
ADD OBJECT Text1 AS Textbox WITH ;
Top = 40, ;
Left = 10, ;
Width = 80, ;
Height = 22
ADD OBJECT Text2 AS Textbox WITH ;
Top = 72, ;
Left = 10, ;
Width = 80, ;
Height = 22
ADD OBJECT CmdEnter AS MyEnterButton WITH ;
Top = 10, ;
Left = 130
ADD OBJECT cmdSave AS MySaveButton WITH ;
Top = 40, ;
Left = 130
PROCEDURE Init
THISFORM.AddProperty("aPeople[1]")
THISFORM.Combo1.RowsourceType = 5
THISFORM.Combo1.RowSource = "THISFORM.aPeople"
THISFORM.Combo1.ColumnCount = 2
THISFORM.Combo1.ColumnWidths = "70,70"
THISFORM.FillCombo()
ENDPROC
PROCEDURE FillCombo
SELECT 0
SELECT * ;
FROM multitest ;
INTO ARRAY THISFORM.aPeople
CURSORSETPROP("Buffering",3,"multitest")
GO RECCOUNT() IN "multitest"
THISFORM.Combo1.Requery()
THISFORM.Combo1.DisplayValue = THISFORM.Combo1.ListItem(1)
ENDPROC
PROCEDURE Destroy
CLEAR EVENTS
ENDPROC
ENDDEFINE
DEFINE CLASS MyEnterButton AS CommandButton
Width = 60
Height = 24
Caption = "ENTER"
PROCEDURE Click
THISFORM.aPeople(THISFORM.Combo1.ListItemId,1) = ALLTRIM(THISFORM.Text1.Value)
THISFORM.aPeople(THISFORM.Combo1.ListItemId,2) = ALLTRIM(THISFORM.Text2.Value)
THISFORM.Combo1.Requery()
ENDPROC
ENDDEFINE
DEFINE CLASS MySaveButton AS CommandButton
Width = 60
Height = 24
Caption = "SAVE"
PROCEDURE Click
LOCAL x
SET STEP ON
SELECT multitest
FOR x = ALEN(THISFORM.aPeople,1) TO 1 STEP -1
REPLACE firstname WITH THISFORM.aPeople(x,1)
REPLACE lastname WITH THISFORM.aPeople(x,2)
IF CURSORGETPROP("Buffering","multitest") = 3
THIS.CheckForConflicts()
ENDIF
IF NOT BOF()
SKIP -1
ENDIF
ENDFOR
IF CURSORGETPROP("Buffering","multitest") = 5
THIS.CheckForConflicts()
ENDIF
GO RECCOUNT()
ENDPROC
PROCEDURE CheckForConflicts
LOCAL success
success = TABLEUPDATE()
IF success = .F.
IF MESSAGEBOX("Update conflict. Revert?",35,"") = 6
TABLEREVERT()
ELSE
TABLEUPDATE(.T.,.T.)
ENDIF
ENDIF
ENDPROC
ENDDEFINE
DEFINE CLASS MyCombo AS Combobox
PROCEDURE Click
THISFORM.Text1.Value = THISFORM.aPeople(THIS.ListItemId,1)
THISFORM.Text2.Value = THISFORM.aPeople(THIS.ListItemId,2)
ENDPROC
ENDDEFINE