need to change a field type on the fly

[hag]

I need to change a field type on the fly and change size etc. I'm lost. Help

[MarcoBoschi]

Please,
what you have to do?
Marco

[hag]

I have a field in a data base that for past users is an incorrect data type (character when it should be numeric. My programing error). So I need to change the field from a character to numeric and the length from 4 to 18 and decimals from 0 to 6. It need to be done on the fly.

Thanks for the quick response.

[Rick Lipkin]

Harvey

Here is what I would do ..

1 - open the database in question .. test for valtype( table->field)
If valtype returns "N" .. then write a routine like this .. ( psudo code )

A) Open your old table excl ..
B) dbcreate a ( temp .. exclusive ) new table with all your fields and the correct the valtype
C) write a routine like this

Code: Select all  Expand view

select 1
use ( oldtable ) via "dbfcdx" excl

select 2
use ( temp ) via "dbfcdx" excl

select 1
go top

Do while .not. eof()

     select 2
    append blank
   // append your fields here

   select 1
   skip

End Do

CLose databases

ferase( "OldTable" )
Rename ( Temp ) to ( oldtable )
 

Don't forget to re-index your new table ..

Hope that helps .. you can leave the routine in your program at startup since it will always look for the Valtype( table->field) = "N" .. and since you have made the change .. it will not run your update routine again.

Hope my psudo code makes sense .. if you need any help .. just let me know.

Rick Lipkin

[MarcoBoschi]

Try this
http://www.emagsoftware.it/EmagDbu240.zip

All users have to close this dbf

Make a copy of your table

Open dbf with emagdbu

press F3
select wrong field

press RETURN

change type (second column)
change len and dec
click first btnbmp "Conferma"

that's all folks

[hag]

Rick:

If I append blank it adds another field of the same name to the dbf rather then change the existing field.

[hag]

Rick, Marco:
Is there a way to remove a field while the program is running then I can append a new field with the same name. Now I get 2 fields with the same name.

[MarcoBoschi]

You cannot modify dbf structure while the dbf is open by other users

[hag]

Code: Select all  Expand view

     if valtype(field->RATE)    == "C" [b]// if rate is "C" need to make "Numeric"
[/b]
      copy to atemp3 structure extended
            use atemp3

            append blank    // when I append blank it adds another field with the same name. 
            replace field_name with "rate",field_type with "numeric",field_len with  18, field_dec with 6
     
      create atemp9 from atemp3
             
      use atemp9 alias atemp9
            append from (gld)
            
      atemp9->(dbclosearea())
             
            erase (gld)
            
            rename atemp9.dbf to (gld) //"gl.d"+cEntity
                   
   endif

Code works fine but i have two fields with same name.

[Rick Lipkin]

Harvy

Consider this code .. add this to your main program and it will run every time

Code: Select all  Expand view

Local nType

select 1
Use ( "OldTable.dbf" ) via "DBFCDX" Shared  // test in shared first

nType := valtype( a->fieldinquestion)
Close databases

If nType = "N"
     _Update()
Else
   CLose Databases
Endif

//--------------
Static Func _Update()

Local DBFSTRU

DBF_STRU := { }
AADD( DBF_STRU, { "FIELD1",               "C",  18,  0 } )
AADD( DBF_STRU, { "FIELD2,                "C",  30,  0 } )
AADD( DBF_STRU, { "FIELDTOCHANGE",   "N",   1,  6 } ) // was char in orig table
//..  add the rest or your fields from the orig table here
 )

DBCREATE( TEMP.DBF, DBF_STRU )

Select 1
Use ( "OldTable.dbf" ) via "DBFCDX" Excl

Select 2
Use ("Temp.dbf") via "DBFCDX" Excl

Select 1
Go Top

Do While .not. eof
     
     select 2
     append blank

      b->field1 := a->field1
      b->field2 := a->field2
      b->FIELDTOCHANGE := val(a->oldfield)  // may have to convert to val with dec
     
      select 1
      skip

Enddo

CLose databases

ferase( "OldTable.dbf" )
rename ( "temp.dbf" ) to ( "OldTable.dbf" )

Return(.t.)
 

[hag]

Thank you all for the help. Couldn't get it working. Came up with a solution which is not on the fly. Thanks again.