PRB: After SELECT-SQL FoxPro Deletes Compound Structural Index

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, and 2.5b
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, and 2.5b

SYMPTOMS

If you issue an SQL SELECT statement that outputs to a database file that has a compound structural index attached to it, the index is deleted from the disk drive. The new database file will not contain any reference to the compound structural index.

CAUSE

When an SQL SELECT statement outputs to an existing database, FoxPro re-creates the entire file. Since the header record of the database file is created anew, no reference to the compound structural index is retained. FoxPro displays a warning that it will overwrite the current database file. FoxPro deletes the file to eliminate the possibility of retaining a compound structural index that contains tags that may not match the new database structure.

RESOLUTION

To retain the index file on the target database after performing the SQL SELECT statement, do one of the following:

• Instead of using a structural compound index, create a nonstructural compound (compact) .CDX index file. This index will have a different name from the database.

  -or-

• Use an .IDX index file with the database.

MORE INFORMATION

Steps to Reproduce Behavior

1. In the Command window, issue the following commands:

         USE CLIENTS.DBF
         COPY TO XCLIENTS.DBF
         USE XCLIENTS.DBF
   

2. From the Database menu, choose Setup.

3. Create a compound structural index with a tag on the State field.

4. In the Command window, issue the following commands:

         USE CLIENTS.DBF
         SELECT * FROM CLIENTS INTO TABLE XCLIENTS.DBF
   

5. A warning message appears saying the target database already exists and asks if you want to overwrite XCLIENTS.DBF. Choose Yes.

6. In the Command window, type:

         DIR XCLIENTS.*