Blocking access to records

[Ubaidullah]

Hi,

I need to prevent two users from accessing/modifying the same record in a table (MS-SQL). Currently, I am using a flag in the table that indicates which user has the record open. This flag is set in ThisWindow.Init and cleared in ThisWindow.Kill. This works fine for most cases but sometimes the flag remains while the user is not in the app.

I am considering keeping these 'flags' in a 'server' app that registers which records are open and if a user exits or times out, then the flag gets cleared automatically. How would I go about doing this?

Appreciate any pointers.

Thanks & Regards,
Ubaidullah Nubar.

[kevin plummer]

I would say that flags in a web app are pretty dangerous as there are too many ways a user can not complete a form which you can't trap.

Not that I have done what you ask but I wonder if you set a SSV of the time when a form is opened to modify the record and then set the time in the record when the save button is pressed you could detect if the record had been updated since the form was opened and throw an alert in the validate record embed and maybe force the 2nd user to cancel? I don't think you could block the update form.

[Ubaidullah]

Hi Kevin,

Thanks for the reply.

This is not a web app, just a normal Clarion app running on a LAN. If this was a web app, then I agree a different approach would be needed. Actually, a web app could handle this more easily by removing the block when a session times out or allow the administrator to remove the block and delete the session simultaneously.

In this case, I have the app running on a LAN where different users are running the same app. I am looking for a robust way to mark records as being in use. Using NetSimple methods, it should be possible. Hopefully, Bruce can chime in and help us out here.

Regards,
Ubaidullah Nubar.

[lanmicro]

Hi Ubaidullah,

Since this is an app running on a LAN I would do something like this:

When entering the form create a record in a file (call it timings) The timing record would contain record type, sysid of record being updated and current date and time.  in the timer event embed ( set to 5 seconds) I would read the record for this record type and sysid and compare the current date and time to the date and time in the timings file.  If 5 seconds have gone by then update the record in the timings file.  when someone else attempts to edit the same record type and sysid they would read the timings files and if there is a record there with a date and time within 5 seconds of the current date and time then display a message and exit the form.

If upon reading the timings file and more than say 30 seconds have gone by since the timings record was updated then I would assume something happened to the other guy and update the record with the current date and time and allow entry.

Upon exiting the form in the kill method I would delete the timings record for this record type and sysid.

[Bruce]

>> This is not a web app, just a normal Clarion app running on a LAN.

probably a critical bit of information missing from your original post.

The problem you have is difficult to solve. It's known as "pessimistic locking".  Some useful background is here;
http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking

By default the Clarion templates employ "optimistic locking", not pessimistic locking.

The big problem with pessimistic clocking is that if the user locks the record, and doesn't unlock it (for whatever reason) the record remains locked. To overcome this usually some sort of "timeout" is employed - for example like Greg described.

There is also a Clarion file driver command, LOCK, which you can use, however this doesn't have the timeout feature, and so can be a pain to use.

cheers
Bruce

[Ubaidullah]

Hi Bruce,

Thanks for the reply.

I do not want to employ database level locking. I was thinking of using Nettalk to keep a list of "in-use" records, probably on an app running on the server. If a client exits/disconnects for whatever reason, it could be detected and the "in-use" flag cleared. This would perhaps be similar to the CloseApp template where and admin app can see all running apps. But in this case, it would see what records are "in-use" by other app instances on the LAN.

Along the same lines, I would like to keep a list of active users on a LAN who are running my app and see what window/proc they have open. Is this something feasible with Nettalk or perhaps there is a template/example somewhere that shows how to do this?

Thanks & Regards,
Ubaidullah Nubar.

[Bruce]

>> I do not want to employ database level locking. I was thinking of using Nettalk to keep a list of "in-use" records, probably on an app running on the server. If a client exits/disconnects for whatever reason, it could be detected and the "in-use" flag cleared

This is certainly do-able, but your disconnection detection would be your biggest challenge. Obviously you can tell if a connection drops, but you don't know _why_. So you won't know if the user's machine died, or if they are still on the form. whatever mechanism you go with here, be sure to include a "manual unlock" ability.

>> Along the same lines, I would like to keep a list of active users on a LAN who are running my app and see what window/proc they have open. Is this something feasible with Nettalk or perhaps there is a template/example somewhere that shows how to do this?

I don't think there's an example of this, but what I'd probably do is hook into the ErrorClass, into SetProcedureName method. but the whole mechanism will need to be clearly thought out - there is certainly room for things to get very tricky here as well.

cheers
Bruce