Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
Using WinZip from FoxPro
We've had a FoxPro database using WinZip without problem for many years but it's recently started to misbehave. The application was originally written in Visual FoxPro 3 and we upgraded it to VFP 9 when we took over the maintenance about five years ago. We've never changed the unzip routine but when the users installed new Windows 7 PCs earlier this year they started to get intermittent failures.
The routine is nothing special, all that it does is extract a zipped table from a branch office and append the data to the head office files. We know we ought to be sharing the head office database with the branch but the import only runs once every month or so and there's no budget for a rewrite. WinZip has been doing the import nicely for a a long time but we've now had a couple of months of intermittent failures.
What happens is that the user will start the import and will then get an Error 1705 - "File access is denied". If they try again then the import might succeed or it might fail again. Eventually it will work. The zip file is on the user's desktop and files are being extracted to the user's local temp folder so there can't be any conflict with other users. We came to the conclusion that there must be some sort of timing problem.
Calling WinZip from FoxPro
The existing code is simple. First it calls WinZip:
Then it goes into a loop waiting for a file to appear:
The line calling WinZip is very old code. It uses ! to call the old DOS command processor and uses square brackets as delimiters around the text of the command string. It's also very wasteful in that the program continues to execute whilst WinZip is running. This turns out to be part of the problem.
The fault was intermittent which eventually suggested that it might be a timing problem. We modified the loop and added a simple test using ADIR() to read the contents of the destination folder whilst the archive was being extracted. We didn't have the user's data so used a large archive from our own data as a test. The first run showed us a possible problem:
|08:48:21.85||0||FoxPro was running fast enough to be reading the directory every few milliseconds. Only the rows where the data has changed are shown.|
WinZip seems to be creating an empty file and then extracting the data in chunks. After the first step the size changes by about 20 Mb every quarter of a second. With our big test file it's inevitable that FoxPro would detect the partially empty file whilst it's still being extracted and would try to USE it. The user's files are smaller than our test so there's a chance that the extraction would complete before Fox detected the file. Once in a while though there's a chance that Fox would try to open a file before it was fully extracted and this seems a plausible cause of their intermittent failures.
Fixing the problemWe made two changes to the loop.
First we added an API call to put Fox to sleep for a second in the middle of the loop. This meant that we'd only be checking the folder once a second rather than several hundred times a second and there'd be less chance of us finding a partial file. It would also free some resources to let WinZip run more quickly and this too would reduce the chance of us seeing bad files.
The second change was to check the size of the file before leaving the loop. If we see the same non-zero size on two successive scans then we'll know that the size hasn't changed in the past second. WinZip was updating the file size four times a second on our XP machine whilst sharing the processor with the FoxPro loop. It'll run at least as fast as that on the users' new machines so if we see the file size stable for a whole second then that should be a safe signal that the extraction is complete.
Did it work?
We've not been able to make it fail under tests but we'll have to wait for the next import in January to see how it behaves with live files.