sqlalchemy: Wrong reflect columns from FireBird

To retrieve a columns from FireBird the following query is used:

        SELECT r.rdb$field_name AS fname, char_length(r.rdb$field_name),
                        r.rdb$null_flag AS null_flag,
                        t.rdb$type_name AS ftype,
                        f.rdb$field_sub_type AS stype,
                        f.rdb$field_length/
                            COALESCE(cs.rdb$bytes_per_character,1) AS flen,
                        f.rdb$field_precision AS fprec,
                        f.rdb$field_scale AS fscale,
                        COALESCE(r.rdb$default_source,
                                f.rdb$default_source) AS fdefault,
                                character_length(r.rdb$field_name)
        FROM rdb$relation_fields r
             JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
             JOIN rdb$types t
              ON t.rdb$type=f.rdb$field_type AND
                    t.rdb$field_name='RDB$FIELD_TYPE'
             LEFT JOIN rdb$character_sets cs ON
                    f.rdb$character_set_id=cs.rdb$character_set_id
        WHERE f.rdb$system_flag=0 AND r.rdb$relation_name='POI'
        ORDER BY r.rdb$field_position

If you calculate size of field_name (char_length(r.rdb$field_name)) it always returns 31

image

And as a result introspection of the table will produce incorrect column names

SELECT 
VIEW_TEST."ID                             " AS "VIEW_TEST_ID      _1", 
VIEW_TEST."PROTO_CODE                     " AS "VIEW_TEST_PROTO_CO_2", 
VIEW_TEST."MOB_DEV_ID                     " AS "VIEW_TEST_MOB_DEV__3", 
VIEW_TEST."DEV_TYPE                       " AS "VIEW_TEST_DEV_TYPE_4"

...

If you add rstrip to https://github.com/sqlalchemy/sqlalchemy/blob/9bd15fd9473fa5cf9e9d627afc0482a01a5a3ed3/lib/sqlalchemy/dialects/firebird/base.py#L823 line then everything works fine.

    name = self.normalize_name(row["fname"].rstrip())

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 15 (10 by maintainers)

Commits related to this issue

Most upvoted comments

@pauldex has taken the lead on the external FB dialect; I’m just helping out.

https://github.com/pauldex/sqlalchemy-firebird

That said, trimming the names returned by those sorts of queries was one of the first things we fixed.