How reliable is a VFP's FPT (memo) ?

Hi Experts

How reliable is a VFP's  FPT (memo) ?

Any check/ repair routine is advisible to run from time to time  to prevent corruption?

Thanks in advance
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

We know about problems in cursors having many memo fields but the memo itself is reliable as much as any other disk data access.

So memo on local harddrive is reliable if your users do not switch the computer off when the app is running. Killing the application in Task Manager or Windows crash can cause the same problem.

Memo in table on remote drive is less reliable than memo on the local drive. An unstable network can corrupt the memo easily. Use SQL Server for remote data preferably. You should avoid WiFi connection for DBF/FPT access.

You have to check errors during all save operations and FLUSH or FLUSH FORCE or even close the tables as soon as possible. You may save some record check sum and use it for data validity checks. More memo fields means more problems. Repairing tools do not know your data so to fix FPT is always tricky operation.

It is very bad if the situation requires some repair tool... If you are observing corrupted DBF/FPT then you should fix the environment. Backup is always better than any repair tool. Of course, you have to weigh the data loss during the restore. Some less important tables is better to fix
instead of the whole data set restore...
Olaf DoschkeSoftware DeveloperCommented:
In regard to a regular "repair" - let's rather talk of a refresh - a PACK of a table is good not only to get rid of deleted rows but also to rewrite the FPT and toss out all unused bloat. No need to PACK MEMO, unless you want to concentrate on regenerating the FPT only, I'd go for a regular PACK of DBFs, which renews DBF, CDX, and FPT files.

Bye, Olaf.
PACK has one drawback: It removes unused data which could be valuable when the memo is corrupted. Of course, to find correct references between DBF rows and unused FPT memo blocks which could be possibly used for the data recovery is not simple work... if not even impossible in most of the cases.

Another problem could occur in applications which actively use deleted records. (Not obvious.)

My favorites are:
1) Regular backup with the period which corresponds to the amount of data you accept for repeated entry.
2) Regular reindex
3) FLUSH after each data write operation
4) Logging of the correct application start/end. Once you detect the problem at the app start then you have to run the reindex and data check with immediate fix if necessary.

We should not make our code much more complex due to the higher data reliability. Much better and easier is to invest into more reliable environment instead of additional lines of code which check for each operation success and possible repeating or writing additional data into tables or saving the data at two places etc. etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Eduardo FuerteDeveloper and AnalystAuthor Commented:

After reading what you posted I'm planning this strategy:

After ever data write operation on a DBF that has a FPT -  FLUSH
Ask to start the backup routine when the app ends.
Automatic Reindex when the app starts or ends.

So it's going to minimize to much the risks.
Olaf DoschkeSoftware DeveloperCommented:
I've had very good success with daily PACK of data (which includes reindex, too). You can easily know, whether your application makes use of deleted records. And it's a bad sign to not foresee a real boolean field for storing two states of a record (active/inactive, valid/invalid, archive/current) and instead misuse the DELETED flag of a row for such information, because it's only halfways queriable in situations with no JOINs.

Therefore I stand to this advice of doing PACK more than anything else about offline/nightly maintenance of data.

The reasoning you could reuse parts of fpt to recover data is as weak as it is to be able to reuse deleted rows. If you like that policy you can avoid ever deleting data and rather create your own logical field for that. Without PACKing you're also not ruling out reuse of FPT file blocks that may contain "valuable" copies of (old) memo values not yet overwritten.

If your data history is important then you may go the route of even only ever inserting data and/or creating audit trail triggers saving every state of data before updates or deletes change records.

I agree to flush after saving, though it doesn't have the final word on what happens in networks, as you don't have the final control over caching, etc. So I also agree with Pavel, it is essential the network is very stable and no code can make up for any instabilities you face because of the filesystem, file protocol fails, bad cache settings and bad hardware.

Have the correct tablevalidate settings, use either the bit combinations biz#1,#2m and #3, that is 1+4+8 = 13, which checks the header health both when opening a table and when saving new data and header. Or a bit more scalable only check at write time with bit#3 (value 8) only. When not checking header validity at opening, there is no need for additional opening locks. At save time you must have a lock anyway, therefore I would rather prefer 8 and in case this always reports header corruptions early you also not need the header check at opening tables.

wOOdy, if you know him, argues for SET TABLEVALIDATE TO 11, which in my opinion is unnecessarily doubling the effort at change time, as bit#1 and bit#3 mainly do the same, but the option of bit#3 was introduced in VFP9, which I think is advanced over what VFP8 already did, when you turn on bit#1. (VFP9 had only 2 bits and thus values 0-3 only, instead of 0-15 now).

I would advise you make your own experiments in a staged situation of two clients changing same table and record over and over again in your network situation, as results may vary.

Bye, Olaf.
Eduardo FuerteDeveloper and AnalystAuthor Commented:

I'm confused... finally is it a good strtegy to PACK before a complete  Reindex ?

The backup is done anyway.
Olaf DoschkeSoftware DeveloperCommented:
PACK is reindexing as it rebuilds a table from scratch: You can simply read the help topic and you know what happens. A new empty DBF temp file is generated alongside FPT and CDX filed, it is filled with all undeleted data. If that all is successful the new file triple is renamed to the original file names. Along that way, FPT and CDX are rebuilt as with PACK MEMO and REINDEX.

There are variants of the PACK command, that only rewrite DBF or FPT file, namely PACK DBF and PACK MEMO, You don't use these variants. Also, you don't use REINDEX. Doing that on top of PACK you do things you already did with PACK, If you only have index problems you could REINDEX, I wouldn't. If mainly Memo fields become corrupt, you could PACK MEMO, I wouldn't. If your DBF becomes corrupt or you generate lots of deleted rows per day to get rid of, you could PACK DBF, I wouldn't. A simple single PACK does what PACK DBF, PACK MEMO and REINDEX do together, just in one step. That's what you do. At a time no user is using the software, via Task Scheduler, for example. It's a thinkg you do as prevention, not as repair. So you keep all aspects of data fresh, not only DBF or FPT or CDX, all files, and PACK does that.

I don't think I convince Pavel from the PACK strategy, but I used it successfully. Pavel's concerns are not very strong. Of course, a used/worn FPT file has sections which became marked unused during all the changes data goes through, that could contain just the one value becoming corrupt in at least an older version of the memo text. But don't rely on it. Blocks, which are too small to be overwritten are just marked unused and can be reused for shorter texts later, but they aren't protected just like blocks of deleted files in the file system are not safe from reuse. So this value of worn FPT as your savior in case of corruptions is questionable.and removing deleted records, removes what only is kept for reasons of performance, you can't reorganise the DBF file with every delete, delete recno 1 from a 100 MB dbf and you need to rewrite 100MB-Recsize() bytes, PACK does so, so you only do it once a day.

And backup is backup, that's not audit trailing, I won't explain that, google what audit trail means, if you never heard of it. It'll enable you to see histories not just of whole tables, not just at the backup times but every change of every record, the audit trail can be seen AS a backup, incremental, you can of course do a backup anyway, for easier file by file recovery, and you do so, but audit trail offers more insight into how your data evolved and enables you to go back for one case of one customer or one order. If you know the month of interest you might restore 31 table versions and look into them, but that's much more cumbersome than maintaining an audit trail.

Bye, Olaf.
Olaf DoschkeSoftware DeveloperCommented:
To stress it out once more: PACK is not a repair tool, it's a tool to keep files tidy and that can help prevent errors. Especially less worn index trees and "tight" memo files are more likely to stay intact.

If you have a corrupt index you can rebuild it with REINDEX, but only as it doesn't have any original data in it, it consists of cleverly reorganized parts of the DBF and FPT in an index binary tree data struct. If you have corruptions in DBF or FPT those bytes are lost and PACK doesn't bring them back, only your backup or audit trail can help you to revert to last known state. Other repairing means removing a corrupt record and/or blanking a corrupt memo field, those repair actions make a file usable again but don't recover their data, that should be clear.

And nothing prevents the problems of oplocks, that should also be clear.

Bye, Olaf.
Yes, my arguments on PACK are weak and the application developer should decide.

So I would summarize my recommendations:
1) FLUSH after each write (and don't leave files open when they are not used)
2) SET TABLEVALIDATE - I am using the default 3 and changing it to 0 when repairing the table header
3) Daily REINDEX
4) Daily backup
5) Identify app crashes (or non-standard exits)
6) PACK on developer's decision as a part of maintenance plan
7) Data integrity check as a part of maintenance plan

Remember PACK does not REINDEX when no data are deleted.

Also to access data on server share from different Windows versions is not good option namely when the collation is different from MACHINE.

Oplocks and SMBs are a different story but we have to count on them... My view is simple: Try to avoid DBFs on remote drive. Use client-server instead.
Olaf DoschkeSoftware DeveloperCommented:
>Remember PACK does not REINDEX when no data are deleted.
I see the help topic mentioning
If no deleted records exist, PACK does not modify the table or its index.

I don't think so as PACK does combine PACK DBF and PACK MEMO and I don't think it analyzes the FPT file for blocks marked unused, it would simply start recreating table files also reindexing on the way. It can't keep the promise of what it's supposed to do only acting if deleted records exist. I think this is a misdocumentation.

What I see is no file creation datetime change, never, both in the case of deleted rows before PACK or not. I don't think PACK first analyzes anything, it simply recreates new table files even inheriting the file creation datetime.

Bye, Olaf.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Still reading your concepts.

What I had already done in a repair procedure - just if something is got wrong and I felt it's necessary - applied only to DBF, a kind of repair.
I don't know something similar exists for FPT.

For correction:

* Open the file with unbuffered read/write access
lnhandle = FOPEN(lcfname,12)

* Test for possible file opening error
IF lnhandle  = -1
  WAIT WINDOW "Arquivo "+ lcfname+" Erro: "+STR(FERROR(),2)

* Read the first 12 characters of the header
lchdrinfo  =FREAD(lnhandle,12)

* Calculate the header length
lnhdrsize  = INT(ASC(SUBSTR(lchdrinfo,09,01)) ;
	       + ASC(SUBSTR(lchdrinfo,10,01)) ;
	       * 256)
* Calculate the record length
lnreclngth = INT(ASC(SUBSTR(lchdrinfo,11,01)) ;
	       + ASC(SUBSTR(lchdrinfo,12,01)) ;
	       * 256)

* lafsize[2] contains the file size in bytes
* Calculate the number of records
lnreccnt = ;
     INT((lafsize[2] - lnhdrsize) / lnreclngth)
lcfirst8 = LEFT(lchdrinfo,4) + SPACE(4)

FOR lni = 3 TO 0 STEP -1
  * Replace characters from right to left
  * starting at 8 and going to 5
  lcfirst8 = STUFF(lcfirst8,lni+5,1, ;
		 CHR(INT(lnreccnt / 256^lni)))
  * Perform modulus division and use remainder
  * as the remaining record count in next pass
  * of the FOR loop.
  lnreccnt = lnreccnt % 256^lni

* Go to top of the file.
* Write 1st 8 positions back to the header
* Close the file

Open in new window

and this "old" one for codepage

* CPZERO -- Poke a codepage byte into a database header
* Author: Walter Kennamer
* Copyright Microsoft Corp, 1993
* Usage: 
*    DO CPZERO WITH dbfname                     && marks the database with codepage 0 (i.e., no codepage)
*    DO CPZERO WITH dbfname, codepage_number    && marks the database with specified codepage
* Some common valid numbers are:
*   Windows            1252
*   DOS                 437
*   International DOS   850

PARAMETER m.fname, m.cpbyte
   m.mtalk = "ON"
   m.mtalk = "OFF"

#define C_TOTAL 20     && total code page numbers suppoted

   m.cpbyte = 0

PRIVATE m.mtalk, m.vuename

#define c_buf_size 32

#define c_noopen   1
#define c_badbyte  2
#define c_notfox   3
#define c_maxerror 4

m.vuename = ""

DECLARE error_array[c_maxerror]
error_array[c_noopen] = "The database could not be opened."
error_array[c_badbyte] = "Invalid code page specified."
error_array[c_notfox] = "Not a FoxPro table."

DO setup
DO main
DO cleanup

m.vuename = SYS(2023)+"\"+SYS(3)+".VUE"
CREATE VIEW (m.vuename)

IF FILE(m.vuename)
   SET VIEW TO (m.vuename)
   DELETE FILE (m.vuename)
SET TALK &mtalk

PRIVATE m.fp_in, m.buf, m.found_one, m.i, m.outbyte

* Set up table of code pages and DBF bytes numbers
cpnums[ 1,1] = 437
cpnums[ 1,2] = 1
cpnums[ 2,1] = 850
cpnums[ 2,2] = 2
cpnums[ 3,1] = 1252
cpnums[ 3,2] = 3
cpnums[ 4,1] = 10000
cpnums[ 4,2] = 4
cpnums[ 5,1] = 852
cpnums[ 5,2] = 100
cpnums[ 6,1] = 866
cpnums[ 6,2] = 101
cpnums[ 7,1] = 865
cpnums[ 7,2] = 102
cpnums[ 8,1] = 861
cpnums[ 8,2] = 103
cpnums[ 9,1] = 895
cpnums[ 9,2] = 104
cpnums[10,1] = 620
cpnums[10,2] = 105
cpnums[11,1] = 737
cpnums[11,2] = 106
cpnums[12,1] = 857
cpnums[12,2] = 107
cpnums[13,1] = 10007
cpnums[13,2] = 150
cpnums[14,1] = 10029
cpnums[14,2] = 151
cpnums[15,1] = 10006
cpnums[15,2] = 152
cpnums[16,1] = 1250
cpnums[16,2] = 200
cpnums[17,1] = 1251
cpnums[17,2] = 201
cpnums[18,1] = 1253
cpnums[18,2] = 203
cpnums[19,1] = 1254
cpnums[19,2] = 202
cpnums[20,1] = 0
cpnums[20,2] = 0

IF EMPTY(m.fname)
   m.fname = getfile("DBF|SCX|FRX|LBX|MNX","DBF name")
IF !EMPTY(m.fname)
   m.outbyte = m.cpbyte
   m.found_one = .F.
   FOR m.i = 1 TO C_TOTAL
      IF m.cpbyte = cpnums[m.i,1]
         m.outbyte = cpnums[m.i,2]
         m.found_one = .T.
   IF m.found_one
      m.cpbyte = m.outbyte
      * Was it a valid DBF byte if it wasn't a valid code page?
      FOR m.i = 1 TO C_TOTAL
         IF m.cpbyte = cpnums[m.i,2]
            m.found_one = .T.
      IF !m.found_one
         DO errormsg WITH c_badbyte
         RETURN TO cpzero
   IF FILE(m.fname)
       m.fp_in = FOPEN(m.fname,2)
       IF m.fp_in > 0
          * First check that we have a FoxPro table...
          IF (SUBSTR(m.buf,1,1) = CHR(139) OR SUBSTR(m.buf,1,1) = CHR(203);
             OR SUBSTR(m.buf,31,1) != CHR(0) OR SUBSTR(m.buf,32,1) != CHR(0))
              DO errormsg WITH c_notfox
              RETURN TO cpzero
              * Now poke the codepage id into byte 29
          DO errormsg WITH c_noopen
          RETURN TO cpzero
       DO errormsg WITH c_noopen
       RETURN TO cpzero

PROCEDURE errormsg
WAIT WINDOW error_array[num] NOWAIT

Open in new window

Something like automatic FPT fix/recovery does not exist. It could just bring additional problems...

PACK could be analyzed under Process Monitor.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Just in my case the reindex routine first delete all the index files - CDX/ IDX and then recreates all of them - so is it still advisable to PACK in this case?
To delete and recreate all indexes is a good approach but it has almost no relation to the PACK command.

Whether to PACK or not should be a separate decision based on the real deleted rows and memo fields usage.

I cannot use PACK in one application as it distributes data to several different sites and this distribution also covers records deletion. The PACK would cause unpredictable discrepancies in data among different sites... A flag field for such deletion seems to be better solution here but it would require app update.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Tjank you for help and guidance!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.