How to Check for updates?

[JNeave]

Hi,

We develop multi user data access systems based on VB6, ADO(2.6), OLE DB provider for SQL Server and SQL Server 2000

Here is the situation.

A recordset holding a single record is opened client side and the connection to the database is tempClosed, no locks are held.

The operator then goes for a cup of tea.

On his return, he then decided to change the details of this piece of equipment.

Here is the question:

How is it possible to tell the DB system to perform the following.

If the Record has not changed since it was opened then
    UPDATE THE RECORD CONTENTS
Else
    THROW AN EXCEPTION

We believe there is some hidden meta data associated with each record in ADO, like a LastUpdateDateTimeStamp, which can be compared with the one on the real record in the DB and will UPDATE or FAIL depending on this.

Is any of this automatically implemented with the LockOptimistic option in ADO?

Thanks,

Jim.

 

[BobRodes]

I dunno, and I ain't going to look it up, but you can simply store the contents of the record to an array when it's opened, and then compare the present contents to the array whenever you like and do whatever you want.

Does that work?

Bob

 

[CCLINT]

You could check the recordset's Value property with the UnderlyingValue property, but this means that the recordset needs to still have an active connection to the database. Not a recommended method usless the data is local.
Using a disconnected recordset, and action queries to update the table is better.
This stateless mode if more efficient and scalable.

With that mentioned, in order to check whether a record has had a changed since last retrieved, you will need to add a TimeStamp field.

Then do this:

1. Add a TimeStamp field.

2. Retreive the record and use a Variant to assign the value of the TimeStamp field to it.

3. Use a stored proceedure with input parameters to hold the values that the fields are to be updated to, and an output parameter which returns True if the record has changed

4. The stored proceedure goes something like this:

CREATE PROCEEDURE spUpdateEmployee
(@ID [int]
@LastName [nvarchar](30),
@FirstName [nvarchar](30),
@TimeStamp [timestamp])

AS

If (SELECT TimeStamp FROM tblEmployee WHERE ID = @ID) = @TimeStamp
UPDATE tblEmployee
SET [LastName] = @LastName,
[FirstName] = @FirstName

WHERE
([ID] = @ID)
ELSE
return = -1

6. Use a Command object to pass the field changes and the value of the timestamp field at the time when the data was retrievd from the table.

This could also be done in the client with two calls to the database (one for the current timestamp value, and one to actually update the record), but it could still cause conflicts because of too long of a time lag between polling the TimpStamp a second time just prior to sending the UPDATE, comparing it with the previous value, and then sending the update.

This way, (using the stored proceedure), the check for if the timestamp has changed, and the update happen on the server all at once, almost instantly, minimizing the conflicts and delays to almost nil.

 

[JNeave]

Yes, that SP would do it and it contains it's own proof that it will work as well.

This will be usefull for critical situations.

However for more mundane situations, to prevent the creating of many SPs we have this working situation. We think. I've only tried it once.

A Resordset is opened as Clientside, Static and LockOptimistic

A second user modifies the data in the same table.

The forst user attempt to call .Update from the recordset, an exception is thrown.

The common thread between the SP and the Optimistic ADO methods is that they both are atomic, which is the reason why we are performing this research. It is our goal to be able to theoretically prove our software is 100% transactionaly save in a multi user environment.

Of course, the SP method is provable. the ADO method stands up to empiric tests, but we have yet to find a theoretical proof of it's stability.

Thanks,

Jim.

 

[CCLINT]

For JET/ACCESS MDB users, or any type of db which doesn't support a true TimeStamp and/or return values from a stored procedure, there is a method to do this also, similar to as mentioned above via stored, even with a JET/ACCESS 3 Mdb and ADO or DAO, (however, with a JET 3 Mdb, there is a lag time of 1 second though, but there are ways to get around this) if anyone is interested....

 

[JNeave]

Sounds like an FAQ to me...

Thankfully our JET days are long gone.

Just not quite long enough yet.

Ta,

J.

 

[CCLINT]

There's one other method with-out using a SP, which is similar to a JET solution, but should also work:

Add to the UPDATE action query, additional criteria for the TimeStamp in the WHERE clause:

"...WHERE ID = " & rs("ID&quot & " AND TimeStamp = '" & rs("TimeStamp&quot & "'"

In the connection's Execute method, stick in a variable of type Long for the argument "RecordsAffected".

After the Update, if this variable = 0, then you know a conflict occured.

You can check the connection's errors collection to make sure it isn't a network error, or whatever.