Create table with firedac without SQL Script
The Firedac library centralizes database behavior and has many methods that work great without worrying about the type of database. In fact, by using native drivers for most common databases, Firedac hides subtle differences in syntax, allowing for very flexible database platform changes.
For example, autoinc generators and fields are easily discoverable, CAST and parameters work great, making it easy to navigate between databases.
How can I use the power of Firedac to create a new table without creating an FDQuery instance that runs SQL Script CREATE TABLE
?
I hope to create any object and, by calling a specific FieldByName for each Object field, write it to the database, but first I need to confirm:
- If the table has already been created
- If the field has already been created
- If the entry has already been created
This is the code I have:
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 this for a specific database like Sqlite or Firebird, but I don't know what to do for any database using Firedac resources.
I found FireDAC.Comp.Client.TFDTable.CreateTable(ARecreate: Boolean = True; AParts: TFDPhysCreateTableParts = [tpTable .. tpIndexes])
as suggested by @Ondrej Kelle but I didn't understand the AParts
usage. Anyone have an example? http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Comp.Client.TFDTable.CreateTable
Many thanks.
source to share
You can create a TFDTable object , describe your table at least by specifying a TableName and adding field definitions to FieldDefs . In practice, you usually create the primary key index for some field as well (which AddIndex can do ). After describing your table, call the CreateTable method . A minimal example would 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;
source to share