Board index » delphi » Determine that fields in database were updated between reading and updating them

Determine that fields in database were updated between reading and updating them

I develop an application in Delphi / MSSQL
How to determine that the fields I want to update was updated by other user
between the moment I read them and the moment I want to write them.

Thank you

 

Re:Determine that fields in database were updated between reading and updating them


Dear friend
When you want to update a record you have to lock it. In MSSQL 7.0 it's
possible, but in MSSQL6.5 you have to lock a page.
In Oracle 8 is possible to lock a field of a record, but in Oracle 8 only.

Best regards

Jed Nicolau Filho
j...@prouser.com.br

Quote
tudor.vlad wrote:
> I develop an application in Delphi / MSSQL
> How to determine that the fields I want to update was updated by other user
> between the moment I read them and the moment I want to write them.

> Thank you

Re:Determine that fields in database were updated between reading and updating them


You're right, but I already know that. Let me put the problem in another
way. I connect to the server, execute an SQL statement, get some records
form the server and then I breakdown the connectin to the server for 1 hour
(kind of "briefcase" technology). I'm changing localy few records. How can I
find out the next time I connect to the server that those records have been
updated in the meantime.

Thank you.

Quote
Pro User <prou...@prouser.com.br> wrote in message

news:3834C2C8.3B86D9B8@prouser.com.br...
Quote
> Dear friend
> When you want to update a record you have to lock it. In MSSQL 7.0 it's
> possible, but in MSSQL6.5 you have to lock a page.
> In Oracle 8 is possible to lock a field of a record, but in Oracle 8 only.

> Best regards

> Jed Nicolau Filho
> j...@prouser.com.br

> tudor.vlad wrote:

> > I develop an application in Delphi / MSSQL
> > How to determine that the fields I want to update was updated by other
user
> > between the moment I read them and the moment I want to write them.

> > Thank you

Re:Determine that fields in database were updated between reading and updating them


Quote
>I develop an application in Delphi / MSSQL
>How to determine that the fields I want to update was updated by other user
>between the moment I read them and the moment I want to write them.

Basically, Delphi does this for you. There is a WhereAll clause in the
provider flags for the TFields of a TDataSet which generates a UPDATE ...
WHERE FIELD1=:OLDVALUE AND ... for you. This UPDATE fails if the records was
changed by another user and throws an exception.
This way has two disadvantages:
- it fails for memos, they are not allowed in a where clause with =
- a where clause should only use fields which are indexed, so on a large
table this approach is useless as it requires a full table scan

Depending on the server there are are other options, kind of timestamps,
rowids you can use in the WHERE clause.

My personal approach is a counter which gets updated on every post of the
record and the provider flags for InWHere are set only for the primary key
and this counter, both are indexed, so this works quite well, but ONLY if
the tables are modifyed by my application, there is no chance to detect
concurrent changes by another application, which does not use this approach.

In general it is bad strategy to lock records or pages in advance, because
this increases problems with concurrency - always bear in mind thtat the
user might go to lunch before committing, thus releasing the locks.

HTH

dato Denkwerkzeuge - Bernd Maierhofer
Corneliusgasse 4/5
A-1060 Wien , Austria
supp...@dato.at

Re:Determine that fields in database were updated between reading and updating them


Quote
"tudor.vlad" wrote:

> You're right, but I already know that. Let me put the problem in another
> way. I connect to the server, execute an SQL statement, get some records
> form the server and then I breakdown the connectin to the server for 1 hour
> (kind of "briefcase" technology). I'm changing localy few records. How can I
> find out the next time I connect to the server that those records have been
> updated in the meantime.

> Thank you.

The fastest way to do this is to retrieve the record (based on it's
primary key) into a separate dataset, and then to compare the fields
yourself.  Any other way of trying to determine this will involve
placing every field in the dataset in the where statement (of either a
select or update statement), which unless you have an index on every
field will require a table scan of the table to check all of the fields.

HTH,
Wayne

--
Bogus email address - use wgshef instead, keep the domain.

Other Threads