Subject Field identification procedure
Author Virna Constantin
Post date 2013-12-31T10:25:36Z
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.

Sorry for my english !

SET TERM ^ ;
CREATE PROCEDURE TAB_CMP (
    M_TAB varchar(15),
    M_CMP varchar(15) )
RETURNS (
    K_CMP varchar(15),
    K_POZ smallint,
    K_TIP varchar(15),
    K_LEN smallint,
    K_ZEC smallint )
AS
DECLARE m_var smallint; 
BEGIN
    m_tab=upper(trim(m_tab));
    m_cmp=upper(trim(m_cmp));

    k_cmp='';
    k_poz=0;
    k_tip='';
    k_len=0;
    k_zec=0;

    select rdb$dbkey_length,rdb$field_id from rdb$relations 
    where rdb$relation_name=:m_tab 
    into :m_var,:k_len;
    if (row_count=0 or m_cmp='') then
    begin
        k_tip=iif(m_var=8,'TABELA',iif(m_var>8,'VIEW','?!! Tab'));
        suspend;
        exit;
    end

    m_var=0;
    k_len=0;
    if (left(m_cmp,1) between '0' and '9') then
        begin
            m_var=cast(:m_cmp as smallint);
            select rdb$field_name from rdb$relation_fields 
            where rdb$relation_name=:m_tab and rdb$field_position=:m_var
            into :k_cmp;

            if (row_count=0) then
            begin
                k_tip='?!! Poz';
                suspend;
                exit;
            end

            m_var=-1;
        end
    else
        k_cmp=m_cmp;

    select 
        case A.rdb$field_type
            when 7 then
                case A.rdb$field_sub_type
                    when 0 then 'SMALLINT'
                    when 1 then 'NUMERIC'
                    when 2 then 'DECIMAL'
                end
            when 8 then
                case A.rdb$field_sub_type
                    when 0 then 'SMALLINT'
                    when 1 then 'NUMERIC'
                    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'
            when 16 then 
                case A.rdb$field_sub_type
                    when 0 then 'SMALLINT'
                    when 1 then 'NUMERIC'
                    when 2 then 'DECIMAL'
                end
            when 27 then 'DOUBLE'
            when 35 then 'TIMESTAMP'
            when 37 then 'VARCHAR'
            when 40 then 'CSTRING'
            when 45 then 'BLOB_ID'
            when 261 then 'BLOB SUB_TYPE'
            else '?!! Tip'
        end,
        B.rdb$field_position,
        iif(A.rdb$field_type in (7,8,16),A.rdb$field_precision,
            iif(A.rdb$field_type in (14,37),A.rdb$field_length,
            iif(A.rdb$field_type=261,A.rdb$field_sub_type,0))),
        iif(A.rdb$field_type in (7,8,16) and A.rdb$field_sub_type=1,
            -A.rdb$field_scale,0)
    from rdb$fields A inner join rdb$relation_fields B
        on A.rdb$field_name=B.rdb$field_source
    where B.rdb$relation_name=:m_tab and B.rdb$field_name=:k_cmp
    into :k_tip,:k_poz,:k_len,:k_zec;

    if (row_count=0) then
        k_tip='?!! Cimp';

    suspend;
END^
SET TERM ; ^