Board index » delphi » ADO -> Lock Record on Edit

Ricardo Hidalgo

Delphi Developer
Mon, 28 Apr 2003 03:00:00 GMT

ADO -> Lock Record on Edit

I can lock a record when making an Edit?
 
Guy Smith-Ferrie

Delphi Developer
Mon, 28 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Ricardo,

Quote
>I can lock a record when making an Edit?

Search recent messages of this newsgroup for the answer to this.

Guy.

Ricardo Hidalg

Delphi Developer
Mon, 28 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Hello Guy.

I have read your latest messages and I haven't arrived to any conclusion...
Could you please give me some explanation about how lock a record while
doing an edit on it???

Thanks

Guy Smith-Ferrier <guysm...@compuserve.com> escribi en el mensaje de
noticias jnil0t8ru7f7b61snlspr04b63q1ur9...@4ax.com...

Quote
> Ricardo,

> >I can lock a record when making an Edit?

> Search recent messages of this newsgroup for the answer to this.

> Guy.

Jumbo Dumb

Delphi Developer
Mon, 28 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Quote
"Ricardo Hidalgo" <rhida...@md-etiquetado.com> wrote in message

news:3a0ad400_1@dnews...

Quote
> Could you please give me some explanation about how lock a record while
> doing an edit on it???

Here is part of a message I recently posted, hope it helps...

ADOExpress modifies the standard ADO record locking scheme in that it applies the
lock only during the actual posting. Thus it is very unlikely that two users would
each post to the same record at exactly the same instant.  Read the source code for
the posting procedure in ADODB.PAS

Except for desktop applications developed by converted xBase and Paradox programmers,
most ADO programmers prefer optimistic locking over pessimistic locking but I'll not
try to convince you of the reasoning for their preference of optimistic locking .

You can use either semaphores or added fields kluges to simulate pessimistic record
locking or just use the following suggestion which will apply a native pessimistic
lock that will be honored by both Access and your applications.

Add to your unit this generalized pessimistic record locking function for Access
databases. Must use ServerSide cursor and Pessimistic locking.

function ApplyLockBeforeEdit(DataSet: TDataSet): boolean;
begin
  Result:= False;
  with DataSet as TCustomADODataSet do
    if (Pos('JET.OLEDB', UpperCase(Connection.Provider))>1) then
      if CanModify and (CursorLocation = clUseServer) and
        (LockType = ltPessimistic) then
    begin  {try}
      UpdateCursorPos;
      with RecordSet do
        if (Fields[0].Properties['ISAUTOINCREMENT'].Value = False) then
          Fields[0].Value := Fields[0].Value
        else if (Fields.Count > 0) then
          Fields[1].Value := Fields[1].Value;
      Result:= True;
    {except}
      {if desired add here your error handler for LockBeforeEdit failure;}
    end;
end;

In each BeforeEdit event you must call the function.
You can use something like this to test if locking is working.

procedure TForm1.ADOTable1BeforeEdit(DataSet: TDataSet);
begin
 if ApplyLockBeforeEdit(DataSet) then
   ShowMessage('Record IS Locked')
 else
   ShowMessage('Record NOT Locked');
end;

Duh, that wasn't too hard was it?<g>

Regards,
Jumbo

Guy Smith-Ferrie

Delphi Developer
Mon, 28 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Ricardo,

Quote
>Could you please give me some explanation about how lock a record while
>doing an edit on it???

I don't know of any reliable way to do this. Forcing a record lock in
InternalEdit or BeforeEdit isn't reliable because the underlying
recordset can be used to retrieve more data and, if it is, then the
lock is released.

Guy.

Jumbo Dumb

Delphi Developer
Mon, 28 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Quote
"Guy Smith-Ferrier" <guysm...@compuserve.com> wrote in message

news:7cpl0t45hn0unno0pd3j80aesp4uqigob2@4ax.com...

Quote
> I don't know of any reliable way to do this. Forcing a record lock in
> InternalEdit or BeforeEdit isn't reliable because the underlying
> recordset can be used to retrieve more data and, if it is, then the
> lock is released.

It's possible, just clone the cursor and make the lock on the cloned cursor. but then
you must explicitly release the lock if the edit is canceled. Use a global var for
the cloned recordset and do a cancelupdate on the clone in the before cancel event.
It gets a little messy but it surely can be done.

Jumbo

Guy Smith-Ferrie

Delphi Developer
Tue, 29 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Jumbo,

Quote
>It's possible, just clone the cursor and make the lock on the cloned cursor.

That's a good idea. I must try that.

Guy.

Ricardo Hidalg

Delphi Developer
Tue, 29 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Thank you Jumbo.

I have another question:

The function ApplyLockBeforeEdit can work with another database system, such
as DBase, Oracle or Interbase?

Is it simply necessary to change the Provider?

Thank you again. :->

Jumbo Dumbo <b...@nyett.com> escribi en el mensaje de noticias
8ueo94$5h...@bornews.inprise.com...

Quote

> "Ricardo Hidalgo" <rhida...@md-etiquetado.com> wrote in message
> news:3a0ad400_1@dnews...
> > Could you please give me some explanation about how lock a record while
> > doing an edit on it???

> Here is part of a message I recently posted, hope it helps...

> ADOExpress modifies the standard ADO record locking scheme in that it
applies the
> lock only during the actual posting. Thus it is very unlikely that two
users would
> each post to the same record at exactly the same instant.  Read the source
code for
> the posting procedure in ADODB.PAS

> Except for desktop applications developed by converted xBase and Paradox
programmers,
> most ADO programmers prefer optimistic locking over pessimistic locking
but I'll not
> try to convince you of the reasoning for their preference of optimistic
locking .

> You can use either semaphores or added fields kluges to simulate
pessimistic record
> locking or just use the following suggestion which will apply a native
pessimistic
> lock that will be honored by both Access and your applications.

> Add to your unit this generalized pessimistic record locking function for
Access
> databases. Must use ServerSide cursor and Pessimistic locking.

> function ApplyLockBeforeEdit(DataSet: TDataSet): boolean;
> begin
>   Result:= False;
>   with DataSet as TCustomADODataSet do
>     if (Pos('JET.OLEDB', UpperCase(Connection.Provider))>1) then
>       if CanModify and (CursorLocation = clUseServer) and
>         (LockType = ltPessimistic) then
>     begin  {try}
>       UpdateCursorPos;
>       with RecordSet do
>         if (Fields[0].Properties['ISAUTOINCREMENT'].Value = False) then
>           Fields[0].Value := Fields[0].Value
>         else if (Fields.Count > 0) then
>           Fields[1].Value := Fields[1].Value;
>       Result:= True;
>     {except}
>       {if desired add here your error handler for LockBeforeEdit failure;}
>     end;
> end;

> In each BeforeEdit event you must call the function.
> You can use something like this to test if locking is working.

> procedure TForm1.ADOTable1BeforeEdit(DataSet: TDataSet);
> begin
>  if ApplyLockBeforeEdit(DataSet) then
>    ShowMessage('Record IS Locked')
>  else
>    ShowMessage('Record NOT Locked');
> end;

> Duh, that wasn't too hard was it?<g>

> Regards,
> Jumbo

Jumbo Dumb

Delphi Developer
Tue, 29 Apr 2003 03:00:00 GMT

Re:ADO -> Lock Record on Edit


Quote
"Ricardo Hidalgo" <rhida...@md-etiquetado.com> wrote in message

news:3a0c07af_2@dnews...

Quote
> The function ApplyLockBeforeEdit can work with another database system, such
> as DBase, Oracle or Interbase?

That code is specifically limited to Jet and doubt if it would work for anything but
Access.

Jumbo

Wilso

Delphi Developer
Wed, 28 May 2003 10:05:48 GMT

Re:ADO -> Lock Record on Edit


dear Jumbo Dumbo

i'm using MSSQL 7 and try your procedure / fuction but it still could not
lock the record.
please explain more detail

thanks
wilson

Quote
"Jumbo Dumbo" <b...@nyett.com> wrote in message

news:8ueo94$5hq10@bornews.inprise.com...
Quote

> "Ricardo Hidalgo" <rhida...@md-etiquetado.com> wrote in message
> news:3a0ad400_1@dnews...
> > Could you please give me some explanation about how lock a record while
> > doing an edit on it???

> Here is part of a message I recently posted, hope it helps...

> ADOExpress modifies the standard ADO record locking scheme in that it
applies the
> lock only during the actual posting. Thus it is very unlikely that two
users would
> each post to the same record at exactly the same instant.  Read the source
code for
> the posting procedure in ADODB.PAS

> Except for desktop applications developed by converted xBase and Paradox
programmers,
> most ADO programmers prefer optimistic locking over pessimistic locking
but I'll not
> try to convince you of the reasoning for their preference of optimistic
locking .

> You can use either semaphores or added fields kluges to simulate
pessimistic record
> locking or just use the following suggestion which will apply a native
pessimistic
> lock that will be honored by both Access and your applications.

> Add to your unit this generalized pessimistic record locking function for
Access
> databases. Must use ServerSide cursor and Pessimistic locking.

> function ApplyLockBeforeEdit(DataSet: TDataSet): boolean;
> begin
>   Result:= False;
>   with DataSet as TCustomADODataSet do
>     if (Pos('JET.OLEDB', UpperCase(Connection.Provider))>1) then
>       if CanModify and (CursorLocation = clUseServer) and
>         (LockType = ltPessimistic) then
>     begin  {try}
>       UpdateCursorPos;
>       with RecordSet do
>         if (Fields[0].Properties['ISAUTOINCREMENT'].Value = False) then
>           Fields[0].Value := Fields[0].Value
>         else if (Fields.Count > 0) then
>           Fields[1].Value := Fields[1].Value;
>       Result:= True;
>     {except}
>       {if desired add here your error handler for LockBeforeEdit failure;}
>     end;
> end;

> In each BeforeEdit event you must call the function.
> You can use something like this to test if locking is working.

> procedure TForm1.ADOTable1BeforeEdit(DataSet: TDataSet);
> begin
>  if ApplyLockBeforeEdit(DataSet) then
>    ShowMessage('Record IS Locked')
>  else
>    ShowMessage('Record NOT Locked');
> end;

> Duh, that wasn't too hard was it?<g>

> Regards,
> Jumbo

Jumbo Dumb

Delphi Developer
Thu, 29 May 2003 01:17:37 GMT

Re:ADO -> Lock Record on Edit


Quote
"Wilson" <wil...@email.com> wrote in message

news:90s4ma$s296@bornews.inprise.com...

Quote
> dear Jumbo Dumbo

> i'm using MSSQL 7 and try your procedure / fuction but it still could not
> lock the record.
> please explain more detail

Dear Wilson,

As for the explaination, please carefully read from below that
"...generalized pessimistic record locking function for Access" really does
mean that it is restricted to desktop Access.

As an aside, you shouldn't be trying to do pessinistic locking on relational
MSSQL-7.

Regards,
Jumbo

Quote
> > locking or just use the following suggestion which will apply a native
> pessimistic
> > lock that will be honored by both Access and your applications.

> > Add to your unit this generalized pessimistic record locking function
for
> Access
> > databases. Must use ServerSide cursor and Pessimistic locking.

> > function ApplyLockBeforeEdit(DataSet: TDataSet): boolean;
> > begin
> >   Result:= False;
> >   with DataSet as TCustomADODataSet do
> >     if (Pos('JET.OLEDB', UpperCase(Connection.Provider))>1) then
> >       if CanModify and (CursorLocation = clUseServer) and
> >         (LockType = ltPessimistic) then

Other Threads

1. ADO Record locking / locked by

2. lock records (pessimistic lock) using D5 Access 2k ADO

3. lock records (pessimistic lock) using D5 Access 2k ADO

4. Record locking with ADO Express, ADO 2.5 and Access 2000 .mdb files

5. Pessimistic Locking -> Cannot edit fields

6. ADO-Access2000-Multiuser-Edit Locking?

7. Detecting a Lock record (not a lock table but the record)

8. Help! Record locked - Not in Edit Mode

9. Locking a record while editing, how?????

10. MDI database locks record on everyother edit?