Cambiar nombre a campos en tablas (SOLUCIONADO)

Board index FiveWin para Harbour/xHarbour Cambiar nombre a campos en tablas (SOLUCIONADO)

Compuin

Posts: 1276

Joined: Tue Dec 28, 2010 01:29 PM

Cambiar nombre a campos en tablas (SOLUCIONADO)
Posted: Fri Nov 02, 2018 06:47 PM

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 )

NFACCCLI por NFACCCUS (Aqui se cambia CLI)

Esto habria que hacerlo para mas de 300 tablas

Gracias de antemano

FWH 25.12

Harbour/Hbmk2

Microsoft Visual C++

MySql 8.0

Antigravity

MauroArevalo

Posts: 111

Joined: Thu Jan 19, 2006 11:47 PM

Re: Cambiar nombre a campos en tablas
Posted: Sat Nov 03, 2018 03:36 PM

Compuin:

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

Code (fw): Select all Collapse

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

Edgar Mauricio Arévalo Mogollón.
Bogotá DC. Colombia
FWH FTDN, xHarbour 1.2.1, Pelles C, Fivedit, Visual Studio Code, Borland 7.30, Mysql, Dbfs
http://www.hymplus.com http://www.hymlyma.com
Tratando de retomar la programación....

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Cambiar nombre a campos en tablas
Posted: Sun Nov 04, 2018 12:52 AM

Please use this function. This is very fast.

Code (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

Compuin

Posts: 1276

Joined: Tue Dec 28, 2010 01:29 PM

Re: Cambiar nombre a campos en tablas
Posted: Sun Nov 04, 2018 03:06 PM

nageswaragunupudi wrote:Please use this function. This is very fast.

Code (fw): Select all Collapse

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 (fw): Select all Collapse

#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

FWH 25.12

Harbour/Hbmk2

Microsoft Visual C++

MySql 8.0

Antigravity

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Cambiar nombre a campos en tablas
Posted: Sun Nov 04, 2018 03:11 PM

Please add

Code (fw): Select all Collapse

#include "Fileio.ch"

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Compuin

Posts: 1276

Joined: Tue Dec 28, 2010 01:29 PM

Re: Cambiar nombre a campos en tablas
Posted: Sun Nov 04, 2018 03:13 PM

Enrico Maria Giordano wrote:Please add
Code (fw): Select all Collapse
#include "Fileio.ch"
EMG

No errors but is not doing any change on the field

FWH 25.12

Harbour/Hbmk2

Microsoft Visual C++

MySql 8.0

Antigravity

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Cambiar nombre a campos en tablas
Posted: Sun Nov 04, 2018 05:27 PM

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 (fw): Select all Collapse

#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:

Regards

G. N. Rao.

Hyderabad, India

Compuin

Posts: 1276

Joined: Tue Dec 28, 2010 01:29 PM

Re: Cambiar nombre a campos en tablas
Posted: Sun Nov 04, 2018 05:31 PM

nageswaragunupudi wrote: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 (fw): Select all Collapse

#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

FWH 25.12

Harbour/Hbmk2

Microsoft Visual C++

MySql 8.0

Antigravity

Xevi

Posts: 174

Joined: Wed Nov 29, 2017 11:42 AM

Re: Cambiar nombre a campos en tablas (SOLUCIONADO)
Posted: Wed Nov 21, 2018 10:44 AM

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.

Un Saludo,

Xevi.

Aprendiz de la vida!!!

FiveTech Support Forums

Continue the discussion