Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
DATABASES, TABLES AND QUERIES >>  INDEXING IN A MULTI-USER ENVIRONMENT.

  Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy



It should be noted that you can create indexes in a shared environment, but the actual need to do this is quite rare. In most cases, the INDEX command need not be used. This FAQ is for new developers who are just now, moving to a multi user environment. For those rare instances where you actually do need to create an index over a shared table, go to this link: http://www.foxite.com/articles/read.aspx?id=14&document=who-says-you-cant-build-indexes-in-a-shared-environment

In a shared environment your purpose is to open your data to a number of users at the same time. When you open a table, you issue the command:

USE MyTable IN 0 SHARED


or you drop the table onto the dataenvironment of a form and you make sure that the .Exclusive property of that table is set to .f. Doing this allows other users to access that same table at the same time.

When you open a table using either of these methods, you also open that table’s structural index. This is a critical piece of information for you. The structural index is the index that you create using the table designer. Yes, you can create structural indexes programmatically, but for the purposes of this FAQ, we will stick with the following method.

Go into your project, select the data tab, click on one of your tables and then click the “Modify” button. This will open the table designer for that table. Now click on the second tab – the “Indexes” tab. When you create indexes here, you add them to a structural complex index (CDX file) that is opened when ever the table is opened. The key here is that these indexes will be updated when ever a record is modified, deleted or added. The only time you need to issue a REINDEX command is when your index becomes damaged. (We will look at that issue later.)

One more critical piece of information you need to know is that Rushmore will use the indexes in your structural index to optimize your code (read “speed it up”) but only if those indexes do not contain a filter. Rushmore will also use a series of indexes to optimize your code. This means that you can create simple indexes over each indexable field. (For example, you cannot index memo or general fields.) If you are ever going to want to search the table using a specific field, add an index over that field and Rushmore will use it.

If you are not currently using structural indexes, wait until all of the users are off-line (so you can access your tables exclusively) and go ahead and create the indexes you need using the table designer.

Now that you have your indexes created, for the most part you should not really need to create an index using the INDEX ON command. Instead, you simply select the index you wish to use. For example, let’s say you wish to find a record where value in the RecordID field is equal to the value of a variable called lnRecordID. You have already created an index in your structural index called RecordIDTag

IF NOT USED([MyTable])
   USE MyTable IN 0 SHARED
ENDIF
SET ORDER TO TAG RecordIDTag
IF SEEK(lnRecordID)
   WAIT WINDOW [Found the record]
ELSE
   WAIT WINDOW [Record Not Found]
ENDIF


Now, what happens when you add a record?

lnRecordID = 99999
APPEND BLANK IN [MyTable]
REPLACE RecordID WITH lnRecordID


If the index is up-to-date, you should be able to seek that record.

IF SEEK(lnRecordID,[MyTable],[RecordIDTag])
   WAIT WINDOW [Found the record]
ELSE
   WAIT WINDOW [Record Not Found]
ENDIF


You will note that I do not create any indexes before the SEEK() function, nor do I even REINDEX. I don’t need to. When the table opens, it opens the structural index as well and any changes to the table are automatically reflected in the index (and you didn’t have to write any code to make this happen.) Go ahead and play with this now.

* Now change the field value and seek that new value.  It should find it.

REPLACE ALL MyTable.RecordID WITH 99998 ;
   FOR  MyTable.RecordID = 99999 ;
IN [MyTable]
IF SEEK(99998,[MyTable],[RecordIDTag])
   WAIT WINDOW [Found the record]
ELSE
   WAIT WINDOW [Record Not Found]
ENDIF

* and now delete this record – and seek it again – it should not find the record.

SET DELETED ON
DELETE ALL FOR MyTable.RecordID = 99998 ;
   IN [MyTable]

IF SEEK(99998,[MyTable],[RecordIDTag])
   WAIT WINDOW [Found the record]
ELSE
   WAIT WINDOW [Record Not Found]
ENDIF


Now, there are times when your index can be corrupted. Typically this occurs because of a faulty network, a power outage or a user who exits the app improperly using Ctrl+Alt+Del. The remedies for this are

1 Keep your network hardware and software up to date and well maintained.
2 Install a UPS on every computer (especially on the server)
3 Go find a very big stick and have a pointed chat with that user who is using the “three-fingered-salute” to exit the app.
4 Make sure you have a good backup – indexes are not the only thing that can be corrupted.

Unfortunately, you are now left with a corrupt index and it will need fixing. Again, you cannot INDEX ON or REINDEX when other users are using the app. Kick everybody off the app and then open the table exclusively and attempt a REINDEX or better still, create a REINDEX utility that programmatically erases and then re-creates your structural indexes for you. You would give this utility to your Database Administrator (or your Server Administrator) and when ever you do encounter index corruption have your Admin run this utility.

Now, take a really good look at the help file for the INDEX ON command. You will need this to programatically re-create your structural indexes.

Hope this helps.

FEEDBACK

Binod Binani @ 2/22/2007 12:36:08 PM
Is there any way to open a Table without CDX ie:

Mytable.dbf has Compund Index (CDX) with 2 TAGs
& Index Damageds

When I try to open Mytable.dbf
Error windows : Index Does not Match Recreate Index (114).
Or
Str. CDX file not found (1707)

i wish open the file Some how without this error message Box.

Earlier I was traping Error No. & Simpe Return as per requirements.

Or is there any way so that one can trace that weather the dbf file has
CDX file or not

Binod binani
India

Ken Murphy @ 2/22/2007 2:51:58 PM
Binod,

The error that you are getting is because your CDX file is corrupt. The only real way to fix this is to delete the CDX file and re-create it. Thankfully, there is a way to do this. Take a look at the GENDBC utility that ships with VFP. When you run GENDBC over your database, it will generate a program that will allow you to re-create an empty version of your database. TAKE NOTE - THIS PROGRAM WILL RECREATE AN EMPTY DATABASE. There will be no data in it. If you recreate your database in the same directory as your existing database, it will overwrite your database and you will be left with no data.

Run GENDBC over your database and then look at the program it generates. Create a new program called "RecreateIndexes.prg" and add ERASE *.CDX as the first line. Now go back into the program generated by GENDBC and copy and paste all of the INDEX ON commands into your RecreateIndexes program. Add the appropriate USE MyTable IN 0 EXCLUSIVE and USE IN SELECT([MyTable]) commands before and after the INDEX ON commands for that table.

Now, when ever you run into a corrupt database, you can run your RecreateIndex.prg and it will properly recreate all of the indexes that you need. Note that in order to run this program, you are going to be opening the tables exclusively. This means that no other users can be using the app when you recreate the indexes. This shouldn't be a problem though - if the indexes are corrupt, it is likely that your users won't be able to run your app anyway.

@ 1/21/2008 3:30:38 PM
When you try to open with VFP some table which has structural index associated with it and this index file is missing you get ['Structural CDX file not found' Ignore/Cancel]. You hit 'Ignore' and you can continue without problems. When this happens with application run from exe you'll get error 1707, which won't make the users quite happy, but if you intend to delete the CDX file and recreate it you have to cope withit.

I know two ways. The first is to do what VFP does - ignore the error and open it. !!!NOTE!!! Once you try to open the table and ignore the error, you'll never see it again and the table no longer has CDX associated with it. And this is what I use:

m.lcDataTable ='C:\myTable.dbf'
m.lcIndexFile = 'C:\myTable.cdx'
IF USED('currDataTable')
USE IN SELECT('currDataTable')
ENDIF
DELETE FILE (m.lcIndexFile)
TRY
USE (m.lcdataTable) IN 0 EXCLUSIVE ALIAS currDataTable
CATCH TO oException
IF oException.ErrorNo = 1707
* tmpErr = oException.LineContents
* &tmpErr && I had some problems with this code so I just copied the line again
USE (m.dataTable) IN 0 EXCLUSIVE ALIAS currDataTable && once you try to open the table and go through the error, you'll be able to open it without problems !!!
ELSE
errHandler(oException.ErrorNo,oException.Message,oException.LineContents,oException.Procedure,oException.LineNo)
ENDIF
ENDTRY


The second one is to change the 28th byte of the table header (VFP Table File Structures can be seen at http://msdn2.microsoft.com/en-us/library/st4a0s68(VS.80).aspx ) with low level function.

You can choose which one to use, but either way you have to be able to open the table EXCLUSIVE.

I have tables designed to describe DBs and tables (structures, indexes, relations..) which I use when reindexing. How to save the index expression you'll need is up to you.

And one more - I don't find quite good. Some time ago I was using it to reindex, but in some cases it doubled the size of the CDX file. The tags were correct, the indexes were correct, but the files size was doubled. I never figured out why it happened, but I think it just didn't delete the old tags, although the new ones had the same names.



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: