Index New Field in Clipper 5.3 DBF using FoxPro DOS

[SSDESIGN]

Need to use Clipper 5.3 DBFs in a Foxpro DOS application. Clipper DBFs opens, reindexes, and processes correctly in Foxpro DOS application. However, when a new field - phone_no1 - is added to the Clipper DBF using Foxpro DOS and is index on, the following error is generated:

ERROR: Variable 'PHONE_NO1' NOT FOUND
CODE: INDEX ON phone_no1 TAG posphone1
Missing: PHONE_NO1

The new field was added using COPY STRUCTURE EXTENDED and appending a new record for the new field. The new field can be use as any other field within Foxpro DOS except for indexing.

All suggestions on how to index on the new field will be appreciated...

Thanks
Bill....

 

[DSummZZZ]

I guess I'm not sure what you are trying to do.
"COPY STRUCTURE EXTENDED..." creates a new table with records containing the fields from the current table.

Which are you trying to create an index from?


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[TamarGranor]

I don't know for sure, but I'd be surprised if you can add a new index to a Clipper table using FoxPro.

Tamar

 

[SSDESIGN]

Dave, thanks for the reply....

What I need to do is to add a new field to a DBF coming from a Clipper 5.3 application using Foxpro DOS. Using COPY STRUCTURE EXTENDED is justy to create a modified DBF

In the past, I would do the following:

*******************************************************
USE (item_file) && the numodified dbf
COPY STRUCTURE EXTENDED TO new_fmt
USE new_fmt
APPEND BLANK
* This is the field I'll be indexing on....
REPLACE field_name WITH "phone_no1"
REPLACE field_type WITH 'c"
REPLACE field_len WITH 11
CLOSE ALL
CREATE temp from new_fmt && an empty dbf file
CLOSE ALL
* transfer data from old file to the new modified file
SELECT 2
USE (item_file)

SELECT 1
USE temp

APPEND FROM (item_file)

CLOSE ALL

* rename new file
DELETE FILE (item_file)
RENAME temp.dbf TO (item_file)

CLOSE ALL
**********************************************

I do not think the problem relates to the method of creation a new field but what part of a Clipper file prevents Foxpro from indexing a Clipper field and how do you get rid of the restriction?

Thanks....

 

[SSDESIGN]

One additional point...
All existing Clipper indexes can be reindexed under Foxpro DOS without a problem. Only new fields have the problem..
Thanks

 

[JRB-Bldr]

"Need to use Clipper 5.3 DBFs in a Foxpro DOS application."

If it were me, I'd save off the Clipper DBF's somewhere as an Archive and then use Foxpro to make 100% copies of the files in Foxpro format BEFORE attempting to make any other changes.

You can probably write a small program to do this in an automated manner so that it needn't be done manually.

Then when the data tables were in Foxpro format, you can use the standard Foxpro tools to add all the fields and indexes you need.

Good Luck,
JRB-Bldr

 

[SSDESIGN]

I tried to build Foxpro DOS DBFs using the following without correcting the indexing problem:

** Perform the equivalent of a PACK** get UNIQUE
** record number for a record
** use a modified sys(3) since sys(3) may not be ** ** ** UNIQUE on a 486
lcFile = curr_data + "\" + SUBSTR(SYS(3), 4, 4) + SUBSTR(SYS(2015), 7, 4)
lcFile_dbf = lcFile + ".DBF"
lcFile_fpt = lcFile + ".FPT"
USE POS EXCLUSIVE
DELETE TAG ALL
COPY TO (lcFile) FOR !DELETED()
USE
ERASE POS.DBF
IF FILE('POS.FPT')
COPY FILE POS.FPT TO (lcFile_fpt)
ERASE POS.FPT
ENDIF
RENAME &lcFile_dbf TO POS.DBF
IF FILE('&lcFile_fpt')
RENAME &lcFile_fpt TO POS.FPT
ENDIF
USE POS EXCLUSIVE
*DELETE TAG ALL
INDEX ON DELETED() TAG deleted

INDEX ON invnum TAG pos
INDEX ON ac+DTOS(date) TAG posac
INDEX ON pay+ac TAG pospay
INDEX ON DTOS(date) TAG posdate
INDEX ON name1 TAG posname1

*
* Generated error on new fields just added to Clipper
* DBFs. Error generated for both phone_no1 & category
* i.e. "INDEX ON phone_no1 TAG posphone1 PHONE_NO1"
*

INDEX ON phone_no1 TAG posphone1
INDEX ON category TAG poscat

Same problem with indexing after this rebuild...

What is the method you suggested??

Any suggestion would be appreciated...

Thanks

 

[DSummZZZ]

Let me point out that "COPY TO (lcFile) FOR !DELETED()" will also copy the memo file. Specifically copying it after that using:
IF FILE('POS.FPT')
COPY FILE POS.FPT TO (lcFile_fpt)
ERASE POS.FPT
ENDIF
will overwrite the valid one with the old one.

That said, I had no trouble with creating indexes using the COPY EXTENDED code you posted.

All I did was add the following two lines:
USE (item_file) EXCLUSIVE
INDEX ON phone_no1 TAG posphone1

But then, I was using a Fox table not a clipper table. Which as I remember, wouldn't make a difference unless you were to try using the table in clipper afterward.

The only thing I can think of would be some sort of stray invisible character is somehow getting in the field name.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[TamarGranor]

Just as a test, can you add the index interactive using MODI STRU?

Tamar

 

[SSDESIGN]

Thanks for all of the support...
An update is in order...

1) The new indexing command on phone_no1 was commented out and a new field was added to the CUSTOMER DBF - comment1 C(30). The new CUSTOMER.COMMENT1 field was then indexed using INDEX comment1 TAB custcom1.

THE SAME ERROR #12 OCCURRED with the new indexing command....... So with a new indexing procedure entered new at a different point in the program, the same error occurred.

When I open the customer dbf with the new "comment1" field in the view window and manually entered the index command - INDEX command1 TAG custcom1 - in the command window, I received:
"Unrecognized phase/keyword in command."

I now think a clipper file does not support a new field for indexing in Foxpro DOS even when the DBF is copied over in Foxpro DOS.

Any additional comment would be appreciated... Please keep them coming.

 

[TamarGranor]

If you don't need to maintain Clipper compatibility, then try using COPY TO ... TYPE FOXPLUS. While that's actually a slightly older format, it'll make sure that you store in a Fox-compatible format.

Alternatively, if you have access to Visual FoxPro, try COPY TO ... TYPE FOX2X.

Tamar

 

[JRB-Bldr]

"INDEX command1 TAG custcom1"

I am not sure if you made a typo in your posting above, but the command should be: INDEX ON command1 TAG custcom1

Without the ON you would indeed get an "Unrecognized phase/keyword in command." error message.

Good Luck,
JRB-Bldr

 

[SSDESIGN]

You got me....
I manually retested the "INDEX ON comment1 TAG custcom1" using "ON" and it did index as expected... I also checked all indexing command lines in the application and found them with correct coding....

Which bring us back to the original problem:
"Indexing a new field added to a Foxpro DOS DBF created from a Clipper DBF results in an error...."

ERROR: Variable 'PHONE_NO1' NOT FOUND
CODE: INDEX ON phone_no1 TAG posphone1
Missing: PHONE_NO1

Performed manually - No problem; Performed within the application - A problem.

I tried several setting with "SET COMPATIBLE" without correcting the problem - DB4 and FOXPLUS. Would this be an avenue to continue to pursue?

Since this will be done a number of times during testing of changes made to the Foxpro DOS application, my goal is to have the application import the current Clipper DBFs and not rely on the client to manually import the DBFs.

Thanks to all of you for the feedback. Additional suggestions would be appreciated...

 

[JRB-Bldr]

Missing: PHONE_NO1

First I would check for the existence of the field named EXACTLY as you intend: Phone_No1.
Perhaps the field ended up being spelled slightly differently for some reason.

You could either manually go to MODIFY STRUCTURE for the table and examine the fields (and their spelling).

Or you could use the commands...

  USE POS
  SELECT POS
  [B]?TYPE('POS.Phone_No1')[/B]

If you find that the field PHONE_No1 is not present (type "U" = Undefined), you have found the problem.

Once you have confirmed 100% that the field, spelled as intended, exists then you should be able to perform your INDEX command.

Alternately you can just create a whole new table and intentionally include the Phone_No1 field.
NOTE - Make a backup of the Original Table as an archive - JUST IN CASE.

   cOrigDBF = <Pathed POS>
   USE (cOrigDBF) ALIAS POS

   * --- Create New Temp Table ---
   cTempDBF = <Pathed Temp>
   SELECT POS.*,;
     SPACE(12) AS Phone_No1;
     FROM POS;
     INTO TABLE (cTempDBF)

   SELECT POS
   USE
   
   * --- Replace Original Table With New Table ---
   SELECT Temp
   IF RECCOUNT() > 0
      COPY TO (cOrigDBF)
   ENDIF
   USE
   ERASE (cTempDBF)

   * --- Build All New Indicies ---
   USE (cOrigDBF) ALIAS POS EXCL
   SELECT POS
   INDEX ON <whatever> TAG <Whatever>

Good Luck,
JRB-Bldr

 

[SSDESIGN]

And now for the next update...

I contacted Grafx Software - the current owners of Clipper 5.3 - and found out the following:
1) Clipper 5.3 is compatible with Foxpro DOS 2.5.
2) Clipper 5.3 in not compatible with Foxpro DOS 2.6 in the area of indexing a Foxpro DOS field created in a Clipper DBF. It appears as if Foxpro 2.6 processes the DBF as a Clipper DBF containing a Foxpro field which is not recognized by Foxpro DOS 2.6 when indexing.
3) If the Clipper DBF is copied without the CDX file, you will receive an error when you try to index and Foxpro will try to locate the CDX for the DBF anywhere on your system - i.e. It will copy the Clipper CDX. if it can finds it.
4) jrbbkir - see above - has the solution. Use SELECT SQL to create and insert the new fields with the resulting DBF in a separate directory. You can use the same DBF name as the Clipper DBF if you like. NOTE: When this happens, Foxpro DOS will copy the Clipper DBF over as well as a BAK file. As a precaution, I deleted the Clipper backup file.
5) Once created, the new Foxpro DOS DBF with the new fields can be indexed without an error....
Thanks to all of you for the support. The suggestions have been great.
It's party time....

 

[Olaf Doschke]

What you do is actually creating a new table from the structure data, so the source dbf being clipper will not make any difference at that point. As COPY STRUCTURE EXTENDED worked and CREATE TABLE from the structure data also worked, I could think of three things in your way:

a) you need the new table open exclusive, you do use it without the EXCLUSIVE clause.
b) you need to select the new table before doing INDEX ON. If another table is selected INDEX ON is tried on the other table and if that has no field of that name you get your error.
c) The new record you add to new_fmt may not be sufficient and the field may not have been created. Check temp if it really has the new field.

If you take care of a)-c) this should finally work:
USE temp EXCLUSIVE IN 0
SELECT temp
INDEX ON phone_no1 TAG posphone1

Bye, Olaf.