Check to see if table is open?

[buddyrich2]

Surely there must be a thread here somewhere on this, but I have racked my brain for a couple of hours looking so I will just ask:

I'm getting errors related to "file is opened by another user" and the program crashes. I need to find out if the table is open before I even try to do the routine.

I have tried this but it just hangs up indefinately:

DO WHILE .T.

lnOpen = FOPEN([d:\directory\filename.dbf],2)
IF lnOpen = -1
FCLOSE(lnOpen)
ELSE
FCLOSE(lnOpen)
EXIT
ENDIF
ENDDO

Does anyone have any other suggestions? Thanks.

 

[markros]

If you're looking for DBF, the simplest way is to try to trap the error in use command, e.g.

VFP8 and up

TRY
   use myTable in 0 alias myAlias Shared && if you need shared access
catch to loError
   -- We can not open the table - analyze the error
endtry

 

[JRB-Bldr]

Alternatively you can check to see if an Alias is in USE.

Admittedly this will not, by itself, tell you which table is associated with the particular Alias, but it is a start.

Plus if you have not specifically assigned an alternative Alias name to your table, the alias will default to the table name.

IF !USED('MyTable')
   * --- Alias Not In USE ---
   USE MyTable IN 0  && Alias will default to table name
ENDIF

* --------- OR -------------
USE MyTable IN 0 ALIAS Table1
<do whatever>
IF USED('Table1')
   * --- Alias Table1 Is In USE ---
   SELECT Table1
   USE
ENDIF

Finally you could use a combination of commands including DBF() to find if a table was in USE.

FOR WorkSpace = 1 TO 200 && Or some smaller #
   SELECT (Workspace)
   cDBF = DBF()
   IF !EMPTY(cDBF)
     * --- Test DBF File Name ---
     IF JUSTSTEM(JUSTFNAME(cDBF)) = "MyTable"
         * --- Table Is In USE in Workspace ---
         cAlias = ALIAS(Workspace)
     ENDIF
   ENDIF
ENDFOR

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

JRB-Bldr,

while it's a good idea to check USED(alias) to prevent ERROR 3 "File is in use", what buddyrich talks about is ERROR 108 "File is in use by another user".

Error 108 cannot be prevented or detected before you use a table, used only checks, if the current process, you yourself already use the table. Error 108 happens, if a table has a header lock by another user, which can happen in by any command writing to a table or by a transaction.

Things to do, to make this error less probable is to make all write operations to tables as short as possible.

Even if you put a USE within a TRY CATCH BLOCK, you can later get an 108 error when writing to that table. I'm not saying TRY CATCH is wrong with this, it's the only valid way to approach error 108 when opening a table, as with exclusive use of a table the only way to detect it is to try to open a table and see if you succeed.

So the same rule also applies to an exclusively open table, which will result in ERROR 1705 "file access is denied".

Error 108 is not handled automatic by USE, it is however handled automatic by INSERT, UPDATE, DELETE, REPLACE, APPEND and such depending on how SET REPROCESS is set. Error 108 can happen with any of these commands too, if Reprocess is set to some timeout or finite number of retries.

The USE of a table is not automatically retried, as it does not lock the table itself, but it is of course affected by a lock.

What you can do when error 108 occurs therefore is setting your app to Sleep for a minimum time of say 0.5 seconds and a random additional time between 0 and 2 seconds. And then do a RETRY. This will take care of deadlock situations, when two clients want the same file. Because both wait a different time, sooner or later you have success. And if the reason is a lengthy update or transaction from another client this is solved too. You may give up retries after this special branch of the errorhandler is hit often in a row.

This is all part of programming for multiple users with shared data access. Unfortunately, when you write to a file, even if you don't lock, VFP has to lock the file at least for the short time, otherwise this would lead to header corruptions or even worse, overwritten or broken records in the table body.

This is the downside of a database, that is no real server, every client has to take the precautions and conflict handling normally only a server has. A server has the advanatage, that it can queue requests on the same data and therefore prevent such problems.

Even shorter said: Read the chapter "Programming For Shared Access" -

http://msdn.microsoft.com/en-us/library/h6hhascz.aspx

And maybe even begin with the Topic "Locking data" (

http://msdn.microsoft.com/en-us/library/239f19et.aspx)

and read the parts of it explaining automatic locking. There you have a very good overview of what really causes locks and which locks. and get a feeling about what could cause which problems/conflicts and how to handle this.

Bye, Olaf.

 

[Olaf Doschke]

while I was talking about a general solution to error 108 by letting your app sleep and RETRY, I forgot to tell you need to do this in a general error handler with ON ERROR. This is about the code of the errorhandler needed for that case of error 108:

procedure errorhandler()
 Lparameters ....your choice! There are many things you could pass here, see ON ERROR in VFP help, let tnError be among the parameters

Do Case
   Case tnError = 108
       Declare Integer Sleep in WIN32API Integer milliseconds
       Wait Window Nowait "waiting for file access..."
       Sleep(500+rand()*2000)
       Retry
   Case tnError = ...
   otherwise
Endcase

This is just a simple version of it. It does not include giving up retries after a series of errors 108 happened. It compares to SET REPROCESS AUTOMATIC with a varying retry interval instead of a constant one REPROCESS uses set by SYS(3051). Also see SYS(3052), but I don't recommend using it.

Rather SET REPROCESS TO a small number of retries and use this error handling to have less lock contention.

Bye, Olaf.

 

[buddyrich2]

Olaf, I think this "sleep" process will work, but I'm a little confused about where my code fits into this. For instance, if the problem is "use c:\filename...", does that go in between the case and endcase? ... or do you just start this routine at the very beginning and let it run throughout?

 

[Olaf Doschke]

Helly buddyrich,

this code is the errohandler, any other code does not go into this and this code does not get into other code. The connection to any USE or other command potentially causing an ERROR 108 is ON ERROR. ON ERROR is a command setting what is executed when an error occurs at any place in any code.

You set ON ERROR DO errrorhandler(parameters go here)
to call my error handler.

See the topic on ON ERROR and you get an example you then can extend by my errorhandler version.

Bye, Olaf.