FirebirdSQL logo
 About the Firebird 5.0 Language ReferenceData Types and Subtypes 

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.A TIMESTAMP data type is also available, that is identical to this DATE 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 and DECIMAL 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 and NUMERIC data types) are stored as fixed-point values (scaled integers) for all precisions;depending on the type and precision, they are stored as a SMALLINT, INTEGER, BIGINT or INT128.

    • 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.

Error Conditions

Processing of every SQL statement either completes successfully or fails due to a specific error condition.Error handling can be done both client-side in the application, or server-side using PSQL.