共用方式為

Updating Data

  • 發行項

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.

See Also

Buffering Data | Managing Performance | Programming for Shared Access | TABLEUPDATE( ) | TABLEREVERT( ) | Controlling Access to Data | Management of Conflicts