VFP 6.0: Problems with multi-user access.

[Kimed]

Hello,

I have an application that several people should be able to access at the same time to enter and modify data. After several attempts at buffering in various modes, and not really satisfied with results, I decided to go the longer way. The form used to enter data uses some dummy table in its Data Environment that grids and other control elements can reference at design time as real. The top-level form, though, creates a cursor:

SELECT * FROM real_table INTO CURSOR dummy_table READWRITE WHERE <condition>

before calling the data form (where "Data Session" is set to "1 - Default Data Session", so the form doesn't look for a table if a cursor with the same name already exists); "dummy_table" is the same name that the data form uses. The idea is that the user doesn't work with real data at all, but only with its CURSOR phantom, only interacting with the real table when he decides to confirm entered data - some simulated imitation of optimistic table buffering without actually locking the table at all. In fact, the table with the "dummy_table" name doesn't even physically exist in the common area - I only keep an instance of it on my own computer to be able to edit the form when a need arises. Since each user normally works with his own segment of records, interferences are rare and can be handled easily. And since the user doesn't touch the real table except for short data exchange bursts, there's no chance for access conflicts. Right? Right?

In reality, though, this is not so. Every once in a while I witness the error message #109: "Record is in use by another user" after editing a field in the grid that *only* has references to the table that exists in its CURSOR incarnation and never as a real thing. What could be possibly a reason for such behavior?

Thanks.

 

[Olaf Doschke]

That surely does not come from interacting with a cursor, cursors are always exclusive and in the users TEMP folder, there can be no error 109 with cursors.

You can have that error only when writing to the table.

You can have that with concurrent saves, the more concurrent users the more possible. You need to take care of such things in any case, normally you can supress this full via SET REPROCESS set to infinitley retry, but indeed it can lockup in a situation network fails for example, so you should rather also handle error 109 anyway.

So there is no advantage of doing buffering yourself, really. There is no disadvantage using buffering. Tables buffered correctly also just have that minimum time of loacks needed during writes, if you use optimistic table buffering, that is no locking of data you edit while you edit it. That means update conflict management, but it's not that hard to do.

Andy Kramek has written a very good article about how and why to use buffering and locking:

http://weblogs.foxite.com/andykramek/archive/2005/10/18/948.aspx

Additional to that I would also recommend not applying that to DBFs directly but to cursors, not readwrite ones create by SQL, but either views or cursoradapter cursors. The latter give more freedom about what complex SQL to use than the view designer allows and adds some more options, for example events like BeforeUpdate, AfterInsert, etc.

Just make sure you only buffer the view or CA cursors, not the tables, otherwise you need a two level TABLEUPDATE() of the cursor and the table. And don't touch the tables directly, this is purely the job of TABLEUPATE() or TABLEREVERT().

Bye, Olaf.

 

[Olaf Doschke]

With VFP6, forget about cursoradapters of course.

Bye, Olaf.

 

[Kimed]

That surely does not come from interacting with a cursor, cursors are always exclusive and in the users TEMP folder, there can be no error 109 with cursors.

I know! Hence the bewilderment. I'm not going to discuss virtues of automatic buffering here; they're possibly plentiful. Point is, by all intentions, my solution might seem awfully clumsy, but it couldn't be anything but failproof. Except that it isn't, and I'd like to know why.

 

[Mike Lewis]

Kimed,

I can't answer your question, but I really think you're making this much too complicated.

As I understand it, you are using this "phantom table" because you are "not satisfied" with buffering.

Buffering is the normal way of dealing with data-entry in a multi-user environment. It works reliably and it does what it's supposed to do. I srongly suggest you take a bit of time to understand how buffering works (it's not that difficult), and then get rid of your phantom table.

The Help file has plenty of examples, and you can always ask here if you need additional help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Kimed]

As I understand it, you are using this "phantom table" because you are "not satisfied" with buffering.

I'm not, but not because I don't understand how it works. The reason why I had to renounce buffering is the fact that I don't want just to "change" data when somebody enters a new value for an existing record. I create a new record with the same ID, but keep the old one, setting the "obsolete" flag for it. Since the table contains fields for the user ID and creation time, *dynamics* of changes can be monitored, and every modified number can be backtracked to its author if such a need arises. Buffering may be good for other kinds of works (and there *are* applications where I use it), but keeping full logs is not its mission.

Hence, the original question. If simple RAM-based cursors suddenly start throwing 109 errors, who a guy can rely upon these days? O.O

 

[TamarGranor]

The way most people handle your requirements is to create a change log in a separate table where every change is logged. You can use triggers to ensure that changes get logged and the whole thing happens automatically.

Tamar

 

[Olaf Doschke]

If it's really the cursors updates or inserts that cause that error 109. Just to add the obvious you're talking about a 10+ year old version o VFP, I started with VFP6 in 1999 and it was not new at that time. Do you at least have it at SP5 level? If not I don't think there is a download of Visual Studio 6 Service Packs available.

That said I don't remember any version of VFP failing with error 109 on cursor updates. You might have an implicit table update due to moving record pointers in a table, to which a cursor is related, as you don't use buffering. Handling error 109, see what SYS(2018) returns, this should be the name of the table being used by another user.

Bye, Olaf.

 

[Kimed]

Handling error 109, see what SYS(2018) returns, this should be the name of the table being used by another user.

Tried that. SYS(2018) returns an empty string.