How to add all fields without opening dataset?

Question

I'm using TFIBDataSet (Firebird dataset component) and I'm trying to automatically create all fields at runtime, without loading any record into the dataset.

Here is the code of my test:

uses
  FIBDatabase, FIBDataSet, Dialogs;

...

var
  Db : TFIBDataBase;
  Tr : TFIBTransaction;
  Dst : TFIBDataSet;
begin
  //connection
  Db := TFIBDatabase.Create(Self);
  Db.ConnectParams.UserName := 'SYSDBA';
  Db.ConnectParams.Password := 'masterkey';
  Db.DatabaseName := 'localhost:mydatabase.fdb';
  Db.SQLDialect := 3;
  Db.Connected := True;

  //transaction
  Tr := TFIBTransaction.Create(Self);
  Tr.DefaultDatabase := Db;
  Tr.Active := True;

  //dataset
  Dst := TFIBDataSet.Create(Self);
  Dst.Database := Db;
  Dst.Transaction := Tr;
  Dst.SelectSQL.Text := 'SELECT * FROM rdb$database';

  //...

  ShowMessage(IntToStr(Dst.FieldCount));
end;

The previous code produces '0' as output..

I've tried using Dst.Open() and it produces '5', but it also executes the SQL query.

It produces 0 because you're not creating any fields. Either you need to know about the fields in advance and create them, or you need to retrieve the metadata of the fields from the DB (but still create the fields). — Dave Nottage, Aug 30 '18 at 15:41

Fabrizio · Accepted Answer · 2018-08-31T06:40:49.740

0

I did it by calling FieldDefs.Update and creating the fields from the FieldDefs list.

uses
  FIBDatabase, FIBDataSet, Dialogs;

...

var
  Db : TFIBDataBase;
  Tr : TFIBTransaction;
  Dst : TFIBDataSet;
  I : integer;
begin
  //connection
  Db := TFIBDatabase.Create(Self);
  Db.ConnectParams.UserName := 'SYSDBA';
  Db.ConnectParams.Password := 'masterkey';
  Db.DatabaseName := 'localhost:mydatabase.fdb';
  Db.SQLDialect := 3;
  Db.Connected := True;

  //transaction
  Tr := TFIBTransaction.Create(Self);
  Tr.DefaultDatabase := Db;
  Tr.Active := True;

  //dataset
  Dst := TFIBDataSet.Create(Self);
  Dst.Database := Db;
  Dst.Transaction := Tr;
  Dst.SelectSQL.Text := 'SELECT * FROM rdb$database';

  //create fields
  Dst.FieldDefs.Update();
  for I := 0 to Dst.FieldDefs.Count - 1 do
    Dst.FieldDefs[I].CreateField(Dst);

  ShowMessage(IntToStr(Dst.FieldCount));
end;

edited Aug 31 '18 at 06:40

answered Aug 30 '18 at 16:00

Fabrizio

7,603
6
44
104

Preparing the SQL command (before, or without its execution) usually describes the fields. I believe FIBPlus won't be different. I can take a look if you want, or you may try to search for some kind of `Prepare` method. Besides, isn't there some kind of `TFIBQuery` component? [don't have FIBPlus by hand right now] – Victoria Aug 30 '18 at 16:06
@Victoria: Yes, there is a `TFIBQuery` class. Anyhow, you're right saying that `Prepare` will describe the fields. I've tried calling `Dst.Prepare` and after doing that, the `Dst.FieldDefs` have been updated. I've also found that `TFIBDataSet` initializes the `FieldDefs` by calling `Dst.FieldDefs.Update` method. I've updated the answer – Fabrizio Aug 31 '18 at 06:42
So, your question was how to create field objects from field definition objects? I thought you wanted to assign a SQL command and let the dataset object prepare fields for it, but not from field definitions but from the column descriptions provided by the DBMS for that SQL command. – Victoria Aug 31 '18 at 11:23
@Victoria: And you were right. At the beginning, `Dst.FieldDefs.Count` was 0. Calling `TFIBDataSet.FieldDefs.Update` causes the dataset to takes all fields definitions from the database (Monitoring the SQL, I see that it executes two queries, the first selects from `rdb$relations` and the second selects from `rdb$relation_fields JOIN rdb$fields`). Once loaded the fields definitions, then it has been simple but the core of the question, as you thought, was that to obtain the fields starting from the SQL – Fabrizio Aug 31 '18 at 13:41
Well, in general, if you want to obain fields for a returned resultset and you don't know them in forward (or don't want to be fixed to them), you need to prepare the SQL command (most of the DB libraries describe the fields when doing that). However, in Delphi (so as might be in other modern languages), you can say kind of sentence like _"hey, I know the columns, let me define them and what will the DBMS return I know"_ – Victoria Aug 31 '18 at 13:50
Btw. what is the aim of the task? Reading metadata is handled by FIBPlus in some way.. – Victoria Aug 31 '18 at 13:54

How to add all fields without opening dataset?

1 Answers1