At 09:49 AM 27-05-01 +0000, Andy Garner wrote:

>Am I missing something or is there an inconsistancy in the system
>tables?
>
>A field defined as say numeric(18) has a rdb$field_type value of 16
>in rdb$fields. Should I therefore expect 16 to be included as a
>value for field type in rdb$types?

Yes, you are missing something. :))

rdb$types.rdb$type and rdb$fields.rdb$field_type are not related.

rdb$types is for defining enumerated data types but it's not used for that yet, at least according to the manual. Instead, the first 10 members of that table are "borrowed" for storing the object types (e.g. table, view, trigger, etc.) for use by the rdb$dependencies table, which links its column rdb$dependent_type to rdb$types.rdb$type.

These are *not* datatypes (although, if enum data types are ever implemented, it would be arguable!). It's a coincidence that the object type "Field" happens to use 9 as an identifier and happened to get a "hit" in the join because there is a rdb$field_type of 9.

As to where the control values for rdb$fields.rdb$field_type live in the database, it's anyone's guess. Probably they are just kept there in chains in rdb$fields to discourage anyone from altering them.

Funny join statement, btw. Jumbling up SQL-89 and SQL-92 join syntax sometimes produces unexpected results...

Cheers,
Helen

>ODS 10.0
>Implementation 1.50.1
>Level 1.6
>Server Version WI-T0.9.4.41 Firebird Test 1
>
>To re-produce:
>==============
>
>SET SQL DIALECT 3;
>CREATE DATABASE 'c:\foo.gdb' USER 'SYSDBA' PASSWORD 'masterkey';
>CREATE TABLE ATABLE (PK NUMERIC(18) NOT NULL, DATAFIELD1 NUMERIC(18),
>DATAFIELD2 NUMERIC(5), PRIMARY KEY (PK) );
>
>select
> r.rdb$field_name r_field,
> f.rdb$field_length f_len,
> f.rdb$field_type f_type,
> t.rdb$type t_type,
> t.rdb$type_name t_type_name
>from
> rdb$relation_fields r left outer join
> rdb$fields f on f.rdb$field_name = r.rdb$field_source
>left outer join
> rdb$types t on f.rdb$field_type = t.rdb$type and
> t.rdb$field_name = 'RDB$FIELD_TYPE'
>where
> r.rdb$relation_name = 'ATABLE';
>
>/*
>
>r_field f_len f_type t_type t_type_name
>--------- ----- ------ ------ -----------
>PK 8 16 <null> <null>
>DATAFIELD1 8 16 <null> <null>
>DATAFIELD2 4 8 8 LONG
>
>*/

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________