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
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
- Remove test no longer in sqlalchemy — committed to pauldex/sqlalchemy-firebird by deleted user 4 years ago
@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.