1001 Things You Wanted To Know About Visual FoxPro phần 7 doc

Chapter 8: Data Buffering and Transactions 273

if the order could not be added. A single transaction is not enough, but a pair of nested

transactions will meet the bill very well as illustrated below:

*** Start the outermost ('Wrapper') transaction

BEGIN TRANSACTION

*** Update the Customer table first

llTx1 = TableUpdate( 1, .F., 'customer' )

IF llTx1

*** Customer table updated successfully

*** Start the second, 'inner' transaction for Orders

BEGIN TRANSACTION

llTx2 = TableUpdate( 1, .F., 'orderheader' )

IF llTx2

*** Orders Updated, now try details

llTx2 = TableUpdate( 2, .F., 'orderdetails' )

IF llTx2

*** Both Order tables updated successfully

*** So commit the orders transaction

END TRANSACTION

ELSE

*** Order Detail update failed

*** Roll back entire orders transaction

ROLLBACK

ENDIF

ELSE

*** Order Header update failed - no point in trying details

ROLLBACK

ENDIF

*** But the customer update had already succeeded, so commit

END TRANSACTION

ELSE

*** Customer update failed - no point in proceeding

ROLLBACK

ENDIF

This code may look a little strange at first sight but does emphasize the point that

BEGIN…END TRANSACTION does not constitute a control structure. Notice that there are two

starting commands, one for each transaction and two 'Commit' commands, one for the

customer table and one for the pair of order tables. However there are three rollback

commands. One for the outer transaction but two for the inner transaction to cater to the fact

that either table involved might fail.

The logic gets a little more tricky as more tables are involved but the principles remain the

same. However, when many tables are involved, or if you are writing generic routines to

handle an indeterminate number of tables at each level, it will probably be necessary to break

the transactions up into separate methods to handle Update, Commit and Rollback functions

274 1001 Things You Always Wanted to Know About Visual FoxPro

and to use the TXNLEVEL() function to keep track of the number of transactions. (Remember

that Visual FoxPro is limited to five simultaneous transactions.)

Some things to watch for when using buffering in

applications

Cannot use OLDVAL() to revert a field under table buffering

You may have wondered, when reading the discussion of conflict resolution earlier in this

chapter, why we did not make use of the OLDVAL() function to cancel a user's changes in the

same way that we used the value returned by CURVAL() to clear conflicts in different fields.

The answer is simply that we cannot do it this way when using any form of table buffering if

the field is used in either a Primary or Candidate index without getting a "Uniqueness of index

<name> is violated" error. This is an acknowledged bug in all versions of Visual FoxPro and,

since the work-around offered by Microsoft is to use the TableRevert() function instead, it

seems unlikely to us that it will ever be fixed.

However, this does not seem to be an entirely satisfactory solution because TableRevert()

cannot operate at anything other than "Row" level and so undoing a change to a key field

without losing any other changes in the same record requires a little more thought. The best

solution that we have found is to make use of the SCATTER NAME command to create an object

whose properties are named the same as the fields in the table. The values assigned to the

object's properties are the current values from the record buffer and we can change the

property values using a simple assignment. The following program illustrates both the problem

and the solution:

**********************************************************************

* Program....: ShoOVal.prg

* Compiler...: Visual FoxPro 06.00.8492.00 for Windows

* Abstract...: Illustrates problem with using OldVal() to revert field

* ...........: which is used in a Candidate key

* ...........: Ref: MS Knowledgebase PSS ID Number: Q157405

**********************************************************************

*** Create and populate a sample table

CREATE TABLE sample ( Field1 C(5) UNIQUE, Field2 N(2) )

INSERT INTO sample ( Field1, Field2 ) VALUES ( "one ", 1 )

INSERT INTO sample ( Field1, Field2 ) VALUES ( "two ", 2 )

INSERT INTO sample ( Field1, Field2 ) VALUES ( "three", 3 )

*** Force into Table Buffered mode

SET MULTILOCK ON

CURSORSETPROP( "Buffering", 5 )

*** FIRST THE PROBLEM

*** Change key field

GO TOP

REPLACE field1 WITH "four", field2 WITH 4

SKIP

SKIP -1

*** Revert value using OldVal()

REPLACE field1 WITH OLDVAL( "Field1" )

Chapter 8: Data Buffering and Transactions 275

SKIP

SKIP -1

*** You now get a "Uniqueness of index FIELD1 is violated" error message

*** Click IGNORE and revert the table - loses changes in all fields!

TableRevert(.T.)

*** NOW THE SOLUTION

*** Repeat the Replace

GO TOP

REPLACE field1 WITH "four", field2 WITH 4

SKIP

SKIP -1

*** Scatter the fields to an Object

SCATTER NAME loReverter

*** Revert the Key Field value

loReverter.Field1 = OLDVAL( 'Field1' )

*** Revert the row in the table

TableRevert(.F.)

*** Gather values back

GATHER NAME loReverter

SKIP

SKIP-1

*** NOTE: No error, and the change in Field2 is retained

*** Confirm the reversion

TableUpdate(1)

BROW NOWAIT

At the time of writing, the behavior outlined above was rather erratic

when a character string was used as the candidate key. For example,

if you REPLACE field1 WITH "seven" you do not get an error at all!

However, there is nothing magical about the string "seven" as several other values

were found that did not cause an error, but we could not discern a pattern in, or

formulate any logical explanation for, the observed behavior.

Gotcha! Row buffering and commands that move the record

pointer

We noted earlier that changes to a record in a row buffered table are automatically committed

by Visual FoxPro whenever the record pointer for that table is moved. This is implicit in the

design of Row Buffering and is entirely desirable. What is not so desirable is that it is not

always obvious that a specific command is actually going to move the record pointer and this

can lead to unexpected results. For example, it is predictable that issuing a SEEK or a SKIP

command is going to move the record pointer and therefore we would not normally allow such

a command to be executed without first checking the buffer mode and, if row buffering is in

force, checking for uncommitted changes.

276 1001 Things You Always Wanted to Know About Visual FoxPro

Similarly you would expect that using a GOTO command would also move the record

pointer if the specified record was not already selected. However, GOTO always moves the

record pointer, even if you use the GOTO RECNO() form of the command (which keeps the

record pointer on the same record) and so will always attempt to commit pending changes

under row buffering.

In fact, any command which can take either an explicit record number or has a Scope (FOR

or WHILE) clause is going to cause the record pointer to move and is, therefore, a likely cause of

problems when used in conjunction with Row Buffering. There are many such commands in

Visual FoxPro including the obvious like SUM, AVERAGE and CALCULATE as well as some less

obvious ones like COPY TO ARRAY and UNLOCK.

This last is particularly sneaky. What it means is that if you are using explicit locking with

a row buffered table, then unlocking a record is directly equivalent to issuing a TableUpdate()

and you immediately lose the ability to undo changes. We are not sure whether this was really

the intended behavior (though we can guess!) but it does underline the points made earlier in

the chapter. First, there is no real place for row buffering in an application, and second, the

best way to handle locking when using buffering is to allow Visual FoxPro to do it.