Chapter 2. SQL Language Structure

This reference describes the SQL language supported by Firebird.

2.1 Background to Firebird’s SQL Language

To begin, a few points about some characteristics that are in the background to Firebird’s language implementation.

2.1.1 SQL Flavours

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

• Dynamic SQL (DSQL)

• Procedural SQL (PSQL)

• Embedded SQL (ESQL)

• Interactive SQL (ISQL)

Dynamic SQL is the major part of the language which corresponds to the Part 2 (SQL/Foundation) part 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 the Part 4 (SQL/PSM) part of the SQL specifications. Originally, PSQL extensions were available in persistent stored modules (procedures and triggers) only, but in more recent releases they were surfaced in Dynamic SQL as well (see EXECUTE BLOCK).

Embedded SQL defines the DSQL 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 portion 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.

2.1.2 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 comptibility with legacy databases from very old InterBase versions, v.5 and below. Dialect 1 databases retain 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 strenuously.

  • 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 long 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 Section 2.3, 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 the Firebird client connection and cannot be set in the 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 internally as long fixed point values (scaled integers) when the precision is greater than 9.

  • The TIME data type is available for storing time-of-day data only.

  • The DATE data type stores only date 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.

  • All strings must be delimited with single quotes (apostrophes).

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

2.1.3 Error Conditions

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