Best way to Add records to a shared network table? 1

[kate]

I am using VFP 6.0. I use free tables in my application. The application has multiple users, so I need to keep all the tables open shared.

One part of the application calls for users to write records to a table. I do this by using the INSERT SQL command. The command will insert a record into the table with values memory variables previously created in the program.

The problem is, when more than one user tries to add a record, some of the users get an error, "File is in use by another user".

I realize that the INSERT SQL command will lock the table header and not allow multiple users to add new records until that header is unlocked, but is there a way around this?, or a better way of adding records?

Thanks

 

[alvechurchdata]

You could RLOCK() to test whether the record can be locked before you save the data but a better technique would be to use the newer buffered modes with TABLEUPDATE().

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Kate,

You have two broad options:

- Buffer the table, using one of the pessimistic locking modes. In your error routine, trap "record in use by another" and "table in use by another". Display a user-friendly message asking the user to wait, then issue a RETRY. (Also, use SET REPROCESS to increase the number of times that VFP will automatically re-try the lock.)

- Buffer the table using an optimistic locking mode. When calling TABLEUPDATE(), pass .F. as the second parameter. If TABLEPDATE() returns .F., use AERROR() to find out why. If it is because of a multi-user violation, write some code to deal with that in whatever way makes sense in your application. If your application can't sensibily handle multi-user violations, use pessimistic locking, as above.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kate]

Mike,
I do have an ON ERROR routine already in place that gives the user the option to quit or retry. This error routine handles any error that occurs in the program.

If I use the SET REPROCESS command to increase the amount of times, will it bring up the error routine each time it attempts to lock the file?, or will it just attempt to lock the file automatically?

Thanks

 

[Mike Lewis]

Kate,

If I use the SET REPROCESS command to increase the amount of times, will it bring up the error routine each time it attempts to lock the file?, or will it just attempt to lock the file automatically?

SET REPROCESS lets you specify either a number of seconds during which VFP will keep trying to get a lock, or the number of attempts. While it is making those attempts, the user will see a message saying "Waiting for lock ... " (assuming SET STATUS is on). At that stage, the error routine doesn't fire.

If the lock has not been obtained by the time the number of seconds or the number of attempts has been reached, then the error routine will kick in.

In general, if there is no user interaction going on while a file is being updated, increasing the value of SET REPROCESS is all you need to do in order to avoid any problems.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[kate]

Thanks Mike,
You've answered my questions perfectly!