How to use a FireDAC TFDConnection to iterate through tables, fields and field definitions

Question

I am teaching myself to use SQLite and FireDAC together in Delphi. I am not very experienced with the latest incarnations of databases and tools so after writing a very simple application to display a single table from an SQLite file, I decided that I would put together a simple viewer 'frame' that would help me learn and maybe give me (eventually) a debugging tool to put in my Application for engineering use.

So, I've used a simple TTreeView and I wish to populate it with a hierarchy of 'databases' (catalogues?), 'tables', 'field names' and 'field types'. So far it has been remarkably easy to list the catalogues, tables and fields (using TFDConnection.Getxxxxx) but I cant see how to go deeper to get field definitions. Is this possible to do from a TFDConnection? Or do I need to open a temporary query?

My existing code is shown below and my 'field types' would be a further nested loop when shown as '// xxxxxxxxxxxxxxxxxxx'

procedure TForm1.Button1Click(Sender: TObject);

  procedure DatabaseToTreeView( AConnection : TFDConnection; ATreeView : TTreeView );

    procedure ProcessConnection;

      procedure ProcessCatalogueName( const ACatalogueName : string; ARoot : TTreeNode );

        procedure ProcessTableName( const ATableName : string; ARoot : TTreeNode );
        var
          List : TStrings;
          {Node : TTreeNode;}
          I    : integer;
        begin
          List := TStringList.Create;
          try
            AConnection.GetFieldNames( ACatalogueName, '', ATableName, '', List );
            for I := 0 to List.Count-1 do
              begin
              {Node := }ATreeView.Items.AddChild( ARoot, List[I] );
              // xxxxxxxxxxxxxxxxxxx
              end;
          finally
            List.Free;
          end;
        end;

      var
        List : TStrings;
        Node : TTreeNode;
        I    : integer;
      begin
        List := TStringList.Create;
        try
          AConnection.GetTableNames( ACatalogueName, '', '', List );
          for I := 0 to List.Count-1 do
            begin
            Node := ATreeView.Items.AddChild( ARoot, List[I] );
            ProcessTableName( List[I], Node );
            end;
        finally
          List.Free;
        end;
      end;


    var
      List : TStrings;
      Node : TTreeNode;
      I    : integer;
    begin
        List := TStringList.Create;
        try
          AConnection.GetCatalogNames( '', List );

          if List.Count = 0 then
            ProcessCatalogueName( '', nil )
           else
            for I := 0 to List.Count-1 do
              begin
              Node := ATreeView.Items.AddChild( nil, List[I] );
              ProcessCatalogueName( List[I], Node );
              end;
        finally
          List.Free;
        end;
    end;


  begin
    ATreeView.Items.Clear;
    ATreeView.Items.BeginUpdate;
    try
      ProcessConnection;
    finally
      ATreeView.Items.EndUpdate;
    end;
  end;

begin
  FDConnection1.Open;
  FDQuery1.Active := true;

  DatabaseToTreeView( FDConnection1, TreeView1 );

end;

Many thanks, Brian.

You can use the [`TADMetaInfoQuery`](http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=uADCompClient_TADMetaInfoQuery.html) class and query the `mkTableFields` type of [`MetaInfoKind`](http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=uADCompClient_TADMetaInfoQuery_MetaInfoKind.html). That will return you `COLUMN_DATATYPE` and `COLUMN_TYPENAME` (see the [`Metadata Structure`](http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=Metadata_Structure.html) chapter). — TLama, Jan 09 '14 at 16:42
@TLama: This worked for me and would seem to be the 'correct' way, thanks. After following your link to the FD documentation I stumbled on a perfect example project in "C:\Users\Public\Documents\RAD Studio\12.0\Samples\Delphi\Database\FireDAC\Samples\Comp Layer\TFDMetaInfoQuery" which does pretty much everything. Why oh why cant we get a detailed list of Delphi examples and what they do!! — Brian Frost, Jan 10 '14 at 10:11
You're welcome! Yup, it seems to be the best fitting component for your task. And yes, it would be nice to have a list of example descriptions. — TLama, Jan 10 '14 at 10:56

score 1 · Answer 1 · answered Jan 09 '14 at 16:26

1

One solution is to instantiate a TFDTable, connect it to AConnection and call FieldDefs.Update. This won't fetch any data.

answered Jan 09 '14 at 16:26

Uwe Raabe

45,288
3
82
130

Thanks for the suggestion but this only works if I replace Table.FieldDefs.Update with Table.Active := True. I am only doing Table.Connection=xxx and Table.TableName := ATableName though, is there another field I should initialise? – Brian Frost Jan 10 '14 at 09:41

Victoria · Answer 2 · 2017-06-27T06:15:46.720

1

Use the TFDMetaInfoQuery component. It is unified for getting metadata information, so it's usable with any kind of supported DBMS. It populates metadata resultsets by the requested MetaInfoKind and given DBMS object description.

edited Jun 27 '17 at 06:15

answered Jun 27 '17 at 06:09

Victoria

7,822
2
21
44

score 0 · Answer 3 · answered Jul 25 '14 at 07:50

Instead of using a temporary query with a false condition to get table schema without any data (such as "select * from tablename where 1=0" - I assume that is what you meant), depending on your database, you could also use a query to get table information. Like;

For MySQL:

show columns from  tablename;

For SQLite:

PRAGMA table_info(tablename)

For MS SQL Server:

select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'tablename';

I believe PostgreSQL also has such a function, however I dont have a pgsql installation handy at this time.

How to use a FireDAC TFDConnection to iterate through tables, fields and field definitions

3 Answers3