TableUpdate(), TableRevert()

Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.

`TableUpdate()`, `TableRevert()`

These functions commit or discard changes to buffered data.

Usage

lSuccess = TableUpdate( [ lAll | nUpdateWhat [, lForce
                        [, cAlias | nWorkArea
                        [, ErrorRecordsArray ] ] ] ] )
nAffected = TableRevert( [ lAll [, cAlias | nWorkArea ] ] )

Parameter	Value	Meaning
lAll	Logical	Should we update or revert all records or just the current record? Relevant only when using table buffering.
nUpdateWhat	0	Update only the current row.
	1	If table buffering is on, update all rows. If row buffering is on, update the current row. Give up as soon as an error occurs, leaving whatever has already been updated changed.
	2	Same as 1, except if an error occurs, log it in ErrorRecordsArray and continue.
lForce	Logical	Should we insist on our changes going through, even if others have changed the record in the meantime?
cAlias	Character	Commit or discard changes for table cAlias.
cAlias	Omitted	If nWorkArea is also omitted, commit or discard changes for the table in the current work area.
nWorkArea	Numeric	The work area containing the table to have changes committed or discarded.
nWorkArea	Omitted	If cAlias is also omitted, commit or discard changes for the table in the current work area.
ErrorRecordsArray	Array Name	If nUpdateWhat is 2, contains a list of record numbers for which the update was unsuccessful.
lSuccess	.T.	All changes were successfully committed.
lSuccess	.F.	Some changes could not be committed.
nAffected	Numeric	The number of records for which changes were discarded.

When you’re working with buffered data, sooner or later you need to either copy the data from the buffer to the original table or view, or throw away the changes and restore the original data. That’s what these two functions are about.

TableRevert() is simpler. It’s the one you want when the user presses Cancel. You might also use it when you encounter a conflict and the user chooses to keep the other guy’s changes. (You could also handle that case on a field-by-field basis—see GETFLDSTATE().)

For both functions, the lAll parameter is relevant only when you’re using table buffering. With row buffering, whichever value you pass, you still affect only the current record. TableUpdate() offers an alternative approach to specifying what to update, too. You can pass 0, 1 or 2 for nUpdateWhat. 0’s easy—it means update only the current row and corresponds to passing .F. for lAll. 1 is the same as passing .T. for lAll—it says to try to update all the records, but give up as soon as any record can’t be committed. The problem with this approach is that it can leave you with some records updated and some not updated. Unless you’re wrapped in a transaction, this is a recipe for disaster.

Fortunately, in VFP 5 and later, you have another alternative. Passing 2 for nUpdateWhat says to go ahead and commit all the changes you can, and create a log of the records that couldn’t be changed. ErrorRecordsArray is a one-column array containing the record numbers for all those records.

The lForce parameter offers two attitudes toward updating. You can be polite and check whether anyone else has changed the data in the meantime, or you can be rude and ride roughshod over other people’s changes.

Your application can approach updates from two perspectives. You can give it a shot and cope if the update fails. In this case, you’ll probably want lForce to be .F. The other choice is to check for conflicts first and resolve them, then commit your changes with lForce = .T. when you’ve taken care of all the problems.

In VFP 3, when you use table buffering, you have to either commit or revert changes before you can close the form. Otherwise, you get an error message—fair enough. What isn't fair is that when you're using a private data session, the form goes ahead and closes, and leaves you with a data session labeled "Unknown" with the offending table still open. In fact, anything that prevents a table from closing (like a trigger failing) when you close the form causes the same problem. The moral is that you must be sure to either commit or revert changes as part of a form close. If you should get caught in this situation, you can go to the View window, choose the Unknown data session, select the open table, issue TableRevert(.T.), then close the table. Once you've closed every table in the Unknown data session (any relation to the Unknown Soldier?), the session goes away.

In later versions of VFP, failing to either commit or revert changes before closing a form is the same as reverting the changes.

This one isn't exactly a bug itself, but something that could bite you when you're not watching. It's possible to open a table whose header is corrupted by having the record count too low. When you add a new record to such a table, TableUpdate() returns .T., but the new record actually overwrites an existing record (because the record count indicates where to put it). Worse yet, the record count in the header doesn't seem to get properly updated in this setting, so you can add record after record and end up with only one new record.

The best way to fix this kind of corruption is to PACK the table, which cleans up the table header.

The original release of VFP 7 has a very nasty bug that makes it possible to violate and corrupt primary or candidate indexes. If you attempt a TableUpdate() and it fails due to error 1884 ("Uniqueness of index violated"), and then you attempt the same TableUpdate(), it succeeds the second time. That results in bad data in the table and a corrupt index. Not surprisingly, this bug was squashed in Service Pack 1.

Example

* The Click code for a Cancel button may be as simple as:
=TableRevert()

* For a Save button, there are several approaches. If you choose
* to resolve conflicts first (see GETFLDSTATE() for one way to
* do so for a single record), you can finish up with a call
* to TableUpdate():
IF NOT TableUpdate(2, .T., ALIAS(),aSaveErrors)
   * Force changes on all rows, but just in case, track
   * any errors in an array.
   * You'll want something a little more sophisticated here.
   WAIT WINDOW "Unexpected problem with " + ;
               ALEN(aSaveErrors,1) + " records"
ENDIF