mysql - 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;

Answer 1

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:

1. In a new Delphi project, place a TFDConnection, TFDQuery, TDataSource, TDataSource and a TListBox on a form. Save the form and project.

2. Double-click FDConnection1 to pop up its connection editor and configure it so you can successfully connect it to your database.

3. Connect DBGrid1 to DataSource1 and Datasource1 to FDQuery1.

4. Add the code below to the form's OnCreate event.

5. Compile and run.

6. 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.