CODEWITHSUNDEEP
delphifiredac
HeartWare
HeartWare

Reputation: 8251

FireDAC Add new field to existing SQL table

I am trying to create a new field in a FireDAC-compatible database with this PROCEDURE:

PROCEDURE CreateField(Connection : TFDConnection ; CONST TableName : STRING; F : TFieldDefinition);
  VAR
    Table       : TFDTable;

  BEGIN
    Table:=TFDTable.Create(NIL);
    TRY
      Table.Connection:=Connection;
      Table.TableName:=TableName;
      Table.FieldDefs.Updated:=FALSE;
      Table.FieldDefs.Update;
      Table.FieldDefs.Add(F.FieldName,F.FieldType,F.MaxLen,NOT F.Nullable);
      // Commit my changes to the database //
    FINALLY
      FreeAndNIL(Table)
    END
  END;

where

TYPE
  TFieldDefinition      = CLASS
                          PUBLIC
                            FieldName   : STRING;
                            FieldType   : TFieldType;
                            MaxLen      : Integer;
                            Nullable    : BOOLEAN;
                          END;

but I can't seem to "Commit" my changes back to the database (ie. end up executing an ALTER TABLE ADD [COLUMN] statement).

How do I commit my changes to the FieldDefs list of the table? Or is there some other way - using plain FireDAC - that I can use to create a new field in an existing table?

Note: There are already data in the table, so I can't simply "DROP" and then "CREATE" the table again.

Upvotes: 4

Views: 1336

Answers (1)

Rob Lambden
Rob Lambden

Reputation: 2293

FireDAC should understand the sytax used by the supported databases and use the appropriate syntax and decoration. See the documentation here: http://docwiki.embarcadero.com/RADStudio/Sydney/en/Preprocessing_Command_Text_(FireDAC)

TFieldDef is an internal descriptor of the fields in a TDataSet, it's not going to be used to update the table structure automatically. (Although you could write your own procedure that compares your TFieldDefs to the FireDAC MEta Data and creates the DDL (Data Definition Language) statements you need to execute in a TFDCommand ... )

To alter that table structure you will need to provide the DDL (SQL) statement that you execute with TFDCommand - the 'preprocessing' link above will explain how to write it in a dialect abstracted way.

If you use the appropriate FireDAC description it will automatically put in the appropriate SQL decoration for you so the syntax is valid. Depending on your SQL dialect and the FireDAC driver you may hit limitations. (For example using the ODBC driver FireDAC generally won't know the specific details of the underlying database - we had to implement a solution for SAP HANA which had exactly this challenge).

Bear in mind that some SQL dialects support features that others don't - so for example it's not safe to assume that you can position a column when you add it (which MySQL supports for example) as not all dialects allow that in an ALTER TABLE statement.

Upvotes: 1

Related Questions