Open Table

[alisaif]

Hi

What is the best way to open a table whether it is open or not before. I am using the following syntax

If ! Used('RawPtm') && Here an alert occurs "File is in use"
Use rawPtm In 0 Shared && here I ignore it
Endif
Select rawPtm && Here it is saying "Alia RawPtm is not found"
..
..

If if the file is already in use why it is not selecting

Please help!

Saif

 

[Olaf Doschke]

Used() will never cause that error, it simply searches a workarea with the given alias name. But file name and alias can differ.
So while no table or cursor has the name RawPtm used, the RawPtm.dbf might be opened with another alias name already.
If the file is used exclusive by another user another error is triggered by use.

That said there is no simple way to avoid that error without being very stringent in using files with corresponding alias name.
If you are, that code is ok. I used it this way, too, when still working with DBFs, now it's mainly SQL Server and the only thing to avoid is double workarea names.

Anyway, the reason for the error is clear, RawPtm.dbf is in use in that datasession already with another alias name.
It doesn't make sense to create a routine checking that, though you could iterate the array created by AUSED and see whether DBF() of some alias name is the file you want to use. You'll very surely find it with another alias name. But then it's hard to decide automatically what can be done without harm, either using that other alias, that would need code working on any alias name, or closing that alias to be able to open the table with its normal alias name, that might harm any code needing the altered alias name.

Code using tables with different alias name is referential integrity code, for example. If it fails and you suppress an error, the tables might remain open with other alias name and that would cause the error.
Only if you use DBC and have define referential integrity.

More likely is, you have two forms in the same data session, but this would still only lead to that error, if you somwhere used the RawPtm.dbf with another alias.

Bye, Olaf.

 

[Olaf Doschke]

To be clear: The help say Error 3 (File is in use) only occurs with USE, DELETE (more precise delete file) or RENAME. I could imagine something related, but USED() will not cause the error.

If the error is reproducible, single step through the code before the IF !USED line and then execute it. You'll see it branches into the if branch and tries to USE rawPTM and that errors.
If you repeat and stop executing before USE, look into the datasession and determine, which files are open with DBF() on all alias names. You won't find the alias name RAWPTM, as USED also did't find it, but any other alias is the alias for that DBF and causes the error in trying to USE the DBF. Since it can't be used the alias RAWPTM still isn't used and SELECT also fails, yes.

Bye, Olaf.

 

[Olaf Doschke]

It's me - once more

Well, there is a simple solution in your case: USE rawPtm In 0 AGAIN.

Since the file is used, but the alias is not, you can use the alias and AGAIN allows to use the file again. So it's not strictly forbidden to have a file open more than once, but VFP reacts with ERROR 3, as you most probably won't want to use a file again. It could point to a misuse (literally), as you normally don't need a file open twice in the same data session and if, you can explicitly specify with the AGAIN clause. So that is also the solution, but take it with caution. You should rather find out why the file is open with another alias name and then decide, whether that is ok or points to an error elsewhere.

Bye, Olaf.

 

[alisaif]

Thanks for the reply!

So, adding the word "AGAIN" will simply solve my problem?

If I want to close the table ..

if used("RawPtm")
use in RawPtm
endif

Saif

 

[Olaf Doschke]

Yes, adding the AGAIN, but you should look out, why the table file is already open with another alias.

To close is simpler:

USE IN SELECT("RawPtm")

If it isn't open SELECT("RawPtm") is 0 and USE IN 0 does nothing.

Bye, Olaf.

 

[Olaf Doschke]

Once more (its me).

I would really look out for where it happens you open a table with a different alias. It might even be a SELECT INTO CURSOR cursorname without NOFILTER, which in optimizable cases rather makes VFP USE the table you sql select from and filter it with your Where condition. Then it's a filtered cursor and the DBF is open with the cursor name, in most cases different than the file name.

In the worst case you open tableX with alias of tableY and then when you want to work an tableY and see the alias is already used and you use it, you actually work on tableX. So the problem with a table open with another alias may arise, and if it arises, it arises elsewhere. And those are hard problems, hard to debug. It's obvious you don't know why that happened and you didn't do it on purpose, so yes, AGAIN solves your "file is in use" problem here at this part of your app or form, but might cause another problem down the drain elsewhere. Not even a problem showing up as another file or alias is in use error message, but you have a table open under a "wrong" alias and might be working on another table as wanted, if just looking out for that alias.

In that matter, if you're strict and want a certain table with a certain alias and no other alias, you'll need to dig deeper.

Bye, Olaf.

 

[mmerlinn]

As Olaf indicated, the reason your SELECT cannot find the file that is already open is because the file is open with a name or alias different than what you have in your SELECT statement.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[alisaif]

Is there any possibility to check the areas where the files are open.

Saif

 

[vgulielmus]

Yes, in fact it's easy.

CREATE CURSOR cc (ii I)
CREATE CURSOR dd (ii I)
SELECT * FROM cc

FOR lni = 1 TO SELECT(1)
	IF !EMPTY(ALIAS(lni))
		?lni,ALIAS(lni)
	ENDIF
NEXT

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

No, that will show alias names, not file names

As I said DBF("aliasname") will give the filename.

For lnI = 1 TO AUSED(laUsed)
   lcFile = Dbf(laUSed[lnI,1])
   If JustExt(lcFile) == "DBF"
      ? laUSed[lnI,1]," is file ",lcFile
   Endif
EndFor

You may also use AUSED this way:

? AUSED(laUsed,.null.,"C:\YOURDATA\SOMETABLE.DBF")

It's not explicitly said in the help on the third parameter, the help only talks of DBNames, simple or full table name is the only mentioning may meaning the file name. Anyway, I simply tried and it works with the file name. Then you'll have just the rows about that file in laUsed and can see which alias name or names are given to it in the first column of the array.

Bye, Olaf.

 

[vgulielmus]

No, that will show alias names, not file names

Correct, I forgot that.

FOR lni = 1 TO SELECT(1)
	IF !EMPTY(ALIAS(lni))
		?lni,ALIAS(lni),DBF(ALIAS(lni))
	ENDIF
NEXT

Your solution can also be changed to show the work areas

FOR lni = 1 TO AUSED(laTables,1)
	? laTables[lni,2],laTables[lni,1],DBF(laTables[lni,1])
NEXT

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[craigber]

There's a gotcha to all this.....

USED only tells you if YOU have the table open. If someone else has the table open EXCLUSIVE, you'll get an error when you try to use it. The ONLY way to test for this is to USE table then capture the error.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

Yes, craig, in case you want to use a table exclusive, that is a catch.

The error here is not because of exclusive use of a file, simply because of double use with different alias names as in

*somewhere beforehand
USE labels.dbf ALIAS arbry SHARED

* code failing because of file used:
If !Used("labels")
   USE labels IN 0 SHARED
Endif

This results in ERROR 3, not ERROR 1705.

By the way, Saif, AUSED() with the file name parameter is not a solution or replacement for USED with alias, because the code following the use in most cases will depend on that alias and if you find out the table you want to open with that alias is already open with another alias, you still are not able to execute the following code as you now need the wrong alias closed and the table opened with the wanted alias. And that will make your directly following code work, but most probably cause trouble where the other alias name is needed.

And if you solve it by

*somewhere beforehand
USE labels.dbf ALIAS arbry SHARED

* code failing because of file used:
If !Used("labels")
   USE labels IN 0 SHARED [b]AGAIN[/b]
Endif

You have the table open twice and may have other unwanted side effects of buffers, etc.

In the end you have to find out why the non default alias is used, where it is used for what reason. There is no one size fits all fix for that with the AGAIN clause and I hope your question about finding the alias causing the USE to fail is all about debugging, not about finding the ultimate simple solution to simply use some table, only when and if needed.

It all gets easier, when working with private datasessions, as that separates the aliases used in a current form and elsewhere in your application.

Bye, Olaf.

 

[alisaif]

Thanks

Saif

 

[alisaif]

By Running Vilhelm-Ion Praisach's code

FOR lni = 1 TO AUSED(laTables,1)
? laTables[lni,2],laTables[lni,1],DBF(laTables[lni,1])
NEXT

I found that lots of cursors/tables are open at least 40.
( All these cursors are created using Sql-Select and create cursors )

At the time of saving records, I need two tables to be opened rest should be closed.

1. Master (Rawpim.dbf)
2. Transaction (Rawpi.dbf)

Now the question is how can I close all those files and after I want to open the aforementioned tables.

Please guide..

Saif

 

[Olaf Doschke]

If you're sure CLOSE TABLES ALL will close all. You may lose any controlsources of your form, though.
Why bother about all 40 aliases? Only the ones with differing alias for the two tables hinder you to open them with their default alias without AGAIN.

Bye, Olaf.

 

[alisaif]

Thanks for the quick reply!

What is the best place to close the tables or it close automatically when leaving form.

These tables might be opened for others forms or menu (using table).

Saif

 

[Olaf Doschke]

As already said: Using a private datasession. Then your form will close that datasession and you won't need any cleanup code, unless you want to flush/close tables earlier than with form release.

If you work in the default datasession with any form, you can't even simply close the tables you open at init. You always will have to see, whether the tables were already open beforehand and also would need to knwo if no other form started after your form is needing a table. So this is leading nowhere at all. You can't say which tables are in use somewhere as controlsource or any other way.

So rather don't clean up at all.

You may start going in the direction of private datasessions by prefixing all alias names you use in a form with a form short name, eg in your customer.scx use customer alias custcustomer. in your order form use customer alias ordercustomer, etc, so your alias names don't overalap unless you explicitly want and need to work on the same table with same alias in two forms.

Bye, Olaf.

 

[alisaif]

Thanks for the nice explanations!
But sorry to say that I am using default data session. And,in my project it is around 150+ forms.
Please guide what to do? If I open each form and change the data session with 2 (Private). Will it cost me a lot?

Saif