Introduction to Data Buffering in Visual FoxPro
Prepared for the Tallahassee FoxPro Users Group (TFUG)
ã 1999, John Koziol, Advanced Systems Design (
Feel free to distribute this document at will providing the author remains credited.
Introduction
Visual FoxPro 3.0 introduced Fox developers to the concept of data buffering yet another means of managing data in a multi-user environment. Data buffering is defined as holding data in memory to manipulate it without direct access to its source. Throughout this document, I’ll refer to the source table as the base table. There are two types of buffering, defined as table buffering and rows buffering. There are also two modes of buffering, called optimistic buffering and pessimistic buffering. The types and modes can be mixed-and-matched at will; i.e., you can have optimistic row buffering and pessimistic table buffering. I’ll explore each of the possible flavors with some practical scenarios where you might want to consider using them. For now, I’ll discuss some basic definitions pertaining to buffering types and modes.
Buffering takes on a whole new level of complexity when dealing with local views, remote views, and SQL pass-through (aka SPT). Those topics will be addressed in another presentation and document.
Why Buffer?
Data buffering is a requirement to be able to bind data to Visual FoxPro controls. Binding is when the controls ControlSource, RowSource, and/or RecordSource is set directly to a table row and column; i.e., THISFORM.txtLastName.ControlSource="customer.lastname". In the pre-Visual FoxPro days, most of us used to use memory variables and "bind" our GETS to these memvars rather than directly to fields and records. In effect, we did the buffering manually. So, it may be a comforting thought that data buffering is nothing more than a power-assist from VFP allowing us to streamline ways of accessing data that we have already been using with different commands and techniques.
Table Buffering
Table buffering is where the entire table may be append to, deleted from, or multiple rows edited without changes being written to the base table. How these changes are applied to the base table is dependent upon the buffering mode. Table buffering is the preferred type of buffering when dealing with child tables in parent-child data relationships.
Row Buffering
Row buffering is where an individual row of a table is buffered. Changes applied to a given row must be dealt with before the record pointer is moved to a different row. Again, the how is buffer mode dependent. Row buffering is often the preferred type of buffering when dealing with parent rows in a parent-child data relationship.
Optimistic Buffering
Optimistic buffering is a mode of buffering where locks to individual rows or a table are not applied until changes are to be written to the base table. By far, the standard for Visual FoxPro application is to use optimistic buffering.
Pessimistic Buffering
Pessimistic buffering is a mode of buffering where locks to a table or rows are applied when the user begins to change information. In theory, pessimistic buffering provides the greatest security for base tables; in practice, pessimistic buffering, especially pessimistic table buffering, can really ruin your day as it is not very application or user-friendly, as we’ll see.
The Command and Function Cavalcade
The following commands are critical to using and understanding data buffering. I’ll briefly discuss the role each plays; for further reading I would highly recommend the following books:
The Hacker’s Guide to Visual FoxPro 6.0
Visual FoxPro 6.0 Developers Guide
Visual FoxPro 6.0 Language Reference
Listed are the latest versions of these books but earlier versions for Visual FoxPro 3.0 and 5.0 may be found (except for the Hacker’s Guide which only has a VFP 3.0 version). All of these books may be found at
Now, to the commands and functions:
SET EXCLUSIVE ON/OFF
This is an old, old dBASE III+ command. Determines whether tables are opened exclusively or for shared access. Obviously, should be set OFF.
SET MULTILOCKS ON/OFF
Determines whether or not multiple row locks can be set (ON) or not (OFF). Buffering requires this setting to be ON.
=TABLEUPDATE([nRows [, lForce]] [, cTableAlias | nWorkArea][, cErrorArray])
Attempts to change base table data with buffered changes, additions, or deletions. Returns .T. if successful, .F. if not. If the lForce parameter is set .T., will almost always return .T. even if the update was not successful. Values for nRows are as follows:
0 - Only changes to the current row are committed regardless of buffer type.
1 - With table buffering, all changes are committed. With row buffering, only the
changes to the current row are committed.
2 - Works the same as 1, however, no error is generated should rows not be
committed unless an array is specified with cErrorArray.
=TABLEREVERT([lAllRows [, cTableAlias | nWorkArea]])
Discards any buffered changes since the last TABLEUPDATE or TABLEREVERT. Will almost always return .T.. With table buffering, the lAllRows parameter becomes important, as it is the way to specify whether to discard all changes to a table (.T.) or just changes to the current row (.F.)
=CURSORSETPROP(cProperty [, eExpression] [, cTableAlias | nWorkArea])
This function, along with SQLSETPROP() and DBSETPROP(), are the Swiss Army knives of working with tables. Specifically, we’re interested in the "Buffering" value for cProperty as this allows us to chose a buffer type and mode for a table. Valid "Buffering" property values are:
1 - Sets row and table buffering off.
2 - Sets pessimistic row buffering.
3 - Sets optimistic row buffering.
4 - Sets pessimistic table buffering.
5 - Sets pessimistic row Buffering.
BEGIN TRANSACTION, END TRANSACTION, ROLLBACK
These three commands are highly useful when you wish to commit or discard (see TABLEUPDATE and TABLEREVERT commands, respectively, above) multiple changes to multiple tables. Transaction commands are nearly useless with single table updates or discards unless you wish to use them to enforce a business rule such as not being able to commit x amount of records forces a ROLLBACK of the entire commit. Transaction commands are worse than useless with the older LOCK() and FLOCK() functions: There are numerous bugs associated with using the transaction commands and standard non-buffered locking means. As to what they do: BEGIN TRANSACTION opens a transaction. Any data buffer commits or reverts issued after a BEGIN TRANSACTION are themselves buffered until an END TRANSACTION or a ROLLBACK is encountered. END TRANSACTION ends the transaction and commits all changes since the BEGIN TRANSACTION. ROLLBACK discards all changes
since the BEGIN TRANSACTION.
It may be easier to think of transactions as large-scale equivalents of TABLEUPDATE and TABLEREVERT.
=OLDVAL(cExpression [, cTableAlias | nWorkArea]) and
=CURVAL(cExpression [, cTableAlias | nWorkArea])
These two functions, along with simply table.column tell us the value for a given column (cExpression) in a given table (cTableAlias | nWorkArea). OLDVAL() returns the value of the column when it was last read from the base table. CURVAL() returns the current value of the column in the base table. Table.column returns, of course, the current modified value of the column. Sometimes the difference between the current value of the column and CURVAL() is confusing and not well understood. The current value of the column is whatever you have changed it to. It represents the current uncommitted value at your workstation. CURVAL() represents the current column value in the base table. If another user has changed a column value and committed those changes, CURVAL() and the current value will not be the same. CURVAL() and OLDVAL() are very useful in data conflict management.
=GETFLDSTATE(cColumnName | nColumnNumber [, cTableAlias | nWorkArea])
This function returns a numeric value that reflects the change state of a given column or a character value that reflects the change state of the entire table. If cColumnName or nColumnNumber represents a column in the current table or cTableAlias, a numeric value is returned; if –1 is used for nColumnNumber, a character string composed of numeric values for each column in the table is returned. The values returned are:
1 - The column has not been edited or deletion status changed.
2 - Column has been edited or deletion status changed.
3 - Column in an appended record has not been edited nor deletion status changed.
4 - Column in an appended record has been edited or deletion status changed.
If you have a three column table and only the second field has been edited, GETFLDSTATE(2) returns the numeric value of 2. GETFLDSTATE(-1) in the same circumstance returns the character string of "121".
Please note: There is a SETFLDSTATE() function to manually set a column to one of the four values above. This function is highly unreliable as of Visual FoxPro 5.0 and should just simply be ignored up to that version. It purportedly works in Visual FoxPro 6.0.
DataEnvironment.Cursor.BufferModeOverride[ = nValue]
A Form’s DataEnvironment container allows certain Cursor control properties that emulate commands that would normally be done via other commands and functions. The BufferModeOverride property allows us to set a buffer mode for a table opened within a Form’s DataEnvironment, although the property values are slightly different than the equivalent values using CURSORSETPROP(). The valid values for BufferModeOverride are:
0 - None, no buffering.
1 - Use the Form’s BufferMode setting. (See below)
2 - Pessimistic row buffering.
3 - Optimistic row buffering.
4 - Pessimistic table buffering.
5 - Pessimistic row buffering.
Form.BufferMode [ = nValue]
A general mode of buffering for all tables opened in that Form’s DataEnvironment can be set via the BufferMode property. Using this property makes the type of buffering implicit to how that table is being used. Tables bound to a Grid control will be table buffered; all other tables are row buffered. The valid values for BufferMode are:
0 - None. No buffering.
1 - Pessimistic buffering.
2 - Optimistic buffering.
Please Note
Certainly, there are several other Visual FoxPro commands and functions related to data buffering. Most of those, however, go out of scope of the introductory nature of this document.
Specific Buffering Examples
Since a large number of states have elected new governors, let’s use a voter’s registration system as the basis of our examples of the types and modes of buffering and how they may be implemented. We’ll be using two tables: A voters table and a voting table. The voters table contains one row per registered voter; a voting table is a child table of the voters table and contains one record per election in which a particular voter cast a vote.
Optimistic Row Buffering
Optimistic row buffering is where the current row is buffered and changes to that row must be committed or discarded before moving the record pointer to a new row. If a TABLEUPDATE or TABLEREVERT is not explicitly issued when the record pointer is moved, a TABLEUPDATE is implicitly applied. If a table with uncommitted row buffer changes is closed, a Visual FoxPro error will occur and the table remains open.
Optimistic buffering is ideal for situations where only one row at one time may be edited. A primary data entry screen is a good candidate for optimistic row buffering. In our registration system we may wish to use optimistic row buffering for a form in which basic information about a voter is edited.
What do we look out for to implement this? First, we have to ensure that the form cannot be closed nor can the current row be changed without using GETFLDSTATE() to ensure that there are no pending changes and, if there are, TABLEUPDATE() and TABLEREVERT() may be used to either commit or discard changes before the record pointer is moved. This code, for example, could be found in a "Next" button before the SKIP command or equivalent:
IF "2" $ GETFLDSTATE(-1,"voters") OR "4" $ GETFLDSTATE(-1,"Voters")
nDoWhat=MESSAGEBOX("Save changes?",36,"Go Next")
IF nDoWhat = 6
=TABLEUPDATE(0,.T.,"voters")
ELSE
=TABLEREVERT(.T.,"voters")
ENDIF
The previous code assumes that the Form.BufferMode property has been set to 2 (Optimistic) or that the Form.DataEnvironment.Cursor.BufferModeOverride property for the voter table has been set to 3 (Optimistic row buffering) or the table’s buffermode has been set with CURSORSETPROP("Buffering",3,"voters"). It also requires that SET MULTILOCKS ON has been issued.
Optimistic Table Buffering
Optimistic table buffering is the type and mode of buffering where the entire table may be added, deleted or modified without committing changes to any single row. In theory, no updates or reverts are necessary until just before the table is closed, but this is rarely practical or desired. As with row buffering (and, if fact, with any buffering at all) an error will occur if an attempt is made to close a table with uncommitted changes and the table will not be closed.
An ideal use for this style of buffering is with child records in a Grid. Logically, a child table setup through the DataEnvironment, through Grid properties, or with a parameterized view only shows rows specific to the current parent record. In our application, our child table is the voting table. We may wish to show the voting frequency for a given voter and use a Grid to show those voting records. Since a user may change more than one row in a grid at one time, the source table has to be table buffered.
An oddity occurs with new records appended to a table buffered table. The value of RECNO() is actually negative, starting with –1, for all uncommitted appended records.
How do we implement this? Well, more or less the same as for row buffering. A big difference is that we no longer are forced to check to see if records are modified when the record pointer is moved. Generally, we’ll put our "save" code in an OK or Apply button. What is the code? Very simple. To commit all row changes to the table, we only need this:
=TABLEUPDATE(1,.T.,"voting")
To revert (undo) all changes to the table we only need this:
=TABLEREVERT(.T.,"voting")
Why not always use optimistic table buffering if it’s so easy? Because of user expectations and current paradigms, for the most part. If a user changed a row, moved to another row and changed it and then pressed an Undo button (wanting to cancel changes to the second row), he or she would actually be undoing changes to all rows. This would be very unexpected behavior.
Pessimistic Row Buffering
Pessimistic buffering as a whole is not very popular at this time. Due to the advent of more and more friendly user interfaces and user-empowerment advocates such as Alan Cooper (his book About Face: The Essentials of Interface Design is a must read), pessimistic buffering is in disfavor as it prevents the user from having the illusion that they are in control of application data (we FoxPro developers have always known different of course, right?).
With pessimistic row buffering, when a user modifies data in a bound control, the base table is locked. Any other user attempting to modify data in the same row and table will get the error message "Record is in use by another". This is generally not recognized as particularly friendly behavior. The best way to work this out is if your Form will have a dedicated Edit button for a user to request the ability to edit and all controls are disabled until the edit is "granted".
How is this accomplished? In the Click() event of an edit button, you could attempt to place a LOCK() on the row. If the lock fails, another user must be editing the rowand you tell your user such. If it succeeds you enable your controls and maintain the manual lock until the users saves or reverts the record.
There really is no other way to proactively use pessimistic row buffering as any means of enabling controls without an Edit button will cause a Visual FoxPro error if the user attempts to change data bound to those controls. You could trap these errors through ON ERROR or Error Form or control properties, if need be.
Pessimistic Table Buffering
Pessimistic table buffering is where the entire table is locked when the table is modified. I can think of very few reasons to use this means of buffering data. This is the equivalent to issuing a FLOCK() in earlier versions of FoxPro. Very rarely will you have come across a situation where you would want to hold a file lock indefinitely and this is precisely what happens when using pessimistic table buffering.
Conclusions
Beginning to work with data buffering is easy. These steps will set up a good experimental situation for learning more about buffering.
An understanding of data buffering in Visual FoxPro is important as a first step to understanding client/server development, server databases, and n-tier or web application development. What is appealing about the way Visual FoxPro handles data buffering is that the developer can "phase in" new tricks and techniques as he or she learns more about the way it works.
