Best Method For Mass Update 1

[Auguy]

Haven't been able to search (Hope it gets fixed soon) this site for a few days so thought I would post a question. What is the best way to update hundreds or possibly thousands of records in a multi-user situation where various users may have records locked? In the past I haven't had to worry about this too much because of a small number of users or just a few records to update. An example would be to change the status of some of the records from "open" to "audited". At least 3 options are:
(1) Flock and replace where the file lock may be hard to acquire.
(2) Update-Sql where I would have to deal with record lock failure through on error.
(3) buffered record/table and scan through the records one at a time.

Any suggestions on the best method of handling this will be appreciated.

On a technical note, here is my stupid question(s) (of the day) that I thought I knew the answer to until I started thinking about it some more. Does a tableupdate command only update the fields that have been changed, or does it replace all of the fields in the table? Does it generate an error if any field was updated by another user, or only if it was the same field the current user tried to update?


Auguy
Sylvania, Ohio

 

[Koen Piller]

Auguy,
Questions about updating in multiuser situation. These can be answered by reding Andy Kramek's book: 1001 Things about FoxPro. Suggest you get yourself rapidly a copy of this book and read it. All questions, I am sure are answered in this book.
Best regards,
Jockey2

 

[Mike Lewis]

Hi Auguy,

The first thing you need to decide is whether to use optimistic or pessimistic locking. This is a key decision that depends several factors. No-one here can make that decision for you, so you need to start by reading about multi-user access in the Help file, and understanding the issues.

Having decided between optimistic and pessimistic, my advice would be to use table buffering throught the process. Do all the updates in whichever way you prefer (native VFP commands such as REPLACE, or SQL-style UPDATEs and INSERTs), then do either TABLEUPDATE() or TABLEREVERT() to commit or revert respectively.

In answer to your specific questions: when working with VFP tables (DBFs), each update causes the entire record to be overwritten, even if only a few fields have changed. When working with back-end databases, usually only the changed fields are overwritten (but it might depend on the back end and other factors).

With native VFP tables, if you update Field A, and another user has updated Field B, that would constitute a multi-user violation. How you deal with it depends on which style of locking you used.

As you will have realised, this is a complicated issue that requires careful planning. I strongly recommend you to read up on the issues before making any decisions.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Auguy]

Thanks Mike, great information as always. Am I correct in assuming I can include an Update-SQL command in my usual Begin Transaction-TableUpdate-End Transaction procedure?

Auguy
Sylvania, Ohio

 

[Mike Lewis]

Auguy,

Am I correct in assuming I can include an Update-SQL command in my usual Begin Transaction-TableUpdate-End Transaction procedure?

Sure. In that respect, this command behaves just like any other command that updates the database.

The only thing you might need to be cautious about is if you're using a correlated Update, that is, UPDATE ... FROM .... JOIN. In that case, it's possible that the command won't see any modified data that is in the buffer of the joined table. I'm not sure about this (it's never been an issue for me), but it might be worth keeping at the back of your mind.

But in any case, that won't affect the use of the transaction.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk