On 31-12-2013 11:25, Virna Constantin wrote:

>
>
> Hello,
> I built a procedure that returns me:
> - TAB_CMP ('MYTABLE', '') type and number of fields of table or
> - TAB_CMP ('MYTABLE, 11) name fields and the size of field id
> but I have the following problem, in the table RDB$RELATIONS the field
> RDB$FIELD_ID preserve the next value and in table RDB$RELATION_NAME the
> field RDB$FIELD_ID ai have number missing.
> What criteria can be used to have a a connection between
> RDB$RELATIONS.RDB$FIELD_ID and RDB$RELATION_NAME.RDB$FIELD_ID ?
> Mention that the problem appears of tables to have suffered deletions
> and adding fields.
> With this procedure I build SELECT statements without knowing the table
> structure.

RDB$RELATIONS.RDB$FIELD_ID does not contain what you think it does: it
contains the number of columns in the table; it is not a foreign key of
any kind (see Interbase 6.0 Language Reference page 266, or The Firebird
Book, 1st edition page 974 or 2nd edition page 935). As there is no
table RDB$RELATION_NAME, I assume you mean RDB$RELATION_FIELDS.

Take a look at what Jaybird does for DatabaseMetaData.getColumns() it is
similar to what you need:
http://sourceforge.net/p/firebird/code/58836/tree/client-java/trunk/src/main/org/firebirdsql/jdbc/FBDatabaseMetaData.java#l2363

In short, you need to join RDB$RELATIONS.RDB$RELATION_NAME to
RDB$RELATION_FIELDS.RDB$RELATION_NAME and then
RDB$RELATION_FIELDS.RDB$FIELD_SOURCE to RDB$FIELDS.RDB$FIELD_NAME

BTW: You might be able to reduce your stored procedure to a single query
(although I didn't look at all its details).

Mark
--
Mark Rotteveel