Result Set from a TFDScript?

Question

I found some code which works in FlameRobin to return the number of records for all tables in the database but am only able to run it in a TFDScript which works fine but no result set is accessible.

I tried various ways in TFDQuery and TFDCommand and looked at TFDConnection but they all error with 'unable to parse object 'set term'' as they are not expecting a script.

After searching for ages I can't find anyway of accessing results from TFDScript though there is a hint it can as it has a FetchOptions property.

I have the script below and would be grateful for any guidance.

set term !! ;
EXECUTE BLOCK
returns ( stm varchar(60), cnt integer )
as
BEGIN
for select cast('select count(*) from "'||trim(r.RDB$RELATION_NAME)||'"' as varchar(60))
from RDB$RELATIONS r
where (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0) and r.RDB$VIEW_BLR is null
order by 1
into :stm
DO
BEGIN
execute statement :stm into :cnt;
suspend;
END
END

Answer 1

I couldn't immediately find a way of getting the result set from the FDScript. However, it is straightforward to get the result set using an FDQuery configured to access the Firebird database, preferably via an FDConnection.

1. Place the TFDConnection and TFDQuery on the form of a new VCL project.

2. Add a TDataSource and TDBGrid to the form, with the TDataSource's DataSet set to FDQuery1 and DBGrid1's DataSource set to DataSource1. Set the width of DBGrid1 to 500 or more.

3. Set FDQuery1's Sql.Text to

    EXECUTE BLOCK
    returns ( stm varchar(60), cnt integer )
    as
    BEGIN
    for select cast('select count(*) from "'||trim(r.RDB$RELATION_NAME)||'"' as varchar(60))
    from RDB$RELATIONS r
    where (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0) and r.RDB$VIEW_BLR is null
    order by 1
    into :stm
    DO
    BEGIN
    execute statement :stm into :cnt;
    suspend;
    END
    END

Of course, this is just the contents of the script you quoted, minus the Set Term ... statement, which isn't needed.

4. Set FDQuery1's Active to True and the query contents should appear in the DBGrid.

Update I'm not sure why you are getting the error mentioned in your comment as the above code works fine for me in Delphi Seattle against the demo Employee.FDB database. If that problem persists, you can use the code below to generate the necessary Sql yourself and execute it.

procedure TForm2.btnGenSqlClick(Sender: TObject);
var
  Sql : String;
  TableName : String;
const
  sGetTableNames = 'select r.RDB$RELATION_NAME from RDB$RELATIONS r where '#13#10 +
    ' (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0) and r.RDB$VIEW_BLR is null';
begin
  FDQuery1.Open(sGetTableNames);
  Sql := '';
  while not FDQuery1.Eof do begin
    TableName := FDQuery1.Fields[0].AsString;
    if Sql <> ''  then
      Sql := Sql + #13#10' union ';
    Sql := Sql + Format('select ''%0:s'', count(*) from %0:s', [TableName]);
    FDQuery1.Next;
  end;
  FDQuery1.Open(Sql);
end;

Note: In case you're not familiar with it, the construct %0:s means 'substitute the value of the first value in the array of const passes to Format'. I've used it to substitute the TableName value twice in the same format expression.