Jump to content
Sign in to follow this  
Clément

Using firedac to generate table script

Recommended Posts

Hi,

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

 

TIA,

Clément

Share this post


Link to post

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 by programmerdelphi2k

Share this post


Link to post
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);

 

 

Share this post


Link to post

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   

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×