Remaking cdx indexes 1

[tilltek]

This is probably another one that everyone knows except me but here it is.
When I do maintenance, or to fix problems in my Point Of Sale system, I first check that each dbf file exists.
I then run DBRX.EXE to fix all those weird things that happen to DBF files.
So far so good.
If the file has a .cdx index, and most of mine do, I remove it and re-make it.
I've found this to be a good idea for various reasons.

But now my problem.
If the index is missing FoxPro throws up "STUCTURAL CDX NOT FOUND" and won't load the data file.
If the index is out of whack I get "INDEX DOES NOT MATCH DATABASE, RECREATE INDEX" and the data file is not loaded.
What I need to know is, how can one load a data file with missing, corrupt or non matching CDX without the error?

I can't DELETE TAG ALL without first loading the data file and I can't load the data file while its cdx is bad.

In the command window with SET SAFETY OFF a data file will load regardless of the state or existence of the .CDX but this won't work in a program.

I realize I can build a special error trap which looks for related errors (15,19,114,1683,1707) and stops them, but the file still isn't loaded.
Is there some catch or trick that I need to know.
Hope you guys can point me in the right direction.
Ken F

 

[ramani]

Hi

Regarding your comment..

"In the command window with SET SAFETY OFF a data file will load regardless of the state or existence of the .CDX but this won't work in a program..."
This is not correct..

If you have the code..
SET SAFETY OFF
USE myCdxDropedTable

This works.... Probably, you are trying to use the CDX file in the USE statement and so you are not able to continue.

What I sugest is... once you trap the error... call another myRepair.prg... with the relevant file name..
MyRepair.PRG
************
PARAMETERS myFile
SET SAFETY OFF
USE (myFile) EXCLUSIVE
IF myFile = "abc"
INDEX ON ....... ec commands..
ENDIF
USE
WAIT "File error fixed.. Restart" && or some message to user
RETURN TO MASTER

This is distinct from index corruption.. where you can open the table with the corrupted index. Here you can do the reindex or similar above exercise to set it back. This is alo different from Header file corruption.. which I fix using another utilityCall and then do the index creation.

Hope this help you ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[tesar]

In addition, before USE i apply
DELETE FILE myFile.cdx
USE myFile
INDEX ON ...
in each case start program,
regardless index is good or bad.

 

[JRB-Bldr]

If this program is a business-critical program, then I might suggest that you start by creating a reference data table for all of your existing data table indicies.

It is most often a tedious task, but well worth it if something totally destroys a critical index (CDX) file.

You can do this in a variety of ways via either the FoxPro command window or by writing a special program to extract and then save the information.

Once you have this information saved away, you can use it to re-build any critical CDX files if necessary.

If you should need more detailed information on how to do this, please feel free to contact me.

jrbbldr
jrbbldr@yahoo.com

 

[dgrewe]

here is a routine that will open a dbf and save the cdx information to a database memo field.
The top part is the FPW2.x way of doing it
The bottom part is the VFP way of doing it

http://ibs.internationalbid.net/home/dave/code_html/cdxstru.html

database structure the FPW2.x routine references

http://ibs.internationalbid.net/home/dave/code_html/sysindex_dbf.html

David W. Grewe
Dave@internationalbid.com

 

[tilltek]

Thanks guys for the fast response.

Ramani and Tesar,
I have a data file named test indexed on field tag field.
To re build an index I,m doing this.....

set safety off
delete file test.cdx
use test exclusive
index on ...etc etc


But I get a "select database window" presumably because the test file didn't load.
If set safety is ON I get a "MISSING CDX etc etc"
In either case, the file does not load.

Jrbbidr,
I used to have a directory with duplicate indexes and do just as you say but I have streamlined things so I don't have to distibute data bases. I just check to see if they exist and if not, I create new ones.
This alows me to keep all updates in one program file.

Dgrewe,
Thank but this isn't what I want to do. see note to Jrbbidr

 

[tesar]

If .cdx file is damaged,
try before recreation
on error ? chr(7),
to any error cause only beep, not error message.
dbf() say you, if use was succesfull,
and you may create index.

 

[dgrewe]

TillTek,
Here is a routine that will reindex all your tables and recreate fresh cdx's. It uses the CdxStru.prg and Sysindex.dbf I mentioned above

http://ibs.internationalbid.net/home/dave/code_html/sysindex.html

David W. Grewe
Dave@internationalbid.com

 

[tilltek]

* Thanks once again for the quick response to my question but
* perhaps I wasn't clear in the first place.
*
* My question is...
*
* If a data file has a non matching, corrupt or missing CDX,
* how can I load it to fix the index without user intervention?
*
* Trapping (? chr(7) etc.) is no good if the file won't load.
* Further, 99% of my end users would pick up the phone if the
* computer beeped at them, and red warning boxes flashing words
* like "INDEX IS CORRUPT, RECREAT INDEX" would have them calling
* the local priest.
*
* I have included a sample of how I manage it at the moment.
*
* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* My POS program is a stand alone .EXE with several .FXP files
* that only load if and when needed.
*
* Two of these files are UPDATE.FXP and MAKEDBF.FXP.
*
* UPDATE.FXP does most of the work while MAKEDBF.FXP is a whole
* set of procedures that CREATE data base file samples to be used
* by UPDATE.FXP. They could easily be made into one .FXP file.
*
* Below is an example, condensed into a few lines, of what I do
* to update data files and/or reindex them.
*
* This works well but you'll notice that a lot of the programming
* would be redundant if I could load a DBF that had a corrupt or
* missing CDX without trapping and/or end user intervention.
*
* Hence my question...
*
* If a data file has a non matching, corrupt or missing CDX,
* how can I load it to fix the index without user intervention?
*
* The following quickly written code will create or fix a file.
* The actual FXP files are much more elegant and use cursors
* as sample files instead of DBF's.
* They check/fix/update 40+ data files in under 1/2 a second
* (.044 seconds actualy) on Athalon 1200 with 512 meg
*
* It should run without change.
*-----------------------------------------------------------------
close databases

*----- First, check that the file exists. If not, make a new one
if .not. file("test.dbf&quot

*----- There may still be a cdx file so get rid of it
delete file test.cdx

*----- Create a sample datafile
create table testsamp (field1 C(10),field2 C(10),field3 C(10))

*----- Make new test.dbf and test.cdx
copy structure to test
use

*----- Get rid of the sample
delete file testsamp.dbf

endif

*----- I run DBRX.EXE here to fix header etc problems
* ! DBRX test.dbf

*----- The only thing we know for sure at this point is that we have;
TEST.DBF file and possibly TEST.CDX (which may be no good).

*----- CREATE testsamp.dbf
create table testsamp (field1 C(10),field2 C(10),field3 C(10))

append from test

delete file test.dbf
delete file test.cdx

*----- Make whatever indexes the file needs
index on field1 tag field1

*----- Make new test.dbf and test.cdx
copy to test cdx
use

*----- Get rid of the sample
delete file testsamp.dbf
delete file testsamp.cdx

*----- We now have a new test.df and cdx
return
*---------------------------------------------------------------

 

[davidtoranzo]

Hi,

You are positive sure when this routines are reached SAFETY is set OFF?

David.

 

[dgrewe]

* My question is...
*
* If a data file has a non matching, corrupt or missing CDX,
* how can I load it to fix the index without user intervention?
*
The answer to your question is inside the program SysIndex I mentioned above.

Once a CDX is out of sync with a database, it will never load and there is nothing you can do to load it without 3rd party assistance. See http:/

http://www.hallogram.com/foxpro

and look for some dbf / cdx repair programs
(FoxFix, Dsalvage, etc)

The only way to do it from FoxPro is to delete the cdx and recreate the cdx from scratch. to do that you need a library of indexs in the cdx (hince the CdxStur.prg to strip out the cdx info from a health dbf and the sysindex.dbf to record it.)

the SysIndex program will recreate the cdx with the infromation stored in the SysIndex.dbf memo field.

to open a dbf with a damaged cdx this is what you do. (see the SysIndex.prg)
close data
on error **
delete file xxx.CDX
use xxx.DBF
on error
index on xxx tag yyy of zzz.CDX

I hope this puts the pieces of the puzzle together for you. David W. Grewe
Dave@internationalbid.com

 

[tilltek]

Davidtoranzo, yes, set safety is off.

Dgrewe, I think what you are saying is what I believe to be true. A database with a bad, miss matched or otherwise damaged or missing CDX can not be loaded without some kind of user intervention.
What you are suggesting is in fact similar to the system I use except that I keep data base rebuilding (CREATE) routines and the relevant re indexing routines as procedures. A rough view of how I do it is in my bit of script in the message of 5th December.
I find this method easier to maintain as I only need to change one program, the stand alone MAKEDBF.FXP and call it from the main app when needed to do all changes and reindexes.

David G, I think we could all agree now that the answer to my question....

If a data file has a non matching, corrupt or missing CDX,
how can I load it to fix the index without user intervention

You can't.
Ken F

 

[dgrewe]

Ken F,
Your Wrong, You can open a dbf with a bad cdx without user intervention.

procedure errortrap
gnerror = error()
retrun

gnerror = 0
select 0
use xxx.dbf order zzzz
if gnerror > 0
use
delete file xxx.cdx
use xxx.dbf
index on yyyy tag zzzz
use xxx.dbf order zzzz
endif
on error
David W. Grewe
Dave@internationalbid.com

 

[infinitelo]

i hate to but in, (since i dont use cdx's)

can you append from a dbf with a broked cdx into a dbf with no index(temp with same structure)
erase original dbf,and cdx
then copy temp to original
use it and recreate cdx?

not hits no errors no user intervention.

 

[tilltek]

DGREWE
David,
You sound convincing so I gave it a second (seventh actually) shot, but...

When my indexing prg does this to a file with a missing CDX......
------------------------------------------------------
on error do errorhandler

store "dbffile.dbf" to MFILENAME2

set safety off

if file(MFILENAME2)

use &MFILENAME2 exclusive
delete tag all
index on code tag code

else

etc.
etc

*******************************************

My error file records this.....

error 1 Structural CDX file not found
trying to use &MFILENAME2 exclusive

error 2 No database is in USE.
trying to delete tag all


error 3 No database is in USE.
trying to index on code tag code


Now what?

 

[DSummZZZ]

The problem is in your error handling. If you are using a user-defined error handler at this point, FoxPro's internal error processing cannot deal with removal of the structural cdx reference, because your error routine fires instead. Since you're going to recreate the index(es) from scratch anyway, try it this way:

on error &&... use default error routine
store "dbffile.dbf" to MFILENAME2
store "dbffile.cdx" to MCDXNAME2

set safety off

if file(MFILENAME2)
ERASE (MCDXNAME2)
use &MFILENAME2 exclusive
index on code tag code
else
etc.
etc
endif
on error do errorhandler

Dave S.

 

[tilltek]

Thanks for the input Dave (Summzzz), Dave Grewe has been looking at it too and has helped me out along with a few others. What you say should work but for some reason it is not reliable (let's not argue, it's just not).
What I did realize while looking into Dave Grewe's suggestion last night was this....
If a dbf has a bad, out of whack or missing cdx, it is very easy to trap the error, but for whatever reason, the file sometimes is not loaded which is unacceptable.
What I realized was that even when the file does not load, it's cdx reference is always removed, so, putting two "use myfile" one after the other solves my problem.

USE MYFILE && cdx is missing, error trapped, cdx reference removed, but file maybe not loaded.
USE MYFILE && File loads no problem because cdx reference was removed by last line.

Thanks to both Davids.

 

[Guest_imported]

Lots of stuff here. This should be a fairly simple problem.

Somewhere you need to either set the default path or identify the path of the dbf or it's not going to load. Also, the name of the file needs to be in quotes for the File() command. If you are in a networked environment, I assume that you know that no one else can have the dbf open when you do this.

Try this:

SET SAFETY OFF
If FILE('C:\DATA\DATABASE.CDX') = .T.
ERASE C:\DATA\DATABASE.CDX
ENDIF
USE C:\DATA\DATABASE EXCLU
INDEX ON WHATEVER TAG WHATEVER
INDEX ON NEXT TAG NEXT
CLOSE DATA
USE C:\DATA\DATABASE SHARE