Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?
From: Jeroen van Kalken (I_at_dont.like.spam)
Date: 02/23/05
- Next message: John Pugh: "Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?"
- Previous message: Dan Williams: "How would I convert this?"
- In reply to: Dan Freeman: "Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?"
- Next in thread: John Pugh: "Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 23 Feb 2005 23:28:19 +0100
On Wed, 23 Feb 2005 08:40:54 -0800, "Dan Freeman" <spam@microsoft.com>
wrote:
>Record locking on a server-based database will never be supported because
>there is no concept of a "record". It's all based on a set. YOU might run a
>query that returns a single row, but to the server that's a set.
>
>Use refresh(), oldval() and curval() to check each field against the current
>values on the back end. If nobody made any changes while you were editing,
>great! Fire away with your update. If someone changed the phone # field and
>you've changed the name field, great! Update just the name field and there
>is no conflict.
This is of course an 'it works most of the times' solution.
Because inevitably some other computer will update the record just
after you've done the checks.
>If someone changed the same field that you just changed, there is absolutely
>no way on Earth you can write code to detect which change should win. You
>need human intervention at that point. (And probably management consulting
>because the business process is whacked if you have two people editing the
>same data element.) ASK THE USER which value should be saved, and proceed
>accordingly.
>
>Dan
>
>OrderNLogN wrote:
>> I am writing a VFP 8 ODBC application that currently connects to a
>> MySQL 4.1 server and will eventually connect to MS SQL Server. I
>> have a simple data entry form that displays one record at a time from
>> a VFP cursor. This cursor is created by a simple SQLEXEC call to a
>> MySQL table.
>>
>> I have the functionality where I can add/edit/delete records from the
>> cursor (using TABLEUPDATE) and it will do the same for the MySQL
>> table (manually using SQLEXEC calls with INSERT, UPDATE, DELETE).
>> This all works fine and dandy until I come across the situation where
>> two or more users may be modifying the same record at the same time.
>> I have searched hi and low but it seems no one has a definte answer
>> as to how to prevent this. Because both users could send a separate
>> UPDATE statement with SQLEXEC, one will overwrite the other. I've
>> looked into locking but it seems locking is done by the backend DB
>> server and I don't seem to have any control over it (i.e. I would
>> like to request a lock on a specific row and/or table). Maybe there
>> is a way but I am just missing it.
>>
>> I would appreciate any information on this subject as a solution has
>> eluded me. Thanks.
Either use transactions (which might prevent other stations from
reading the record altogether) or use 'optimistic' locking and let VFP
check the updated fields also for any collision of data-update, and
handle all the collisions in the tableupdate.
You can always try to build the locking yourself, Either using a
seperate table in which you store the recordkey and station which has
use of the record, or add an extra field to the existing table that
indicates which computer has the record locked
(always check that after you 'locked' the record , no other computer
has done the same, otherwise: release and relock)
Also make sure that the locking gets reset if a computer crashes...
- Next message: John Pugh: "Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?"
- Previous message: Dan Williams: "How would I convert this?"
- In reply to: Dan Freeman: "Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?"
- Next in thread: John Pugh: "Re: How To Handle VFP ODBC Concurrency Issues - Does It Vary By DBs?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
