FiveTech Software tech support forums

www.FiveTechSoft.com
http://forums.fivetechsoft.com/

Cambiar nombre a campos en tablas (SOLUCIONADO)

http://forums.fivetechsoft.com/viewtopic.php?f=6&t=36303

Page 1 of 1

Cambiar nombre a campos en tablas (SOLUCIONADO)

Posted: Fri Nov 02, 2018 6:47 pm
by Compuin
Saludos

Alguien tiene una funcion que permita cambiar el nombre de _ 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

Re: Cambiar nombre a campos en tablas

Posted: Sat Nov 03, 2018 3:36 pm
by MauroArevalo
Compuin:

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

Code: Select all

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

 

Re: Cambiar nombre a campos en tablas

Posted: Sun Nov 04, 2018 12:52 am
by nageswaragunupudi
Please use this function. This is very fast.

Code: Select all

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
 

Re: Cambiar nombre a campos en tablas

Posted: Sun Nov 04, 2018 3:06 pm
by Compuin
nageswaragunupudi wrote:Please use this function. This is very fast.

Code: Select all

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

#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

Re: Cambiar nombre a campos en tablas

Posted: Sun Nov 04, 2018 3:11 pm
by Enrico Maria Giordano
Please add

Code: Select all

#include "Fileio.ch"
EMG

Re: Cambiar nombre a campos en tablas

Posted: Sun Nov 04, 2018 3:13 pm
by Compuin
Enrico Maria Giordano wrote:Please add

Code: Select all

#include "Fileio.ch"
EMG
No errors but is not doing any change on the field

Re: Cambiar nombre a campos en tablas

Posted: Sun Nov 04, 2018 5:27 pm
by 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

#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

Re: Cambiar nombre a campos en tablas

Posted: Sun Nov 04, 2018 5:31 pm
by Compuin
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

#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

Re: Cambiar nombre a campos en tablas (SOLUCIONADO)

Posted: Wed Nov 21, 2018 10:44 am
by 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.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited