FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 About the Firebird 5.0 Language ReferenceData Types and Subtypes 

This reference describes the SQL language supported by Firebird.

Background to Firebird’s SQL Language

To begin, a few points about some characteristics of Firebird’s SQL implementation.

SQL Flavours

Distinct subsets of SQL apply to different areas of activity.The SQL subsets in Firebird’s language implementation are:

DSQL

Dynamic SQL
PSQL

Procedural SQL
ESQL

Embedded SQL
ISQL

Interactive SQL

Dynamic SQL is the major part of the language which corresponds to Part 2 (SQL/Foundation) of the SQL specification.DSQL represents statements passed by client applications through the public Firebird API and processed by the database engine.

Procedural SQL augments Dynamic SQL to allow compound statements containing local variables, assignments, conditions, loops and other procedural constructs.PSQL corresponds to Part 4 (SQL/PSM) of the SQL specifications.PSQL extensions are available in persistent stored modules (procedures, functions and triggers), and in Dynamic SQL as well (see EXECUTE BLOCK).

Embedded SQL is the SQL subset supported by Firebird gpre, the application which allows you to embed SQL constructs into your host programming language (C, C++, Pascal, Cobol, etc.) and preprocess those embedded constructs into the proper Firebird API calls.

Note

Only a subset of the statements and expressions implemented in DSQL are supported in ESQL.

Interactive ISQL refers to the language that can be executed using Firebird isql, the command-line application for accessing databases interactively.As a regular client application, its native language is DSQL.It also offers a few additional commands that are not available outside its specific environment.

Both DSQL and PSQL subsets are completely presented in this reference.Neither ESQL nor ISQL flavours are described here unless mentioned explicitly.

For ISQL, consult the manualFirebird Interactive SQL Utility.

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.