Cambiar nombre a campos en tablas (SOLUCIONADO)

[Compuin]

Saludos

Alguien tiene una funcion que permita cambiar el nombre de los campos en varias tablas. Algo asi como :

NFCTACLI por NFACCCLI (aqui se cambia CTA )

y

NFACCCLI por NFACCCUS (Aqui se cambia CLI)

Esto habria que hacerlo para mas de 300 tablas

Gracias de antemano

[MauroArevalo]

Compuin:

Yo lo hago así, alguna vez lo saque del foro y lo adapte a mi código.

Code: Select all  Expand view  RUN

DBCLOSEALL()
USE(Pub:HREM) EXCLUSIVE NEW
COPY TO COPIAREM1              //Saco copia temporal de la Base de Datos
IF !NETERR()
  StruVieja:=DBSTRUCT()         //Copia extructura actual
  StruNueva:=(Pub:HR)           //Structura nueva de la Base de Datos
  StrVieja:=LEN(StruVieja)
  StrNueva:=LEN(StruNueva)
  IF StrVieja#StrNueva          //Verifica si hay diferencia crea una nueva temporal y la cambia..
     COPY TO COPIAREM
     DBCREATE("TEMPOREM",StruNueva)
     USE TEMPOREM
     CMS=Pub:DIRARCEMP+Pub:CODEMP+"\HR"+Pub:CODEMP+Pub:ANO
     APPE FROM &CMS
     COPY TO (Pub:HREM)
    ELSE
     IF StrVieja==StrNueva   //Verifica si hay diferencia crea una nueva temporal y la cambia..
       COPY TO COPIAREM
       DBCREATE("TEMPOREM",StruNueva)
       USE TEMPOREM
       CMS=Pub:DIRARCEMP+Pub:CODEMP+"\HR"+Pub:CODEMP+Pub:ANO
       APPE FROM &CMS
       COPY TO (Pub:HREM)
     ENDIF
   ENDIF
 ELSE
ENDIF

 

[nageswaragunupudi]

Please use this function. This is very fast.

Code: Select all  Expand view  RUN

function FW_DbfRenameField( cDbf, cField, cReplace )

   local lSuccess := .f.
   local hFile, nHeaderSize, nAt
   local cBuf     := Space( 32 )

   if !File( cDbf )
      ? cDbf + " not found"
      return .f.
   endif
   if Empty( cField ) .or. Empty( cReplace ) .or. ;
      Len( cField ) > 10 .or. Len( cReplace ) > 10
      ? "Invalid field names"
      return .f.
   endif

   cField            := PADR( UPPER( cField ),   11, CHR( 0 ) )
   cReplace          := PADR( UPPER( cReplace ), 11, CHR( 0 ) )

   if cField == cReplace
      ? "Invalid field names"
      return .f.
   endif

   if ( hFile := FOpen( cDbf, FO_READWRITE + FO_EXCLUSIVE ) ) >= 0
      if FRead( hFile, @cBuf, 32 ) == 32
         nHeaderSize := BIN2I( SUBSTR( cBuf, 9, 2 ) )
         if nHeaderSize > 32
            cBuf     := Space( nHeaderSize )
            FSeek( hFile, 0, FS_SET )
            FRead( hFile, @cBuf, nHeaderSize )
            nAt      := At( cField, cBuf )
            if nAt > 0 .and. At( cReplace, cBuf ) == 0
               FSeek( hFile, nAt - 1, FS_SET )
               if FWrite( hFile, cReplace, 11 ) == 11
                  lSuccess := .t.
               endif
            endif
         endif
      endif
      Fclose( hFile )
   else
      ? "Can not open " + cDbf + " exclusively"
   endif

return lSuccess
 

[Compuin]

Please use this function. This is very fast.

Code: Select all  Expand view  RUN

function FW_DbfRenameField( cDbf, cField, cReplace )

   local lSuccess := .f.
   local hFile, nHeaderSize, nAt
   local cBuf     := Space( 32 )

   if !File( cDbf )
      ? cDbf + " not found"
      return .f.
   endif
   if Empty( cField ) .or. Empty( cReplace ) .or. ;
      Len( cField ) > 10 .or. Len( cReplace ) > 10
      ? "Invalid field names"
      return .f.
   endif

   cField            := PADR( UPPER( cField ),   11, CHR( 0 ) )
   cReplace          := PADR( UPPER( cReplace ), 11, CHR( 0 ) )

   if cField == cReplace
      ? "Invalid field names"
      return .f.
   endif

   if ( hFile := FOpen( cDbf, FO_READWRITE + FO_EXCLUSIVE ) ) >= 0
      if FRead( hFile, @cBuf, 32 ) == 32
         nHeaderSize := BIN2I( SUBSTR( cBuf, 9, 2 ) )
         if nHeaderSize > 32
            cBuf     := Space( nHeaderSize )
            FSeek( hFile, 0, FS_SET )
            FRead( hFile, @cBuf, nHeaderSize )
            nAt      := At( cField, cBuf )
            if nAt > 0 .and. At( cReplace, cBuf ) == 0
               FSeek( hFile, nAt - 1, FS_SET )
               if FWrite( hFile, cReplace, 11 ) == 11
                  lSuccess := .t.
               endif
            endif
         endif
      endif
      Fclose( hFile )
   else
      ? "Can not open " + cDbf + " exclusively"
   endif

return lSuccess
 

Mr. Rao, thanks!

I got this error

Application
===========
Path and name: C:\fwh1705\samples\testconv.exe (32 bits)
Size: 3,738,112 bytes
Compiler version: Harbour 3.2.0dev (r1801051438)
FiveWin version: FWH 17.05
C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 0 secs
Error occurred at: 11/04/18, 10:04:26
Error description: Error BASE/1003 Variable does not exist: FO_READWRITE

Stack Calls
===========
Called from: testconv.prg => FW_DBFRENAMEFIELD( 48 )
Called from: testconv.prg => MAIN( 16 )

System
======
CPU type: Intel(R) Core(TM) i3-4160T CPU @ 3.10GHz 3092 Mhz
Hardware memory: 8110 megs

Free System resources: 90 %
GDI resources: 90 %
User resources: 90 %

Windows total applications running: 5
1 ,
2 , C:\fwh1705\samples\testconv.exe
3 DDE Server Window, C:\Windows\syswow64\OLE32.DLL
4 GDI+ Window, C:\Windows\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.7601.24234_none_5c05ea59a00ef5
5 MCI command handling window, C:\Windows\system32\WINMM.DLL

Variables in use
================
Procedure Type Value
==========================
FW_DBFRENAMEFIELD
Param 1: O Class: ERROR
MAIN
Param 1: C "MXCTACLI.DBF"
Param 2: C "CODCLI "
Param 3: C "CUSTCODE "
Local 1: L .F.
Local 2: U
Local 3: U
Local 4: U
Local 5: C " "
Local 6: S
Local 7: U
Local 8: C "MXCTACLI.DBF"
Local 9: U
Local 10: U
Local 11: U

Linked RDDs
===========
DBF
DBFFPT
DBFBLOB
DBFNTX

DataBases in use
================

Classes in use:
===============
1 ERROR
2 HBCLASS
3 HBOBJECT
4 TWINDOW
5 TBRUSH
6 TFONT
7 TREG32
8 TSTRUCT

Memory Analysis
===============
360 Static variables

Dynamic memory consume:
Actual Value: 524288 bytes
Highest Value: 524288 bytes

Below my code

Code: Select all  Expand view  RUN

#include "FiveWin.ch"

static oWnd

//----------------------------------------------------------------------------//

function Main()
   LOCAL cDbf   :="MXCTACLI.DBF"
   LOCAL cField :="CODCLI"
   LOCAL cReplace :="CUSTCODE"
   
   SET _3DLOOK ON

   DEFINE WINDOW oWnd TITLE "Storing Menus at DBFs" ;
   
   FW_DbfRenameField( cDbf, cField, cReplace )

   SET MESSAGE OF oWnd TO "Storing menus at a DBF"

   ACTIVATE WINDOW oWnd

return nil

function FW_DbfRenameField( cDbf, cField, cReplace )

   local lSuccess := .f.
   local hFile, nHeaderSize, nAt
   local cBuf     := Space( 32 )

   if !File( cDbf )
      ? cDbf + " not found"
      return .f.
   endif
   if Empty( cField ) .or. Empty( cReplace ) .or. ;
      Len( cField ) > 10 .or. Len( cReplace ) > 10
      ? "Invalid field names"
      return .f.
   endif

   cField            := PADR( UPPER( cField ),   11, CHR( 0 ) )
   cReplace          := PADR( UPPER( cReplace ), 11, CHR( 0 ) )

   if cField == cReplace
      ? "Invalid field names"
      return .f.
   endif

   if ( hFile := FOpen( cDbf, FO_READWRITE + FO_EXCLUSIVE ) ) >= 0
      if FRead( hFile, @cBuf, 32 ) == 32
         nHeaderSize := BIN2I( SUBSTR( cBuf, 9, 2 ) )
         if nHeaderSize > 32
            cBuf     := Space( nHeaderSize )
            FSeek( hFile, 0, FS_SET )
            FRead( hFile, @cBuf, nHeaderSize )
            nAt      := At( cField, cBuf )
            if nAt > 0 .and. At( cReplace, cBuf ) == 0
               FSeek( hFile, nAt - 1, FS_SET )
               if FWrite( hFile, cReplace, 11 ) == 11
                  lSuccess := .t.
               endif
            endif
         endif
      endif
      Fclose( hFile )
   else
      ? "Can not open " + cDbf + " exclusively"
   endif

return lSuccess

function AppExit()

   oWnd:End()

return nil

[Enrico Maria Giordano]

Please add

Code: Select all  Expand view  RUN

#include "Fileio.ch"

EMG

[Compuin]

Please add

Code: Select all  Expand view  RUN

#include "Fileio.ch"

EMG

No errors but is not doing any change on the field

[nageswaragunupudi]

I have revised the function. Please use the revised function given below:
Tested with "states.dbf" in the folder \fwh\samples.

This is my test program:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "fileio.ch"

//----------------------------------------------------------------------------//

function Main()

   local cDbf     := "STATES.DBF"  // from folder \fwh\samples\states.dbf
   local cField   := "NAME"
   local cReplace := "STATENAME"

   XBROWSER cDbf FASTEDIT
   ? FW_DbfRenameField( cDbf, cField, cReplace )
   XBROWSER cDbf
   ? FW_DbfRenameField( cDbf, cReplace, cField )
   XBROWSER cDbf

return nil

//----------------------------------------------------------------------------//

static function FW_DbfRenameField( cDbf, cField, cReplace )

   local lSuccess := .f.
   local hFile, nHeaderSize, nAt
   local cBuf     := Space( 32 )

   if !File( cDbf )
      ? cDbf + " not found"
      return .f.
   endif
   if Empty( cField ) .or. Empty( cReplace ) .or. ;
      Len( cField ) > 10 .or. Len( cReplace ) > 10
      ? "Invalid field names"
      return .f.
   endif

   cField            := UPPER( cField ) + CHR( 0 )
   cReplace          := UPPER( cReplace ) + CHR( 0 )

   if cField == cReplace
      ? "Invalid field names"
      return .f.
   endif

   if ( hFile := FOpen( cDbf, FO_READWRITE + FO_EXCLUSIVE ) ) >= 0
      if FRead( hFile, @cBuf, 32 ) == 32
         nHeaderSize := BIN2I( SUBSTR( cBuf, 9, 2 ) )
         if nHeaderSize > 32
            cBuf     := Space( nHeaderSize )
            FSeek( hFile, 0, FS_SET )
            FRead( hFile, @cBuf, nHeaderSize )
            if ( nAt := FieldAt( cField, cBuf ) ) > 0
               if FieldAt( cReplace, cBuf ) == 0
                  cReplace    := PADR( cReplace, 11, CHR( 0 ) )
                  FSeek( hFile, nAt - 1, FS_SET )
                  if FWrite( hFile, cReplace, 11 ) == 11
                     lSuccess := .t.
                  endif
               else
                  ? cReplace + " exists in dbf"
               endif
            else
               ? cField + " not in dbf"
            endif
         endif
      endif
      Fclose( hFile )
   else
      ? "Can not open " + cDbf + " exclusively"
   endif

return lSuccess

//----------------------------------------------------------------------------//

static function FieldAt( cField, cBuf )

   local nAt      := 33
   local nFldLen  := Len( cField )

   for nAt := 33 to Len( cBuf ) - 2 STEP 32
      if cField == SubStr( cBuf, nAt, nFldLen )
         return nAt
      endif
   next

return 0

//----------------------------------------------------------------------------//
 

This is the result:

[Compuin]

I have revised the function. Please use the revised function given below:
Tested with "states.dbf" in the folder \fwh\samples.

This is my test program:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "fileio.ch"

//----------------------------------------------------------------------------//

function Main()

   local cDbf     := "STATES.DBF"  // from folder \fwh\samples\states.dbf
   local cField   := "NAME"
   local cReplace := "STATENAME"

   XBROWSER cDbf FASTEDIT
   ? FW_DbfRenameField( cDbf, cField, cReplace )
   XBROWSER cDbf
   ? FW_DbfRenameField( cDbf, cReplace, cField )
   XBROWSER cDbf

return nil

//----------------------------------------------------------------------------//

static function FW_DbfRenameField( cDbf, cField, cReplace )

   local lSuccess := .f.
   local hFile, nHeaderSize, nAt
   local cBuf     := Space( 32 )

   if !File( cDbf )
      ? cDbf + " not found"
      return .f.
   endif
   if Empty( cField ) .or. Empty( cReplace ) .or. ;
      Len( cField ) > 10 .or. Len( cReplace ) > 10
      ? "Invalid field names"
      return .f.
   endif

   cField            := UPPER( cField ) + CHR( 0 )
   cReplace          := UPPER( cReplace ) + CHR( 0 )

   if cField == cReplace
      ? "Invalid field names"
      return .f.
   endif

   if ( hFile := FOpen( cDbf, FO_READWRITE + FO_EXCLUSIVE ) ) >= 0
      if FRead( hFile, @cBuf, 32 ) == 32
         nHeaderSize := BIN2I( SUBSTR( cBuf, 9, 2 ) )
         if nHeaderSize > 32
            cBuf     := Space( nHeaderSize )
            FSeek( hFile, 0, FS_SET )
            FRead( hFile, @cBuf, nHeaderSize )
            if ( nAt := FieldAt( cField, cBuf ) ) > 0
               if FieldAt( cReplace, cBuf ) == 0
                  cReplace    := PADR( cReplace, 11, CHR( 0 ) )
                  FSeek( hFile, nAt - 1, FS_SET )
                  if FWrite( hFile, cReplace, 11 ) == 11
                     lSuccess := .t.
                  endif
               else
                  ? cReplace + " exists in dbf"
               endif
            else
               ? cField + " not in dbf"
            endif
         endif
      endif
      Fclose( hFile )
   else
      ? "Can not open " + cDbf + " exclusively"
   endif

return lSuccess

//----------------------------------------------------------------------------//

static function FieldAt( cField, cBuf )

   local nAt      := 33
   local nFldLen  := Len( cField )

   for nAt := 33 to Len( cBuf ) - 2 STEP 32
      if cField == SubStr( cBuf, nAt, nFldLen )
         return nAt
      endif
   next

return 0

//----------------------------------------------------------------------------//
 

This is the result:

Mr. Rao

Works perfect!

Thanks

[Xevi]

Funciona de lujo!!!
Ahora bien... una funcion como la de cambiar el nombre del campo, para cambiar la longitud del campo.

Lo que mas 'odio' en la tarea de actualizar programa es el cambiar el ancho de determinador campos de las bases de datos, ya sa porque el cliente se queja de que le queda corto y necesita ampliar la longitud de campos de texto.

Hay o alguien puede dar con la función como esta de útil y rapida que no sea copiando la base de datos a una temporal y hacer un APPEND FROM... que es como lo estoy haciendo actualmente.???

Gracias.