<< Changing the database mode | Firebird Database Housekeeping Utility | Database housekeeping and garbage collection >>

Setting the database dialect

The dialect of the database is simply a term that defines the specific features of the SQL language that are available when accessing that database. There are three dialects at present (Firebird version 2.0), these are:

• Dialect 1 stores date and time information in a DATE data type and has a TIMESTAMP data type which is identical to DATE. Double quotes are used to delimit string data. The precision for NUMERIC and DECIMAL data types is less than a dialect 3 database and if the precision is greater than 9, Firebird stores these as DOUBLE PRECISION. INT64 is not permitted as a data type.
• 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. This dialect cannot be set for a database using gfix. (See below.)
• Dialect 3 databases allow numbers (DECIMAL and NUMERIC data types) to be stored as INT64 when the precision is greater than 9. The TIME data type is able to be used and stores time data only. The DATE data type stores only date information. Double quotes can be used but only for identifiers that are case dependent, not for string data which has to use single quotes.

The command to change the SQL dialect for a database is:

 gfix -s[ql_dialect] DIALECT database_name

The DIALECT parameter is simply 1 or 3.

The following example changes a database to use dialect 3 which will allow many newer features of SQL 92 to be used.

 linux> gfix -sql_dialect 3 my_employee

 linux> gstat -header my_employee | grep dialect
 Database dialect 3

 linux> gfix -sql_dialect 1 my_employee

 linux> gstat -header my_employee | grep dialect
 Database dialect 1

Because you cannot use gstat remotely, you may also use the isql command SHOW SQL DIALECT from a remote location to see which dialect your client and database are using, as follows:

 remote> isql my_employee -user norman -password whatever
 Database: my_employee

 SQL> show sql dialect;
 Client SQL dialect is set to: 3 and database SQL dialect is: 3

Although dialect 2 is possible on the client, trying to set a dialect of 2 will fail on the server as the following example shows:

 linux> gfix -sql_dialect 2 my_employee
 Database dialect 2 is not a valid dialect.
 -Valid database dialects are 1 and 3.
 -Database dialect not changed.

To set dialect 2 for your client connection, you use isql as follows:

 linux> isql my_employee
 Database: my_employee

 SQL> set sql dialect 2;
 WARNING: Client SQL dialect has been set to 2 when connecting -
 to Database SQL dialect 3 database.

 SQL> show sql dialect;
 Client SQL dialect is set to: 2 and database SQL dialect is: 3

Note: The WARNING line above has had to be split to fit on the page of the PDF version of this manual. In reality, it is a single line of text.

See also:
SQL dialect
Firebird Interactive SQL Utility: SQL dialects

back to top of page
<< Changing the database mode | Firebird Database Housekeeping Utility | Database housekeeping and garbage collection >>