How do I lock tables so that a series of SQL statements will get
current and consistent data (that is, no updates while the series is
executing)? I am having to bolt on multi-user use to my app when
single-user was a basic spec.

Sincerely,

Gene Wirchenko

Gene Wirchenko wrote:

Are you using VFP data on the backend or client server?
If client server, what server is on the backend?

If these are VFP tables, your tables need to be part of a DBC and then you
can use transactions.

BEGIN TRANSACTION

UPDATE yourtable SET field1='some value' WHERE keyid=x

UPDATE othertable SET field1 = 'other vlaue' WHERE kyid=x

END TRANSACTION

You can use the ROLLBACK command to cancel the transaction.

VFP9 can do transactions on free tables, see ISTRANSACTABLE() and
MAKETRANSACTABLE() functions

For backend tables, you'll need to use SQLEXEC to start/end it's transaction
capabilities.
--
Fred
Microsoft Visual FoxPro MVP


"Gene Wirchenko" <genew (AT) ucantrade (DOT) com.NOTHERE> wrote

This *could* be a really bad idea, depending on how long "the series" takes.
Just wanted to get that little warning out of the way first. It's one of
those "always there" things that always needs to be mentioned. <s>

You might be surprised how multi-user friendly VFP is without your having to
do a thing. Implicit locking handles a lot more than you might think.

However, for your "series" you could always explicitly FLOCK() the tables
involved. That will keep updates/inserts out for the duration. Of course,
back to the first paragraph, your update/insert routines have to be written
to handle situations where they might happen during your "series".

Dan



Gene Wirchenko wrote:

On Tue, 20 Sep 2005 22:16:42 -0400, Eugene Vtial <news (AT) microsoft (DOT) com>
wrote:

No, I am not.

Sincerely,

Gene Wirchenko

[reordered to chronological for readability]

On Wed, 21 Sep 2005 16:36:54 GMT, "Dan Freeman" <spam (AT) microsoft (DOT) com>
wrote:

The series is short, but longer than one. I do not want
incorrect results due to something changing between one SQL statement
and the next.

But does it handle in between two SQL statements?

Not everything can be done in ONE SQL statement.

Sincerely,

Gene Wirchenko

Gene Wirchenko wrote:
Ok, I will assume using VFP tables and not sure what version of VFP you
are using.


Can't you just FLOCK() the table before doing your query ?

USE SomeTable1 IN 0
USE SomeTable2 IN 0
FLOCK("SomeTable1")
FLOCK("SomeTable2")

SELECT * FROM SomeTable1 WHERE something = .t.

* do some processing that may take some time

SELECT * FROM SomeTable1 ;
LEFT JOIN SomeTable2 ON SomeTable2.Field1 == SomeTable1.Field1 WHERE
somethingnew = .t.


UNLOCK IN "SomeTable1"
UNLOCK IN "SomeTable2"

USE IN SELECT("SomeTable1")
USE IN SELECT("SomeTable2")

Hi Gene

pure native Foxpro code, without SQL statements. Then I would first SEEK or
LOCATE FOR the necessary records in all necessary tables, rlock() all these
records (maybe SET MULTILOCKS ON is necessary), do all updates and after this
unlock all the involved tables.

Regards
Bernhard Sander