Updating Data

Article
10/23/2006

To update data, you can use buffers, transactions, or views.

Performing Updates with Buffers

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:

In the Form Designer, set the BufferModeOverride property of the cursor in the data environment of the form.

-or-
In code, set the Buffering property.

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
`OPEN DATABASE testdata USE customers CURSORSETPROP('Buffering', 2)`	In the form Init code, open the table and enable pessimistic record buffering.
`lModified = .F. FOR nFieldNum = 1 TO FCOUNT() IF GETFLDSTATE(nFieldNum) = 2 lModified = .T. EXIT ENDIF ENDFOR`	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.
`IF lModified nResult = MESSAGEBOX; ("Record has been modified. Save?", ; 4+32+256, "Data Change")`	Locate the next modified record.
`IF nResult = 7 TABLEREVERT (.F.) ENDIF ENDIF`	Present the current value and give the user the option to revert the change to the current field.
`SKIP IF EOF() MESSAGEBOX( "already at bottom") SKIP -1 ENDIF THISFORM.Refresh`	SKIP guarantees that the last change is written.

Managing Updates with Transactions

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.

Wrapping Code Segments

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.

Commands that Control Transactions

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

Using Transactions

The following rules apply to transactions:

A transaction starts with the BEGIN TRANSACTION command and ends with the END TRANSACTION or ROLLBACK command. An END TRANSACTION statement without a preceding BEGIN TRANSACTION statement generates an error.
A ROLLBACK statement without a preceding BEGIN TRANSACTION statement generates an error.
A transaction, once begun, remains in effect until the corresponding END TRANSACTION begins (or until a ROLLBACK command is issued), even across programs and functions, unless the application terminates, which causes a rollback.
Visual FoxPro uses data cached in the transaction buffer before using disk data for queries on the data involved in transactions. This ensures that the most current data is used.
If the application terminates during a transaction, all operations roll back.
A transaction works only in a database container.
You cannot use the INDEX command if it overwrites an existing index file, or if any .cdx index file is open.
Transactions are scoped to data sessions.

Transactions exhibit the following locking behaviors:

Within a transaction, Visual FoxPro imposes a lock at the time a command directly or indirectly calls for it. Any system or user direct or indirect unlock commands are cached until the completion of the transaction by ROLLBACK or END TRANSACTION commands.
If you use a locking command such as FLOCK( ) or RLOCK( ) within a transaction, the END TRANSACTION statement will not release the lock. In that case, you must explicitly unlock any locks explicitly taken within a transaction. You should also keep transactions containing the FLOCK( ) or RLOCK( ) commands as brief as possible; otherwise, users could be locked out of records for a long time.

Nesting Transactions

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:

You can nest up to five BEGIN TRANSACTION...END TRANSACTION pairs.
Updates made in a nested transaction aren't committed until the outermost END TRANSACTION is called.
In nested transactions, an END TRANSACTION only operates on the transaction initiated by the last issued BEGIN TRANSACTION.
In nested transactions, a ROLLBACK statement only operates on the transaction initiated by the last issued BEGIN TRANSACTION.
The innermost update in a set of nested transactions on the same data has precedence over all others in the same block of 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
`DO WHILE TXNLEVEL( ) > 0 ROLLBACK ENDDO`	Cleanup from other transactions.
`CLOSE ALL SET MULTILOCKS ON SET EXCLUSIVE OFF`	Establish environment for buffering.
`OPEN DATABASE test USE mrgtest1 CURSORSETPROP('buffering',5) GO TOP`	Enable optimistic table buffering.
`REPLACE fld1 WITH "changed" SKIP REPLACE fld1 WITH "another change" MESSAGEBOX("modify first field of both" + ; "records on another machine")`	Change a record. Change another record.
`BEGIN TRANSACTION lSuccess = TABLEUPDATE(.T.,.F.)`	Start transaction 1 and try to update all modified records without force.
`IF lSuccess = .F. ROLLBACK AERROR(aErrors) DO CASE CASE aErrors[1,1] = 1539 ... CASE aErrors[1,1] = 1581 ... CASE aErrors[1,1] = 1582`	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.
`CASE aErrors[1,1] = 1585 nNextModified = getnextmodified(0) DO WHILE nNextModified <> 0 GO nNextModified RLOCK() FOR nField = 1 to FCOUNT() cField = FIELD(nField) if OLDVAL(cField) <> CURVAL(cField)`	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.
`nResult = MESSAGEBOX; ("Data was changed " + ; "by another user — keep"+ ; "changes?", 4+48, ; "Modified Record")`
`IF nResult = 7 TABLEREVERT(.F.) UNLOCK record nNextModified ENDIF`	If user responded "No," revert the one record and unlock it.
`EXIT ENDIF ENDFOR`	Break out of the "FOR nField..." loop.
`ENDDO`	Get the next modified record.
`BEGIN TRANSACTION TABLEUPDATE(.T.,.T.) END TRANSACTION UNLOCK`	Start transaction 2 and update all non-reverted records with force. End transaction 2. Release the lock.
`CASE aErrors[1,1] = 109 ... CASE aErrors[1,1] = 1583 ... CASE aErrors[1,1] = 1884 ... OTHERWISE MESSAGEBOX( "Unknown error "+; "message: " + STR(aErrors[1,1])) ENDCASE`	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.
`ELSE END TRANSACTION ENDIF`	End transaction 1.

Protecting Remote Updates

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
`hConnect = CURSORGETPROP('connecthandle') SQLSETPROP(hConnect, 'transmode', DB_TRANSMANUAL)`	Get the connect handle and enable manual transactions.
`BEGIN TRANSACTION`	Begin the manual transaction.
`lSuccess = TABLEUPDATE(.T.,.F.) IF lSuccess = .F. SQLROLLBACK (hConnect) ROLLBACK`	Try to update all records without force. If the update failed, roll back the transaction on the connection for the cursor.
`AERROR(aErrors) DO CASE`	Get the error from AERROR( ).
`CASE aErrors[1,1] = 1539 ...`	If a trigger failed, handle it.
`CASE aErrors[1,1] = 1581 ...`	If a field doesn't accept null values, handle it.
`CASE aErrors[1,1] = 1582 ...`	If a field rule was violated, handle it.
`CASE aErrors[1,1] = 1585 nNextModified = GETNEXTMODIFIED(0) DO WHILE nNextModified <> 0 GO nNextModified`	If a record was changed by another user, handle it. Loop through all modified records, starting with the first record.
`FOR nField = 1 to FCOUNT() cField = FIELD(nField) IF OLDVAL(cField) <> CURVAL(cField) nResult = MESSAGEBOX; ("Data has been changed ; by another user. ; Keep changes?",4+48,; "Modified 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 nResult = 7 TABLEREVERT(.F.) ENDIF EXIT ENDIF ENDFOR nNextModified = ; GETNEXTMODIFIED(nNextModified) ENDDO`	If user responded "No," revert the one record. Break out of the "FOR nField..." loop. Get the next modified record.
`TABLEUPDATE(.T.,.T.) SQLCOMMIT(hConnect)`	Update all non-reverted records with force and issue a commit.
`CASE aErrors[1,1] = 109 * Handle the error`	Error 109 indicates that the record is in use by another user.
`CASE aErrors[1,1] = 1583 * Handle the error`	Error 1583 indicates that a row rule was violated.
`CASE aErrors[1,1] = 1884 * Handle the error`	Error 1884 indicates that the uniqueness of the index was violated.
`OTHERWISE * Handle generic errors.`
`MESSAGEBOX("Unknown error message:" ; + STR(aErrors[1,1])) ENDCASE`	Present a dialog box to the user. End of error handling.
`ELSE SQLCOMMIT(hConnect) END TRANSACTION ENDIF`	If all errors were handled and the entire transaction was successful, issue a commit and end the transaction.