On Mon, 14 May 2012 15:55:56 +0200, Michele Federici
<mfederici@...>
wrote:

> Hi,
>
> I've extract datatype by this query and i've a question to detect BIGINT

> Datatype
>
> SELECT a.RDB$RELATION_NAME, b.RDB$FIELD_NAME, b.RDB$FIELD_ID,
> d.RDB$TYPE_NAME,
> c.RDB$FIELD_LENGTH, c.RDB$FIELD_SCALE , c.RDB$FIELD_PRECISION,
> C.RDB$COMPUTED_SOURCE,c.RDB$FIELD_TYPE, c.RDB$FIELD_SUB_TYPE,
> CASE
> WHEN c.RDB$FIELD_PRECISION > 0 AND c.RDB$FIELD_SUB_TYPE = 1 THEN
> 'NUMERIC'
> WHEN c.RDB$FIELD_PRECISION > 0 AND c.RDB$FIELD_SUB_TYPE = 2 THEN
> 'DECIMAL'
> WHEN d.RDB$TYPE_NAME = 'LONG' THEN 'INTEGER'
> WHEN d.RDB$TYPE_NAME = 'SHORT' THEN 'SMALLINT'
> WHEN d.RDB$TYPE_NAME = 'INT64' THEN 'NUMERIC'
> WHEN d.RDB$TYPE_NAME = 'DOUBLE' THEN 'DOUBLE PRECISION'
> WHEN d.RDB$TYPE_NAME = 'VARYING' THEN 'VARCHAR'
> WHEN d.RDB$TYPE_NAME = 'TEXT' THEN 'CHAR'
> WHEN d.RDB$TYPE_NAME = 'BLOB' THEN 'BLOB'
> ELSE d.RDB$TYPE_NAME
> END AS MY_FIELD_TYPE,
> CASE
> WHEN c.RDB$COMPUTED_SOURCE IS NULL THEN '0'
> WHEN c.RDB$COMPUTED_SOURCE IS NOT NULL THEN '1'
> END AS MY_FIELD_CALCULATED
> FROM RDB$RELATIONS a
> INNER JOIN RDB$RELATION_FIELDS b ON a.RDB$RELATION_NAME =
> b.RDB$RELATION_NAME
> INNER JOIN RDB$FIELDS c ON b.RDB$FIELD_SOURCE = c.RDB$FIELD_NAME
> INNER JOIN RDB$TYPES d ON c.RDB$FIELD_TYPE = d.RDB$TYPE
> WHERE a.RDB$SYSTEM_FLAG = 0 AND d.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'

and

> a.RDB$RELATION_NAME = :IN_TBLNAME AND b.RDB$FIELD_NAME = :IN_FLDNAME
>
> It's correct if I detect a field "BIGINT" by check if
> "RDB$FIELD_SUB_TYPE" is 0. If this field isn't "0" it can be a "NUMERIC"

> or "DECIMAL" field.
>
> Is it right?

Subtype 0 => BIGINT, Subtype 1 => NUMERIC, Subtype 2 => DECIMAL
Be aware that it is possible that sometimes a numeric or decimal has
subtype 0, but this can be detected by looking at the field precision (I am
not sure if and when this occurs, but the code in Jaybird does take this
into account).

Your current construct does not take fields of NUMERIC(x,0) and
DECIMAL(x,0) into account. Also your use of precision and sub-type to
decide on NUMERIC and DECIMAL for all field types is risky (I am not sure
if there are subtypes with precision > 0 for other types, but if there are
your code will produce incorrect info).