PACKing problem. 1

[mmerlinn]

One of my tables which I have been successfully PACKing for over 10 years is now refusing to PACK. When I run the following code, either in the COMMAND window, or in a program, I get the error message, "File already exists."

[tt]CLOSE DATA[/tt]
[tt]USE partinfo EXCLUSIVELY[/tt]
[tt]PACK <<< I get the error on this line[/tt]
[tt]USE[/tt]​

When I look in the directory where the table resides, I see a temporary file which I assume is the new packed file. It appears to me that for some reason the old file is not getting deleted so that the new file can be renamed to the original file name.

Except for the PACKing problem, this table works fine. It just keeps getting larger because of the files marked for deletion.

Can anyone tell me how to solve this PACKing problem? I have tried everything that I can think of short of writing a short program to manually pack the table. I don't have this problem with any other table.

I believe I have all of the above correct, as I am doing this from memory. I was going to post this from work where I could copy/paste it, but when I went to submit it, there were no SUBMIT nor PREVIEW buttons. Never had that problem in the past, so something has changed somewhere.

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

 

[Dan Freeman]

I've seen network permissions issues cause this.

On some networks, if the file's "owner" no longer exists you'll be prevented from modifying it. (On older Novell networks, it also wouldn't be allowed to get any bigger -- sort of the opposite of your current problem.

Or you may have just lost the permission to delete it.

 

[mmerlinn]

Hmmm, this is used on a network, so I will try using it on a different computer that may have caused the problem and see what happens.

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

 

[mmerlinn]

Even though I have run the program on both computers on the network, I get the same results every time, "File already exists." It seems that neither computer is releasing the table even though there are no problems with any other table being used at the same time.

So, how do I get the table released? I needed to do a global REPLACE to a field in the table, and even that does not work.

And, more importantly, once I get the table working again, how do I prevent the same problem in the future?

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

 

[Olaf Doschke]

Did you remove the temporary file? Further PACKS saying "file already exists" surely point on that file PACK normally only creates temporary.

You know you can do your own PACK: USE original, COPY TO pack.dbf WITH CDX [DATABASE xyz], ZAP in original, Select original, APPEND FROM pack.dbf

That works a little different than what PACK does, because it recycles the original table. PACK deletes the original table and renames its copy.

Bye, Olaf.

 

[Olaf Doschke]

...and don't forget afterwards to DROP TABLE pack if the DATABASE clause was used and ERASE pack.* otherwise, or you have the same problem again.

Bye, Olaf.

 

[Dan Freeman]

So, how do I get the table released? I needed to do a global REPLACE to a field in the table, and even that does not work.

Can you explain "does not work"? Does it simply do nothing? Does it give an error message? Do flames shoot out your speakers?

This is sounding more and more like a filesystem access issue so far, but without more information it's hard to be sure.

 

[mmerlinn]

Did you remove the temporary file? Further PACKS saying "file already exists" surely point on that file PACK normally only creates temporary.

The result was the same whether I removed the temporary tables or let them pile up and removed them a week or so later.

You know you can do your own PACK: USE original, COPY TO pack.dbf WITH CDX [DATABASE xyz], ZAP in original, Select original, APPEND FROM pack.dbf

I did this:

[tt]USE original.dbf[/tt]
[tt]COPY TO pack.dbf WITH CDX[/tt]
[tt]ZAP original.dbf[/tt]
[tt]SELECT original.dbf[/tt]
[tt]APPEND FROM pack.dbf[/tt]

Did a search of my hard drive to find the location of pack.dbf (was 4 levels down in my FoxPro directory).

[tt]ERASE pack.dbf[/tt]​

This did NOT solve my problem. Yes, this did PACK the original DBF just fine, but it did not release the FILE-IN-USE flag in the DBF. Therefore, I still had a file that could not be PACKed with the PACK command.

After a week I tried this:

Made copies of the original DBF, FPT, and CDX files.

[tt]USE original.dbf[/tt]
[tt]COPY TO pack.dbf WITH CDX[/tt]
[tt]ZAP original.dbf[/tt]
[tt]ERASE original.dbf[/tt]

Did a search of my hard drive to find the location of pack.dbf (was in the top level in my FoxPro directory).

Renamed pack.dbf, pack.fpt, & pack.cdx to original.dbf, etc

Moved them to my DBF directory where the original files resided before I erased them.​

This DID solve my problem. Now I can PACK that DBF with the PACK command just as I could before this problem surfaced.

After a week of no problems, I went back and ERASEd the copies.

One thing I did notice. The COPY TO command wrote the pack.dbf to one location the first time and to a different location the second time, even though I did not specify any location either time.

I still have one issue. Over the last few weeks I made numerous attempts to solve this problem resulting in numerous files that could not be deleted because they were "IN USE" according to the flag. When everything was working correctly again, I tried erasing those FoxPro files with the ERASE command. I was able to erase all but two of them. I still have two FP files that I CANNOT ERASE with the ERASE command, and which I have been unable to delete any other way, so they currently reside in my "CannotBeDeleted" directory.

This is sounding more and more like a filesystem access issue so far, but without more information it's hard to be sure.

It probably is some sort of access issue that caused the problem in the first place, although I have no clue how to figure that out.

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

 

[Olaf Doschke]

You didn't the intention of this code. It does what PACK does, it is meant to be used instead of PACK, not as preparation for PACK.

You stress the fact you can't PACK the original.DBF but this is not needed, because what does PACK do?

PACK creates the original table without deleted data. It also reorganizes CDX and FPT in doing this, because it does really create a new table and then deletes the original and renames the copy.

My code replacement is having the same result but doing it in a way OS restrictions on generating files can be avoided.

The essential thing to do is CD or SET DEFAULT to a directory, in which you can write before doing COPY TO. And if you do that you also don't have to search for the file.
Without CD or SET DEFAULT the directory in which COPY TO writes is simply the current directory. ERASE also works in the current directory, so unless you can't write into the current directory you can make use of COPY TO and ERASE and be sure you erase the right file in whatever directory is current.

The only reason you would still want and need to pack the original.dbf after doing what I suggested is you did so with SET DELETED OFF. Because then all my code would just copy data forth and back without any gain from that. You want to get rid of deleted data, so you do that while SET DELETED is ON. That's all there is to it.

Bye, Olaf.

 

[mmerlinn]

You didn't the intention of this code. It does what PACK does, it is meant to be used instead of PACK, not as preparation for PACK.

Yes, Olaf, I am aware that your code is intended to replace the PACK command. And it works well doing that, albeit noticeably slower.

As it turned out my issue was not with the PACK command, which is easily circumvented with your code, but with the fact that the DBF had a FILE-IN-USE flag set even when the file was not in use.

Your suggestion allowed me to pack the table, but did not release the flag, which is the solution that really I needed, even though I did not know that when I first posted.

As it turned out, all that I needed was an alternative way to pack the table, which you so ably showed, then a way to release the flag, which ERASE does. I did not know that until I did some research after you mentioned the ERASE command, a command that I have never used before.

All in all, you pointed me in the direction I needed to go.

There are two reasons for needing the flag released. 1) I make an onsite and an offsite copy of my data every day. The previous onsite copy is deleted every day before a new copy is made. The file-in-use flag prevented me from deleting that copy. 2) I programmatically pack that DBF about once per month, and that flag was preventing that when I was using the PACK command.

Your solution solved #2 above, but did not solve #1.

And, duh, I should have realized that the current directory, whatever it was, would be the working directory.


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

 

[Olaf Doschke]

OK. It's also easy to see the reason my solution takes about double time: It copies the data twice, once to pack.dbf and then back to the original file.
The way you do it you copy once, then pack (unnecessary) and rename. Especially, if you don't pack this will only copy data once and of course that's faster.

I do propose to replace PACK wherever it doesn't work out with copying data with COPY TO, ZAP original and APPEND copied data anyway, because you keep the original file with all it's original permissions/security, secondary file streams, ACLs etc. applied to it. Unfortunately, as you say, you also keep the file-in-use flag, of which I hear for the first time. Typically we are talking about file handles and there only is one, if you can ZAP the file - your own file handle, ZAP like PACK only works with exclusive access. You just need to USE (close) the file after packing it the one way or the other and it's free of any file handles.

In your case the new file is free of handles, yes, because it was never used exclusive, you erase the original and move the copy into it's place.

Bye, Olaf.