A couple of months ago I wrote about the basics of buffering, but I deliberately left out any discussion of the two most vital functions when working with buffering - namely TableUpdate() and TableRevert(). These are the basic means by which you, the developer, control the transfer of data between the Visual FoxPro buffers and the underlying data source. TableUpdate() takes pending changes from the buffers and commits them to the underlying table while TableRevert() refreshes the buffers by re-reading the data from the underlying data source. Successful completion of either function results in a 'clean' buffer meaning that, as far as Visual FoxPro is concerned, the buffers and the underlying data source are synchronized.
Since Visual FoxPro supports both Row and Table buffering, both of the data transfer functions can operate either on the 'current record only' or on 'all changed records.' The first parameter that is passed to either function determines the scope of the operation and, alas, we have another source of possible confusion right here. The functions operate in slightly different ways and have different types of return value.
In Version 3.0 of Visual FoxPro, both TableUpdate() and TableRevert() would accept only a logical parameter to determine the scope of the change that they managed. Passing a value of .T. meant that all pending changes were to be processed, while .F. restricted operations to the current row only, irrespective of the buffer mode in force.
TableRevert() returns the number of rows which were reverted and cannot really 'fail' - unless there is a physical problem, like losing a network connection. In a row buffered table, or when specifying the scope as .F., the return value will, therefore, always be 1.
TableUpdate() returns a logical value indicating whether the specified update succeeded, irrespective of the scope. In other words a return value of .T. indicates that all records in the scope have been successfully updated. However, the behavior when you use a logical parameter to determine the scope and the update fails for any reason, is that no error is generated and the function returns a value of .F. leaving the record pointer at the record which failed.
If you are updating a single record this is quite straightforward, but if you are updating multiple records in a table, and one record cannot be updated, it means that any further updates have not been tested. So, after resolving the conflict for the record that failed, there is no guarantee that re-submitting the update will not fail on the very next record. This can be a problem!
The behavior of TableUpdate() was, therefore, changed in Version 5 to accept either a logical or a numeric parameter for the scope, where 0 is equivalent to .F. and 1 to .T. The new behavior, which can only be specified by passing "2" as the scope parameter, specifically addresses the issues of updating multiple records.
When using table buffering, calling TableUpdate() with a scope parameter of "2" Visual FoxPro attempts to update all records which have pending changes. However, if a record cannot be updated, instead of stopping, the function logs the record number which failed to an array (whose name can be specified as the fourth parameter) and continues trying to update any other changed records. The function will still return .F. if any record fails to update, but it will also have at least tried to update all available records. The output array contains a list of the record numbers for those records that failed to update.
One major difference between the syntax for TableUpdate() and TableRevert() is that the former can take an extra, logical, parameter in the second position in the parameter list. This controls the way in which the update behaves whenever a conflict is encountered.
By default, Visual FoxPro will reject an update whenever a conflict between the buffered data and the underlying data is detected (see below for a full discussion of conflict detection and resolution). By specifying a logical ".T." as the second parameter you can force an update to be accepted even in situations where it would otherwise fail. Naturally, this is not something that you would wish to do by default but there are, as we shall see later, situations where this behavior is not only desirable but essential.
Both TableUpdate() and TableRevert() operate on one table at a time. Their default behavior is that, unless specifically directed otherwise, they will act on the table in the currently selected work area. If no table is open in this work area, you get an error (Error #13: Alias is not found). Both, however, can act on any open table available in the current data session and can accept either an ALIAS name (third parameter for TableUpdate(), second for TableRevert()) or a work area number.
We do not recommend the use of work area numbers in this, or any, situation where you are specifying a table other than the currently selected one. As far as we can see this functionality is included only for backward compatibility and has no place in the VFP environment. There are two reasons for avoiding the use of work area numbers. Firstly, they make your code dependent upon specific tables being opened in specific work areas - which is a major limitation if things change! Secondly you do not actually have any control over where VFP opens tables, cursors or views when you use a Form's DataEnvironment anyway. So relying on the work area number, rather than the alias, is a very risky strategy and is unnecessary.
The only time we recommend using the work area number is when saving the current work area by storing the return value of the SELECT(0) function. Using the work area number in this case ensures that should the current work area actually be empty, or the table it contains be closed during whatever operation you are doing, you can still return to it without error.
The only time we recommend using the work area number is when saving the current work area by storing the return value of the SELECT(0) function. Using the work area number in this case ensures that should the current work area actually be empty, or the table it contains be closed during whatever operation you are doing, you can still return to it without error.
Conclusion
There is a lot of functionality and flexibility hidden away inside TableUpdate() and TableRevert(). When using buffering, you do need to be aware of exactly what the various combinations of their parameters can provide and to ensure that you are using the correct combination to meet your needs. While TableRevert() is pretty straightforward, TableUpdate() is more complex and so Table 2 below provides a summary of what the various "practical" combinations of parameters for TableUpdate() will do.
Parameters
| ||||
Scope
|
Force
|
Table
|
Output
|
Action
|
0 or .F.
|
.F.
|
Attempt to update the current row only of current alias
| ||
0 or .F.
|
.T.
|
Force update of the current row only of current alias
| ||
0 or .F.
|
.F./.T.
|
Alias
|
Attempt/Force update of current row only of specified alias
| |
1 or .T.
|
.F.
|
Attempt update of all available rows of current alias. Stop on Failure
| ||
1 or .T.
|
.T.
|
Force update of all available rows of current alias
| ||
1 or .T.
|
.F./.T.
|
Alias
|
Attempt/Force update of all available rows of specified alias. Stop on Failure
| |
2
|
.F.
|
Alias
|
Array
|
Attempt all to update all available rows of specified alias. Note failures but do not stop
|
2
|
.T.
|
Force update all available rows of current/specified alias
| ||
2
|
.F./.T.
|
Alias
|
Array
|
Attempt/Force update of all available rows of specified alias. Note failures but do not stop
|
The preceding section discussed the mechanism for updating a buffered table, and in the previous paper, recommended that Table Buffering with Optimistic Locking should be the normal choice for most applications. So the next issue to address is that of ensuring that changes are not going to cause an update conflict when saved. One of the inherent problems with using optimistic locking in a multi-user environment is that it is possible for more than one user to make changes to the same record at the same time. You may wonder why such a thing would ever be possible - surely two users could not be updating the same record at the time?
In practice there are lots of scenarios where this can legitimately happen. Consider the situation in a Sales Order processing system. When an order is placed for an item, the current "available stock" must be adjusted to reflect the reduction. If two customers, being handled by two operators simultaneously, include the same item on their orders there is a good chance that a conflict will arise. Obviously this cannot happen if the system uses pessimistic locking but that has other, usually undesirable, consequences. In this scenario, the second operator who tried to access the item in question would receive a message that the record was in use by someone else and would not be able to make changes - not much help! Moreover pessimistic locking can only be used when a Visual FoxPro table is used directly as the data source - you cannot pessimistically lock a view of any sort (either for local data or for remote data).
When using buffering, Visual FoxPro makes a copy of all data as it is retrieved from the physical table and, when an update is requested, compares this copy with the current state of the data. If there are no changes, the update is allowed, otherwise the appropriate update conflict error (#1585 for Views, #1595 for tables) is generated.
The basis of all conflict detection lies in two native functions, OldVal() and CurVal(), which access the intermediate cursors created by Visual FoxPro when using buffered data. As their names imply, OldVal() retrieves the value of a field as it was when the user last read the data from the source, while CURVAL() retrieves the current state of the data in the source table. Both of these functions operate at the field level and, although both can accept an expression that evaluates to a list of fields, they are best used to retrieve field values singly to avoid the problem of resolving different data types.
There is one gotcha! with using CurVal() to check the status of a view. Visual FoxPro actually maintains an additional level of buffering for a view which, unless the view is refreshed immediately before checking a field's value, may cause CurVal() to return the wrong answer.
Before getting into the discussion of how to detect a conflict, let us be clear about what Visual FoxPro's definition of a conflict is. As stated earlier, in the discussion of buffering, Visual FoxPro actually makes two copies of a record whenever a user accesses data. One copy is made available as the editable cursor and is where a user makes changes. The other is retained in its original state. Before allowing an update to proceed, Visual FoxPro compares this original cursor with the data currently stored on disk. A conflict occurs when these two versions of the data do not match exactly, and there are two ways in which that can happen. The first, and most obvious, is because the current user makes changes to a record and tries to save those changes after someone else has already changed and saved the same record.
The second is a little less obvious and arises when a user makes a change, but then changes everything back to its original value. The result is that no changes were actually made but when they try to "save" the record Visual FoxPro will still see this as a conflict because the OldVal() and CurVal() values are in fact different. This is best handled by not actually calling TableUpdate() unless the current user really has made changes. To avoid this sort of error you cannot simply rely on GetFldState() but must expressly compare the values in the current buffer with those in OldVal().
So, having avoided the possibility of conflicts when the current user has not made any changes by simply not trying to commit the record, there are basically two strategies you can adopt to detect conflicts. The first is the 'Try It and See' approach. This simply means that you do not try and detect potential conflicts but just trap the result of a call to TableUpdate() and, when it fails, take steps to find out why.
The second is the 'Belt and Braces' approach in which each changed field is checked individually and any conflicts are resolved before attempting to update the underlying table. While this looks more defensive, and therefore 'better', there is actually a hidden problem with it. In the amount of time that it takes (albeit very small) to check all changed fields against their current values, another user may succeed in changing the very record you are checking. So, unless you also explicitly lock the record before starting to check values, the actual update could still fail. Since we really want to avoid explicitly placing locks, you need to incorporate exactly the same check of the result of the TableUpdate(), and provide the same handling for failure as in the much simpler just 'try it and see' strategy!
Therefore, unless you have an overriding reason for pre-validating changes, we strongly recommend that you let Visual FoxPro detect conflicts and just trap for and handle such errors as they arise.
There are four basic strategies for handling update conflicts. You can choose one, or combine more than one in an application depending on the actual situation:
[1] The Current User Always Wins This strategy is appropriate only in those situations in which the user who is actually trying to save is one whose data is assumed to be the most accurate. Typically this would be implemented on the basis of the ID of the user who is actually doing the save and would implement a business rule that certain people's information is of more value than others.
An example might be in a TeleSales application where an operator talking to the customer could have override rights to contact information for the customer (on the basis that the person actually talking to the customer is most likely to be able to get the correct details). Conflicts could arise in this situation when an administrator is updating a customer's details from data on file or last order, while an operator is getting new details directly from the customer. The implementation of this strategy in Visual FoxPro is very simple indeed. Simply set the "FORCE" (second) parameter in the TableUpdate() function to ".T." and re-submit the update.
An example might be in a TeleSales application where an operator talking to the customer could have override rights to contact information for the customer (on the basis that the person actually talking to the customer is most likely to be able to get the correct details). Conflicts could arise in this situation when an administrator is updating a customer's details from data on file or last order, while an operator is getting new details directly from the customer. The implementation of this strategy in Visual FoxPro is very simple indeed. Simply set the "FORCE" (second) parameter in the TableUpdate() function to ".T." and re-submit the update.
[2] The Current User Always Loses This is the exact reverse of the above situation. The current user is only allowed to save changes providing that no other user has made changes. Again this would normally be implemented on the basis of a User's ID and would reflect the probability that this particular user is likely to be working from "historical", rather than "current", information. The implementation in Visual FoxPro is also very simple. The current user's changes are reverted, the source data is re-queried, and the user has to make any changes still required all over again. This is probably the strategy that is most often adopted - but usually on an across-the-board basis!
[3] The Current User Sometimes Wins This strategy is the most complex of the four to implement but is actually quite common. The underlying principle is that when an update conflict occurs, you determine whether any of the fields that the current user has changed will affect the changes that were made by the other user. If not, the current user's record is updated automatically (using the CURVAL() values) so that the cause of the conflict is negated and the update is then re-submitted. However, because you cannot change the values returned by OldVal(), you need to force this second update.
Incidentally, this strategy also addresses the issue of how to handle a 'false positive' update conflict. This occurs when there is a discrepancy between the values on disk and those in the current user's buffer, but the current user's changes do not actually conflict with any of the changes made. Clearly this situation is not really a conflict but does need to be handled.
While not trivial, the implementation in Visual FoxPro is relatively easy. First, the CURVAL() function is used to determine how to update the current user's buffer so that it will not overwrite changes made by another user. Then the update is applied using the FORCE (second) parameter in the TableUpdate() to tell Visual FoxPro to ignore the conflict that will arise because OldVal() and CurVal() do not correspond.
Incidentally, this strategy also addresses the issue of how to handle a 'false positive' update conflict. This occurs when there is a discrepancy between the values on disk and those in the current user's buffer, but the current user's changes do not actually conflict with any of the changes made. Clearly this situation is not really a conflict but does need to be handled.
While not trivial, the implementation in Visual FoxPro is relatively easy. First, the CURVAL() function is used to determine how to update the current user's buffer so that it will not overwrite changes made by another user. Then the update is applied using the FORCE (second) parameter in the TableUpdate() to tell Visual FoxPro to ignore the conflict that will arise because OldVal() and CurVal() do not correspond.
[4] The Current User Decides This is the "Catch All" scenario. The conflict does not fall under any recognizable business rule so the only solution is to ask the user whose save action has triggered the conflict what they wish to do about it. The underlying idea here is that you present the user who has triggered the conflict with a list of values - the one they just entered, the value that was in the table (i.e. that which they have just changed) and the value that is now in the table (i.e. that to which someone else has changed the original value). The user can then decide whether to force or revert their own changes. The basic tools for implementing this strategy have already been discussed in the preceding sections. All that is required is to determine which fields actually conflict and to present them to the user in such a way that the user can decide on a field by field basis what to do about the conflicts. In practice, this strategy is usually combined with Strategy [3] above so that the user is only presented with a list of fields where there is actually a conflict in the fields they have themselves modified.
In the next article in this series, I will discuss the design and implementation of a conflict handling class that that can be dropped on to any form.
Published Tuesday, December 27, 2005 1:24 PM by andykr
No comments:
Post a Comment