Memo fields or fixed length fields? For text that needs updating 3

[KarenLloyd]

Dear Gurus

Please could you share your opinions on the use of memo fields?

More specifically:

1) Memos used in a multi-user environment in a VFP6 application
2) Where existing text can be updated and overwritten many times
3) Where 100s of records are added daily
4) And the file is only packed and reindexed once per week

I have been reading threads relating to memo use, and have referred back to the following:

thread184-1612797
thread184-1644583
thread184-1641916
thread184-240424

When a memo file is updated just storing new records, it is a very useful thing and tends not to run into problems.

But I have some memo text that can be updated, going back a sizeable block, say if updating text on job costs from a couple of weeks or even months prior.

The users don't work in the actual table, or type into the live memo. Instead a set of records is copied to a temporary file, which is worked on, and the records are then updated back to the main file using SCATTER/GATHER to overwrite each existing record for the job, and the newly appended lines.

I have lost a lot of time comparing memo files from restored data, trying to locate blocks of text that have since become corrupted. More recently, the corruption was not caused by any hardware issue, but by the presence of non-keyboard characters -namely ASCII Chars 0, 19 and 21 - two of which I also bring up by accident when typing.

OK - so next, I can add validation to the editbox to remove any non-keyboard characters from the text at the point of update.

But - is that all I should do?

There is still the potential for memo problems occuring in this file due to it's constant updating. It has 85000 records so far, just from two years of jobs.

I don't want to banish memo fields from this system and rewrite the whole thing, but do want the data to be secure. Also, I want to restore confidence in the text without having to run periodic memo file checks.

The reason I seek your advice is that I now have to consider whether to change over to text fields on this file. This will still mean editing in a temporary file in memo format, but saving back to the main file into fixed length fields, splitting longer text across multiple records when necessary.

I'm not sure that I really have any choice...

I would really appreciate any feedback you can give me here, before I plan my next move.

Thank you in advance...

Karen

 

[Mike Lewis]

Karen,

First, I would say that if you strip out those non-keyboard characters, that should solve many of the problems. I've had similar issues with non-visible characters creeping into memos (usually as a result of the user pasting text from an email). A simple dose of STRTRAN() to get rid of them has always been beneficial.

Regarding the wider issue, I wouldn't have thought your volumes or the volatility of the memos should cause any problems. But it's hard to be sure.

If at all possible, you should avoid using multiple fixed-width character fields in place of memos. Such a scheme can be horrible to maintain, and will nearly always be less efficient.

No doubt others here will give you their views as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Hello Karen,

I started with VFP6 in 1999 and we went to VFP7 quite soon. In my Foxpro life I may have been lucky, but I never had problems with fpt corruption. Corruptions? Yes! index, dbf, if I could identify the problem it was those files, mainly wrong reccount and index nodes pointing to nirvana or missing. There also were corruptions I couldn't verify, might also been fpt issues, but no definitive one I could talk of.

The principle for maybe the main reason of fpt corruptions - memo bloat - stayed up to vfp9. It's clear vfp can't stuff a longer text into a reserved block without shifting everything else. But this is too much overhead, so vfp instead invalidates the current block and appends the larger text at the end of the fpt. It's not a very vulnerable process in itself. It just uses up more space and in the worst case might go over 2GB, but 2GB still is much.

To get to the point: I have not and would not go the cumbersome route of using multiple c(254) fields to avoid fpt files. So I agree on that with Mike.

You might think about daily processes doing PACK MEMO, that takes less time than a full pack and may be possible in your case, if a full maintainance is not.

Even in shared mode you can copy over all data from the central dbf/fpt into a new one instead of a pack, that also stripes off the fragmentation.

If you choose memo (binary), such control characters as asc(0)-asc(19) you mention, could not harm, or should not. I also don't see how they could from the structre of fpt files. Besides a header the fpt file has not much meta data anyway, each block of net memo data has another header, see

http://msdn.microsoft.com/en-us/library/8599s21w(v=vs.80).aspx

It's a very simple structure. I'd rather expect corruptions from block headers specifying wrong block lengths, not from certain chars inside the memo blocks. Such an error can cause a chain reaction, when vfp assumes more block space available then there really is, overwriting a block into the next block due to that assumptio, which in turn creates a totally wrong header for another record pointing to that postion, now midst of the new memo value, and so on.

It wouldn't be hard to write a fpt health check with the knowledge about the structure. Also you can keep foxfix or similar at hand to mend corrupted files.

Bye, Olaf.

 

[Dan Freeman]

Hi Karen,

1) Creating temporary records with SCATTER/GATHER for editing is, in effect, creating a buffer. We all did it for many years. Buffering was built into VFP from the start which obviated the need for writing our own.

Your temporary "for editing" records should really be an update3able parameterized local view instead, which means you'll never have to debug your hand-coded buffering or updating again because VFP takes care of all of it for you.

2) Memo fields work perfectly for people who do not experience problems with them. There are some people who absolutely never have problems with them and they cannot understand how others have problems with them.

Some people have problems with them, they have constant problems, and it ticks them off that nobody can tell them why.

3) Typical causes for memo corruption are environmental. An app that frequently ends abnormally (i.e. not a normal shutdown) will have problems. A network that has flaky hardware will have problems. A disk that is on the edge of failing will have and cause problems. Etc.

There's a rule in VFP: it suffers bad hardware poorly. If there is a flaw *anywhere* (NIC, cable, router, server, etc.) VFP will find the flaw and exploit it. It's like the Murphy's Law of VFP.

It's going to be up to you to find what it is in your environment that corrupts memos. You absolutely cannot change the way VFP handles them, but you can change *WHERE* it handles them. IOW, you can change its environment.

(And you can stop writing your own buffers. <g>)

 

[Olaf Doschke]

Very well said, dan. Though I belong to the fraction not having problems with memos, I agree. Also on not rewriting a buffer mechanism. Also memo updates to buffers don't change the dbf until you tableupdate(), so that also only means one net update of a memo, if there is.

Bye, Olaf.

 

[Olaf Doschke]

Very well said, dan.

Though I belong to the fraction not having problems with memos, I agree. Also on not rewriting a buffer mechanism.

Memo updates to buffers don't change the dbf until you tableupdate(), so that also only means one net update of a memo, if there is. All previous go into buffer, meaning are only yet changed in memory. But I remember vfp6 was not that stable as later versions and while I can't say it was buffering, that caused c5s or defects, I can totally understand you write the one or other workaround sooner or later.

Bye, Olaf.

 

[KarenLloyd]

Hi

Thank you for all your responses.

Mike

I agree with you and Olaf regarding the use of fixed length fields. That approach does not appeal to me at all.

Other similar applications have been running for years with the same structure and have only needed the occasional FoxFix or re-copy to fix the memo file, so I will persevere.

I will add the validation to check to remove any extra characters and then continue to monitor the text before deciding whether further steps are needed.

Olaf

I have seen from previous threads that you understand how the memo files work. I appreciate your insight and your explanation regarding the structure.

I say about the ASCII characters because I updated a memo field (in BROWSE mode) to remove CHR(19) from a one line memo, which then caused that updated line to duplicate within a different record. Odd behaviour indeed.

I had not considered Memo (Binary) before - is that used to prevent translation of characters?

Dan

The "Murphy's Law of VFP" certainly rings true. And then there are times when people simply switch off the wrong socket...

In this particular case, each set of records may have been changed to have records inserted above and between, updated in the middle and possibly deleted from the end. So I believe that I still have to handle it in this way. That is, to overwrite what is there (regardless of key fields), append anything new or delete any remains.

Although this means that the original blocks of text are then invalidated and the new text appended, as Olaf says, I don't have any memo bloat issues - due to the weekly PACKing.

It alsomeans that should the system "crash" while updating a job, the user is given the option to recover their unstored costs from the temporary file when the system restarts. Unless it crashes while writing the updates back to the main file - in which case we can still have the same memo missing/invalid type problem.

All of this aside - I still haven't done any work with views and table updates, so you have reminded me that I am not making the best use of the tools VFP has to offer.

You have given me cause to think about some of my other update screens. So although I haven't been able to take much time to learn more about these in the past, I will take another look.


I thank you all once again for your help.

Kind regards

Karen