How to know if a table is used by another user 2

[ledu]

Hello!
How can i do to know if a table is used by another user !
I Want To Depurate a table but I Cannot 'cause is in use .
Id thank so much your request!

 

[torturedmind]

maybe the simplest way is to USE the table EXCLUSIVELY and if it generates an error, that means another user has opened that table in another workstation. of course, you need to trap the error generated.

hope this helps. peace!

kilroy
philippines

"Illegitimis non carborundum!"

 

[craigsboyd]

Augmenting what torturedmind suggested:

?IsTableInUse("C:\mytable.dbf")

FUNCTION IsTableInUse(tcTableName)
	SET ASSERTS ON
	ASSERT PCOUNT() > 0 MESSAGE "Developer Error: Table name parameter is required!"
	ASSERT TYPE("tcTableName") = "C" MESSAGE "Developer Error: Table name parameter must be a character!"
	ASSERT !EMPTY(tcTableName) MESSAGE "Developer Error: Table name parameter is empty!"
	ASSERT FILE(tcTableName) MESSAGE "Developer Error: Table does not exist!"
	
	LOCAL lcErrorHandlerWas, llReturn
	lcErrorHandlerWas = ON("ERROR")
	ON ERROR llReturn = .T. && Could call proc that checks for ERROR() = 3 "File is in use"
	USE (tcTableName) IN 0 EXCLUSIVE
	USE IN SELECT(JUSTSTEM(tcTableName))
	ON ERROR &lcErrorHandlerWas
	RETURN llReturn
ENDFUNC

...note this only tells you if the file is in use somewhere. It doesn't tell you which user has it open or which application is currently using it. It could even be that we have it opened in some datasession. But, if none of the rest of that matters then this code will work. It could be further refined by calling a procedure when the error happens and making sure that the error() is 3 "file is in use" rather than some other error.

http://www.sweetpotatosoftware.com

 

[torturedmind]

sir craig,

i could be wrong but maybe you meant error code 1705 (file access is denied). cuz i tried opening a table exclusively that i am sure opened in another workstation but didn't get error code 3. i did get code 1705 though. anyways, nice piece of code sir.

sir ledu,

that being mentioned as above, maybe you could also try checking if error code is 1705 (file access is denied). just to be sure.

hope this helps. peace!

kilroy
philippines

"Illegitimis non carborundum!"

 

[louie728]

Why not use the USED() function?

IF USED('tablename')
?"Table is being used"
ELSE
?"table is not being used"
USE tablename in 0
ENDIF

HTH

 

[rob444]

The USED() function returns whether or not the runner of that command has a table in use or not.
It does not inform you about other users in a multi-user environment.

I solved the problem by creating a table called INUSE.DBF
fields:
table c(10)
user c(10)
when datetime

When a user opens files it gets registerred in the INUSE table.
When a user quits the entries are deleted.
A few times a day a program runs to check if tables can be openend in exclusive mode.
If so all entries of that table are removed from the INUSE table.
At the end of business all entries in INUSE are deleted.

I also have a version of INUSE that even registers whether users have a lock on a table.

That way I have the most possible info on who's using what and how.

Rob.

 

[craigsboyd]

rob,

With that particular solution, what do you do if the program crashes on the user, the power goes out, they end task, or any other kind of irregular shutdown that might result in your application terminating prematurely? I mean isn't the inuse.dbf then all messed up? What advantages do you see to this kind of set up as opposed to checking the tables with a function like the one posted above? (just in case this reads wrong, know that my questions are not facetious. I honestly want to know if there is a better way, like the one you've proposed, or you've found a good way to handle the irregular shutdown with this setup)

http://www.sweetpotatosoftware.com

 

[rob444]

Craig,

As I mentionned a few times each day a program checks to see if files are in use.
If not the INUSE table is cleaned up.
The program also checks network connections for the users in the INUSE table.
If there is no connection the INUSE table gets cleaned up for that particular user.

This solution runs foolproof for 1 year now on a network application with about 150 users.
There have been some computer crashes but those events were handled ok by the checking program.
In this case is was important for the network administrator to be able to see who was using what tables and when.

Rob.

 

[bobmpalmer]

rob, Craig
Just a suggestion but if the inuse function also applied a record lock to the particular entry when it was made, if a premature termination occurs the lock is automatically released. The Inuse clean up code can simply check for locks and cleanup all unlocked entries. We use this method on our user log so the system supervisor can carry out maintenance and view logged nodes.


Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[craigsboyd]

bobmpalmer,

Yes, that is the way I currently prefer to do it. We discussed something similar to this awhile back in:

Limiting the number of users in an application
thread184-536977

...and I posted a link there to what MS had to say on the various approaches. rob's is similar to Approach #1. Anyways, even though it's in that other thread here's the link again in case anyone's interested:

http://support.microsoft.com:80/support/kb/articles/q80/8/63.asp&NoWebContent=1]MS Ways to Limit Users[/url]

http://www.sweetpotatosoftware.com

 

[hjohnson]

I've one more option to throw into the pot. There is a dos command line utility that will list open files and allow you even close them if you wish.

http://www.merxsoft.com/Free/Free_Utilities.htm

Just offering my two bits.

Thanks
Hal

 

[medic]

Craig,

I'm confused on one of the points in that [blue]MS Ways to Limit Users[/blue] article. It mentioned about the need for access to the application's source code to use Approach 2 (Locking portions of a file). Maybe my 'batteries' are getting low, but can you help me understand why that approach needs access to the source code?

 

[craigsboyd]

medic,

An initialization procedure that opens an audit file and attempts to lock certain blocks of that file is added to the application

This is what they are talking about. They are saying that in order to implement this type of limiting you will need to be able to access the application's code in order to write the code necessary for this initialization procedure. Approach #1 can be written this way too, or as they say it can just be an external utility which wouldn't require any changes to the application's source code. Approach #3 requires no source code to be changed in the application.

http://www.sweetpotatosoftware.com

 

[MNfox]

Hi Rob,

Can you tell me how do you register opened tables to the INUSE? Do you handle it at the application level (insert a new entry to INUSE table whenever you open/close a table)? Or do you handle it at the server level (auto detect open tables, and insert entries)?

I am facing a similar issue with multi-user environment. We have about 40 processing machines and a large file server (all tables are stored there) and we have "file access is denied" every single day. I have admin right to the file server so I can check it very quick, but it's a real pain for operators since they have to go thru a lot of machines to see which has the table open.

Thanks
MN

 

[mm0000]

Another available option :

nHandle = FOPEN(cTableName,12)
if nHandle < 0
=messagebox('Table is being used by someone else')
else
=messagebox('Table is NOT being used by someone else')
endif