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

SQL	Dialect 1	Dialect 2	Dialect 3
Date	Date & Time (Timestamp)	ERROR Message	Date only
Time Stamp	Timestamp (v.6.x only)	Timestamp	Timestamp
Time	Error message	Error message	Time only
<"quoted item">	String	Error message	Symbol only
Precision: 1/3 =	0.3333333... (double precision)	0	0
Numeric 11	double precision	64 bit int	64 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