INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Change Field Name in Database

Change Field Name in Database

(OP)
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.

RE: Change Field Name in Database

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.

RE: Change Field Name in Database

(OP)
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.

RE: Change Field Name in Database

I found this on www.the-oasis.net

CODE

*+²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²²
*+
*+    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 ====-----------------------
      http://www.dejanews.com/     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

RE: Change Field Name in Database

(OP)
LyndonOHRC  - Thank You.

RE: Change Field Name in Database

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

RE: Change Field Name in Database

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?

RE: Change Field Name in Database

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close