Display database structure from Delphi (rad studio)

Question

Here is my procedure. When I execute it nothing happens; why is this?

 FData.FDQuery1.SQL.Clear;
 FData.FDQuery1.SQL.Add('select StrDBName FROM INFORMATION_SCHEMA.TABLES');

 FData.FDQuery1.ExecSQL;

 while FData.FDQuery1.Eof do
    ShowMessage(FData.FDQuery1.Fields[0].ToString);
     end;

Call `Open` instead of `ExecSQL` and include `FData.FDQuery1.Next` in your `while` loop after `ShowMessage`. But I'm voting to close this because it lacks an MVCE. And what has Paradox got to do with your q? — MartynA, Mar 31 '17 at 08:15
Also **while FData.FDQuery1.Eof do** should be **while *not* FData.FDQuery1.Eof do** — Dsm, Mar 31 '17 at 08:19
Btw, this is about the third time that a very similar faulty `while ...eof` loop has been included in code here in the past few days. Did you get it from some website? — MartynA, Mar 31 '17 at 08:37
Which field list are you talking about? In the IDE or at runtime? — MartynA, Mar 31 '17 at 10:39
ExecSQL is for SQL statements that do not return a result set (INSERT, DELETE, UPDATE). For SELECT, which does return a resultset, use `FDQuery1.Open;` instead. (That's one of several problems with the code you've posted.) — Ken White, Mar 31 '17 at 12:38

MartynA · Accepted Answer · 2017-04-02T09:36:50.883

As already explained to you in comments, your while loop should look something like this:

 while **not** FData.FDQuery1.Eof do **begin**
    ShowMessage(FData.FDQuery1.Fields[0].ToString);
    **FData.FDQuery1.Next;**
 end;

(minus the asterisks, of course). However, that would not overcome the problem that your SQL is incorrect.

So, try this instead:

In a new Delphi project, place a TFDConnection, TFDQuery, TDataSource, TDataSource and a TListBox on a form. Save the form and project.
Double-click FDConnection1 to pop up its connection editor and configure it so you can successfully connect it to your database.
Connect DBGrid1 to DataSource1 and Datasource1 to FDQuery1.
Add the code below to the form's OnCreate event.
Compile and run.
You should immediately see the cause of your problem. As the error message told you, there is no strDBName field in the INFORMATION_SCHEMA.TABLES table.

So you need to backtrack to the MySQL online help, starting e.g. here

https://dev.mysql.com/doc/refman/5.7/en/tables-table.html

and work out exactly what it is that you are looking for, if you don't already know, and how to get it from within your project.

Btw, if you are not sure what you are doing, you should always try your SQL first in the MySql Workbench utility.

Code

  FDQuery1.SQL.Text := 'SELECT * FROM INFORMATION_SCHEMA.TABLES';
  FDQuery1.Open;
  FDQuery1.GetFieldNames(ListBox1.Items);

I have a MySql database called 'MATestDB'. To get a list of the fields (columns) in its tables, I would add this code to TForm1.FormCreate:

  FDQuery2.SQL.Text := 'select * from information_schema.columns where table_schema = ''MATestDB''';
  FDQuery2.Open;

If you want FDQuery2 and its grid to track the selected table in FDQuery1, you can use code like the following to set up a master-detail relationship between them:

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDQuery1.SQL.Text := 'SELECT * FROM INFORMATION_SCHEMA.TABLES';

  FDQuery2.SQL.Text := 'select table_schema, table_name, column_name, data_type, character_maximum_length, ordinal_position from information_schema.columns where table_schema = :Table_Schema and table_name = :Table_Name';
  FDQuery2.IndexFieldNames := 'table_schema;table_name;ordinal_position';
  FDQuery2.MasterFields := 'table_schema;table_name';
  FDQuery2.MasterSource := DataSource1;

  FDQuery1.Open;
  FDQuery1.GetFieldNames(ListBox1.Items);

  FDQuery2.Open;
  FDQuery2.GetFieldNames(ListBox2.Items);

end;

Btw, you will not be able to get schema info for a Paradox database in the same way, but you should be able to google how to find out what information you want to gather from Paradox.

Btw #2: In the Sql you quoted in your deleted answer, one problem would be the reference to DBGrid2.SelectedField.ToString. If DBGrid2 gets its data from FDQuery2, then you may have meant DBGrid**1**.SelectedField.ToString. If you are still having problem with that, I suggest you ask about it in a new q, but make sure you include all the code necessary to reproduce the problem.

Thank you very much the code you gave me works perfectly – sasa Mar 31 '17 at 12:25 — sasa, Mar 31 '17 at 12:25

Display database structure from Delphi (rad studio)

1 Answers1

Linked