FireDAC Add new field to existing SQL table

Question

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.

Why not execute a SQL request to add the column? The SQL statement is ALTER TABLE SomeTableName ADD SomeColumnName SomeDataType; Check your database SQL syntax for more details. — fpiette, Mar 19 '21 at 13:02
I don't think that FireDAC allows you to alter table structure using `FieldDefs`. Adding new column to table is not always an easy task - e.g. if the new column is does not allow nulls. I'm afraid you will need to generate [DDL](https://en.wikipedia.org/wiki/Data_definition_language) command(s) yourself. — Peter Wolf, Mar 19 '21 at 13:03
@fpiette: Because I want to be 100% independent of the specific SQL syntax for the underlying database. Like, do I use " or [] to surround table and field names (in case they are the same as reserved words)? Is the valid type for a boolean field BOOL, BOOLEAN or BIT? Is it VARBINARY(MAX), IMAGE or BLOB? VARCHAR(MAX) or CLOB? Is the string for a true value "1" or "TRUE"? That's what FireDAC is there to hide from me... — HeartWare, Mar 19 '21 at 13:07
Have you tried passing false as the first parameter (ARecreate) to `TFDTable.CreateTable`? — Brian, Mar 19 '21 at 13:22
@Brian: Yep: "TABLE AREADY EXISTS" error. And from the trace into it, it seems like it can only do a CREATE TABLE (with or without a prior DROP TABLE) and not an ALTER TABLE. — HeartWare, Mar 19 '21 at 13:54

Rob Lambden · Answer 1 · 2021-03-19T15:04:26.107

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.

FireDAC Add new field to existing SQL table

1 Answers1