Firebird, Delphi, and XCore

Firebird supports three dialects of SQL.  Dialect '1' is the original Interbase SQL dialect.  Dialect '3' is a newer dialect that attempts to be closer to the ANSI standard.  Dialect '2' is used only for debugging: it throws errors for any SQL statement whose behaviour has changed between dialects '1' and '3'.  Starting with Firebird 1.5, dialect '3' is now the default.

A Firebird client must indicate which dialect it is using.  It does this using:

set sql dialect 1;

The default dialect for a new database is set to whatever dialect the client happens to be using at the instant the database is created.  So, to force a particular dialect, use:

set sql dialect 1;
create database 'c:\dir\mydb.fdb' page_size 8192 user 'me' password 'secret';

You can change the dialect of an existing database using the gfix command line tool:

gfix -sql_dialect 1 -user 'me' -password 'secret' 'c:\dir\mydb.fdb'

I haven't tried it, but supposedly you can set the SQL dialect in Delphi using:

TIBDatabase.SQLDialect = 1;

The default value is '1' in Delphi 5 and '3' in Delphi 6.

This all came up when I tried to use XModel to create a new model in an empty Firebird 1.5 database.  The error message was:

SQL error code = -817
Metadata update statement is not allowed by the current database SQL dialect 3.