Codejock Developer Community : Database Muiti user Saving

Database Muiti user Saving : Thanks again gibra.

Thu, 05 May 2011 05:31:39 +0000

Author: markmark
Subject: 18236
Posted: 05 May 2011 at 5:31am

Thanks again gibra.

Database Muiti user Saving : Also, about multi-user environment...

Thu, 05 May 2011 04:53:09 +0000

Author: gibra
Subject: 18236
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(VS.80).aspx

Although this articol is for NET, the concepts for Optimistic Concurrency are valid for any language programming.

Database Muiti user Saving : Many thanks gibraI will starting...

Thu, 05 May 2011 03:54:47 +0000

Author: markmark
Subject: 18236
Posted: 05 May 2011 at 3:54am

Many thanks gibra

I will starting work on this next week, and will give it a go

Database Muiti user Saving : markmark wrote: When the product...

Fri, 22 Apr 2011 11:59:01 +0000

Author: gibra
Subject: 18236
Posted: 22 April 2011 at 11:59am

markmark wrote:

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.

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

where is your ADODB.Connection object

Database Muiti user Saving : Many Thanks Mgampi for your...

Tue, 19 Apr 2011 04:00:18 +0000

Author: markmark
Subject: 18236
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
Mark

Database Muiti user Saving : Hi Markmark;we're successfully...

Mon, 18 Apr 2011 08:05:37 +0000

Author: mgampi
Subject: 18236
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.

Database Muiti user Saving : Hi All Our application...

Mon, 18 Apr 2011 07:20:24 +0000

Author: markmark
Subject: 18236
Posted: 18 April 2011 at 7:20am

Hi All

Our application is a multi user database app, using Accessor SQL server database.

Currently I handle uses saving the same product data using atime stamp.

In that when I load the data to the screen I read a lastSaved date.

When the user saves the product, it first looks at theproduct lastsaved date on screen and the lastsaved date in the database and ifthey are the same then it allows the save, If not, then an message is displayedto the user that someone else has saved this product while they were editing andwill 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 assoon as a user starts to edit a products data, it will lock that product insome way

Any user trying to edit the locked product will be notifiedthat the product is being edited and is locked.

My first thought was to create a new table calledproduct_locking, and enter the productID as a row as soon as a user starts editing.

When the product is saved or cancelled it would delete therow.

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 productID, saved on a shared folder on the server, and that would be used instead ofthe table.

Has any one used a better method that has been successfulthat they would like to share with me?

Many Thanks

Mark