Using firedac to generate table script

[Clément 148]

Hi,

Is it possible to generate a "create table" script using Firedac? How about "Create Index"? Any sample?

 

TIA,

Clément

[Dmitry Arefiev 101]

TFDTable.CreateTable

[programmerdelphi2k 237]

you can use "https://docwiki.embarcadero.com/RADStudio/Sydney/en/Querying_Metadata_(FireDAC)" functions to create yourself if necessary!

 

try some like this: using: FDConn + FDPhysXXX + FDWaitxxxxx + FDMetaInfoQuery to scan your database

-- FDScript to execute yourself script!!!

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDConnection1.Open;
  FDMetaInfoQuery1.MetaInfoKind := mkResultSetFields; // any other kind existent
  FDMetaInfoQuery1.Open;
end;

 

Edited December 2, 2022 by programmerdelphi2k

[Clément 148]

3 hours ago, Dmitry Arefiev said:

TFDTable.CreateTable

I looked the code and managed to build what I need. But I'm still not sure the code is correct.

The only way I found is to derive from TFDTable like:


 

  TFDMyTable = Class( TFDTable )
  public
     procedure GenerateSQLCreate( aGenerateDrop : Boolean; AParts: TFDPhysCreateTableParts; aResult : TStrings );
  end;

In order to get any result I must execute as:

 

procedure TForm98.FormCreate(Sender: TObject);
var
  lTable : TFDMyTable;
begin
  
  lTable := TFDMyTable.Create(nil);
  try
    lTable.Connection := FDConnection1;
    lTable.TableName := 'Employees';
    lTable.Open;
    lTable.Close;
    lTable.GenerateSQLCreate(true,[tpTable,tpGenerators,tpPrimaryKey,tpIndexes], Memo1.Lines);
  finally
    lTable.Free;
  end;
end;

If I don't "Open/Close" before calling "GenerateSQLCreate" I get an Access Violation

 

  try
    // Build Table from FieldDefs and IndexDefs
    CheckTable;       // Access Violation here if I don't Open/Close before the call
    OpenIndexes;

The table (lTable in this case) won't be attached to any visual components (like DBGrid or such). How many record will Open retrieve? In this case I really don't need any records, just metadata should be enough. Is there a method I should call that will only retrieve metadadata info?

 

And the result is pretty cool!

 

DROP TABLE Employees;

CREATE TABLE Employees (
  EmployeeID INT IDENTITY(1,1) NOT NULL,
  LastName NVARCHAR(20) NOT NULL,
  FirstName NVARCHAR(10) NOT NULL,
  Title NVARCHAR(30),
  TitleOfCourtesy NVARCHAR(25),
  BirthDate DATETIME2,
  HireDate DATETIME2,
  Address NVARCHAR(60),
  City NVARCHAR(15),
  Region NVARCHAR(15),
  PostalCode NVARCHAR(10),
  Country NVARCHAR(15),
  HomePhone NVARCHAR(24),
  Extension NVARCHAR(4),
  Photo VARBINARY(MAX),
  Notes NVARCHAR(MAX),
  ReportsTo INT,
  PhotoPath NVARCHAR(255)
);
ALTER TABLE Employees ADD CONSTRAINT [PK_Employees] PRIMARY KEY (EmployeeID);
CREATE INDEX LastName ON Employees (LastName, EmployeeID);
CREATE INDEX PostalCode ON Employees (PostalCode, EmployeeID);

 

 

[Roger Cigol 103]

I just use an TFDQuery with the SQL statement set to SQL CreateTable statement (created either at design time or at run time as suits your needs) and then call Execute() method of the TFDQuery