CODEWITHSUNDEEP
sqlfirebird
lueda
lueda

Reputation: 301

How to select a field named start (a reserved word)

I've got a strange problem. A customer named a field in FirebirdSQL start. Now I cannot select this field:

select t.start from table t

Gives me the error: Invalid token

Any idea on how I can select this field?

Upvotes: 2

Views: 457

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109260

The word start is a reserved word in Firebird and therefor needs to be quoted. The quote symbol in Firebird is the double quote. So you need to use:

select t."start" from "table" t

Note that I quoted table as well, as it is also a reserved word. Quoting makes the name case sensitive, so you may need to use "START" and "TABLE".

In your comments you mention that quoting yields a constant value 'table'. This means that you are using dialect 1, the deprecated (and legacy) dialect of Firebird. This legacy dialect cannot quote object names, and the double quote is for string literals (next to the SQL standard single quotes). Either your database is a dialect 1 database, or you specified the wrong connection dialect in your connection library.

If your database is actually dialect 3, solving this is as simple as specifying connection dialect 3 (or 0 to auto select based on the database dialect). However if your database is a dialect 1 database you will need to migrate to dialect 3 to be able to solve this.

If this is simply to try things out: make sure you create a dialect 3 database (for most tools this is the default).

How to determine database dialect

To determine the database dialect, you can use the gstat tool:

gstat mydatabase.fdb -header

In recent Firebird versions you can also query the MON$DATABASE monitoring table. Most query tools also allow you to view the database properties.

How to migrate from dialect 1 to dialect 3

That is I think too complicated to write down here (and I have never done it myself as it was considered deprecated years ago). However the Interbase 6.0 Getting Started, available from Firebird: Reference Manuals (at the end of the page) describes the basic process of migration, and I am sure that there are pages to be found with some googling.

Upvotes: 1

Related Questions