In software updates, we may add new fields to some tables. I'd like to add
some code where this is not done explicitly new field by new field, but some
loop over the permanent fields of the table, and then create fields that are
not found in the physical table.

What would be the most elegant way to do this?

Royke

Royke,

<< In software updates, we may add new fields to some tables. I'd like to
add some code where this is not done explicitly new field by new field, but
some loop over the permanent fields of the table, and then create fields
that are
not found in the physical table. >>

Are you talking only about the physical columns in the table ?  If so, then
you should look at some of the code in the edbreverse.pas unit included with
the EDB Manager source code.  It shows you how to query the Information
schema for a database and generate SQL for creating tables.  You would need
to adapt this to altering a table also, but this should at least give you a
start.

--
Tim Young
Elevate Software
www.elevatesoft.com

Royke,

I use a file. ini with the definition of the tables and a procedure that creates a new
table with the new structure and will add the records of the original table after deleting
original table and renaming the new one. This procedure it will be valid for any table.
Like this:

"Tables.ini"

[Tables]
Table1  = Parametros
Table2  = DatosEmpresa
Table3  = Usuarios

[Parametros]
Campo1  = IVA1,N,5,2
Campo2  = IVA2,N,5,2
Campo3  = IVA3,N,5,2
Campo4  = RE1,N,5,2
Campo5  = RE2,N,5,2
Campo6  = RE3,N,5,2

[DatosEmpresa]
Campo1  = NOMBRE,C,40,
Campo2  = DIRECCION,C,40,
Campo3  = CODP,C,5,
Campo4  = POBLACION,C,30,
Campo5  = PROVINCIA,C,30,
Campo6  = CIF,C,10,
Campo7  = TELEFONO,C,30,

[Usuarios]
Campo1  = CODIGO,C,6,
Campo2  = NOMBRE,C,40,
Campo3  = TOTAL,N,12,2
Campo4  = FECHA,D

Indice1   = CODIGO,CODIGO,""                       <------------ This table uses two index
Indice2   = NOMBRE,NOMBRE,""

Regards

It is interesting to see the different approaches.

What we implemented is to replace Open calls for the table by Table.DoOpen
(in our desecdent of TTable), which then checks if all fields (as defined in
the DFM and PAS file) really exist, and if not, adds them. Adding a new
field is now a matter of manually editing the DFM/PAS of the DataModule.

procedure TMLTable.DoOpen;
// check if any new fields should be added to the physical table, then Open
begin
 CheckFields;
 Open;
end;

CheckFields loops over all fields in the DFM, and adds the fields that do
not exist.

procedure TMLTable.CheckFields;
var
 i: Integer;
begin
 for i := 0 to Fields.Count - 1 do
   if Fields[i].FieldKind = fkData then
     if not FieldExists(Fields[i].FieldName) then
     begin
       case Fields[i].DataType of
         ftString: AddField(Fields[i].FieldName, 'VARCHAR(' +
IntToStr(Fields[i].Size) + ')');
         ftInteger: AddField(Fields[i].FieldName, 'INT');
         ftBoolean: AddField(Fields[i].FieldName, 'BOOLEAN');
         etc ...
       end;
     end;
end;

The FieldExists function can probably be done quicker/smarter, but this
works faster than we expected:

function TMLTable.FieldExists(fn: string): Boolean;
var
 Tbl: TMLTable;
begin
 Tbl := TMLTable.Create(nil);
 Tbl.DatabaseName := DatabaseName;
 Tbl.TableName := TableName;
 Tbl.SessionName := SessionName;
 Tbl.Open;
 result := Tbl.FindField(fn) <> nil;
 Tbl.Close;
 Tbl.Free;
end;

And fields get added like this:

procedure TMLTable.AddField(const fieldname: string; const fieldtype:
string);
var
 Query: TQuery;
begin
 Query := TQuery.Create(nil);
 Query.DatabaseName := DatabaseName;
 Query.SessionName := SessionName;

 Query.SQL.Clear;
 Query.SQL.Add('ALTER TABLE "' + TableName + '" ADD ' + fieldname + ' ' +
fieldtype);
 Query.ExecSQL;
 Query.Free;
end;

If anyone has a nicer method of implementing one of these tasks, please let
us know.

Royke