Board index » delphi » Informix row locking

Informix row locking

Does anyone have experience locking rows in an Informix table through Delphi?
Using Informix ESQL you use the FOR UPDATE clause in a SELECT and then FETCH
to lock a row, but how can this be done through Delphi?

I'm currently using the new native Informix driver (SQL Links 3.5) with C/S Delphi.
I'm using Informix INET 7.1.  

Any help or advice would be appreciated!

Marshall Norris
mnor...@metronet.com

 

Re:Informix row locking


I had thought that Informix implicitly locked the row when you
updated/deleted it?  It's been a few years since I worked with it, and
probably an earlier version, but the row being modified would be
locked when an update/delete command was issued without an explicit
command before to lock it.  Then, all it took was a Commit or a
Rollback.  I would imagine that, before you start updating the data
(row or rows, which ever is the case), issue the StartTransaction in
your TDataBase component.  Then, the same TDataBase component has a
Commit or Rollback method.  Maybe you can play around with these
commands on your TDataBase component to see if that will help out with
locking those rows in Informix.

Hope it helps,
Ed

Quote
mnor...@fohnix.metronet.com (Marshall Norris) wrote:
>Does anyone have experience locking rows in an Informix table through Delphi?
>Using Informix ESQL you use the FOR UPDATE clause in a SELECT and then FETCH
>to lock a row, but how can this be done through Delphi?
>I'm currently using the new native Informix driver (SQL Links 3.5) with C/S Delphi.
>I'm using Informix INET 7.1.  
>Any help or advice would be appreciated!
>Marshall Norris
>mnor...@metronet.com

Re:Informix row locking


On 25 Nov 1996 23:11:59 -0600, mnor...@fohnix.metronet.com (Marshall

Quote
Norris) wrote:

Marshall,

you can use transaction to rows locking.

Example:

Query1.SQL.Clear;
Query1.SQL.Add('select * from customer where state="CZ''');
Query1.Open;
{declare A cursor for select * from customer ... for update}
Database1.StartTransaction;  {begin work}
Query1.First;
while not Query1.eof do {foreach A into B.*}
begin
Query1.Edit;
Query1.FIeldByName('sale').AsInteger := 0;
Query1.Post; {update customer set sale=0 where current of A }
Query1.Next;
end; {end foreach}
Database1.Commit; {commit work}

Leos

Quote
>Does anyone have experience locking rows in an Informix table through Delphi?
>Using Informix ESQL you use the FOR UPDATE clause in a SELECT and then FETCH
>to lock a row, but how can this be done through Delphi?

>Marshall Norris
>mnor...@metronet.com

Re:Informix row locking


On 25 Nov 1996 23:11:59 -0600, mnor...@fohnix.metronet.com (Marshall

Quote
Norris) wrote:
>Does anyone have experience locking rows in an Informix table through Delphi?
>Using Informix ESQL you use the FOR UPDATE clause in a SELECT and then FETCH
>to lock a row, but how can this be done through Delphi?

You can use transaction for rows locking.

Example:

Database1.StartTransaction; {begin work}
while not Query1.eof do {foreach A into B.*}
begin
 Query1.Edit;
 Query1.FieldByName['xxxx'].AsString := 'aaaaaa';
 Query1.Post; {update table t1 set xxxx='aaaaaaa' where current of A}
end; {end foreach}
{Pause - all selected rows are locked. }
Database1.commit; {commit work}

Leos

Re:Informix row locking


Leos Urban <l...@aitix.anet.cz> wrote in article
<329b1905.2725...@news.felk.cvut.cz>...

Quote
>>(Marshall Norris) wrote:
> >Does anyone have experience locking rows in an Informix table through
Delphi?
> >Using Informix ESQL you use the FOR UPDATE clause in a SELECT and then
FETCH
> >to lock a row, but how can this be done through Delphi?

...Various solutions posted.

Unfortunately, this will not work in Delphi - I have tried mightly to
temporarily lock a row in Informix without success (i.e. allow no other
changes while one client is editing the row).  Since the BDE buffers all
your cursor handling and row locking, it will NOT look locked to other
clients.  My advice is switch to Optimistic Locking (ie.use Transactions
for Posting/Updating).  If you must have locking, implement a scheme that
has a 'locked' field in the table and use that to support exclusion..

If you create a Query that is updatable you cannot use 'FOR UPDATE' with
it.

Saying all this, if  you do have a working solution I would love to hear
about it, because all my research into the BDE says you can't really do it.

Neil

Other Threads