If you create an application that will run on several machines in a network environment, or if several instances of a form will access the same data, then you need to program for shared access. Shared access means providing efficient ways of using and sharing data among users, as well as restricting access when necessary.
Visual FoxPro provides support for shared or exclusive access to data, locking options, data sessions, record and table buffering, and transactions. Although these features are particularly useful in shared environments, you can use them in single-user environments too.
This chapter discusses:
Since you access data in files, effective data management begins with control over the environment of these files. You must choose how to access the data and how and when to limit that access.
In a shared environment, you can access data in two ways: from exclusive files or from shared files. If you open a table for shared access, other users also have access to the file. If you open a table for exclusive access, no other user can read or write to that file. Because exclusive use defeats many of the benefits of sharing data on a network, it should be used sparingly.
The most restrictive way to open a file is to open it exclusively. When you open a table through the interface, it opens for exclusive use by default. You can also explicitly open a table for exclusive use by using Visual FoxPro commands.
To open a table for exclusive use
SET EXCLUSIVE ON
USE cMyTable
-or-
USE cMyTable EXCLUSIVE
The following commands require you to open a table for exclusive use:
Visual FoxPro returns the error, “Exclusive open of file is required,” if you try to execute one of these commands on a shared table.
You can restrict access to a table by using the FLOCK( ) function. If you use FLOCK( ) to lock the table, other users cannot write to the table but they can read it.
When you open a table for shared use, more than one workstation can use the same table at the same time. When you open a table through the interface, you can override the default ON setting for SET EXCLUSIVE. You can explicitly open a table for shared use by using Visual FoxPro commands.
To open a table for shared use
SET EXCLUSIVE OFF
USE cMyTable
-or-
USE cMyTable SHARED
When you add or change data in a shared table, you must first lock the affected record or the entire table. You can lock a record or a table opened for shared use in the following ways:
Associated memo and index files always open with the same share status as their table.
If your application uses a table for lookup purposes only and all users of the application access it, then you can improve performance by marking the table as read-only.
If you share access to files, you must also manage access to data by locking tables and records. Locks, unlike access permissions, can provide both long- and short-term control of data. Visual FoxPro provides both automatic and manual locking.
Record locking, whether automatic or manual, prevents one user from writing to a record that’s currently being written to by another user. Table locking prevents other users from writing to, but not reading from, an entire table. Because table locking prohibits other users from updating records in a table, it should only be used sparingly.
In addition to record or table locking, you can also choose automatic or manual locking. Many Visual FoxPro commands automatically attempt to lock a record or a table before the command is executed. If the record or table is successfully locked, the command is executed and the lock is released.
Commands that Automatically Lock Records and Tables
| Command | Scope of lock |
| ALTER TABLE | Entire table |
| APPEND | Table header |
| APPEND BLANK | Table header |
| APPEND FROM | Table header |
| APPEND FROM ARRAY | Table header |
| APPEND MEMO | Current record |
| BLANK | Current record |
| BROWSE, CHANGE and EDIT | Current record and all records from aliased fields in related tables once editing of a field begins |
| CURSORSETPROP( ) | Depends on parameters |
| DELETE | Current record |
| DELETE NEXT 1 | Current record |
| DELETE RECORD n | Record n |
| DELETE of more than one record | Entire table |
| DELETE – SQL | Current record |
| GATHER | Current record |
| INSERT | Entire table |
| INSERT - SQL | Table header |
| MODIFY MEMO | Current record when editing begins |
| READ | Current record and all records from aliased fields |
| RECALL | Current record |
| RECALL NEXT 1 | Current record |
| RECALL RECORD n | Record n |
| RECALL of more than one record | Entire table |
| REPLACE | Current record and all records from aliased fields |
| REPLACE NEXT 1 | Current record and all records from aliased fields |
| REPLACE RECORD n | Record n and all records from aliased fields |
| REPLACE of more than one record | Entire table and all files from aliased fields |
| SHOW GETS | Current record and all records referenced by aliased fields |
| TABLEUPDATE( ) | Depends on buffering |
| UPDATE | Entire table |
| UPDATE – SQL | Entire table |
Record Lock Characteristics
Commands that attempt record locks are less restrictive than commands that lock tables. When you lock a record, other users can still add or delete other records. If a record or table is already locked by another user, an attempted record or table lock fails. Commands that attempt to lock the current record return the error, “Record is in use by another,” if the record cannot be locked.
The BROWSE, CHANGE, EDIT, and MODIFY MEMO commands do not lock a record until you edit the record. If you're editing fields from records in related tables, the related records are locked if possible. The lock attempt fails if the current record or any of the related records are also locked by another user. If the lock attempt is successful, you can edit the record; the lock is released when you move to another record or activate another window.
Header and Table Lock Characteristics
Some Visual FoxPro commands lock an entire table while others only lock a table header. Commands that lock the entire table are more intrusive than commands that only lock the table header. When you lock the table header, other users cannot add records, but they can still change data in fields.
Users can share the table without causing a conflict when you issue the APPEND BLANK command, but an error can occur while another user is also appending a BLANK record to the table. You can trap for the error, “File is in use by another,” which is returned when two or more users execute APPEND BLANK simultaneously. Commands that lock an entire table return the error, “File is in use by another,” if the table cannot be locked. To cancel the attempted lock, press ESC.
Example: Automatic Locking
In the following example, the user automatically locks the table header by appending records from another table, even though customer was opened as a shared file:
SET EXCLUSIVE OFF
USE customer
APPEND FROM oldcust FOR status = "OPEN"
You can manually lock a record or a table with locking functions.
To manually lock a record or a table
RLOCK()
LOCK()
FLOCK()
RLOCK( ) and LOCK( ) are identical and lock one or more records. FLOCK( ) locks a file. The LOCK( ) and RLOCK( ) functions can apply to a table header. If you provide 0 as the record to LOCK( ) or RLOCK( ) and the test indicates the header is unlocked, the function locks the header and returns true (.T.).
Once you lock a record or table, be sure to release the lock by using the UNLOCK command as soon as possible to provide access to other users.
These manual locking functions perform the following actions:
Tip If you want to test the lock status of a record in your session without locking the record, use the ISRLOCKED( ) or ISFLOCKED( ) function.
If an attempt to lock a record or table fails, the SET REPROCESS command and your current error routine determine if the lock is attempted again. SET REPROCESS affects the result of an unsuccessful lock attempt. You can control the number of lock attempts or the length of time a lock is attempted with SET REPROCESS.
Example: Manual Locking
The following example opens the customer table for shared access and uses FLOCK( ) to attempt to lock the table. If the table is successfully locked, REPLACE ALL updates every record in the table. UNLOCK releases the file lock. If the file cannot be locked because another user has locked the file or a record in the file, a message is displayed.
SET EXCLUSIVE OFF
SET REPROCESS TO 0
USE customer && Open table shared
IF FLOCK()
REPLACE ALL contact ; && Replace and unlock
WITH UPPER(contact)
UNLOCK
ELSE && Output message
WAIT "File in use by another." WINDOW NOWAIT
ENDIF
After you establish a record or file lock and complete a data operation in a shared environment, you should release the lock as soon as possible. There are several ways to release locks. In some cases, simply moving to the next record is enough to unlock the data. Other situations require explicit commands.
To unlock a record that's been automatically locked, you need only move the record pointer, even if you set MULTILOCKS ON. You must explicitly remove a lock from a record that you’ve manually locked; simply moving the record pointer is not enough.
The following table describes the effects of commands on manual and automatic record and table locks.
| Command | Effect |
| UNLOCK | Releases record and file locks in the current work area. |
| UNLOCK ALL | Releases all locks in all work areas in the current session. |
| SET MULTILOCKS OFF | Enables automatic release of the current lock as a new lock is secured. |
| FLOCK( ) | Releases all record locks in the affected file before locking the file. |
| CLEAR ALL, CLOSE ALL, USE, QUIT |
Releases all record and file locks. |
| END TRANSACTION | Releases automatic locks. |
| TABLEUPDATE( ) | Releases all locks after updating the table. |
Caution If a record was automatically locked in a user-defined function and you move the record pointer off and then back on the record, the lock will be released. Use table buffering to avoid this problem.
To ensure that each user in a shared environment has a secure, exact duplicate of the working environment, and to ensure that multiple instances of a form can operate independently, Visual FoxPro provides data sessions.
A data session is a representation of the current dynamic work environment. You might think of a data session as a miniature data environment running inside one open Visual FoxPro session on one machine. Each data session contains:
The concept of a data session is easily understood when you consider what happens when you open the same form simultaneously from separate workstations in a multi-user application. In this case, each workstation is running a separate Visual FoxPro session, and therefore has its own set of work areas: cursors representing open base tables, indexes, and relationships.
However, if you open multiple instances of the same form in a single project, on one machine, within the same Visual FoxPro session, the forms share the Default data session, representing a single dynamic work environment. Each instance of the open form open in the same Visual FoxPro session uses the same set of work areas, and actions in one instance of a form that move the record pointer in a work area automatically affect other instances of the same form.
If you want to have more control over multiple instances of form, you can implement Private data sessions. When your form uses private data sessions, Visual FoxPro creates a new data session for each instance of the Form, FormSet, or Toolbar control your application creates. Each private data session contains:
The number of available data sessions is limited only by available system memory and disk space.
You implement private data sessions by setting the DataSession property for the form. The DataSession property has two settings:
By default, the DataSession property of a form is set to 1.
To enable private data sessions
Choose one of the following options:
-or-
For example, type:
frmFormName.DataSession = 2
Note You can only set the DataSession property at design time. The DataSession property is read-only at run time.
When a form uses private data sessions, each instance of a form open on a single machine in a single Visual FoxPro session uses its own data environment. Using private data sessions is similar to running the same form simultaneously from separate workstations.
Equivalent multiple data sessions
Each private data session is identified separately. You can see the contents of each data session in the Data Session window. You can also change the data session description through commands in the Load event code.
You can view the identification number for each data session by using the DataSessionID run-time property. The following example displays the DataSessionID property of a form named frmMyForm:
DO FORM frmMyForm
? frmMyForm.DataSessionID
If you activate the form using the NAME clause, you can use the form’s name to access the DataSessionID property, as in the following code:
DO FORM MyForm NAME one
? one.DataSessionID
The DataSessionID property is designed only to identify a particular data session. Avoid changing the DataSessionID of a form instance because data-bound controls lose their data sources when you change the DataSessionID.
While private data sessions generate separate work areas containing separate copies of a form’s open tables, indexes, and relationships, every copy of the form references the same underlying base tables and base index files. When a user updates a record from one instance of a form, the base table referenced by the form is updated. You see the changes made from another instance of the form when you navigate to the changed record.
Locks taken on records or tables in one private data session are respected by other private data sessions. For example, if the user of data session 1 takes a lock on a record, the user in data session 2 cannot lock the record. If the user in session 1 opens a table exclusively, the user in data session 2 cannot open the table. By respecting locks taken by other data sessions, Visual FoxPro protects the integrity of updates to the underlying base tables.
Because data sessions control the scope of certain SET commands, you can use private data sessions to establish custom SET command settings within a single session of Visual FoxPro.
For example, the SET EXACT command, which controls the rules used when comparing character strings of different lengths, is scoped to the current data session. The default setting for SET EXACT is off which specifies that, to be equivalent, expressions must match, character for character, until the end of the expressions on the right side is reached. You might want to enable “fuzzy” or equivalent searches by leaving SET EXACT set to OFF for the default data session; however, your application might contain a specific form that requires exact matches. You could set the DataSession property for the form requiring exact matches to 2, to enable private data sessions, and then SET EXACT to ON for that form. By issuing a SET command only for the form using private data sessions, you preserve the global Visual FoxPro session settings while enabling customized session settings for a specific form.
When private data sessions for a form are in use, changes you make to data in one form are not automatically represented in other instances of the same form. If you want all instances of a form to access the same data and to immediately reflect changes to common data, you can override automatic data session assignment.
To override automatic data session assignment
SET DATASESSIONTO 1
-or-
SET DATASESSIONTO
Both commands enable the default data session to be controlled by the Command window and the Project Manager.
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.
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
Record buffering provides appropriate process validation with minimal impact on the data update operations of other users in a multi-user environment.
Table buffering provides the most effective way to handle several records in one table or child records in a one-to-many relationship.
You can use transactions alone, but you gain additional effectiveness by using transactions as wrappers for record or table buffering commands. For more details, see the section, Managing Updates with Transactions, later in this chapter.
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 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 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 Chapter 6, Querying and Updating Multiple Tables, in the User’s Guide.
Note Set MULTILOCKS to ON for all buffering modes above 1.
Enable record buffering with the CURSORSETPROP( ) function.
To enable pessimistic record locking in the current work area
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
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.
Enable table buffering with the CURSORSETPROP( ) function.
To enable pessimistic locking of multiple records in the current work area
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
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:
When table buffering is enabled, Visual FoxPro attempts updates only after a TABLEUPDATE( ) command.
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
Appended records have sequential ascending negative RECNO( ) numbers.
To remove an appended record from a table buffer
Note The TABLEREVERT( ) function also affects the status of deleted and changed rows.
To remove all appended records from a table buffer
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.
To update data, you can use buffers, transactions, or views.
After choosing the buffering method and the type of locking, you can enable record or table buffering.
To enable buffering
Choose one of the following options:
-or-
For example, you can enable pessimistic row buffering by placing the following code in the Init procedure of a form:
CURSORSETPROP('Buffering', 2)
You then place code for the update operations in the appropriate method code for your controls.
To write edits to the original table, use TABLEUPDATE( ). To cancel edits after a failed update operation in a table constrained by rules, use TABLEREVERT( ). TABLEREVERT( ) is valid even if explicit table buffering isn’t enabled.
The following sample demonstrates how to update records when pessimistic record buffering is enabled.
Example of Updating Using Record and Table Buffers
| Code | Comment |
|
In the form Init code, open the table and enable pessimistic record buffering. |
|
Go through fields, checking for any field that's been modified. Note This code might be in the Click event of a “Save” or “Update” command button. |
|
Locate the next modified record. |
|
Present the current value and give the user the option to revert the change to the current field. |
|
SKIP guarantees that the last change is written. |
Even with buffering, things can go wrong. If you want to protect update operations and recover from an entire section of code as a unit, use transactions.
Adding transactions to your application provides protection beyond Visual FoxPro record and table buffering by placing an entire section of code in a protected, recoverable unit. You can nest transactions and use them to protect buffered updates. Visual FoxPro transactions are available only with tables and views contained in a database.
A transaction acts as a wrapper that caches data update operations to memory or to disk, rather than applying those updates directly to the database. The actual database update is performed at the end of the transaction. If for any reason the system cannot perform the update operations on the database, you can roll back the entire transaction and no update operations are performed.
Note Buffered update operations made outside a transaction are ignored within a transaction in the same data session.
Visual FoxPro provides three commands and one function to manage a transaction.
| To | Use |
| Initiate a transaction | BEGIN TRANSACTION |
| Determine the current transaction level | TXNLEVEL( ) |
| Reverse all changes made since the most recent BEGIN TRANSACTION statement | ROLLBACK |
| Lock records, commit to disk all changes made to the tables in the database since the most recent BEGIN TRANSACTION, and then unlock the records | END TRANSACTION |
You can use transactions to wrap modifications to tables, structural .cdx files, and memo files associated with tables within a database. Operations involving variables and other objects don't respect transactions; therefore, you cannot roll back or commit such operations.
Note When using data stored in remote tables, transaction commands control only updates to the data in the local copy of the view cursor; updates to remote base tables are not affected. To enable manual transactions on remote tables use SQLSETPROP( ), and then control the transaction with SQLCOMMIT( ) and SQLROLLBACK( ).
In general, you should use transactions with record buffers rather than with table buffering, except to wrap TABLEUPDATE( ) calls. If you place a TABLEUPDATE( ) command in a transaction, you can roll back a failed update, address the reason for the failure, and then retry the TABLEUPDATE( ) without losing data. This ensures the update happens as an “all-or-nothing” operation.
Though simple transaction processing provides safe data update operations in normal situations, it doesn’t provide total protection against system failures. If power fails or some other system interruption occurs during processing of the END TRANSACTION command, the data update can still fail.
Use the following code template for transactions:
BEGIN TRANSACTION
* Update records
IF lSuccess = .F. && an error occurs
ROLLBACK
ELSE && commit the changes
* Validate the data
IF && error occurs
ROLLBACK
ELSE
END TRANSACTION
ENDIF
ENDIF
The following rules apply to transactions:
Transactions exhibit the following locking behaviors:
Nested transactions provide logical groups of table update operations that are insulated from concurrent processes. BEGIN TRANSACTION...END TRANSACTION pairs need not be in the same function or procedure. The following rules apply to nested transactions:
Notice in the following example that because changes in a nested transaction aren't written to disk but to the transaction buffer, the inner transaction will overwrite the changes made to the same STATUS fields in the earlier transaction:
BEGIN TRANSACTION && transaction 1
UPDATE EMPLOYEE ; && first change
SET STATUS = "Contract" ;
WHERE EMPID BETWEEN 9001 AND 10000
BEGIN TRANSACTION && transaction 2
UPDATE EMPLOYEE ;
SET STATUS = "Exempt" ;
WHERE HIREDATE > {^1998-01-01} && overwrites
END TRANSACTION && transaction 2
END TRANSACTION && transaction 1
The following nested transaction example deletes a customer record and all its related invoices. The transaction will roll back if errors occur during a DELETE command. This example demonstrates grouping table update operations to protect updates from partial completion and to avoid concurrency conflicts.
Example of Modifying Records in Nested Transactions
| Code | Comments |
|
Cleanup from other transactions. |
|
Establish environment for buffering. |
|
Enable optimistic table buffering. |
|
Change a record. Change another record. |
|
Start transaction 1 and try to update all modified records without force. |
|
If the update failed, roll back the transaction. Get the error from AERROR( ). Determine the cause of the failure. If a trigger failed, handle it. If a field doesn’t accept null values, handle it. If a field rule was violated, handle it. |
|
If a record was changed by another user, locate the first modified record. Loop through all modified records, starting with the first record. Lock each record to guarantee that you can update. Check each field for any changes. Check the buffered value against the value on disk, and then present a dialog box to the user. |
|
|
|
If user responded “No,” revert the one record and unlock it. |
|
Break out of the “FOR nField...” loop. |
|
Get the next modified record. |
|
Start transaction 2 and update all non-reverted records with force. End transaction 2. Release the lock. |
|
If the record is in use by another user, handle it. If a row rule was violated, handle it. If there was a unique index violation, handle it. Otherwise, present a dialog box to the user. |
|
End transaction 1. |
Transactions can protect you from system-generated errors during data updates on remote tables. The following example uses a transaction to wrap data-writing operations to a remote table.
Example of a Transaction on a Remote Table
| Code | Comment |
|
Get the connect handle and enable manual transactions. |
|
Begin the manual transaction. |
|
Try to update all records without force. If the update failed, roll back the transaction on the connection for the cursor. |
|
Get the error from AERROR( ). |
|
If a trigger failed, handle it. |
|
If a field doesn’t accept null values, handle it. |
|
If a field rule was violated, handle it. |
|
If a record was changed by another user, handle it. Loop through all modified records, starting with the first record. |
|
Check each field for any changes. Check the buffered value against the value on disk, and then present a dialog box to the user. |
|
If user responded “No,” revert the one record. Break out of the “FOR nField...” loop. Get the next modified record. |
|
Update all non-reverted records with force and issue a commit. |
|
Error 109 indicates that the record is in use by another user. |
|
Error 1583 indicates that a row rule was violated. |
|
Error 1884 indicates that the uniqueness of the index was violated. |
|
|
|
Present a dialog box to the user. End of error handling. |
|
If all errors were handled and the entire transaction was successful, issue a commit and end the transaction. |
Once you have a working multi-user application, you can use the following suggestions to improve performance:
Visual FoxPro creates its temporary files in the Windows default Temp directory. Text editing sessions can also temporarily create a backup copy of the file being edited (a .bak file).
If local workstations have hard drives with plenty of free space, you can improve performance by placing these temporary work files on the local drive or in a RAM drive. Redirecting these files to a local drive or a RAM drive increases performance by reducing access to the network drive.
You can specify an alternate location for these files by including the EDITWORK, SORTWORK, PROGWORK and TMPFILES statements in your Config.fpw configuration file. For more information about managing files, see Chapter 4, Optimizing Your System, in the Installation Guide.
When the data contained in a table is relatively static, processing sorted tables sequentially without an order set improves performance. This doesn't mean that sorted tables cannot or should not take advantage of index files — the SEEK command, which requires an index, is incomparable for locating records quickly. However, once you locate a record with SEEK, you can turn ordering off.
Commands that run when no other users require access to the data, such as overnight updates, can benefit by opening the data files for exclusive use. When files are open for exclusive use, performance improves because Visual FoxPro doesn't need to test the status of record or file locks.
To reduce contention between users for write access to a table or record, shorten the amount of time a record or table is locked. You can do this by locking the record only after it's edited rather than during editing. Optimistic row buffering gives you the shortest lock time.
For more information on improving performance, see Chapter 4, Optimizing Your System, in the Installation Guide. You can also find information on improving performance in your client/server applications in Chapter 22, Optimizing Client/Server Performance.
You can use the update conflict management technology built into Visual FoxPro views to handle multi-user access to data. Views control what is sent to the base tables underlying the view by using the WhereType property. You can set this property for both local and remote views. The WhereType property provides four settings:
By choosing one of these four settings, you control how Visual FoxPro builds the WHERE clause for the SQL Update statement sent to the view’s base tables. You can choose the setting you want using the Update Criteria tab of the View Designer, or you can use DBSETPROP( ) to set the WhereType for a view definition. To change the WhereType setting for an active view cursor, use CURSORSETPROP( ).
For example, suppose you have a simple remote view based on the Customer table that includes seven fields: cust_id, company, phone, fax, contact, title, and timestamp. The primary key for your view is cust_id.
The Update Criteria tab displays the updatable fields in your view.
You've made only two fields updatable: contact_name and contact_title. You want the user to be able to change the company contact and their title from the view. However, if other facts about the company change, such as the company address, you want the changes to go through a coordinator who’ll identify the impact of the changes on your company, such as whether the sales region for the customer will change. Now that your view has been set up to send updates, you can choose the WhereType according to your preferences.
Now suppose that you change the name in the contact field for a customer, but you don’t change the value in the other updatable field, title. Given this example, the following section discusses how the WhereType setting would impact the WHERE clause that Visual FoxPro builds to send the new contact name to the base tables.
The least restrictive update uses the DB_KEY setting. The WHERE Clause used to update remote tables consists of only the primary key field specified with the KeyField or KeyFieldList property. Unless the value in the primary key field has been changed or deleted in the base table since you retrieved the record, the update goes through.
In the case of the previous example, Visual FoxPro would prepare an update statement with a WHERE clause that compares the value in the cust_id field against the cust_id field in the base table row:
WHERE OLDVAL(customer.cust_id) = CURVAL(customer_remote_view.cust_id)
When the update statement is sent to the base table, only the base table’s key field is verified.
The key field in your view is compared against its base table counterpart.
The DB_KEYANDMODIFIED setting, the default, is slightly more restrictive than DB_KEY. DB_KEYANDMODIFIED compares only the key field and any updatable fields you’ve modified in the view against their base table counterparts. If you modify a field in the view, but the field isn’t updatable, the fields are not compared to the base table data.
The WHERE clause used to update base tables consists of the primary fields specified with the KeyFieldList property and any other fields that are modified in the view. In the case of the previous example, Visual FoxPro would prepare an update statement that compares the values in the cust_id field because it is the key field, and the contact field because the contact name has been changed. Even though the title field is updatable, title is not included in the update statement because we haven’t modified it.
The key and modified fields in your view are compared against their base table counterparts.
The DB_KEYANDUPDATABLE setting compares the key field and any updatable fields (whether modified or not) in your view against their base table counterparts. If the field is updatable, even if you haven’t changed it in the view, if anyone else has changed that field in the base table, the update fails.
The WHERE clause used to update base tables consists of the primary fields specified with the Key Field or KeyFieldList property and any other fields that are updatable. In the case of the example, Visual FoxPro would prepare an update statement that compares the values in the cust_id, contact, and title fields against the same fields in the base table row.
All the updatable fields in your view are compared against their base table counterparts.
The DB_KEYANDTIMESTAMP is the most restrictive type of update, and is only available if the base table has a timestamp column. Visual FoxPro compares the current timestamp on the base table record against the timestamp at the time the data was fetched into the view. If any field in the base table’s record has changed, even if it’s not a field you're trying to change, or even a field in your view, the update fails.
In the case of the example, Visual FoxPro prepares an update statement that compares the values in the cust_id field and the value in the timestamp field against those fields in the base table row.
The timestamp for your view’s record is compared against the timestamp on the base table record.
In order to successfully update data using the DB_KEYANDTIMESTAMP setting with a multi-table view, you must include the timestamp field in your view for each table that is updatable. For example, if you have three tables in a view and want to update only two of them, and you choose the DB_KEYANDTIMESTAMP setting, you must bring down the timestamp fields from the two updatable tables into your result set. You can also use logical values in the CompareMemo property to determine whether memo fields are included in conflict detection.
Whether you choose buffering, transactions, or views, you must manage conflicts during the update process.
You can make data update operations more efficient by carefully choosing how and when to open, buffer, and lock data in a multi-user environment. You should limit the time a record or table is subject to access conflicts. Still, you must anticipate and manage the inevitable conflicts that result. A conflict occurs when one user tries to lock a record or table that’s currently locked by another user. Two users cannot lock the same record or table at the same time.
Your application should contain a routine to manage these conflicts. If your application doesn't have a conflict routine, the system can lock up. A deadlock occurs when one user has locked a record or a table and tries to lock another record that’s locked by a second user who, in turn, is trying to lock the record that’s locked by the first user. While such occurrences are rare, the longer that a record or table is locked, the greater the chance of deadlock.
Designing a multi-user application or adding network support to a single-user system requires that you deal with collisions and trap for errors. Using Visual FoxPro record and table buffers simplifies some of this work.
If you attempt to lock a record or table already locked by another user, Visual FoxPro returns an error message. You can use SET REPROCESS to automatically deal with unsuccessful lock attempts. This command, in combination with an ON ERROR routine and the RETRY command, enables you to continue or cancel the lock attempts.
The following example demonstrates automatic reprocessing of a failed operation, using SET REPROCESS.
Using SET REPROCESS and ON ERROR to Manage User Collisions
| Code | Comment |
|
This routine runs if an error occurs. Open the files non-exclusively. Reprocessing of unsuccessful locks is automatic. Open the table. Create the APPEND FROM table if needed. |
|
The main routine starts here. These commands are examples of codes that could be executed in the course of your program. |
|
The main routine ends here. |
|
Routine to append a blank record. |
|
Routine to replace data in the current record. |
|
Routine to replace data in all records. |
|
Routine to replace data in the current record. |
|
Routine to append records from another file. |
The following example demonstrates an error procedure that starts when the user presses ESC.
Error Handling Using the ESC Key
| Code | Comment |
|
This program is called when an error is encountered and the user escapes from the wait process. |
|
Figure out what kind of error this is. Is it “File is in use by another”? |
|
|
|
Or “Record is in use by another”? |
|
Or is it unknown? |
|
Display the error message in a dialog box with an exclamation point and an OK button. |
During data update operations, especially in shared environments, you might want to determine which fields have changed or what the original or the current values are in changed fields. Visual FoxPro buffering and the GETFLDSTATE( ), GETNEXTMODIFIED( ), OLDVAL( ) and CURVAL( ) functions, enable you to determine which field has changed, find the changed data, and compare the current, original, and edited values so you can decide how to handle an error or conflict.
To detect a change in a field
GETFLDSTATE( ) works on unbuffered data; however, this function is even more effective when you've enabled record buffering. For instance, use GETFLDSTATE( ) in the code of a Skip button on a form. When you move the record pointer, Visual FoxPro checks the status of all fields in the record as in the following example:
lModified = .F.
FOR nFieldNum = 1 TO FCOUNT( ) && Check all fields
if GETFLDSTATE(nFieldNum) = 2 && Modified
lModified = .T.
EXIT && Insert update/Save routine here.
ENDIF && See the next example
ENDFOR
To detect and locate a changed record in buffered data
GETNEXTMODIFIED( ), with zero as a parameter, finds the first modified record. If another user makes changes to the buffered table, any changes encountered by a TABLEUPDATE( ) command in your buffer will cause conflicts. You can evaluate the conflicting values and resolve them using the CURVAL( ), OLDVAL( ), and MESSAGEBOX( ) functions. CURVAL( ) returns the current value of the record on disk, while OLDVAL( ) returns the value of the record at the time it was buffered.
To determine the original value of a buffered field
OLDVAL( ) returns the value of a buffered field.
To determine the current value of a buffered field on disk
CURVAL( ) returns the current value on disk of a buffered field before any edits were performed.
You can create an error-handling procedure that compares the current and original values, enabling you to determine whether to commit the current change or to accept an earlier change to data in a shared environment.
The following example uses GETNEXTMODIFIED( ), CURVAL( ), and OLDVAL( ) to provide the user with an informed choice in an update operation. This example continues from detection of the first modified record and might be contained in an Update or Save button on a form.
Click Event Code for an Update or Save Button
| Code | Comment |
|
Loop through buffer. Lock the modified record. |
|
Look for conflict. Compare the original value to the current value on the disk, and then ask the user what to do about the conflict. |
|
If the user selects “No,” revert this record, and then remove the lock. Find the next modified record. |
|
Force update to all records. |
You can use the CompareMemo property to control when memo fields are used to detect update conflicts. This view and cursor property determines whether memo fields (types M or G) are included in the update WHERE clause. The default setting, True (.T.), means that memo fields are included in the WHERE clause. If you set this property to False (.F), memo fields don’t participate in the update WHERE clause, regardless of the settings of UpdateType.
Optimistic conflict detection on Memo fields is disabled when CompareMemo is set to False. For conflict detection on memo values, set CompareMemo to True (.T.).
Managing conflicts encountered in multi-user environments can require extensive and repetitive code. A complete conflict management routine does the following:
For an example of a conflict management routine, see the data checker class in Samples.vcx, located in the Visual Studio …\Samples\Vfp98\Classes directory. Just add the class to a form and call the CheckConflicts method before any operation that writes buffered data to the table, for example moving the record pointer if you're using row buffering, closing a table, or issuing TABLEUPDATE( ).