How can I get the table description (fields and types) from Firebird with dbExpress

Question

I have written a tool for displaying database structures using the GetTableNames and GetFieldNames methods of TSQLConnection. How can I get the types of each field name similar to the following list (which is part of the DDL required to build the table)?

TABLE: ARTICLES
ID INTEGER NOT NULL
PRINTED SMALLINT DEFAULT 0
ACADEMIC SMALLINT
RELEVANCE SMALLINT
SOURCE VARCHAR(64) CHARACTER SET WIN1251 COLLATE WIN1251
NAME VARCHAR(128) CHARACTER SET WIN1251 COLLATE WIN1251
FILENAME VARCHAR(128) CHARACTER SET WIN1251 COLLATE WIN1251
NOTES VARCHAR(2048) CHARACTER SET WIN1251 COLLATE WIN1251

Try to look for [`Detailed table field info`](http://www.alberton.info/firebird_sql_meta_info.html) or look at [`this FAQ`](http://www.firebirdfaq.org/faq174/). — TLama, Aug 22 '12 at 09:41
@TLama: The detailed table field info gave me the necessary clues - certainly much better to iterate over the system tables than open a query with one record and determine the field types from that query. How can I reward you? Maybe turn your comment into an answer with a code example. — No'am Newman, Aug 22 '12 at 10:59
Thanks! I would post the answer, but I couldn't verify the result (I was just hoping the query from the article works :-), that's why I've made it a comment. Feel free to post and accept your own answer. It will be helpful to keep it here since we don't know how long the sites I've referred to will be alive. — TLama, Aug 22 '12 at 11:48
DBX is about compatibility to different engines... TBXTable has property ValueType[const Ordinal: TInt32]: TDBXValueType read GetValueType; Converting reault to DB-specific script might be separate task. For basic FB types probably TIBExtract component would work. — Arioch 'The, Aug 22 '12 at 12:09
if you want to make it absolutely with "SELECT * FROM rdb$ ..." , the following solution is not for you. But if you want an easy and fast way, you can use isql.exe with "CreateProcess(nil, Pchar('isql.exe' +' '+ Params)" and Params point to a file e.g. myTableFieldinfo.sql. All the information you need is available in file myTableFieldinfo.sql — moskito-x, Aug 23 '12 at 01:40

score 21 · Accepted Answer · answered Aug 22 '12 at 13:47

This is incomplete (because I've never used Firebird array data types) and not much tested but perhaps it will give you a good starting point:

SELECT
  RF.RDB$FIELD_NAME FIELD_NAME,
  CASE F.RDB$FIELD_TYPE
    WHEN 7 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'SMALLINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 8 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'INTEGER'
        WHEN 1 THEN 'NUMERIC('  || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 9 THEN 'QUAD'
    WHEN 10 THEN 'FLOAT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 14 THEN 'CHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
    WHEN 16 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'BIGINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 27 THEN 'DOUBLE'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN 'VARCHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 40 THEN 'CSTRING' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 45 THEN 'BLOB_ID'
    WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
    ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
  END FIELD_TYPE,
  IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, 'NOT NULL') FIELD_NULL,
  CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
  DCO.RDB$COLLATION_NAME FIELD_COLLATION,
  COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
  F.RDB$VALIDATION_SOURCE FIELD_CHECK,
  RF.RDB$DESCRIPTION FIELD_DESCRIPTION
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (RF.RDB$RELATION_NAME = :TABLE_NAME) AND (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$FIELD_POSITION;

This code probably works, but in the version (1.5) of Firebird which I am using, I got an error message about line 35, which is the IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0 part. — No'am Newman, Aug 24 '12 at 13:33
That's probably because Firebird 1.5 doesn't support `IIF`. You can replace it with functionally equivalent [`CASE`](http://www.firebirdsql.org/refdocs/langrefupd15-case.html) statement: `CASE COALESCE(RF.RDB$NULL_FLAG, 0) WHEN 0 NULL ELSE 'NOT NULL' END`. — Ondrej Kelle, Aug 24 '12 at 13:58
I'm accepting your answer, with two caveats: (1) The line quoted above is missing the word 'THEN' before 'NULL', and 'NULL' should be quoted; (2) There's also no TRUNC function so I'm deleting that keyword and performing the TRUNC in the program itself. — No'am Newman, Aug 25 '12 at 11:35

Andrej Kirejeŭ · Answer 2 · 2012-08-22T13:59:45.383

Use direct access to RDB$ tables. For example:

SELECT * FROM rdb$relations

will give you a list of all tables in a database.

SELECT
  *
FROM
  rdb$relation_fields rf JOIN rdb$fields f
    ON f.rdb$field_name = rf.rdb$field_source
WHERE
  rf.rdb$relation_name = :RN

will result in a list of all fields of given table with information of field type. Param RN is a name of the table.

Using information from RDB$tables one can easily construct DDL statement. The query below gives you a hint how to do it:

SELECT
  TRIM(rf.rdb$field_name) || ' ' ||
  IIF(rdb$field_source LIKE 'RDB$%',
  DECODE(f.rdb$field_type, 
    8,  'INTEGER', 
    12, 'DATE', 
    37, 'VARCHAR', 
    14, 'CHAR', 
    7,  'SMALLINT'),
  TRIM(rdb$field_source)) ||
  IIF((rdb$field_source LIKE 'RDB$%') AND (f.rdb$field_type IN (37, 14)),
    '(' || f.rdb$field_length || ')',
    '') ||
  IIF((f.rdb$null_flag = 1) OR (rf.rdb$null_flag = 1), 
    ' NOT NULL', '')
FROM
  rdb$relation_fields rf JOIN rdb$fields f
    ON f.rdb$field_name = rf.rdb$field_source
WHERE
  rf.rdb$relation_name = '<put_your_table_name_here>'

There's a minor spelling mistake after the word JOIN - should be rdb$fields. Anyway, the query didn't return what I was looking for. I'll upvote the answer but it wasn't THE answer. — No'am Newman, Aug 22 '12 at 11:06

score 3 · Answer 3 · answered Aug 23 '12 at 04:19

Using the link which TLama provided, I found my own solution, which is somewhat similar to the above solutions, but simpler.

SELECT R.RDB$FIELD_NAME AS field_name,
CASE F.RDB$FIELD_TYPE
 WHEN 7 THEN 'SMALLINT'
 WHEN 8 THEN 'INTEGER'
 WHEN 9 THEN 'QUAD'
 WHEN 10 THEN 'FLOAT'
 WHEN 11 THEN 'D_FLOAT'
 WHEN 12 THEN 'DATE'
 WHEN 13 THEN 'TIME'     
 WHEN 14 THEN 'CHAR'
 WHEN 16 THEN 'INT64'
 WHEN 27 THEN 'DOUBLE'
 WHEN 35 THEN 'TIMESTAMP'
 WHEN 37 THEN 'VARCHAR'
 WHEN 40 THEN 'CSTRING'
 WHEN 261 THEN 'BLOB'
 ELSE 'UNKNOWN'
END AS field_type,
F.RDB$FIELD_LENGTH AS field_length,
CSET.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS R
LEFT JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
LEFT JOIN RDB$CHARACTER_SETS CSET ON F.RDB$CHARACTER_SET_ID = CSET.RDB$CHARACTER_SET_ID
WHERE R.RDB$RELATION_NAME= :p1
ORDER BY R.RDB$FIELD_POSITION

p1 is the table name which is passed as a parameter to the query.

In context, I have a treeview which has as its nodes the table names of a given database; for each node, the child nodes are the fields along with their definitions.

 sqlcon.GetTableNames (dbTables);    // sqlcon is the TSQLConnection
 tv.items.Clear;
 for i:= 1 to dbTables.count do
  begin
   node:= tv.items.Add (nil, dbTables[i - 1]);
   with qFields do                   // the above query
    begin
     params[0].asstring:= dbTables[i - 1];
     open;
     while not eof do
      begin
       tv.items.addchild (node, trim (fieldbyname ('field_name').asstring) + ', ' +
                                trim (fieldbyname ('field_type').asstring) + ', ' +
                                fieldbyname ('field_length').asstring + ', ' +
                                fieldbyname ('field_charset').asstring);
       next
      end;
     close
    end
  end;

Here is a screenshot of the program in action. I realise that the format is not the same as the DDL which I quoted, but it's obvious what each field means (at least to me, and this is a program for my private use).

enter image description here

@TOndrej: what's wrong? Maybe incomplete. What IS wrong is that the character set should be Win1255 but I didn't know better when I defined the database and I understand that it's too late to change now. — No'am Newman, Aug 24 '12 at 03:50
Compare with my answer. You get wrong data type in some cases, and wrong length with multi-byte character set fields. It might report correct values for your special case by coincidence, though. — Ondrej Kelle, Aug 24 '12 at 08:01

Pablo T. · Answer 4 · 2014-04-25T23:26:05.433

I made a litle change to the first option to support computed by fields, add field_position and made a view to make more easy.

CREATE VIEW TABLES (
  TABLE_NAME,
  FIELD_NAME,
  FIELD_POSITION,
  FIELD_TYPE,
  FIELD_NULL,
  FIELD_CHARSET,
  FIELD_COLLATION,
  FIELD_DEFAULT,
  FIELD_CHECK,
  FIELD_DESCRIPTION
)
AS
SELECT
  RF.RDB$RELATION_NAME,
  RF.RDB$FIELD_NAME FIELD_NAME,
  RF.RDB$FIELD_POSITION FIELD_POSITION,
  CASE F.RDB$FIELD_TYPE
    WHEN 7 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'SMALLINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 8 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'INTEGER'
        WHEN 1 THEN 'NUMERIC('  || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 9 THEN 'QUAD'
    WHEN 10 THEN 'FLOAT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 14 THEN 'CHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
    WHEN 16 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'BIGINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 27 THEN 'DOUBLE'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN
     IIF (COALESCE(f.RDB$COMPUTED_SOURCE,'')<>'',
      'COMPUTED BY ' || CAST(f.RDB$COMPUTED_SOURCE AS VARCHAR(250)),
      'VARCHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')')
    WHEN 40 THEN 'CSTRING' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 45 THEN 'BLOB_ID'
    WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
    ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
  END FIELD_TYPE,
  IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, 'NOT NULL') FIELD_NULL,
  CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
  DCO.RDB$COLLATION_NAME FIELD_COLLATION,
  COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
  F.RDB$VALIDATION_SOURCE FIELD_CHECK,
  RF.RDB$DESCRIPTION FIELD_DESCRIPTION
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$FIELD_POSITION
;

How can I get the table description (fields and types) from Firebird with dbExpress

4 Answers4

Linked