Using reserved word TimeStamp as a field name

Question

I am extending the data layer of an existing application to work with Firebird 2.5, in addition to SQL Server and SQLite, but I have hit a stumbling block.

I have a field called TimeStamp which stores the data/time as type TimeStamp. This works fine under SQL Server and SQLite where the type is datetime, but fails on Firebird. The following SQL:

SELECT SysTrnId,'TimeStamp' from "TRANSACTIONS"

seemingly works, but the TimeStamp field is returned as fieldname "CONSTANT" and the contents are the text "timestamp"

Is it possible to do this under Firebird, or am I going to have to rename the field and change the code in the other data layers.

Smithereens

Answer 1

The Firebird escape character is the double quote ". Note that in Firebird if you have a field that you have to escape with double quotes, it also becomes case-sensitive. So, if you've declared your field as TimeStamp, then select "TIMESTAMP" ... will fail with a field not found error.

Islam

Thanks, that did it. I realise that the field name will need to be changed, but need to fix this first, before I can dive in and make it so throughout the different databases supported. Thanks. – Smithereens

Very helpful. BTW, here's a full list of the reserved keywords: firebirdsql.org/refdocs/… – Gert

Answer 2

I have no idea if it works, but have you tried double quotes around "TimeStamp", single quotes usually indicate a constant string.

Pachyderm

Yes, I tried that but got the error: Dynamic SQL Error SQL error code = -206 Column unknown timestamp At line 1, column 29 Colin – Smithereens

Answer 3

TL;DR: You need to enclose the name in double quotes, so use "TimeStamp" (or maybe "TIMESTAMP").

---

Firebird has two types of identifiers: regular identifiers, and delimited identifiers (not supported in deprecated dialect 1).

Regular identifiers have a number of limitations:

• Case-insensitive (or more specifically, handled as the uppercase equivalent delimited identifier)
• Must start with A-Z or a-z, and can only contain A-Z, a-z, 0-9, $, and _
• Cannot be a reserved word

Delimited identifiers are enclosed in double quotes, and can contain any valid Unicode code point (subject to limitations of your current connection character set). Delimited identifiers have the following limitations:

• Case-sensitive
• Double quotes in an object name must be escaped by doubling them (i.e. " -> "", so an object name Has"double quote requires using the delimited identifier "Has""double quote" (though in general, I'd strongly advice avoiding double quotes in identifiers).

These rules mean that the following identifiers all refer to the same object name:

• COLUMN_NAME
• column_name
• Column_Name
• (.. and other unquoted variations in case)
• "COLUMN_NAME"

The following identifiers are distinct from the above name and each other

• "column_name"
• "Column_Name"
• (.. and other quoted variations in case)

If you have objects (columns, tables, etc.) with the same name as a reserved word, you must use a delimited identifier:

select SysTrnId, "TimeStamp" from "TRANSACTIONS"

The "TimeStamp" must match the exact case as stored in the system tables, so if instead the column name is actually TIMESTAMP, you will need to use "TIMESTAMP".

If you have an identifier chain (e.g. table or table alias and a column name, <table>.<column>), you need to delimit the individual identifiers in the chain:

select "TRANSACTIONS"."TimeStamp" from "TRANSACTIONS"

To find the reserved words of a Firebird version, check its language reference (e.g. for Firebird 4.0, see here).

Since Firebird 5.0, you can also use the RDB$KEYWORDS table:

select RDB$KEYWORD_NAME
from RDB$KEYWORDS
where RDB$KEYWORD_RESERVED

What about dialect 1?

If you're using dialect 1 (which you really shouldn't, as it was deprecated in InterBase 6.0 before Firebird was forked, back in 1999), then you're out of luck: you cannot refer to columns with the same name as a reserved word, and double quotes delimit string literals. Upgrade your database to dialect 3, or try and use the transitional dialect 2 (connection dialect only). Note that dialect 3 changed a number of other things, primarily behaviour of NUMERIC/DECIMAL calculation, and DATE is a real DATE and not TIMESTAMP-like type like in dialect 1.

Fioritura