Firebird – SELECT Gives Token Unknown Error

firebirdselect

I have a Firebird database with a table foo that have a field called WHEN. Is there a way to perform a query using the field name in SELECT?

This works:

SELECT * FROM foo;

This not:

SELECT WHEN FROM foo;
SQL error code = -104
Token unknown - line 1, column 18
WHEN

Any hints?

Best Answer

The token WHEN is a reserved word, and can't be used as a column name without explicitly quoting it. Either rename the column, or quote it:

select "WHEN" from foo

Be aware that quoting names makes them case sensitive. Unquoted object names in Firebird are case insensitive, but stored in uppercase.

Related Solutions

No luck trying out Firebird 3.0

The problem is twofold:

First Firebird 3 uses tighter security and has the old authentication mechanism disabled by default. So clients depending on older connection libraries will not work out of the box.

To allow the old authentication mechanism to work, the following setting needs to be set in firebird.conf:

AuthServer = Legacy_Auth, Srp, Win_Sspi

(be sure to remove the # prefix)

Second, the pure-java wire protocol implementation of Jaybird (the Firebird JDBC driver) uses an outdated authentication mechanism that is not supported by this Legacy_Auth option. Using Jaybird to connect to Firebird 3 at this time will only work if you use the native option (with the jdbc:firebirdsql:native: URL-prefix and jaybird2x.dll and fbclient.dll).

Addendum 1

Firebird 3 (since Alpha 1) has now also implemented the old authentication method used by Jaybird, so Jaybird will now also work without using the native library (as long as the legacy authentication is enabled, and the user has been setup with the legacy usermanager).

Addendum 2

Jaybird 3 and higher support the more secure Srp authentication model out of the box, and Jaybird 3.0.4 introduced support for the wire protocol encryption. This means that it can connect to Firebird 3 without additional configuration.

How to build table relationships in a firebird database

While I'm pretty sure you should redesign your database at first (and probably read something on SQL basics), you can get what you want with triggers :)

This one inserts row into daily collection with appropriate balance:

CREATE trigger masterlist_ai for masterlist
active after insert position 0
AS
begin
  insert into "daily collection" (  "date",     balance)
                        values (new."date", new.balance);
end

And this one updates balance in masterlist after updates of daily collection (assuming date is the primary key):

CREATE trigger "daily_collection_au" for "daily collection"
active after update position 0
AS
begin
  update masterlist
  set balance = new.balance
  where "date" = new."date";
end