foxpro 2.6a for windows corrupt .fpt file

[Criccieth TV]

Hi - old newcomer alert.
I built a FoxPro programme more than twenty years ago and it is, today, still very important to my business. Last week I had corruption of about 10 records in the memo field of my invoice table (out of approx. 16000). As I scroll through the table, the memo field shows in a separate window, and for these bad records, the data in the memo field is incorrect. One record displays what must be quite a few memos lumped together.
I was thinking about deleting the bad records, packing the table, then rewriting the records again with the correct data, but I don't know if this might spread the corruption.
I have seen a few fixes, and noticed some in here. Could anyone offer any advice?

Thanks

Dave Hookings

 

[Scott24x7]

Hi Criccieth TV,

One question, do you have any index(s) on the table? If so, are any of them compound? (Uses more than one field). Also, how many index(s) are there?

The process you mention could become worse based on the response above, but there is a solution for that too, but I need to know more to guide you in the right drection.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Criccieth TV]

Hi Scott, thanks for your help.
Every time I start this application I check for exclusive use and if I have it I reindex every table. In the case of this table "saleslsd" I run the following code.
use saleslsd.dbf exclusive
delete tag all
index on DTOS(date)+inv_no tag date
index on DTOS(date)+inv_no for paid = "N" tag notpaid
index on SUBSTR(category,5,2)+DTOS(date)+inv_no tag report
index on inv_no tag invoice
index on DTOS(date_paid)+inv_no tag date_paid
index on cus_no + dtos(date) tag custno
index on cus_no + inv_no tag cust_inv
index on left(saleslsd.category,1) tag inv_not_t for saleslsd.Trans_inv = .F.
index on left(saleslsd.category,1) tag inv_not_p for saleslsd.Trans_pd = .f. and upper(lsd.paid) = "Y"
use salesinv.dbf exclusive

I hope that gives you a start on my thinking. The last line is a different version of the same table structured for transporting to Access, which is not yet used.

Regards

Dave

 

[Mike Lewis]

time I start this application I check for exclusive use and if I have it I reindex every table.

I don't know why you do that, Dave. It is not normally necessary to re-index the tables, much less every time you start the application.

I suggest you use Scott's advice as a starting point (and nice to see you back here after all this time, Scott). Alternatively, write a little program that recreates the table and copies all the records from the old to the new. If you know which records have the corrupted memo, omit them from the new table, then later add them manually. Something like this:

CREATE TABLE <new table (same structure as existing)
<create indexes for new table>
USE <existing table> IN 0
SCAN <existing table>
  IF <record does not have corrupted memeo)
    SCATTER 
    SELECT <new table>
    GATHER
  ENDIF
ENDSCAN

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Criccieth TV]

Hi Mike, and thanks for your input.
The reason for reindexing is lost in the mists of time, but I seem to remember having problems with indexes which cleared up when I started doing this. I just checked, and from clicking on the Foxpro icon to having my application waiting for input was five seconds, so not a big cost to reindex.
I can doctor every record that has a corrupt memo field by putting a unique character sequence in one of the fields and scanning for that, but then, if there is a way to detect a corrupt memo field, I would not have to rely me picking them out and find them automatically. This would ensure a definite clean start.

Regards

Dave

 

[Criccieth TV]

<waiting for input was five seconds, so not a big cost to reindex.>

Sorry, there was another user on line so that was not reindexed. I started again, this time exclusively and the timing was fifteen seconds, so still not bad.

Regards

Dave

 

[Mike Lewis]

Dave, I take your point about the non-seriousness of the startup delay. But if there was a problem with indexes - however long ago - it would make sense to track down that problem and fix it, rather than simply to bypass it by re-indexing. It could be that it is that same problem that is causing the corruption of the memo fields.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Criccieth TV]

Hi Mike,
<It could be that it is that same problem that is causing the corruption of the memo fields.>
Possibly but I really doubt it.
In our place of work we have 6 PCs. One is set up as a file server, no keyboard, no mouse. This one holds all the Foxpro files. The whole system has worked fine for many years with no corruption - then along came Covid 19. And home working. So our IT guy uses a software program with a Japanese sounding name to create a VPN between the server and my home computer. I log on a couple of nights ago and start using Foxpro. It was slow....dreadfully....The startup time I was mentioning before was now over two minutes. After an hour's work stuttering through at this painfully slow rate I gave up and shut down. When I got into work the next day I noticed the corrupted memo files. (and a fault on another table, which I have not mentioned as it should be easier to fix).
Which fetches me back to the current position.
IF <record does not have corrupted memeo)
Can you think of any way that I can interrogate the memo field to determine the answer to this programmatically?

Thanks for all your help so far.


Dave

 

[Mike Lewis]

Can you think of any way that I can interrogate the memo field to determine the answer to this programmatically?

Unfortunately, no. My suggestion was based on the assumption that you already knew which records are corrupted - perhaps their record number or primary key - based on what the users have told you. You also said that you could see the bad data when you were scrolling through the table (presumably in a Browse). But I can see how that would be difficult with 16,000

Interesting that this ultimately came about because of Covid-19. A new interpretation on a computer attacked by a virus. (Sorry, i know that doesn't help.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Olsson]

I have had similar problems, maybe not exactly the same since you seem to be able to browse the table without problems.
I'd copy the table to a new one.
Then it stops copying when there is a corrupt memo.
I then REPLACE the memo WITH ""
When at last I've been able to copy the whole table all corrupt memos are gone
I PACK the table (we call it NEW)
SELECT 0
USE <the last backup> AS old
INDEX ON RECNO()
SELECT new
SET RELATION TO RECNO() INTO old
REPLACE memo WITH old.memo FOR EMPTY(memo)
That way all your memos will be restored except for them you put in since your last backup and maybe you can recall/restore them manually.

 

[Criccieth TV]

I had a further look at this problem yesterday evening.
I found at least 150 records with a problem memo field, just looking at it showed it was part or all of a memo field but was in the wrong place. I just replaced them with "". It is a loss of data that I can handle as I do have paper invoices I can refer to if necessary. All I have to do now is to make the table stable. I do have another unused memo field in the table, which I was going to use in my overall plan to transfer to Microsoft Access. If I step through each record using scatter, reduce the length of the variable holding the memo to reasonable length, then gather to the new memo field, I think I should achieve this. I should then end up with a table slightly corrupt but stable.
I'll have to do something soon as I have data queuing up to be entered.

Regards

Dave

 

[Mike Lewis]

Your plan sounds good - assuming you can easily identify the 150 or so bad records.

I was going to suggest a possible way to identify the corrupted memo fields automatically. My thinking was that, if the corrupted data includes non-printable characters, like binary zeroes or something similar, it should be possible to write a program that parses the FPT file, looking for such characters, and generating a list of the corresponding record numbers.

But from what you have just said, Dave, it sounds as if that wouldn't work, because the bad memos still contain printable text, but it's just in the wrong place. Still, something to keep in mind.

Let us know how you get on.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Criccieth TV]

Hi Mike,

Yes, I have started entering new data into Foxpro, keeping a wary eye on what is happening - so far so good.
At the back of my mind I am wishing foxpro had something like the autonumber primary key of Access, which guarantees unique keys.
Anyway thanks for your help.

Regards

Dave

 

[Mike Lewis]

FoxPro does have autonumber primary keys. It is the "integer (autoinc)" data type, which generages sequential IDs. And it supports primary keys (which might or might not be ("integer (autoinc)") which guarantee uniqueness.

However, these are features of Visual Foxpro. If you are using 2.6, these features are not available.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Criccieth TV]

If you are using 2.6, these features are not available.

That's me

Dave

 

[mjcmkrsr]

Hi,

Way back in the 1990's I had a similar problem. This is what I did:

Close the table
Remove all indexes via "modify structure"
Open the table exclusively
Browse through the table with the Down-Arrow - takes some time but is feasible with 16000 records only

If a corrupted record was hit it did NOT allow me to "jump over it" - I deleted it and was able to move on

After finishing I packed the table and re-indexed it.

So our IT guy uses a software program with a Japanese sounding name to create a VPN between the server and my home computer. I log on a couple of nights ago and start using Foxpro. It was slow....dreadfully....The startup time I was mentioning before was now over two minutes. After an hour's work stuttering through at this painfully slow rate I gave up and shut down.

Well, then I guess you know where the problem started - the link between you home PC and the server was interrupted and one or more files weren't closed properly. This setup is error prone and your IT guy might want to look for a different solution for the home workers.

hth

MarK

 

[Criccieth TV]

This setup is error prone and your IT guy might want to look for a different solution for the home workers.

I have solved the problem - there are only three people writing to foxpro, including me..so emails only at home.

Regards

Dave

 

[Scott24x7]

Dave,
Sounds like he is using Hamachi which is part of the LogmeIn suite. Instead of using that, he should just do a direct "LogMeIn" and sign in as a user. Then he is a remote use on THAT system, instead of sending the data back and forth. An interruption to the connection then still leaves the remote box working fine, waiting for a reconnect, just like if you get up and go get coffee and come back. Even on a slow internet connection, this is still useable.
The other option is something like RDP (Remote Desktop Protocol) or TightVNC, which allows remote control of the machine as well. This is a much better arrangement for not tanking the tables. When you have so many (and compound) indexes on the file, and memo fields, you're just asking for trouble if you get a bad shutdown.

If you're still developing on the system too, I would HIGHLY recommend rethinking your indexes. You can probably get away with only a single (two at most) indexes which will make it much faster. (You just have to put a bit more programming muscle behind it to "DO WHILE" on a set of records that match your index criteria). Remember the other thing with FP2.x tables is, you REALLY want your index to create a unique result, or you can wind up with a lot of index problems, and unexpected behavior elsewhere in your application.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Criccieth TV]

Hi Scott,
Yes that was the name, Hamachi.
I was never very keen on the idea of working from home.
Foxpro has to use NTVDM to work on windows 8.1 and although it works fine on the four machines networked at work, it won't work on my partner's setup using Windows 10, 64bit. So I am reluctant to spend any more time on it.
As an example I tried to get the table to pack, using the couple of suggestions here, but my Foxpro allows me to scroll past the corrupt memos without a pause and I can copy the table to another one with no error messages. But when I try to pack the table I get the no memo/or damaged memo file message.
I feel inclined to carry on using the database while concentrating on getting the data ready for exporting to Access.
I am trying to normalize Access structures to be able to produce invoices, line by line, knowing how far to go, trying to anticipate future problems.

At my age my brain is not the force it was, so I sometimes have to rethink a problem a few times.
Hey Ho!

Regards

Dave

 

[Scott24x7]

Dave,
All the more reason this solves your problem.
The machine that is hosting the application can be Win 8.1. You remote into it from a Win10 box, and you're done.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.