How to prompt a " File in Use" message

[#1]

Hi,

I'm currently creating a JMP file which acts as a database. Therefore I would like to prompt a "File in use" message and keep the file locked for editing when someone else already opened the database and is making changes to the file. For example, the same message you get when you open an Excel file on a server location that is already in use by someone else:

I think I already found a part of the answer in the following Community post: Multi-User Access 

If I'm correct I would still need to figure out how to lock the table if it is opened by another user.

Could someone please help with creating such a script?

Thanks

[#2]

A JMP table is locked for writing by the first user that opens it. Subsequent users get read-only access (they can't save their changes). The script @David_Burnham wrote assumes the user with the lock will close the table fairly soon (200 tries) and then opens the table for the new user, which should get the lock for the new user. It might work pretty well for 2 or 3 users that use a carefully written program to update the table. It is not for a user that might open the table then go to lunch without closing it.

[#3]

Thanks for the respons Craige.

Would it be possible to prompt a message/warning that the file is already in use by someone else? Currently, the second user could be unaware that the file is already opened by another user. So if the second user makes any changes to the file, these changes cannot be saved to the orginal file.

[#4 · Accepted solution]

David's script has the code that checks for the visible indication:

Several copies of JMP open, same table in two copies, one of them is locked out by the other.

[#5]

Hmm. I can see it works in JMP 14, but maybe not 15.

[#6]

Ok, thanks for looking into this Craig.

I'll try setting up the script from David in my JMP file.

[#7]

It will probably work in JMP 14. I reported the JMP 15/16 issue I was seeing. 

If you must make it work in 15, you can do something like this function I used in the FileSnapper add-in.

	FileSnapper:isFileOpenElsewhere = Function( {filepath}, {dt, savelog},
		If( endswith(filepath,".jmp") & File Exists( filepath ),
			dt = Open( filepath, private );		
			savelog = Log Capture( dt << save );
			close(dt,"nosave");
			Length( savelog ) != 0; // error message means another JMP has dt open
		, // else
			0 // not open if not exists	
		)
	);

It works by trying to save the file as soon as it is opened and catching the log message. If there is no log message, the save worked. You'd want to rearrange that a bit, probably not closing the table if the save was successful, and not using private. The downside is if the file is very big, the extra save might take some time, and it doubles the chance of having a power failure during a save, corrupting the data. (For FileSnapper, the file is a sentinel, small, about to be deleted, and re-created each time, so it works OK.)

[#8]

In testing I'm finding a scenario where I can open the table with no message in savelog, but when I come to save I get a an error "Unable to get End of File marker".  Not only does this prevent the latest data from saving, but the entire file disappears, and all data is lost.

In the test environment I have 2 scripts, identical except one script updates the 1st column of the table and the 2nd script updates the 2nd column.  Each script attempts the update the table 100 times.  If the open reports (via savelog) that the table is locked then it waits and re-attempts (along the lines of my original script). However, maybe once or twice   in the 100 attempts there will be an end of file marker error.  This might occur when opening the file (with the savelog test) or when saving the results (closing and saving on the assumption that there is write access).  I've implemented a similar savelog test on close, but by then it is too late, the damage is done.

My experience of this error is that if you get it when trying to save results you're in big trouble and risk losing the table entirely.  So I try and focus on eliminating the error occurring in the first instance by trying to reliably gain read-write access when opening the table.  99% of the time I achieve this, but I can always get the error eventually.  I suspect what happens is that there is a slim chance that both scripts get read-write access to the file (atleast, as reported by savelog) but at some point one script keeps read-write access and the other script gets an end of file marker error - probably when the other script has made an update).

[#9]

Can you post the test? And is the file a network file or a local file? I'm not sure what JMP or the OS might do differently, but at least sometimes I think JMP saves to an alternate file, then deletes/renames/swaps to make the window for a power loss failure as small as possible. It could be that process is getting in the way. @briancorcoran 

[#10]

The problems are worse with a network drive (or in my instance with a file on OneDrive).  But my testing has been with a local file.  Windows 10,, JMP 17.

The scripts “locktest1” and “locktest2” are identical except that the first script updates column 1 of the results table whereas the second script updates the column 2.

By running each script simultaneously in separate JMP instances it is possible to stress-test the logic for handling file-locks.

Line 75 of the scripts contains the path for the results table.  Place the results table in a suitable location and update the path variable to point to this location.

Open a JMP session and open locktest1.  Show the embedded log.

Open a second JMP session and open locktest2, with an embedded log.

Run the two scripts (I run the first, wait until the log shows i=5 and then run the second, but that is just superstition).

The loop defined on line 79 performs 50 iterations.  If the two scripts finish successfully then the outcome should be that the results table contains numbers 1 to 50 in each of the two columns.

There is a table script that will allow you to reset the table contents.

In practice, there are 3 possible failure modes:

1. One or more “unable to get end of file marker” messages are displayed.  This error occurs when saving, and it is not possible to save the results.  Therefore there will be some missing values in the results table. 
   
   
2. At some point, the results table will vanish.  If the file cannot be found the scripts abort execution with a “file not found” message.
   
   
3. Occasionally, both scripts fail to get a lock on the table.  This is indicated by both scripts making 500 attempts to get a lock.  If you abort the scripts, and then try opening the results table, it will have a file_lock table variable.  Both instances of JMP will have the table locked.  The only way to unlock it is to close both sessions of JMP.

50 iterations is usually sufficient to observe (1) and (2) but occasionally you need to increase the number of iterations to see (2).  Scenario (3) is more likely with 100s of iterations and with the wait on line 100 removed.