Databases - Firebird's SQL Dialects

One of the first decisions you need to make is

Which SQL Dialect should I use?

Firebird FAQ - How do the SQL dialects differ? really does not cover the problems I've had using Firebird 1.0. Apparently, the problems were actually with the Interbase 5 & 6 tools - they don't know about different dialects.

Dialect	Features
1	Supports only a Date field (but it also includes the time) Does not use double quotes around field and table names in queries All names must be upper case only Generators are 32-bit integers (same as MS Access) The way Firebird 1.0 works
3	Supports Date, Time, and Timestamp fields Places table and field names in double quotes, appears to support spaces in field and table names Allows mixed case names Generators are 64-bit integers Not supported by the tools supplied with Interbase 5 & 6 The default Firebird 1.5 uses to create new databases (*.fdb files)

According to A Beginner's Guide to Firebird Generators, when inserting 1000 rows per second, a 64-bit generator would take around 300 million years (!) before it rolls over ... but a 32-bit generator would roll over after about 25 DAYS (!!!) .

Of course, at once a minute, a 32-bit generator rolls over in about 4,000 years.

Delphi

Delphi 5 can read Firebird Dialect 3 data if the following is set.

TIBDatabase.SQLDialect = 3  // default = 1 in Delphi 5
                            //         = 3 in Delphi 6

Otherwise IBTable1 produces (paraphrased)

Dynamic SQL error -> -204 - unknown table
SOME_TABLE_NAME_IN_UPPERCASE

This appears to be because my tables have mixed case names and Dialect 1 converts them to upper case names.

With Dialect 3, IBQuery1 works with queries of the type

select * from "Barcodes_tbl"

(notice the double quotes around the table name)

Interbase Tools

According to the provided "API Guide", Interbase 5 DSQL does support different Dialects (some commands have a Dialect paramater). This probably explains how Delphi 5 can access Dialect 3 databases.

However,

SET SQL DIALECT 3;

is not supported.

Since Interbase 5 Windows ISQL does not have a menu option to set the Dialect, and because it does not allow double quotes in queries, it can not be used to access data in any tables with mixed case names.

Interbase 6 IBConsole does support Dialect 3.

Author: Robert Clemenzi - clemenzi@cpcug.org