Checking for Table usage

[ryupb]

Hi everyone, another question here.... though i think this is simple enough, how can I check if a table is in use by another user???

 

[torturedmind]

one ssolution is to use the table exclusively and if that returns an error then it is in use by another user. something like this:

USE MyTable EXCLUSIVE IN 0 ALIAS DBExcl
IF ERROR() = 1705     [COLOR=green]&& file access is denied[/color]
     ?MESSAGE()
     ?"Table is in use by another user."
ELSE
     ?"Table is NOT in use by another user."
ENDIF

hope this helps. peace!

kilroy
philippines

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get one million miles to the gallon, and explode once a year, killing everyone inside."

 

[alvechurchdata]

All I'd add is that you should check whether some other part of your own app has the table open because that will stop you reopening it exclusive in another area.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[rgbean]

You could also wrap torturedmind's code in VFP 8.0+'s TRY ... ENDTRY block. Or use something like this in earlier versions:

FUNCTION DBFUsed
LPARAMETERS tcDBF
LOCAL lcONError, llError

lcONError = ON("ERROR")
ON ERROR llError=.T.

LOCAL llError = .F.
USE (tcDBF) IN 0 EXCLUSIVE
USE IN SELECT(tcDBF)

ON ERROR &lcONError
RETURN llError
ENDFUNC
[code]
Rick

 

[Olaf Doschke]

Another solution would be to try and FLOCK() the table. There is no need for errorhandling this way, as FLOCK just returns .F., if it can't lock the table, because another user has that table open. But if you then want to get exclusive access, you need to unlock the table and reopen it exclusively, which may be the moment, another one accesses that table.

As already said you could also check if you've got the table open YOURSELF.

Mostly IF USED("myTable") should be sufficient, but it depends if you have several datasessions and if you use tables with alias names different from the real table name. That could make it very hard to detect if you already got that table open yourself. You then would need ASESSION(), AUSED() and scan through all sessions and aliases and find out the file name with DBF(cAlias), which should be the table name. But you may also define a table with name 'myName' that is stored in a file 'otherName.dbf'!

If you need exclusive access to a table for PACK or REINDEX, you should do that in a nightly maintanance task, not in your application. Then modify Ricks code and delete the line USE IN SELECT(tcDBF) to not close the table you got exclusive. Then call DBFUSED() in a loop until it returns .F., put WAIT inside the loop, to keep network loads from trying to open a file exclusively low and just try once every minute or so. Exit the loop if DBFUSED returns .F. and do whatever you want to...

Bye, Olaf.