Re: ADO Data Control Concurrency Problem

From: Andrew D. Newbould (newsgroups_at_NOzadSPANsoft.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 23:37:42 +0000

<snip>
>> Two users open the same row of a table in Oracle for editing ; one of them
>> commits the changes to the database. After this event, the 2nd user has an
>> outdated version of the row on his/her screen. ADO doesn't notify the
>> application that a row has changed.
>> Editing of row in table is done with the help of ADO Data Control. We have
>> used Cursor Location "adClient Side" for the connection.
>>
>
>A 10 cent answer in four parts.
>
>1) ADO has nothing to do with it.
>2) By requesting adClient you have basically asked for a bag of stuff as it
>exists at the time of the request that you can search thru and generally
>play with - all logical connection with Oracle has been lost. ie, the server
>has no remaining interest in what it delivered.

Not exactly true. Cursor location does not bare on whether the
connection to Oracle has been lost just that the data resides in the
Client or Server memory. The "ActiveConnection" needs setting to
"Nothing" in order to break the connection (ie: Disconnected Recordsets
of Firehose cursors).

The "Cursor Type" and "Lock Type" control whether ADO will generate a
trapable error due to dirty data. If the recordset has been opened with
a Cursor Type of adOpenDynamic then changes made on the server should be
reflected in the open recordset (eventually - question of speed). If you
have used adOpenStatic then ADO would generate an "Underlying Data has
changed" error that is trapable. You should also use a Lock Type of
adLockOptimistic or adLockPessimistic depending on your requirements.

>3) Modern RDMSs provide data integrity but never guarantee concurrancy. You
>have to set up your own transactional logic to manage it.

I agree. It is far better to build safer logic into your application
rather than rely on ADO getting in write every time.

>4) Managing 'stale' data is a classical problem in programming and there are
>an endless variety of techniques to manage it - triggers, signals, 'bad
>bits', replication, merge strategies, activity flags, &etc - find one that
>fits 'your problem domain'.
>

Kind Regards, 

-- 
Andrew D. Newbould                  E-Mail:  newsgroups@NOSPAMzadsoft.com
ZAD Software Systems                Web   :  www.zadsoft.com

Relevant Pages

  • Re: DAO to ADO Recordset Options
    ... DAO was really fast when dealing with Access data (faster than ADO) but was ... Recordset. ... For client-sided cursors, there is only Static cursor type regardless as ... determines how often data is fetched from the server. ...
    (microsoft.public.data.ado)
  • Re: ADODB RECORDSET Optimierung
    ... Recordset mit einem Static Cursor öffnest? ... Damit zwingst Du ADO die ganze Tabelle in den Speicher einzulesen und eine Kopie aller Records anzulegen. ...
    (microsoft.public.de.access.clientserver)
  • Pessimistic locking with approles.
    ... It now uses ADO, server side cursors, application roles, ... say you have an ADO recordset open on a table Employee ... (Pessimistic locking requires a server side cursor.) ...
    (microsoft.public.sqlserver.security)
  • Re: ADO Performance
    ... sections (this will vary a little depending on cursor location): ... Find out how long it takes to run the query and get the recordset ... place some logic on the server side to reduce the execution time. ... loop with oRS.MoveNext command. ...
    (microsoft.public.vb.general.discussion)
  • Re: Cursor ODBC
    ... Aus der SQL Server Dokumentation zu DECLARE CURSOR ... gibts auch keine absoluten Positioniervorgänge. ... > Ja, solange dieses Recordset nicht geschlossen wird, bleiben die ...
    (microsoft.public.de.access.clientserver)