Create table with firedac without SQL Script

Question

Firedac library centralizes database behavior and have a lot of methods which works fine without care about the Database Type. Actually, using native drivers for most common databases, Firedac hides subtle differences on syntax allowing very flexible changes of database platform.
For example, generators and autoinc fields are easily detectable, CAST and parameters works fine allowing easy migration between databases.

How to use Firedac power to create New Table without instantiate FDQuery, which runs a SQL Script CREATE TABLE?

I hope to create any Object and, calling specific FieldByName for each Object Field, record it on database, but first I need to certify:

If Table is already created
If Field is already created
If record is already created

This is the code I have, so far:

TRecCustomer = record
  Id:integer;
  Name:String;
  Birthday:TDate;
end;

ICustomer = interface
  procedure setCustomerId(Value: Integer);
  procedure setCustomerName(Value: String);
  procedure SetBirthday(Value: TDate);
  procedure Post;
end;

TCustomer = class(TInterfacedObjet, ICustomer)
  CustomerObject=TRecCustomer;

  procedure setCustomerId(Value: Integer);
  procedure setCustomerName(Value: String);
  procedure SetBirthday(Value: TDate);
  procedure Post;
end;

procedure TCustomer.Post;
begin
  if not TableExists('Customer') then CreateTable('Customer');
  if not FieldExists('Name') then CreateField('Customer','name',ftString,[],40);
  if not FieldExists('Id') then CreateField('Customer','Id',ftInteger,[cAutoInc,cNotNull]);
  if not FieldExists('Birthday') then CreateField('Customer','birthday',ftDate);
end;

Imagine the procedures

CreateTable(Tablename: String)
CreateField(FieldName: String; FieldType: TDataType; Constraints: TConstraints; Length: Integer = 0);

where

TConstraints = set of (cAutoInc, cNotNull, cUnique, cEtc);

I can do it for specific database, for example Sqlite or Firebird, but I don't know hou to do for any database using Firedac resources.

I found FireDAC.Comp.Client.TFDTable.CreateTable(ARecreate: Boolean = True; AParts: TFDPhysCreateTableParts = [tpTable .. tpIndexes]), suggested by @Ondrej Kelle but I don't understood AParts usage. Somebody have an example? http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Comp.Client.TFDTable.CreateTable

Thanks a lot.

thanks @OndrejKelle, I edited the question with your recomendation but I yet need an example to do that. — user6695319
– user6695319, Commented May 15, 2017 at 15:39

Victoria · Accepted Answer · 2017-06-09 05:57:09Z

5

You can create a TFDTable object, describe your table at least by specifying TableName and adding field definitions in the FieldDefs collection. In practice you'll usually create also a primary key index for some field (that's what can be done by the AddIndex method). After you describe your table, call CreateTable method. A minimal example can be:

var
  Table: TFDTable;
begin
  Table := TFDTable.Create(nil);
  try
    Table.Connection := FDConnection1;
    { specify table name }
    Table.TableName := 'MyTable';
    { add some fields }
    Table.FieldDefs.Add('ID', ftInteger, 0, False);
    Table.FieldDefs.Add('Name', ftString, 50, False);
    { define primary key index }
    Table.AddIndex('pkMyTableID', 'ID', '', [soPrimary]);
    { and create it; when the first parameter is True, an existing one is dropped }
    Table.CreateTable(False);
  finally
    Table.Free;
  end;
end;

edited Jun 9, 2017 at 5:57

answered Jun 9, 2017 at 5:31

Victoria

7,9522 gold badges23 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Reversed Engineer Over a year ago

If you want to use the SQLite ROWID feature (ID column INTEGER PRIMARY KEY is also the row identifier and thus will be generated automatically), change the type of field ID from ftInteger to ftAutoIncrement, and the Required parameter to true, i.e. Table.FieldDefs.Add('ID', ftAutoIncrement, 0, True); . FireDAC knows what to do. The convention then is also to call the field ROWID. If fact, in tests, this seems to work (with FireDAC) even without creating the index.

Collectives™ on Stack Overflow

Create table with firedac without SQL Script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related