Rename a foxpro field programatically

[snix2]

I needed to rename a field in a foxpro table programatically and without touching the data. I wrote the following code which modifies the header information.

Disclaimer: Back up your data! This code uses low-level functions to manipulate the database header. I can't accept any responsibility for damage to your data.


CLOSE ALL

jcDBFName = 'enter path and filename of dbf file here (including .DBF)'
jcOldVal = 'enter old field name here (max 10 chars)'
jcNewVal = 'enter new Field name here(max 10 chars)'

jnRetVal = changeFld(dbfName, jcOldVal, jcNewVal)

do case
case jnRetVal = -1
jcMessage = 'Incorrect number of parameters passed'
case jnRetVal = -2
jcMessage = 'Could not open or create output file'
case jnRetVal = -3
jcMessage = 'Field not found'
case jnRetVal > 0
jcMessage = alltrim(str(jnRetVal)) + ' bytes replaced successfully'
otherwise
jcMessage = 'Unknown error returned.'
endcase

wait window jcMessage


********************************************************
FUNCTION ChangeFld
********************************************************
*
* Written by: Nick G - September 2004
*
* Parameters: DBF Name (full path and filename)
* Old Field Name (character)
* New Field Name (character)
* Returns: number of bytes written, or
* negative error value
*
********************************************************

FUNCTION changeFld
PARAMETERS dbfname, jcOldVal, jcNewVal

IF EMPTY(dbfname) OR EMPTY(jcOldVal) OR EMPTY(jcNewVal)
RETURN -1
ELSE
fh = FOPEN(dbfname,12)
ENDIF

IF fh < 0
WAIT 'Cannot open or create output file' WINDOW NOWAIT
RETURN -2
ELSE
* Locate the offset position of the start of the data itself
=FSEEK(fh, 8,0)
jcdataoff = FREAD(fh, 2)
jcoffset = dec2hex(ASC(RIGHT(jcdataoff, 1))) + dec2hex(ASC(LEFT(jcdataoff, 1)))
jnoffset = hex2dec(ALLTRIM(STR(VAL(jcoffset))))

* Scan through Fields until match found, then replace it with new value
FOR mvcount = 32 TO (jnoffset-33) STEP 32
=FSEEK(fh, mvcount, 0)
jcField = ALLTRIM(STRTRAN(FREAD(fh, 11), CHR(0), ''))
* wait window jcField
IF ALLTRIM(UPPER(jcField)) == ALLTRIM(UPPER(jcOldVal))
* Field found, replace it with 'jcNewVal'
=FSEEK(fh, mvcount, 0)
lnBytes=FWRITE(fh, STRTRAN(PADR(jcNewVal, 10), ' ', CHR(0)))
=FCLOSE(fh)
RETURN lnBytes
ENDIF
ENDFOR
ENDIF
RETURN -3

********************************************************
FUNCTION dec2hex
********************************************************
*
* Written by: Ralph P. Morse Jr. (c) 1995
* RalphMorse@msn.com
*
* Parameter: Numeric Input From 0 To 255
*
* Returns: Character string from "00" to "FF"
*
********************************************************
PARAMETER pminput
PRIVATE jcbit1, jcbit2
STORE INT(pminput/16) TO jcbit1
STORE pminput - (jcbit1 * 16) TO jcbit2
RETURN CHR(IIF(jcbit1 > 9, 55, 48) + jcbit1) + ;
CHR(IIF(jcbit2 > 9, 55, 48) + jcbit2)


********************************************************
FUNCTION hex2dec
********************************************************
*
* Written by: Ralph P. Morse Jr. (c) 1995
* RalphMorse@msn.com
*
* Parameter: Character string from "00" to "FF"
*
* Returns: Numeric value (0-255)
*
********************************************************
PARAMETER pchexcode
PRIVATE jchex1, jchex2
STORE ASC(UPPER(SUBSTR(pchexcode, 1, 1))) TO jchex1
STORE ASC(UPPER(SUBSTR(pchexcode, 2, 1))) TO jchex2
STORE jchex1 - IIF(jchex1 > 60, 55, 48) TO jchex1
STORE jchex2 - IIF(jchex2 > 60, 55, 48) TO jchex2
RETURN jchex1 * 16 + jchex2

 

[rgbean]

However, I don't see where you have addressed the problem when this field is part of an index expression. This can complicate the generic situation when you have to worry about both .IDX and .CDX files.

Have you tried this on a memo field? Do you need to alter the .FPT also?

Rick

 

[JRB-Bldr]

Another method that I have used in the past is something similar to what follows:

* --- Original Table ---
SELECT OldDBF

* --- Get Original Table Field Names ---
mnFldCnt = AFIELDS(aryFlds)

* --- Change Desired Field Name(s) In Array ---
FOR i = 1 TO mnFldCnt
   IF aryFlds(i,1) = "WHATEVER" && locate desired Field Name(s) in array aryFlds
      aryFlds(i,1) = "NEWFLDNAM" && change Field Name(s) as desired
   ENDIF
ENDFOR

* --- Create New Table From Modified List of Fields ---
mcDBF = <full path description> + "Temp.dbf"
CREATE TABLE (mcDBF) FROM ARRAY aryFlds

* --- Bring Previous Data Into New Table ---
SELECT Temp
APPEND FROM DBF('Temp')

* --- Get Rid Of Original Table ---
SELECT OldDBF
USE
ERASE <fully pathed orig. table name>
ERASE <fully pathed orig. table cds file name>

* --- Rename New Table ---
SELECT Temp
USE
RENAME <fully pathed new table name> TO <fully pathed orig. table name>

* --- If Necessary, Re-Build Table Indicies ---
USE <fully pathed new table name> IN 0 ALIAS NewDBF EXCLUSIVE
SELECT NewDBF
INDEX ON <whatever> TAG <whatever>

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[snix2]

It doesn't modify the Indexes; thanks for pointing that out. Modifying memo fields is fine. I'll look at the indexing, if i can find info on the structure...

 

[JRB-Bldr]

By using TAG() in conjunction with SYS(14) you should be able to determine the pre-change Index TAG's and Expressions.

Then modify the index expressions as necessary if one of expression field names changes.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[craigsboyd]

While the code given is useful in some respects for understanding the header section of a dbf and how it can be programmatically manipulated, I would like to know what the real-world scenario is that a field has to be renamed without "touching the data"? I have in times past used the jrbbldr approach. If there are no real-world situations where the low-level modification would come into play, then are there any advantages to it's use over what jrbbldr posted (speed would perhaps be one)?

http://www.sweetpotatosoftware.com