Bulk update of tables 1

[AndrewMozley]

I am trying to establish a systematic and secure way of performing a bulk update of a database with a set of similar transactions.

Each transaction consists of updating 7 or 8 tables - most of these updates consist of replacing a few fields in one or more records of each table; in some tables I may be required to add records.

In the past I have been content to just lock each record and (if successful) update it. I recognise that this is not the best approach, partly because other applications may want to update the same tables (unlikely), and partly because I may fail to update a table (perhaps I do not find a record which I expect to exist). In this last case I wish to abandon the transaction with an error message, and undo any table updates.

I am not sure whether I should use record buffering, and tableupdate() / tablerevert(), or whether I should use BEGIN TRANSACTION / END TRANSACTION and perhaps ROLLBACK. – or indeed a combination of both approaches.

The general structure of the processing of one transaction (there is no user interaction) is to call a series of methods to update each table, and (if all goes well) to commit the whole set of updates.

Open_my_tables()
Ans1 = Update_table1()
Ans2 = Update_table2()
. . .
Ans8 = Update_table8()
. . .
IF Ans1 .AND. Ans2 .AND Ans3 . . .
* commit the transaction
ELSE
* reverse all updates
ENDIF

I have looked at several VFP manuals but am still unsure. I would very much appreciate guidance. Thank you

 

[Olaf Doschke]

A combibnation of both is good, because:

1. The TRANSACTION handles ENTRANSACTION vs ROLLBACK of all involved tables the easiest way
2. TABLUPDATE() handles multiple record inserts/updates/deletes in one go.

Therefore you can't beat the combination.

Both Buffering and Transactions work in conjunction with USEing tables or Data Environment but also local/remote views or cursoradapters.

As a very short example:

lRollback = .T.
BEGIN TRANSACTION
If Tableupdate(1,.F.,"parenttable")
   If Tableupdate(1,.F.,"childtable1")  
      If Tableupdate(1,.F.,"childtable2")
         END TRANSACTION
         lRollback = .F.
         MessageBox("saved") && if you show a message at all
      Endif
   Endif
Endif

If lRollback
   ROLLBACK
   MessageBox("something went wrong") && you might be more detailed when inspecting the single tableupdate result, eg also in nRows=2 mode with an errorarray. 
Endif

Instead of nested if statements you might use chained dataaccess objects, chained via a Parent and/or Child property pointing to the next object in the chain, calling a "savedata" method, which returns success or failure und defining a main/parent object which starts and ends the transaction.

Bye, Olaf.

 

[Dan Freeman]

The important part of using buffering/tableupdate() in conjunction with transactions is what happens when the transaction fails and you ROLLBACK. You're left with the buffered changes still in place, and still buffered so you can examine/doctor them in code (or manually) and fix whatever caused the transaction to fail in the first place, and then go again.

 

[AndrewMozley]

Thank you very much, Olaf, Dan and Mike Lewis - I should have acknowledged much earlier!