SQL Dialects
SQL dialect is a term that defines the specific features of the SQL language that are available when accessing a database.SQL dialects can be defined at the database level and specified at the connection level.Three dialects are available:
-
Dialect 1 is intended solely to allow backward compatibility with legacy databases from old InterBase versions, version 5 and below.A “Dialect 1” database retains certain language features that differ from Dialect 3, the default for Firebird databases.
-
Date and time information are stored in a
DATE
data type.ATIMESTAMP
data type is also available, that is identical to thisDATE
implementation. -
Double quotes may be used as an alternative to apostrophes for delimiting string data.This is contrary to the SQL standard — double quotes are reserved for a distinct syntactic purpose both in standard SQL and in Dialect 3.Double-quoting strings is therefore to be avoided.
-
The precision for
NUMERIC
andDECIMAL
data types is smaller than in Dialect 3 and, if the precision of a fixed decimal number is greater than 9, Firebird stores it internally as a double-precision floating point value. -
The
BIGINT
(64-bit integer) data type is not supported. -
Identifiers are case-insensitive and must always comply with the rules for regular identifiers — see the section [fblangref50-structure-identifiers] below.
-
Although generator values are stored as 64-bit integers, a Dialect 1 client request,
SELECT GEN_ID (MyGen, 1)
, for example, will return the generator value truncated to 32 bits.
-
-
Dialect 2 is available only on a Firebird client connection and cannot be set in a database.It is intended to assist debugging of possible problems with legacy data when migrating a database from dialect 1 to 3.
-
In Dialect 3 databases,
-
numbers (
DECIMAL
andNUMERIC
data types) are stored as fixed-point values (scaled integers) for all precisions;depending on the type and precision, they are stored as aSMALLINT
,INTEGER
,BIGINT
orINT128
. -
The
TIME
data type is available for storing time-of-day data. -
The
DATE
data type stores only date information. -
The
TIMESTAMP
data type stores date and time information. -
The 64-bit integer data type
BIGINT
is available. -
Double quotes are reserved for delimiting non-regular identifiers, enabling object names that are case-sensitive or that do not meet the requirements for regular identifiers in other ways.
-
Strings must be delimited with single quotes (apostrophes) or using Q-strings.
-
Generator values are stored as 64-bit integers.
-
Important
|
Use of Dialect 3 is strongly recommended for newly developed databases and applications.Both database and connection dialects should match, except under migration conditions with Dialect 2. This reference describes the semantics of SQL Dialect 3 unless specified otherwise. |