InterBase Array Fields and FireDAC

InterBase Array Field

The IoT Award Winning database InterBase, has for a long time supported the ability to store data in an array field type. This was originally introduced as a new field type to reduce API calls and speed up data write time for a specific Boeing project many years ago. Writing each value into an array can also simplified the data model as well as benefiting database performance.

While array field in InterBase have been around for a long time, they have not aways been that easy to access from the components layer reducing their adoption.

With the growth of IoT, and the ability to store multiple data points per record becoming much more common place today, the ability to work with data in the InterBase array field type using FireDAC components has been added to 10.1 Berlin.

Explore the sample in depth, and get the link the sample below.

Inserting data to InterBase array fields

Inside the samples (path at the bottom of this blog) for RAD Studio 10.1 Berlin you will find a FireDAC InterBase sample for working with arrays.

Before you run the sample you need to make a table for the sample to work with. (tip open the FDConnection on the sample form, connect to an existing database and run this form the script tab)

CREATE TABLE FDQA_Array (
 id INTEGER NOT NULL PRIMARY KEY,
 name VARCHAR(20),
 items VARCHAR(5)[4]
);

Here you can see the array field called items created as a VARCHAR(5) holding 4 elements.

With the table created, we can now explore sample code to write data into the database. Here a simple insert statement is prepared and the items field parameter defined.

Qry.SQL.Text := 'insert into FDQA_Array '+
                ' values '+
                '(:id, :name, :items)';
// TFDParam.DataTypeName must be set to 
// <Table name>.<Field name>
Qry.Params[2].DataTypeName := 'FDQA_ARRAY.ITEMS';
// TFDParam.ArrayType must be set to atTable,
// not to atArray
Qry.Params[2].ArrayType := atTable;
// TFDParam.ArraySize must be set to array size
Qry.Params[2].ArraySize := 4;
for i := 1 to 5 do begin
  Qry.Params[0].AsInteger := i;
  Qry.Params[1].AsString := 'aaa' +  IntToStr(i);
  Qry.Params[2].AsStrings[0]:= 'A1.'+IntToStr(i);
  Qty.Params[2].AsStrings[1]:= 'A2.'+IntToStr(i);
  Qry.Params[2].AsStrings[2]:= 'A3.'+IntToStr(i);
  Qry.Params[2].AsStrings[3]:= 'A4.'+IntToStr(i);
  // Use ExecSQL, not Execute(4, 0) or similar
  Qry.ExecSQL;
end;

FireDAC requires you set the DataTypeName property to the  <Table>.<FieldName>, for example FDQA_Array.items; and the  ArrayType to atTable.

While ArrayFields are great for storage of an array of data points, they don’t seem to be compatible with using ArrayDML through FireDAC.

Reading data from InterBase array fields.

Unit Data.DB defines a special field for dealing with arrays – TArrayField. This makes working with arrays very simple indeed. In fact, the VCL Grid already supports array fields so you can easily add to existing VCL applications InterBase Array Fields.

Following the creation of the data in the above method, showing it is as simple as running a select statement e.g.

Select * from 'FDQA_ARRAY.ITEMS';
VCL Grid with InterBase Array Field
VCL Grid with InterBase Array Field

TArrayField

In this example, the Items field is returned as a TArrayField. As FieldByName returns a TField, we need to type cast the TField as a TArrayField.  TArrayField allows you access to extra methods enabling you to query e.g. the size of the array and the variants in the array.

To illustrate, I’ve added a new button to the sample with the following code to list the first value in the array via a ShowMessage.

procedure TFrmMain.Button1Click(Sender: TObject);
var
 F: TArrayField;
 V: Variant;
begin
 F := TArrayField(FDQuery1.FieldByName('ITEMS'));
 V := F.FieldValues[0];
 ShowMessage(VarToStr(V));
end;

Working with TArrayField

I hope that gets you started. I’m already thinking this is a great field type for storing Latitude / Longitude values for example.

InterBase Array Example Source Code

  • InterBase Arrays – SVN
  • Open Samples from the IDE and follow the path to
    • \Samples\Object Pascal\Database\FireDAC\Samples\DBMS Specific\InterBase\Arrays

If you are looking to speed up data writing then don’t forget also to look at my InterBase ArrayDML blog post for fast insertion of data. 

Leave a Reply

Your email address will not be published.