VFP6 - "Delay" in changing DBF files file modified time

[ducklord]

Hi, my company is using an invoicing software (written under VFP6). Programs and table files are hosted on a server pc running Windows Server 2016 Essentials whereas users are running the software via a network path shortcut (e.g.: \\192.168.1.2\account\billing.exe) on their pc running Windows 10 22H2.

I notice that whenever an user issues and saves a new invoice, file modified time ("FMT") of the relevant table files hosted on server would not be updated immediately. Instead, FMT would only change immediately after the user closing the said program. However, users of other pcs are still able to view the new records on their respective pc before the said FMT are updated.

Is this issue something to do with the OS settings? Or due to the way the software is written.

Thanks.

 

[StefanFoerner]

Hi ducklord,

Your observation is correct

the modification date is updated when the table is closed: manually with USE - or - automatically by VFP

and this is not an issue but a feature: the modification date does not have to be updated for every user change

Regards, Stefan

 

[Mike Lewis]

I agree with Stefan.

If you need a record of the time that a new invoice is created, you need to handle that within your program - either update a separate field in the invoice table, or maintain a separate table for that type of information. The file's timestamp is not intended for that purpose.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Is this issue something to do with the OS settings? Or due to the way the software is written.

I haven't tried myself, but I trust if both Stefan and Mike make the same observation, the direct answer to your question is this is due to the way the software is written. Because the software could close tables and thereby update the FMT without ending the invoice application process. It's quite usual for FoxPro software to keep files open, though, so you wouldn't program to close files everytime you just modified one record.

Even more so, if transactions or buffering techniques are used, which would allow the user to cancel a change before its finally written to the dbf file and then it would have been premature to change the file modified time.

You say other users are already seeing new records, which is fine in itself. That tells me that neither buffering nor transactions are hindering the file modification time to change, but the simpler reason files are not closed. And that's not a sign of bad programming. As said, you don't close files just because you made one change, files are usually closed when closing forms and not needing this table anymore or eventually when quitting the application.

So overall it's FoxPros way to keep file modification times unchanged, even when it already changed files. You can therefore not use types of live backups saving files as soon as you detect a change by FMT, if that's the motivation of your question. A record modification also doesn't change file size at all, as records are fixed size. If changes in certain data types are involved the related fpt file would change, but also not necessarily in length or file modification time.

One more general advice when we already talk about these details, Foxpro software is sensible to AV scanning so even only read access to DBFs of Foxpro software is bad for the stability of it anyway. So any concerns about security of these files in any direction like backup or scanning of them should be addressed when the Foxpro software does not run.

By the way, there is no headway for a virus hiding in a dbf file anyway, it would simply appear within records and even if it would try to hide in the few bytes of a dbf header there are not many bytes without a meaning which would mean a change would corrupt the table and be detected by software problems. It is advisable to restrict access by file permissions to users of the software, therefore. I know that, of course, does not protect against a virus running under the permission of such a user, but that's the best you can do in that aspect.

When your concern is about AV the problems known with AV scanning of DBFs is usually addressed by making specific directories and the dbf/fpt/cdx files in them exceptions to AV scanning during the usage time of the Foxpro software. I don't know of solutions that work with parallel file read access, though it's theoretically possible, as Foxpro software does only use write access to data files temporarily to enable shared use of data, but for some reason AV scanning has bad impact on Foxpros data file handling, though it should only need(shared) read access and not hinder anything else including the Foxpro application to also read files while they are scanned for viruses. Similarly reading files to copy them shouldn't be a technical problem, though it would with no doubt be over the top if any change would lead to a backup copy of dbfs when changes in them can easily happen in fast succession.

I don't know if an AV software vendor has addressed that problem, I assume not. And as Foxpro is a discontinued product regarding Microsoft an AV software vendor would have a hard time interviewing anyone of the Foxpro developer team for ways to enable the AV readonly scan of FoxPro data files flawlessly, as there isn't such a team anymore.

Chriss

 

[ducklord]

Thanks Stefan, Mike and Chriss for the confirmation.

Actually, we are using a simple external program which would copy the table files (dbf,cdx,fpt) via Robocopy to a separate location of the server and export some of the data to csv file for analysis. When the copy is done before the VFP software (or the form I believe) is closed, the table files copied do not contain the new records. Is there a way the copy can be done together with the new records without closing the software?

Thanks.

 

[Chris Miller]

When the copy is done before the VFP software (or the form I believe) is closed, the table files copied do not contain the new records.

First of all I know usage of robocopy is on the list of problematic parallel usage, I used it to make a full copy of Foxpro data for separate serverside data mangling and that was done afterhours on a local copy of the data for several reasons, mainly performance. Also, it wasn't possible to do on the original file server.

Anyway, you suggest there are changes in records or new records seen in other clients of the software, yet robocopy doesn't write them to the copy? That sounds impossible, even considering the reasons of buffering or transactions, as other clients would not be able to participate in the same buffers or transactions, so if robocopy doesn't see changes they would also not see changed or new records.

One reason robocopy could fail is when changes are made on very large tables during its copy, it would restart and never finish copying. And one other bad case is that when you start copying a DBF with a certain specific number of records, denoted in the file header in the first few bytes of a dbf, and a new record is added while you copy you can end up with a dbf that has the new record in it and would be healthy, except the record count in the header of the dbf copy is outdated as it copied the header from the old state of the dbf while it also copied the new record at the end of the file.

Chriss

 

[ducklord]

Anyway, you suggest there are changes in records or new records seen in other clients of the software, yet robocopy doesn't write them to the copy?

Yes. That is the case we are facing. Even if I have tried copying manually (Ctrl+C > Ctrl+ V under Windows envinronment), the new records are not seen in the copy. I will check if there are other table files which store those new records temporary before writing into the main table file.

Thanks again.

 

[Tore Bleken]

AFAIK, Flush() will update this info.

 

[Mike Lewis]

Flush() will update this info

Tore,

As far as I can see, the FLUSH command does not update the timestamp. But FLUSH FORCE does. I don't understand the reason for that. I thought that the only effect of adding FORCE is that it does not flush temporary or read-only files. But I might have got this completely wrong.

I wonder if the Windows FlushFileBuffers() API function would update the timestamp in this case.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Flush would only help, if you could modify the applications EXE, of course.

I created a little test that updates, inserts or deletes a record of a dbf by a timer doing one of these things randomly once every second. I can't say that the modified file time stays the same. I neither close the dbf nor do I flush it. But it's not a networked file in my test, just a file on a local drive.

Ducklord, do you have Foxpro yourself to make a little test under your network conditions?

I will check if there are other table files which store those new records temporary before writing into the main table file.

That would be the simplest explanation for seeing no changes. But why? If the software uses local files other clients could not see changes, too. It would need to do something like keeping current day data separate from the overall data, yet also use that data shared. I never programmed this way, the most similar thing I know is systems keeping local data until the end of day, because sharing data accumulated during the day isn't neccessary, but then other clients also don't see it until next day.



Chriss

 

[Mike Lewis]

Ducklord, it would be helpful to know if you are able to modify your main Visual Foxpro program - the one that creates the invoices, etc. In other words, do you have access to its source code, and do you have the technical skills to make changes to the code and to rebuild the executable?

If yes, we can probably suggest ways of modifying the program so that it fluhes the changes. If successful, this would solve the problem of the timestamp.

That said, according to the Robocopy docs, it is unable to copy open files. So even with the timestamp problem solved, you would still have an issue. (But I wonder if that's correct, given that you are already using Robocopy to copy the file, albeit without the most recent updates.)

As a very short term solution, if you were to quit the application after it creates each batch of invoices, and run Robocpy and then relaunch the program, that would achieve your goal. Obviously it's not a very practical solution, but might be worth considering as a last resort, pending a longer term fix.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ducklord]

Ducklord, do you have Foxpro yourself to make a little test under your network conditions?

Yes. I have tried copying the file today under no time stamp changed condition and browsing the copied table under Foxpro. Surprisingly the new records are shown. So I am wrong that "the table files copied do not contain the new records". I notice that the table size changed even the file modification time is unchanged.

Chris, you mentioned that changed table files do not necessarily change file modification time nor file size (under certain scenarios). If I would like to avoid copying when source files are unchanged as compared to target files, what should I do? Previously what I did was checking the FMT before copying. And checking both the FMT and file size changes may not work also, right?

Ducklord, it would be helpful to know if you are able to modify your main Visual Foxpro program - the one that creates the invoices, etc.

I am unable to.

 

[Mike Lewis]

Let's take a step back.

Your overall goal is to copy some of the data to a CSV file for analysis. So forget about timestamps and Robocpy for a moment. You simply need a program that can access live data from an open DBF fle and save that data to some other location. Such a program could run concurrently with your main invoicing program, and would access the same DBF files.

It is very easy to write such a program in FoxPro. I realise you don't have the skill to do that. Can you find a local VFP programmer to whom you could sub-contract the work?

But it doesn't have to be Foxpro. There are many tools available that can read DBFs. Microsoft Access, perhaps? Or why not do the whole thing in Excel? Excel can read many types of databases including DBFs (using Microsoft Query for example) and can certainly write to CSVs, and the whole process can probably be automated via Visual Basic (VBA).

My point is that there are several ways of achieving your overall goal. You just need to look beyond Robocop and timestamps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Are you showing the file times of the copied files or the source files of the copy?

I notice the file association of DBFs is with Open Office. That's nothing to worry about because not having Foxpro itself installed but instead software like Office or Open Office some of VFPs file extensions will be associated with them. It still makes me wonder if that could play a role. I don't know how, though.


Unchanged file size could only really apply if existing data only is updated and no new invoices are stored. It's not unlikely for a single use case of changing invoice details or just a text associated with an invoice stored in the invoices.fpt file to let this modification not change the DBF file, it can even be completely unchanged when you only modify some text field that's in the invoice.fpt file. But it's quite unlikely you don't have any new records during a whole day, isn't it? And we know by file size the DBF has new records. It's strange to see the file date unchanged in the grown DBF. From the file time perspective, only the FPT file changed, but that's clearly not true.

In the end, you can always look at the triple of files and regard any change in any of them as the indicator to copy them all, that's how I would handle this. I know there's no robocopy option to do this, so that requires some trickery to first build a list of the file names without file extension and then copy this list with name.* to cover all files of each triple.

I know Robocopy has the option /L to not do anything but listing files, and that could be written into a text file. Then you could process that to shrink the list to file names without extension and use that list to build a series of single file copy commands with.* as the file extension.

Chriss

 

[Steve Meyerson]

It's been years since I wrote a batch file (.bat), but it seems to me a .bat could accomplish the copy, possibly not on open files, but perhaps executed at the "right" time?

Steve

 

[ducklord]

You just need to look beyond Robocop and timestamps.

I believe the copy program was done to give an impression that it would be less intrusive to the source files? (copying tables vs accessing the tables directly and exporting the relevant records). However, I will explore on options you mentioned.

Are you showing the file times of the copied files or the source files of the copy?

Both copied files and source files shown unchanged FMT but changed file size. The top pic were files copied yesterday whereas bottom pic were files copied today. Both .dbf have same FMT but different file size. Given that the possibility of unchanged file size is small, I would see what can done at the copy program script to make both FMT and file size indicators for copy the files.

Thanks again.

 

[ducklord]

Steve, yes, in fact I am exploring the "right" time to copy the files. e.g. when FMT changed? or when FMT / file size changed?

 

[Mike Lewis]

This topic seems to be a good example of a so-called "XY problem". This is where a user needs to achieve some goal ("Y"), and they try a solution ("X") which doesn't work. So they ask for help. But instead of asking for help in achieving the original goal, they focus on the reasons that their attempted solution didn't work.

In this case, the goal is to perform some sort of analysis on the invoice data. The attempted solution was to copy that data to another file, and then to export it to CSV from where the analysis could take place. The attempted solution didn't work because of issues with timestamps. So we spend the thread analysing these timestamp issues and trying to find a solution or workaround for them. But what we should be doing is helping the user achieve his goal of analysing the invoice data.

I'm not attaching any blame here. I'm simply pointing it out. It is in fact a very common situation in many technical forums. But perhaps the onus is on us to make more of an effort in steering the questioner towards their overall goal rather than giving all our attention to the immediate question.

Well, it's just a thought. Others may disagree.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike, I hear what you're saying but recommending to copy files based on any change of any file of the triple is directly achieving the goal of knowing what to copy. So I worked on getting to that solution.

Also notice, ducklord said:

So I am wrong that "the table files copied do not contain the new records"

So was it wrong to question what he initially said?

And actually, I'm still not sure ducklord just has wrong expectations about how the attributes of a copied file should be, they are not necessarily simply the same as the original file. In robocopy you can specify what should specifically be done with file attributes including timestamps by command options and we don't even know what robocopy command with which options of which robocopy version under which Windows version is used to copy the files.

That's also - besides other things - why I asked what I see in his screenshot the listing of the copied files or the listing of the original files.

Notice I also couldn't reproduce what you and Stefan saw about the file modification timestamp when you keep files open in VFP.

We can make it short and simply say when you make a copy of files once a day, then why even consider anything else than the simple copy of all files no matter whether file dates or sizes changed or not, because its safe to assume that between two business days data has changed and needs to be copied.

You could of course also question the overall process and change it to make the CSV exports from the original files, but I assume there are reasons to make copies first, I've been in that situation myself, as I stated.

Chriss

 

[Mike Lewis]

Notice I also couldn't reproduce what you and Stefan saw about the file modification timestamp when you keep files open in VFP.

I have to admit that I was surprised at that result. I'll do some more tests on that when I can grab a moment.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads