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
| 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
isqltype:
cosmos> isql -sql_dialect n
(where n refers to the dialect number)
- Within a SQL script or
isqlsession, 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 >>
