Firebird error error code = -206 Column unknown

Question

I use IBDac with Delphi to connect to Firebir db and suddenly when I tried to run the software I got this error

Dynamic SQL ErrorSQL error code = -206 Column unknown table1.MyField_ID At line 6, column 18

the error show up when I try to open the dataset

mydataset.open;

the column exists of course. it is a foreign key to another table. I really dont know why I get this error !! here is the SQL I used in the component:

CREATE TABLE table1 (
      pkfield1 INTEGER NOT NULL,
      field_no INTEGER NOT NULL,
      fk1_id INTEGER NOT NULL,
      fk2_id INTEGER,
      MyField_ID INTEGER,
      bfield INTEGER);     

  select 
        table1.pkfield1,
        table1.field_no,
        table1.fk1_id,
        table1.fk2_id,
        table1.MyField_ID, <<<------- the field that cause the error
        table1.bfield,
        table2.desc1,
        table2.desc2,
        table2.desc3
    from table2
    right outer join table1 on (table2.pk1_id = table1.fk1_id)
    Order by table1.fk1_id, table1.field_no desc

Please advise

thanks

I can't see table1 in FROM or JOIN clause, shure this SQL should work? — bummi, Nov 23 '12 at 13:55
Are you sure the column isn't defined as `"MyField_ID"`, in which case it is case sensitive and won't be found without quoting. — Mark Rotteveel, Nov 23 '12 at 14:16
I updated the query and added table1. plz check again. I can run the SQL in an admin and I can open the table in design time but at run time I get this error — zac, Nov 23 '12 at 15:24
I am with similar problem, but the error says the column that does not exists is the name of the component. — NaN, Jun 08 '13 at 13:59

jachguate · Accepted Answer · 2012-11-23T15:35:44.043

Change your case, as it looks like your database layer is automatically quoting it if you use CamelCase.

A quick test in a Firebird 2.5 database here shows:

SQL> select t1.usuario, t1.NoExisto from sg_usuario t1;
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-T1.NOEXISTO
-At line 1, column 23


SQL> select t1.usuario, "t1.NoExisto" from sg_usuario t1;
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-t1.NoExisto
-At line 1, column 34

As you see, the database engine generates an error with camel case only when it gets the SQL query using quotes.

I suggest you to try myfield_id or use the correct case you have in your field name.

    select 
        table1.pkfield1,
        table1.field_no,
        table1.fk1_id,
        table1.fk2_id,
        table1.myfield_id, 
        table1.bfield,
        table2.desc1,
        table2.desc2,
        table2.desc3
    from table2
    right outer table1 on (table2.pk1_id = table1.fk1_id)
    Order by table1.fk1_id, table1.field_no desc

Firebird error error code = -206 Column unknown

1 Answers1