CODEWITHSUNDEEP
sqlfirebirdsqldatatypesdatefield
mtsys
mtsys

Reputation: 249

Firebird Data Type column field is not correct

I'm trying to extract the structure of a table of Firebird 1.5. I managed to grab the data but there is a field that shows how IBExpert numeric and my select shows double.

The field is TR_VALOR. SQL return decimal and IBExpert DDL numeric.

Return select * from RDB$TYPES where RDB$TYPE = 27, this: RDB$TYPE_NAME = Double.

Why this difference? How do the two cases return the same?

SQL:

SELECT
    a.RDB$FIELD_NAME Coluna,
    RDB$TYPE_NAME Tipo,
    RDB$FIELD_LENGTH Tamanho,
    RDB$FIELD_SCALE Escala,
    b.*
FROM
    RDB$RELATION_FIELDS a
    JOIN RDB$FIELDS b ON  b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE
    inner join RDB$TYPES c on c.RDB$TYPE = b.RDB$FIELD_TYPE

WHERE
    a.RDB$RELATION_NAME = 'TRANSACAO' and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
order by
    a.RDB$FIELD_POSITION;

SQL Result: enter image description here

IBExpert DDL: enter image description here

Upvotes: 2

Views: 1384

Answers (2)

IBExpert
IBExpert

Reputation: 11

You should analyze RDB$FIELD_SUB_TYPE too. Value of 1 means NUMERIC, 2 - DECIMAL.

Upvotes: 1

Martijn Tonies
Martijn Tonies

Reputation: 473

For NUMERIC or DECIMAL (basically the same), it's quite the mess in the system tables.

If sub_type is 1, it was defined as NUMERIC, if it's 2, it was DECIMAL. In case of 0, but a SCALE <> 0, it can be considered NUMERIC. If subtype = 0 and scale = 0, it's an original DOUBLE.

Take note, the TYPE can also be 7 (int16), 8 (int32) or 16 (int64). Same rules apply as above.

When creating a new column of this data type, Firebird tries to fit the specification in the smallest native datatype, so that's int16, int32, int64 and in Dialect 1, double.

Hope this helps.

Upvotes: 4

Related Questions