Database Muiti user Saving
Printed From: Codejock Forums
Category: Codejock Products
Forum Name: General Discussion
Forum Description: Topics Related to Active-X COM Development in General
URL: http://forum.codejock.com/forum_posts.asp?TID=18236
Printed Date: 05 May 2024 at 5:11pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com
Topic: Database Muiti user Saving
Posted By: markmark
Subject: Database Muiti user Saving
Date Posted: 18 April 2011 at 7:20am
|
Hi All Our application is a multi user database app, using Access or SQL server database. Currently I handle uses saving the same product data using a time stamp. In that when I load the data to the screen I read a last Saved date. When the user saves the product, it first looks at the product lastsaved date on screen and the lastsaved date in the database and if they are the same then it allows the save, If not, then an message is displayed to the user that someone else has saved this product while they were editing and will have to refresh their data and make the changes again. This has worked very well for the past 4 years. However I now reluctantly have to change this, so that as soon as a user starts to edit a products data, it will lock that product in some way Any user trying to edit the locked product will be notified that the product is being edited and is locked. My first thought was to create a new table called product_locking, and enter the product ID as a row as soon as a user starts editing. When the product is saved or cancelled it would delete the row. There may be delay issues here as ADO has a lazy save delay. I could use a text file with the file name as the product ID, saved on a shared folder on the server, and that would be used instead of the table.
Has any one used a better method that has been successful that they would like to share with me? Many Thanks Mark ------------- Product: Xtreme SuitePro (ActiveX) version 13.0.0 Platform: Windows XP (32bit) - SP 2 Language: Visual Basic 6.0 |
Replies:
Posted By: mgampi
Date Posted: 18 April 2011 at 8:05am
|
Hi Markmark; we're successfully using this approach on SQL server for several years and have no problem at all: Generally spoken, all updates are handled by stored procedures that get all old and new data and then handle the update by first selecting the row to be updated providing ALL old data using SELECT ... WITH(ROWLOCK) option. if the record could be found with the old data the update is done and the transaction is committed which releases the row lock. if the record could not be found the SP rolls back the transaction and then checks whether it can find the row by only using the primary key column(s). If it could be found --> another user has changed the record. If it could not be found --> the record has been deleted by another user. The SP returns errors with return values greater 0 but the SP also returns -1 if the record has been changed by another user or -2 if its been deleted. A return value of 0 means SUCCESS. If you need an early lock you have to call SELECT ... WITH(ROWLOCK) as soon as the user tries to start editing. I don't know how to implement the same using Access, but all our SQL databases (ORACLE, SQL Server, PostgreSQL, ...) work with this approach. ------------- Martin Product: Xtreme Toolkit v 19.0.0, new Projects v 19.1.0 Platform: Windows 10 v 1909 (64bit) Language: VC++ 2017 |
Posted By: markmark
Date Posted: 19 April 2011 at 4:00am
|
Many Thanks Mgampi for your reply I will give this some thought; we have never used SP, all SQL selects, Updates etc are all in code, and because we had to support access as well as SQL server are limited. I guess our data stamp method in use at the moment is similar to what you use, in that it stops a save if some one has previously saved that product. But the change I have to implement is to give a user warning that the current record he is about to edit is locked, so that they don't make changes that will never get saved, which is what happens now. So your early lock method is where I have to look at more closely Thanks again ------------- Product: Xtreme SuitePro (ActiveX) version 13.0.0 Platform: Windows XP (32bit) - SP 2 Language: Visual Basic 6.0 |
Posted By: gibra
Date Posted: 22 April 2011 at 11:59am
To avoid delay, after each INSERT or UPDATE you must force the refresh of cache-data using the
Microsoft Jet and Replication Objects 2.6 library (MSJRO.DLL):
1) in your VB6 project add a reference to the library
2) declare a public object variable into a BAS module
Public Jet As JRO.JetEngine
3) Create the istance
Set Jet = New JRO.JetEngine
4) after each INSERT or UPDATE call
Jet.RefreshCache <CN>
where <CN> is your ADODB.Connection object
------------- gibra CJ SuiteControl v: 13.x to 19.x Windows 10 64bit VS2019 - VB6.0 SP6 <a href="http://nuke.vbcorner.net/Home/tabid/36/language/en-US/Default.aspx" rel="nofollow">VS/VB 6.0 Installer v6.8 |
markmark wrote:Posted By: markmark
Date Posted: 05 May 2011 at 3:54am
|
Many thanks gibra I will starting work on this next week, and will give it a go ------------- Product: Xtreme SuitePro (ActiveX) version 13.0.0 Platform: Windows XP (32bit) - SP 2 Language: Visual Basic 6.0 |
Posted By: gibra
Date Posted: 05 May 2011 at 4:53am
|
Also, about multi-user environment I don't use any lock as explained in :
Using Optimistic Concurrency http://msdn.microsoft.com/en-us/library/aa0416cz%28VS.80%29.aspx - http://msdn.microsoft.com/en-us/library/aa0416cz(VS.80).aspx
Although this articol is for NET, the concepts for Optimistic Concurrency are valid for any language programming.
 ------------- gibra CJ SuiteControl v: 13.x to 19.x Windows 10 64bit VS2019 - VB6.0 SP6 <a href="http://nuke.vbcorner.net/Home/tabid/36/language/en-US/Default.aspx" rel="nofollow">VS/VB 6.0 Installer v6.8 |
Posted By: markmark
Date Posted: 05 May 2011 at 5:31am
|
Thanks again gibra. ------------- Product: Xtreme SuitePro (ActiveX) version 13.0.0 Platform: Windows XP (32bit) - SP 2 Language: Visual Basic 6.0 |