Cambiar nombre a campos en tablas (SOLUCIONADO)

Cambiar nombre a campos en tablas (SOLUCIONADO)

Postby Compuin » Fri Nov 02, 2018 6: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 )

y

NFACCCLI por NFACCCUS (Aqui se cambia CLI)

Esto habria que hacerlo para mas de 300 tablas

Gracias de antemano
Last edited by Compuin on Sun Nov 04, 2018 5:46 pm, edited 1 time in total.
FWH 20.12
Hbmk2 32/64 Bits (Build 19.29.30133)
Microsoft Visual C 32 Bits
MySql 8.0.24 32/64 Bits
VS Code
Compuin
 
Posts: 1214
Joined: Tue Dec 28, 2010 1:29 pm
Location: Quebec, Canada

Re: Cambiar nombre a campos en tablas

Postby MauroArevalo » Sat Nov 03, 2018 3:36 pm

Compuin:

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

Code: Select all  Expand view

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....
User avatar
MauroArevalo
 
Posts: 107
Joined: Thu Jan 19, 2006 11:47 pm
Location: Bogota DC. Colombia

Re: Cambiar nombre a campos en tablas

Postby nageswaragunupudi » Sun Nov 04, 2018 12:52 am

Please use this function. This is very fast.
Code: Select all  Expand view
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
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Cambiar nombre a campos en tablas

Postby Compuin » Sun Nov 04, 2018 3:06 pm

nageswaragunupudi wrote:Please use this function. This is very fast.
Code: Select all  Expand view
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
#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 20.12
Hbmk2 32/64 Bits (Build 19.29.30133)
Microsoft Visual C 32 Bits
MySql 8.0.24 32/64 Bits
VS Code
Compuin
 
Posts: 1214
Joined: Tue Dec 28, 2010 1:29 pm
Location: Quebec, Canada


Re: Cambiar nombre a campos en tablas

Postby Compuin » Sun Nov 04, 2018 3:13 pm

Enrico Maria Giordano wrote:Please add

Code: Select all  Expand view
#include "Fileio.ch"


EMG


No errors but is not doing any change on the field
FWH 20.12
Hbmk2 32/64 Bits (Build 19.29.30133)
Microsoft Visual C 32 Bits
MySql 8.0.24 32/64 Bits
VS Code
Compuin
 
Posts: 1214
Joined: Tue Dec 28, 2010 1:29 pm
Location: Quebec, Canada

Re: Cambiar nombre a campos en tablas

Postby nageswaragunupudi » Sun Nov 04, 2018 5: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: Select all  Expand view
#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:
Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Cambiar nombre a campos en tablas

Postby Compuin » Sun Nov 04, 2018 5: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: Select all  Expand view
#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:
Image


Mr. Rao

Works perfect!

Thanks
FWH 20.12
Hbmk2 32/64 Bits (Build 19.29.30133)
Microsoft Visual C 32 Bits
MySql 8.0.24 32/64 Bits
VS Code
Compuin
 
Posts: 1214
Joined: Tue Dec 28, 2010 1:29 pm
Location: Quebec, Canada

Re: Cambiar nombre a campos en tablas (SOLUCIONADO)

Postby Xevi » 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!!!
User avatar
Xevi
 
Posts: 174
Joined: Wed Nov 29, 2017 11:42 am
Location: Girona


Return to FiveWin para Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 49 guests

cron