Multi User APPEND in Shared Table

[thetempuser]

Hello,

I have a VFP table opened as shared. The table sits in a shared folder (with read/write priv) on my LAN. Users append records from a form then they close it. It works great. But only one user at a time can append. If more than 1 user opens the form and appends, the first one that appends can write into the table, the rest have to wait for him to close the form. I know that append blank locks the table header.
Ideally it would be to lock just appended blank records, so that all users can append. But I don't know the recno() to LOCK(). Is there a way for users to append all in the same shared table?
How can I overcome this so that users won't have to wait for 1 user to finish?
Or is there any other method for multi-appending in a shared table, besides append blank?

I used this as example:

Select c:\testing\test2 SHARED
APPEND BLANK
------how can I lock just this appended record?

Thank you,
Andrei.

 

[Olaf Doschke]

You have wrong assumptions. the table header is just very temporary and gone after APPEND BLANK.
Multiple users can APPEND and work on a record. There is something else causing a pessimistic lock look into form properties, eg form.Buffermode. Should be either 0 or 2.
Look into CURSORGETPROP("Buffering",0) which determines the buffermode for any opened table, too. And last not least the DE may interfere.

With all at default APPEND BLANK does not apply a permanent table header lock.

Bye, Olaf.

 

[Tore Bleken]

Don't use Append blank followed by Replace. Use Insert Into instead, check help.

 

[Mike Lewis]

Don't use Append blank followed by Replace. Use Insert Into instead, check help

I'm not sure if that will make any difference from the point of view of locks. What Olaf said about the header only being locked temporarily is true both for APPEND BLANK and INSERT.

The most likely explanation for the problem is that pessimistic record locking is in force. In fact, off-hand, I can't think of any other way in which you would see the behaviour described.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Hi tbleken,

Tore Bleken I assume?
Long time no see. Welcome here.

You're right you can make an INSERT instead of APPEND BLANK followed by REPLACE, but I think the situation here is the APPEND BLANK is used to create a new empty record to be filled by a data entry form and the blank fields are bound to form controls. This record is most probably pessimistically locked, which still will not be a problem for other users to append their own new records.

I saw people having a form in two modes, one bound for editing existing data and one unbound for entry of new records, which then are finally inserted into a dbf. That is double effort, because with APPEND BLANK you can use the same bound form to edit this new record and in that regard APPEND BLANK is worth gold. The fields you want populated with default values ARE populated with default values. There even is no reason to avoid APPEND BLANK for canceling out of creating a new record, you simply use a buffering mode and can then later decide to cancel or save via tablerevert or tableupdate.

Bye, Olaf.

 

[TamarGranor]

I'm wondering whether your actual problem isn't this line:

Select c:\testing\test2 SHARED

SELECT doesn't have SHARED as an option. The table is open either shared or exclusive and you can't change it along the way without closing and re-opening it.

Tamar

 

[Dan Freeman]

SELECT also takes an alias, not a path/filename.

 

[thetempuser]

Hello to all,

I apologise for this late reply, it's been a hectic few days. Because of that, I made the mistake of using an old form with controls on it. I thought I'd deleted all of them, but there was also a grid, way down on the form, and i forgot completly about it. Anyway, I decided to start fresh, I've created a new form, a new test table, use it as shared, and started to append from 2 session of VFP. IT WORKS !!! So I'm happy.
But my original method of input into the table is to use a grid.
I first use a prg like so:

launch.prg

USE registru15\registru SHARED
SET DATE TO DMY 
SET ORDER TO nr 
SET CENTURY ON 
DO FORM registru.scx

On the registru.scx i have a grid locked to registru table, residing in registru15 folder.
There are also 2 bubttons on the form: ADD and DELETE
ADD BUTTON:

SELECT registru 
APPEND BLANK 
thisform.grid1.RecordSource="registru"
thisform.grid1.SetFocus
GO TOP

DELETE BUTTON

SELECT registru
DELETE FOR (nr)=VAL(ALLTRIM(thisform.text1.Value))
SET ORDER TO nr 
thisform.text1.Value=""
thisform.text1.setfocus

Each user appends blank to the registru table, from a lan pc. It works great when only one users appends at a time. If another user tries to append, he has to wait, for the user to go to another record, or close the form.

I've tried this, before I launch the form:

USE registru15\registru SHARED
SET MULTILOCKS ON
CURSORSETPROP("Buffering",3)
SET ORDER TO nr 
SET DATE TO DMY 
SET CENTURY ON 
DO FORM registru.scx

Then, at the ADD button:

SELECT registru 
APPEND BLANK 
thisform.grid1.RecordSource="registru"
thisform.grid1.SetFocus
GO TOP 
TABLEUPDATE()

I've tried CURSORSETPROP("Buffering",2), CURSORSETPROP("Buffering",4), CURSORSETPROP("Buffering",5), and on the form buffer mode 0 and 2. I get an UPDATE CONFLICT, not when I close the form, but when I relaunch it. Sometimes only one user's record is added, even thou some other user has append blank and added one.

I like this method, because the grid enables the user to see the blanked record being inserted, he then writes what he wants, and also has all the other records in the grid, in front of him
Anyone has any clue if this approach is possible, with the grid and a shared table?

 

[Olaf Doschke]

It should be possible with form.buffermode =0 or 2

Setting Cursorsetprop("Buffering") before starting registru.scx is useeless, if the SCX uses private datasession.

You should open tables in the form, not before calling it. The form should be self contained and simply work by running it.

Bye, Olaf.

 

[craigber]

There is a gotcha with USE table SHARED. If the DBC is not already opened, it will open it automatically, but the DBC will be opened based on the current SET EXCLUSIVE setting. Meaning, if SET EXCLUSIVE ON, the DBC will not be opened in multiuser mode.

Solutions are to SET EXCLUSIVE OFF
or
OPEN DATABASE dbc SHARED
Either of these need to be done before USE

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

What craig said could be a catch, but would lead to error 1569: "Database ....dbc: File Access denied" and not to lock problems and update conflicts.

I'd look into the keyfield definition and whether you create the key values correctly and unique for all users.

Another thing leading to lock problems would be starting a transaction. A transaction must not start at the begin of creating a new record, but when saving to more than one table can group all these changes to one set of changes wither commit as whole or not at all.

In regard to exclusive: The IDE setting default is ON, because as developer you mostly will need exclusive access to your tables for ease of development and altering database, etc. The default setting in an EXE at runtime is OFF, so you might see differences in testing within IDE vs runtime behaviour. This is also just one of the settings differing in IDE and EXE/runtime. It's ok to debug in the IDE, but neither users should run with the IDE - as a side note:you break licensing if you gave users the vfpN.exe - nor you should run with IDE defaults in test/debugging. Take a look at all session specific environment settings by reading the help topic "Commands that Scope to a Data Session".

Bye, Olaf.

 

[craigber]

Another thing to keep in mind... don't bind any controls directly to the table. Always buffer the data in some way.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[thetempuser]

I decided to start again and create a fresh new form with a fresh new grid.
I am using
form.buffermode=0
datasession=1

and I AM ALWAYS GOING TO USE THESE WHEN DEALING WITH SHARED TABLE:
SET EXCLUSIVE OFF
SET MULTILOCKS ON
use .....
CURSORSETPROP("Buffering",3)
Thank you very much for these, HUGE DIFFERENCE.
And it all works, I can append from different sessions, no errors, so far. I'm still reading and experimenting with transactions.
Also, in the code for the ADD button I managed to position the user to the EXACT appended record:

APPEND BLANK 
a=MAX(RECCOUNT(),0)
GO a
SET ORDER TO nr 
thisform.grid1.Refresh

This is useful for the user to see it's own appended record, until I manage the transaction errors part.

I am also opening tables with the form, I have not yet made any key unique values, (I'm going to do this when I'm a pleased with the current configuration and I am convinced that everything works). Up next, I'll test EXE vs session and see the differences.

So far I am just starting to learn about shared tables.
Thank you all for your help.
Andrei.

 

[Olaf Doschke]

After APPEND BLANK you are positioned at the new record. There is nothing to do here.
Your code in fact may fail in case someone else is appending a record at the same time.

You would only set order once at form init so all code you need will be:

APPEND BLANK 
thisform.grid1.Refresh

No more, no less

Bye, Olaf.