How to know if a table is already open 1

[dsandlin]

I've been using the following code for years to avoid the "File is in use" error when trying to open a table:

IF USED('mytable')
SELECT mytable
ELSE
SELECT 0
USE mytable
ENDIF

Recently, I have hit upon a problem where the table is open with an alias other than the table name. Since the USED function is testing the alias, it fails to detect that the table is open with the above code.

Does anyone know of a way to test if a table is open with the table name, rather than its alias? I am on the brink of writing a function that would LIST STATUS to a file and then parse the file, looking for the table name, but I thought that I would ask first before going to all that trouble.

Thanks, -- Dick

 

[csr]

The only way I know of to determine that is to actually attempt to open the file exclusively. If you can, it is not open ... otherwise it is.


Don

 

[mejiaks]

maybe you can try LLF something like this

local lnFH
lnFH = FOPEN("c:\yourpath\yourTable.DBF",0)
if lnFH < 0 then
*:already used
select yourTable
else
fclose(lnFH)
select 0
use c:\yourpath\yourTable
endif

 

[StewartUK]

Dick,

mejiaks is a good idea and would be fast as low level functions always are.

In a database situation though, the actual dbfs could be in a number of folders which might make using the FOPEN a little tricky. An alternative is to use the AUSED function which creates an array of aliases.

You could then run through this array, and use the dbf() function which would tell you which actual dbfs were in use.

Hope that helps,

Stewart

 

[darrellblackhawk]

Here's a function that works.

Failing on a fopen() will tell you that the
table is locked and open somewhere, but not
necessarily if it’s in the current datasession.

It could be open shared. So the fopen()
test for a lock may not work unless you
provide it with a write or read/write argument.

Also, the example I've posted shows that the aused()
function uncovers a useful detail about opening tables.

It appears the table open section of VFP's engine,
implements it as a stack(LIFO). This could be very handy
in algorithms.

Darrell

[tt]
SUSP
CLOSE DATA ALL

USE &quot;c:\dev\clients\abs\vfp\abs main app\data\general\keygen.dbf&quot; ALIAS zTable IN 3000 SHARED

USE &quot;c:\dev\clients\abs\vfp\abs main app\data\general\paymethods.dbf&quot; IN 0 SHARED

LOCAL nWkArea
? IsTblOpen(&quot;keygen&quot;,@nWkArea)

FUNCTION IsTblOpen(cChkTbl,nWkArea)
IF pcount() < 1 .OR. VARTYPE(cChkTbl)<>&quot;C&quot; .OR. EMPTY(cChkTbl)
RETURN .F.
ENDIF
cChkTbl = UPPER(cChkTbl)
LOCAL bTblOpen, nOpnTbls, i
LOCAL ARRAY aOpnTbls[1]
bTblOpen = .F.
nOpnTbls = AUSED(aOpnTbls)
FOR i = 1 TO nOpnTbls
bTblOpen = JUSTSTEM(DBF(aOpnTbls[i,1])) == UPPER(cChkTbl)
IF bTblOpen
nWkArea = aOpnTbls[i,2]
EXIT
ENDIF
NEXT
RETURN bTblOpen
ENDFUNC
[/tt]

 

[KeithTrangmar]

If you're not particularly concerned whether the table is already open one way or another, then you could try the following approach instead:

IF USED('mytable')
SELECT mytable
ELSE
USE mytable IN 0 AGAIN ALIAS mytable
ENDIF

In fact, if you're not concerned about potentially closing & re-opening the file occasionally, then you can be even more succinct:

USE mytable IN select('mytable') AGAIN ALIAS mytable

Keith

 

[dsandlin]

Thanks much for all the great replies.

I think that I like DarrellBlackHawk's function the best, because I am concerned about having the table open in too many places. I needed a good general-purpose routine and that seems to be it. Also, once you know the work-area for the table, it is easy to use the ALIAS() function to find out what else it is being called.

Keith also had a good idea that is probably faster to execute, but I'm a little worried about opening so many work-areas inside of a big program.

Thanks again for your help!! -- Dick

 

[csr]

This would be my choice...

USE mytable IN select('mytable') AGAIN ALIAS mytable

because it does not expand the number of workareas being used.

If the table is already open, it closes it and opens it again in the SAME WORKAREA.

As he stated, the only downside is possible thrashing of the disk by opening and closing files unnecessarily.






Don

 

[dbMark]

DBF() returns the table name:

USE C:\test\mytable ALIAS mine IN 0

SET FULLPATH ON
? DBF(&quot;mine&quot && C:\TEST\MYTABLE.DBF
SET FULLPATH OFF
? DBF(&quot;mine&quot && C:\MYTABLE.DBF

Of course, in the above example you know the alias name, but you don't need to know the work area.

You could do a fast loop, but beware if you have 2 identically named tables open from different drives or directories:

FUNCTION isinuse
PARAMETER cDbfName
PRIVATE lUsed
lUsed=.F.
FOR x = 1 TO 225
   IF "\"+UPPER(cDbfName)+"." $ DBF(x)
      lUsed=.T.
      EXIT
   ENDIF
NEXT
RETURN lUsed

dbMark

 

[darrellblackhawk]

dsandlin:

Thanks for the star! I noticed that I left in an
unnecessary upper() function:

[tt]
FOR i = 1 TO nOpnTbls
bTblOpen = JUSTSTEM(DBF(aOpnTbls[i,1])) == UPPER(cChkTbl)
IF bTblOpen
...[/tt]

I set cChkTbl to upper earlier in the function to avoid
having to call it for every table. I just forgot to take
out this one.


Darrell

 

[clarkrj]

Why not set up an simple error handler.

on error do test

llContinue = .t.

use mytable in 0

on error

if !llContinue
?'failed'
return
endif



Proc test
llContinue = .f.
return


Not very elegant but it should work

 

[darrellblackhawk]

clarkrj:

Yes it does work, but it won't tell you if it's just
open by some other app exclusively or if the current
instance has it open in its' own data session.

Plus, if you are going to be performing a fair amount of
checking, depending on an error handler comes with a
performance penalty. I'm not sure how VFP implements error
handler calls, but I'm pretty sure it uses &quot;far calls&quot;;
which by their nature are very processor/resource intensive.

My reason for being insistent on not using side effects
of an operation to determine a fact about something, is
that when you create “muli-instance” apps, they need to
know as much about their operating environment as possible.

Darrell