Firebird dialect doesn't quote column names properly

[zoomorph]

I'm trying to connect to an existing Firebird database with the following table, using fdb driver:

class Studio(Base):

    __tablename__ = 'studios'

    sid = Column(Integer, primary_key=True)
    value = Column(String, nullable=False)

SQLAlchemy generates "SELECT studios.sid, studios."value" FROM studios;"

This doesn't work. It appears that all column names must be quoted, otherwise I get an "unknown column" error.

I've temporarily worked around this by adding force=True arg to sql\compiler.py line 645 (and modifying line 2704 to actually use this argument).

[Mike Bayer]

I haven't gotten to test firebird in a long time (and for that reason IMO the firebird dialect may need to be moved out externally), however I used to run it all the time and I have never observed this issue; columns do not have to be quoted unless they are reserved words, unless firebird has changed or this is perhaps some odd version or configuration of it. Can you please provide the exact error message that Firebird is reporting? unfortunately I have limited resources to deal with Firebird as it's a very awkward platform.

[zoomorph]

Fair enough, I don't care about Firebird much, was just using SQLAlchemy to convert an old database to SQLite and figured I'd report this in case anyone else encounters the same issue.

SQL> select sid from studios;
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-SID
-At line 1, column 12

Putting quotes around "sid" (or any other column name) appears to be required.

This database comes from some old embedded database Delphi crap, so maybe this doesn't apply to all Firebird databases.

[Mike Bayer]

maybe sid is also a reserved word. If you have an fdb handy to test with maybe try a test table with some less controversial names? we can add "sid" to the reserved word list easy enough.

[zoomorph]

I've figured it out. Firebird has some kind of weird case sensitivity: http://www.firebirdfaq.org/faq76/

CREATE TABLE test (xxxxyy INTEGER PRIMARY KEY);

SELECT xxxxyy from test; WORKS SELECT "xxxxyy" from test; FAILS

CREATE TABLE test2 ("xxxxyy" INTEGER PRIMARY KEY);

SELECT xxxxyy from test2; FAILS SELECT "xxxxyy" from test2; WORKS

CREATE TABLE test3 ("XXXXYY" INTEGER PRIMARY KEY);

SELECT xxxxyy from test3; WORKS SELECT "xxxxyy" from test3; FAILS

Not sure if it's possible to detect and support this easily in SQLAlchemy.

[Mike Bayer]

Oracle has the same thing though firebird might be behaving more badly here. If the identifiers were created quoted as case sensitive then yes you have to ensure the identical casing used with quotes, otherwise you're using a case-insensitive identifier. Oracle/Firebird consider ALL_UPPERCASE to be case insensitive. The examples do look a little strange but not sure it's anything I want to further fix on the SQLAlchemy side.

[zoomorph]

Thanks Mike. I guess setting quote=1 on all columns would handle things.