Interbase Array Columns: A Practical Guide with GPT Insights

Ian Barker's Blog Post

“InterBase supports array column types, but documentation is sparse and real-world examples are even rarer. Inspired by a recent blog post by Embarcadero’s Ian Barker, I decided to dig deeper—armed with a Custom GPT trained to navigate InterBase’s DocWiki.”

You can see Iain's blog post at the following link.

How To Read And Write Array Field Types In PostgreSQL and InterBase

Kudos to Barker for giving this seldom used feature some valuable exposure, and double kudos for including Postgres in the discussion. I've often wondered about the feature, but have generally had scant time to explore it in depth, especially given the often opaque nature of Interbase documentation.

Custom GPT

I created a Custom GPT during a recent Coursera course on AI Prompting. It’s configured with simple instructions and access to Embarcadero’s online InterBase DocWiki. It’s not a trained model—but a well-aimed tool. Here’s what it helped uncover about Interbase array fields:

►►What is the syntax to create an array field in Interbase?

◄◄Here’s how you define an array field in InterBase:

---

Term: Array field syntax in InterBase

DocWiki: Defining Arrays

InterBase lets you declare one- or multi-dimensional arrays in columns by appending dimensions in square brackets after a standard data type. The syntax resembles:

CREATE TABLE TableName (
  Column1 DataType,
  ArrayCol DataType[dim1, dim2, …],
  …
);

• For a single‑dimensional array of 12 integers:

  ArrayCol INTEGER[12]
  

  This implicitly defines bounds 1..12.([docwiki.embarcadero.com]^[1], [docwiki.embarcadero.com]^[2])

• To specify explicit lower and upper bounds (e.g. from 0 to 3):

  ArrayCol INTEGER[0:3]
  

  The subscripts then run from 0 through 3 inclusive.([docwiki.embarcadero.com]^[2:1])

• Multi-dimensional arrays use commas between dimensions:

  Matrix INTEGER[2:4, 0:3]
  

  This defines a 2× dimension from indices 2–4, and second from 0–3.([ib-aid.com]^[3])

Example: CREATE TABLE syntax in InterBase

From documentation and examples, to create a table with a one-dimensional integer array of 12 elements:

CREATE TABLE TABLE_WITH_ARRAY (
  THE_YEAR INTEGER NOT NULL,
  MY_ARRAY INTEGER[1:12],
  CONSTRAINT UNIQUE_YEAR UNIQUE (THE_YEAR)
);

This creates MY_ARRAY as an array containing integers at subscripts 1 through 12.([blogs.embarcadero.com]^[4])

---

Example: Usage in Delphi via FireDAC

If using Delphi with FireDAC, suppose you have defined:

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

In Delphi code:

• Prepare an insert SQL, then configure parameters:

  Qry.Params[2].DataTypeName := 'FDQA_ARRAY.ITEMS';
  Qry.Params[2].ArrayType := atTable;
  Qry.Params[2].ArraySize := 4;
  Qry.Params[2].AsStrings[0] := 'A1';
  Qry.Params[2].AsStrings[1] := 'A2';
  …
  Qry.ExecSQL;
  

  FireDAC uses a TArrayField at runtime for reading, making access to individual elements easy.([delphiaball.co.uk]^[5])

---

Summary

• For a simple 1D integer array of N elements:

  MyArrayField INTEGER[N]
  

• To control index range explicitly:

  MyArrayField INTEGER[lower:upper]
  

• For multiple dimensions:

  MyArrayField INTEGER[dim1_lower:dim1_upper, dim2_lower:dim2_upper, …]
  

---

🛠 Examples

CREATE TABLE DemoArrays (
  one_dim  INTEGER[12],           -- 1 to 12
  zero_based CHAR(10)[0:3],      -- subscripts 0 through 3
  matrix2D  INTEGER[2:4,0:3]      -- dims: [2..4] × [0..3]
);

In Delphi/FireDAC:

• Set TFDParam.ArrayType := atTable
• Set TFDParam.DataTypeName := '<Table>.<Field>'
• Provide ArraySize to match dimension count

---

Let me know if you’d like help reading or updating slices of those arrays or integrating them into embedded SQL or FireDAC code!

---

1. https://docwiki.embarcadero.com/InterBase/2020/en/Defining_Arrays?utm_source=chatgpt.com "Defining Arrays - InterBase - Embarcadero DocWiki" ↩︎

2. https://docwiki.embarcadero.com/InterBase/2020/en/Specifying_Subscript_Ranges?utm_source=chatgpt.com "Specifying Subscript Ranges - InterBase - Embarcadero DocWiki" ↩︎ ↩︎

3. https://ib-aid.com/en/articles/data-types/?utm_source=chatgpt.com "Data types - IBSurgeon" ↩︎

4. https://blogs.embarcadero.com/how-to-read-and-write-array-field-types-in-postgresql-and-interbase/?utm_source=chatgpt.com "How To Read And Write Array Field Types In PostgreSQL and ..." ↩︎

5. https://delphiaball.co.uk/2016/04/29/interbase-array-fields-firedac/?utm_source=chatgpt.com "InterBase Array Fields and FireDAC - Stephen Ball's Technical Blog" ↩︎

Trials and Experiments

I didn't have time to do more than define an Interbase table in a couple of different ways. I didn't write any code to test CRUD operations on the table and the array columns. However, table and column definitions did work as advertised and seem to have been reasonably handled by my software. I followed a few simple steps:

• I used XCase, a somewhat old but reliable Entity Relational Modeling tool I use regularly. The test entity I defined appears at the top of this blog post. You can see that there are two columns defined as arrays.
  • One column is a one-dimensional array of integers.
  • The other column is a two-dimensional array of UTF8 strings.
• XCase correctly generated the CREATE statement needed to define the table and all columns.

CREATE DOMAIN ID_KEY CHAR(16) CHARACTER SET OCTETS NOT NULL COLLATE OCTETS
^

CREATE TABLE  Array_Entity
( 
     AE_KEY ID_KEY NOT NULL,
     AE_INTEGER_1 INTEGER [5:24] NOT NULL,
     AE_VARCHAR_2 VARCHAR(10) [1:50,1:50] CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
        CONSTRAINT PK_Array_Entity PRIMARY KEY (AE_KEY)
)
^

• Using IBConsole, the properties of the table appear to be correct.
• IBConsole shows the table metadata to be what I expected.
  

Conclusion and Takeaways

While rarely used, InterBase array columns are powerful tools for modeling data with fixed-size sequences. FireDAC makes working with them relatively straightforward. With good modeling tools like XCase and the right parameter setup in Delphi, you can simplify schema design without sacrificing performance. Let me know if you’d like to see more examples—or FireDAC CRUD operations using array fields—in a future post.

I hope this has made using array columns a little easier. Let me know in the comments if I have missed any important details.