Buffering Data

Article
10/23/2006

If you want to protect data during updates, use buffers. Visual FoxPro record and table buffering help you protect data update and data maintenance operations on single records and on multiple records of data in multi-user environments. Buffers can automatically test, lock, and release records or tables.

With buffering, you can easily detect and resolve conflicts in data update operations: the current record is copied to a memory or disk location managed by Visual FoxPro. Other users can then still access the original record simultaneously. When you move from the record or try to update the record programmatically, Visual FoxPro attempts to lock the record, verify that no other changes have been made by other users, and then writes the edits. After you attempt to update data, you must also resolve conflicts that prevent the edits from being written to the original table.

Choosing a Buffering Method

Before you enable buffering, evaluate the data environment to choose the buffering method and locking options that best suit the editing needs of your application, the record and table types and sizes, how the information is used and updated, and other factors. Once you enable buffering, it remains in effect until you disable buffering or close the table.

Visual FoxPro has two types of buffering: record and table.

Visual FoxPro record and table buffering

To access, modify, and write a single record at a time, choose record buffering.

Record buffering provides appropriate process validation with minimal impact on the data update operations of other users in a multi-user environment.
To buffer the updates to several records, choose table buffering.

Table buffering provides the most effective way to handle several records in one table or child records in a one-to-many relationship.
To provide maximum protection for existing data, use Visual FoxPro transactions.

You can use transactions alone, but you gain additional effectiveness by using transactions as wrappers for record or table buffering commands.

Choosing a Locking Mode

Visual FoxPro provides buffering in two locking modes: pessimistic and optimistic. These choices determine when one or more records are locked, and how and when they're released.

Pessimistic Buffering

Pessimistic buffering prevents other users in a multi-user environment from accessing a particular record or table while you're making changes to it. A pessimistic lock provides the most secure environment for changing individual records but it can slow user operations. This buffering mode is most similar to the standard locking mechanism in previous versions of FoxPro, with the added benefit of built-in data buffering.

Optimistic Buffering

Optimistic buffering is an efficient way to update records because locks are only taken at the time the record is written, thus minimizing the time any single user monopolizes the system in a multi-user environment. When you use record or table buffering on views, Visual FoxPro imposes optimistic locking.

The value of the Buffering property, set with the CURSORSETPROP( ) function, determines the buffering and locking methods.

The following table summarizes valid values for the Buffering property.

To enable	Use this value
No buffering. The default value.	1
Pessimistic record locks which lock record now, update when pointer moves or upon TABLEUPDATE( ).	2
Optimistic record locks which wait until pointer moves, and then lock and update.	3
Pessimistic table locks which lock record now, update later upon TABLEUPDATE( ).	4
Optimistic table lock which wait until TABLEUPDATE( ), and then lock and update edited records.	5

The default value for Buffering is 1 for tables and 3 for views. If you use buffering to access remote data, the Buffering property is either 3, optimistic row buffering, or 5, optimistic table buffering. For more information on accessing data in remote tables, see Querying Multiple Tables and Views.

Note Set MULTILOCKS to ON for all buffering modes above 1.

Enabling Record Buffering

Enable record buffering with the CURSORSETPROP( ) function.

To enable pessimistic record locking in the current work area

Use this function and value:
```
CURSORSETPROP("Buffering", 2)
```

Visual FoxPro attempts to lock the record at the pointer location. If the lock is successful, Visual FoxPro places the record in a buffer and permits editing. When you move the record pointer or issue a TABLEUPDATE( ) command, Visual FoxPro writes the buffered record to the original table.

To enable optimistic record locking in the current work area

Use this function and value:
```
CURSORSETPROP("Buffering", 3) 
```

Visual FoxPro writes the record at the location of the pointer to a buffer and permits edits. When you move the record pointer or issue a TABLEUPDATE( ) command, Visual FoxPro attempts a lock on the record. If the lock is successful, Visual FoxPro compares the current value of the record on the disk with the original buffer value. If these values are the same, the edits are written to the original table; if these values are different, Visual FoxPro generates an error.

Enabling Table Buffering

Enable table buffering with the CURSORSETPROP( ) function.

To enable pessimistic locking of multiple records in the current work area

Use this function and value:
```
CURSORSETPROP("Buffering", 4) 
```

Visual FoxPro attempts to lock the record at the pointer location. If the lock is successful, Visual FoxPro places the record in a buffer and permits editing. Use the TABLEUPDATE( ) command to write the buffered records to the original table.

To enable optimistic locking of multiple records in the current work area

Use this function and value:
```
CURSORSETPROP("Buffering", 5)
```

Visual FoxPro writes the records to a buffer and permits edits until you issue a TABLEUPDATE( ) command. Visual FoxPro then performs the following sequence on each record in the buffer:

Attempts a lock on each edited record.
Upon a successful lock, compares the current value of each record on the disk with the original buffer value.
Writes the edits to the original table if the comparison shows the values to be the same.
Generates an error if the values differ.

When table buffering is enabled, Visual FoxPro attempts updates only after a TABLEUPDATE( ) command.

Appending and Deleting Records in Table Buffers

You can append and delete records while table buffering is enabled: appended records are added to the end of the buffer. To access all records in the buffer, including appended records, use the RECNO( ) function. The RECNO( ) function returns sequential negative numbers on records you append to a table buffer. For instance, if you initiate table buffering, edit records 7, 8, and 9, and then append three records, the buffer will contain RECNO( ) values of 7, 8, 9, – 1, – 2, and – 3.

Buffer after editing and appending records

You can remove appended records from the buffer only by using the TABLEREVERT( ) command. For any appended record, both TABLEUPDATE( ) and TABLEREVERT( ) delete the negative RECNO( ) value for that record while maintaining the sequence.

Buffer after editing, deleting an appended record, and appending another

While using a table buffer, you can use the GO command with the negative RECNO( ) value to access a specific appended record. For instance, using the previous example, you can type:

GO 7      && moves to the 1st buffered record
GO -3      && moves to the 6th buffered record (3rd appended)

To append records to a table buffer

Use the APPEND or APPEND BLANK command after you enable table buffering.

Appended records have sequential ascending negative RECNO( ) numbers.

To remove an appended record from a table buffer

Use the GO command with a negative value to position the record pointer at the record to be deleted.
Use the DELETE command to mark the record for deletion.
Use the TABLEREVERT( ) function to remove the record from the buffer.

Note The TABLEREVERT( ) function also affects the status of deleted and changed rows.

To remove all appended records from a table buffer

Use the TABLEREVERT( ) function with a value of true (.T.).

TABLEREVERT( ) removes appended records from a table buffer without writing the records to the table. TABLEUPDATE( ) writes all current buffered records to a table, even if they've been marked for deletion.