Multiple user database update issue

[robsuttonjr]

I have a foxpro 8 application which I use remote views to retrieve and update SQL server 7 data. The issue is that I may have multiple users looking at the same table records and trying to update them. So one user opens a line item to update its color to blue. Another user may also be looking at this same item and decide to change the color to red. How do other programmers handle this? Also if someone makes a change what is the best way to indicate to the other users these changes if they are looking at the remote view data that is now different? Many thanks for any tips to be shared.

 

[FoxDevil]

robsuttonjr, here is a suggestion, but i dont know if this will work with the SQL Server. Try using LOCK(). I m using following code to lock and prevent multi user to edit a record at the same time.

SELECT table alias
mrecno = RECNO()
IF LOCK(mrecno) = .F. then
MESSAGEBOX("Record is being updated by another user....&quot
ELSE
"here goes the routine for edit"
endif

--------------------
for using this u must enable buffering, and set the mode to optismistic table/record buffering, (which suites your sitaution)
--------------------

IF THIS IDEA WORKS THEN PLZ LET ME KNOW SO THAY MY KNOWLEDGE COULD BE INCREAED.

 

[TheMissionary]

With a shared database, there are two issues to deal with:
1 Buffering
2 Locking

Recommend you go to the INSERT INTO/UPDATE commands instead of REPLACE. To do this, you will need to set the table buffering method to optimistic/pessimistic, Record/Table I use optimistic record buffering where ever possible (this only locks the record when it is being updated (saved.) For high use records, I recommend pessimistic record (it slows the users down but ensures data integrity as the record remains locked when a user has it open.) I try to stay away from table buffering as this can lock an entire table.

Locking allows you to lock records or tables as required. This requires more programming effort, thought and planning. Consequently, you end up with more code to maintain. I don't use the RLOCK()/FLOCK() commands unless I absolutely have to.

The real issue may be the table. Why would two users be working on the same record at the same time? Is there a way to batch user input in order to reduce record sharing conflict? Could you set that table up as a local table (in setup, create that table on each user's computer?)

 

[Mike Lewis]

Rob,

In the situation you describe, SQL Server will return an error. Specifically, this will happen if user A retrieves a record (by opening or requerying the view), then user B retrieves the same record, then one of those users updates the record, then the other user updates the same record to a different value. In that case, the second update will fail.

This assumes that you have selected an option other than Key Fields Only in the Update Where section of the Update Criteria tab in the view designer.

You can trap the error by checking the return value from TABLUPDATE(0,.F.). If the value returned is .F., an error has occurred. You can then use AERROR() to find out which error occurred. I don't know the SQL Server error codes off hand, but you should be able to figure it out with a little experimentation.

By the way, I don't think FoxDevil's suggesion will work. You can LOCK() the view, but there's no point in doing so, and in any case, that doesn't affect the actual data on the server.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Mike Lewis]

TheMissionary,

Your comments are very useful and relevant for VFP databases. However, Mr Sutton is interested in SQL Server, and SQL Server does not support pessimistic locking.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk