Change Field Name in Database

[Sware]

I'm developing a Windows application (VB6) that will use a Clipper .DBF database. I've discovered that one of the field names conflicts with a reserved word in the database driver I'm using in the Windows application.

How can I use my existing Clipper program to create a copy of the database file with a changed field name? I assume the general approach is to:

1) Use COPY FILE to create a copy with a new name
2) Change the conflicting field name in the copy
3) Outside of Clipper, rename the file to its original name
for use with the Windows application.

I'm looking for Clipper code to do 2). Thanks.

 

[DTracy]

The way I would do this:

1) Use Windows or DOS to copy the yourfile.dbf and rename.
2) Use DBU to open the dbf and change the field name.
3) Proceed with the VB stuff.

 

[Sware]

DTracy,

Thanks for your input. I would use your approach if I wanted to make the field name change for just myself (and I will use it to develop a changed file for testing of the VB application).

However, the VB application will be used by multiple users with existing databases and the Clipper program. What I plan to do is give them an updated version of the Clipper program that includes an automated conversion capability for the .dbf file (along with the conversion of some other files that I've already implemented).

Therefore, I need to do the field name change for the .dbf file within the Clipper program.

 

[LyndonOHRC]

I found this on

http://www.the-oasis.net

*+²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²
*+
*+    Source Module => CHGFIELD.PRG
*+
*+    Functions: Function CHANGEFLD()
*+               Function RenFld()
*+               Procedure RenameFields()
*+
*+    Reformatted by Click! 1.13b3 on May-25-1998 at  2:34 pm
*+
*+²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²

/*
On Wed, 03 Dec 1997 06:02:01 GMT, mhamilton@bunge.com.au (Mab) wrote:

>In article <19971203041000.XAA15157@ladder01.news.aol.com>, someone calling themselves jaanth@aol.com (JAAnth) wrote:
>>>I am part-way through writing a little utility to rename a bunch of 
>>>fields in tables in my database by creating a new table, then copying 
>>>the old records across field-by-field.
>
>>Take a look at the \clipper5\source\dbu\DBUSTRU.prg. It creates a new table
>>structure with the updated field names, then does a SDF export/import to move
>>the data from the old table to the new table.
>
>Yep - I've already looked there. Is that actually any faster than a 
>record-by-record copy?
>

Try this simple routine, it will change field name before you blink an
eye :). This is the basic thing, so feel free to modify the routine
for a complex structure of DBF eg pass the nField and cFName in
arrays.
*/

#define FIELD_ENTRY_SIZE 32
#define FIELD_NAME_SIZE  11
#include "FILEIO.CH"
// Syntax : lResult := ChangeFld( cDBF, nField, cFName )
// Where : cDBF := DBF File Name
//                 nField := The field number to be changed
//                 cFName := New Field Name
//                  lResult := .T. if successsful, otherwise .F.

*+±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±
*+
*+    Function CHANGEFLD()
*+
*+±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±
*+
function CHANGEFLD( cDBF, nField, cFName )

local nPos    := ( nField * FIELD_ENTRY_SIZE )
local nPos
local nHandle
if file( cDBF )
   // Open Database
   if ( nHandle := fopen( cDBF, FO_READWRITE ) ) <> - 1
      // Goto File Offset of the nField-th Field
      fseek( nHandle, nPos, FS_SET )
      // Write New Field Name Thereon
      fwrite( nHandle, padr( cFName, 10 ) + chr( 0 ), FIELD_NAME_SIZE )
      // Close Database
      fclose( nHandle )
      // Return True = Success
      return .T.
   endif
endif
return .F.

/*
Elp

The fastest way of renaming is to use the binary file functions
found in Clipper.

For your example to work, try something like this.
*/

#include 'fileio.ch'

*+±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±
*+
*+    Function RenFld()
*+
*+±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±
*+
function RenFld( cFile, ;               // The filename e.g. 'cust.dbf'
                 aFldList )             // Two dimensional array containing
// {{old1, new1}[, {old2, new1}, ...]}
local nHandle := fopen( cFile )
local cBuffer
local nPos
local nFld
local nFldCnt
if nHandle >= 0
   fseek( nHandle, 0 )
   // 1024 chars will suffice for 63 fields in dbf
   cBuffer := freadstr( nHandle, 1024 )
   nFldCnt := len( aFldList )
   for nFld := 1 to nFldCnt
      if !empty( nPos := at( upper( trim( aFldList[ nPos, 1 ] ) ), ;
                         cBuffer )
         cBuffer := ;
                 stuff( cBuffer, ;
                 nPos, ;
                 10, ;
                 padr( upper( trim( aFldList[ nPos, 2 ] ) ), ;
                 10, ;
                 chr( 0 ) ) )
      endif
   next
   if nFldCnt > 0
      fseek( nHandle, 0 )
      fwrite( nHandle, cBuffer )
   endif
   fclose( nHandle )
endif
return NIL

/*
I have not tested this so first try it on a dummy dbf,
but there is no reason why it should not work.

With this approach there is no records to copy and you
will get sub-second results.

Nice one to change your DBU with especially if working
with large dbf's.

I hope this will help.

Regards

Johan Nel
*/

/*
-------------------==== Posted via Deja News ====-----------------------
      [URL unfurl="true"]http://www.dejanews.com/[/URL]     Search, Read, Post to Usenet

Ok! Thanx to everyone who replied! Here is the code that I grabbed 
from one kind respondant, which (after some hacking) does exactly what 
he described - renames a bunch of fields in a dbf all in one hit:
*/

/*
   Call like this:

   RenameFields('Table.dbf', { { 'OldField1', 'NewField1'}, ;
                               { 'OldField2', 'NewField2'} } )
*/

*+±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±
*+
*+    Procedure RenameFields()
*+
*+±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±±
*+
procedure RenameFields( TableName, FieldList )

local i
local n
local FieldIndex
local FieldCount
local Buffer
local Table      := fopen( TableName, O_READWRITE )

if ( Table >= 0 )
   fseek( Table, 0 )

   // 1024 chars will suffice for 63 fields in dbf
   Buffer     := space( 1024 )
   n          := fread( Table, @Buffer, 1024 )
   FieldCount := len( FieldList )

   if ( FieldCount > 0 )
      for i := 1 to FieldCount
         FieldIndex := at( upper( trim( FieldList[ i, 1 ] ) ), Buffer )

         if ( FieldIndex > 0 )
            Buffer := stuff( Buffer, FieldIndex, 10, ;
                             padr( trim( FieldList[ i, 2 ] ), 10, chr( 0 ) ) )
         endif
      next

      fseek( Table, 0 )
      fwrite( Table, Buffer, n )
   endif
   fclose( Table )
endif
return

/*
Sure hope this helps someone else out there!!!

|~\  /~| /~~| |~|   The .sig wears a ring of polymorph! --More--
|  \/  |/ / |_| |__ The .login hits! The .cshrc bites!
|      ' /| |_| | / ________________________________________________
| |\/|  /\  | |  /  Official member of STI:
| |  |_/  \_| | /           The Search for Terrestrial Intelligence!
=\|===========|/==========- The Mabster:  mhamilton@bunge.com.au -==
*/

*+ EOF: CHGFIELD.PRG

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[Sware]

LyndonOHRC - Thank You.

 

[LyndonOHRC]

No prob - let us know if it works.

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[fbizzell]

I was interested in your use of VB6 to develop windows application using Clipper DBF files. How is that project going and is there anything special needed for VB to read and write to the Clipper DBF files?

 

[Sware]

fbizzell, what's need is to use a Connection with a Driver that supports .DBF files. There are some dBase drivers that I had trouble with for various reasons. I ended up using a Visual FoxPro driver, of which there are two to choose from: ODBC and OLE. The OLE driver (vfpoledb) is preferred. If you Google "Connection String" you will find several sites that provide Connection String examples.