SQL dialects

<< Setting the ISC_USER and ISC_PASSWORD environment variables | Firebird Interactive SQL Utility | Terminator character >>

SQL dialects

Firebird supports three SQL dialects in each client and database server. These SQL dialects are differentiated in the context of the date-time format and the precision of a numerical data type. The dialects serve to instruct the Firebird server on how to process features implemented in legacy Borland InterBase databases, earlier than version 6.0. Dialects are set up at runtime and can be changed for the client at connection time or with a SET SQL DIALECT command.

Note: Dialect 2 is only used when converting a dialect 1 database to a dialect 3 database.

The following table illustrates the differences between the dialects.

Table 1. SQL dialects

SQLDialect 1Dialect 2Dialect 3
DateDate & Time (Timestamp)ERRORMessage Date only
Time StampTimestamp (v.6.x only)TimestampTimestamp
TimeError messageError messageTime only
<"quoted item">StringError messageSymbol only
Precision: 1/3 =0.3333333... (double precision)00
Numeric 11double precision64 bit int64 bit int

Note: Currently it is possible to create databases in Dialect 1 and 3 only, however it is recommended that you use Dialect 3 exclusively, since Dialect 1 will eventually be deprecated. Dialect 2 cannot be used to create a database since it only serves to convert Dialect 1 to Dialect 3.

When connecting to a database using isql, the utility takes on the dialect of the database, unless you specify otherwise. Dialects cannot be set as a parameter of a CREATE DATABASE statement. So, when creating a database using isql, the database will be in the dialect that is current in isql at the time the CREATE DATABASE statement is issued. You may set the dialect using the isql utility in two ways:

  • When you start isql type:
 cosmos> isql -sql_dialect n

(where n refers to the dialect number)

  • Within a SQL script or isql session, type:
 SQL> SET SQL DIALECT n;

Note: Prior to Firebird 2.0 when isql disconnected from a database, either by dropping it or by trying to connect to a non-existent database, it remembered the SQL dialect of the previous connection, which lead to some inappropriate warning messages. This has been fixed in 2.0

See also:
Setting the database dialect
SQL dialect

back to top of page
<< Setting the ISC_USER and ISC_PASSWORD environment variables | Firebird Interactive SQL Utility | Terminator character >>