InterBase 6.0 SQL Dialects

What are Dialects?

InterBase 6.0 introduces a lot of new features. However, some of these features change the way that InterBase behaves. We didn't want your databases and applications to stop working after 6.0 was installed. Thus, we introduced the concept of dialects. Dialects act as a type of versioning mechanism for InterBase features. For example, a 6.0 database with dialect 1 will see numeric(15,2) types as doubles where a dialect 3 database will see the same datatype as an int64 (dialect 1 and 3 will be explained later).

Dialects will determine how InterBase functions in relation to some of the new and existing features. In particular, dialects control how InterBase interprets:

  • double quotes
  • the DATE datatype
  • decimal and numeric datatypes
  • new 6.0 reserved keywords

Features that are entirely new or features that do not change the way previous versions of InterBase functioned are not controlled by the dialect. For example, the new background sweeper thread or the new read-only database feature are not controlled by the dialect used. No matter which dialect is used the background sweeper thread will function. Likewise, you can make a database read-only no matter which dialect it uses.

InterBase 6.0 has 3 dialect versions (1, 2, and 3).

Dialect 1 preserves pre-6.0 behavior. If you are using dialect 1 you will get the same behavior as you would from the 5.5 server.

Dialect 2 is used for debugging. When dialect 2 is used it will generate errors whenever a statement uses a feature that has changed in functionality. For example, pre-6.0 DATE datatypes were actually timestamps. They stored both a date and a time. IB 6.0 introduces a new DATE datatype that stores just date information. Thus, behavior has changed between previous versions and 6.0. When accessing a date field while using dialect 2 an error is generated. The following snippet shows the error that is generated because dialect 2 was being used.

SQL> create table foojoe (d1 date);
Statement failed, SQLCODE=-104
Dynamic SQL Error
-SQL error code=-104
-DATE must be changed to SQLDATE

Dialect 3 is used to access all the new features that IB 6.0 introduces. To utilize 64-bit integers, delimited identifiers, and the new DATE and TIME datatypes you must be using dialect 3. However, because the new features do change the functionality of InterBase there will be a migration process required for both the database and the client application that accesses it. We will discuss the migration process later on.

To further confuse the issue both a database and a client have a dialect. That's right a database has its own dialect as well as a client application.

Database Dialect

The reason for a database dialect is to control the behavior of the database objects. In general, the database dialect deals with DDL statements (metadata) executed against the database. When a client executes an SQL statement the server must know how to deal with the statement. The database dialect controls how the server handles the client's SQL statement. For example, the following create trigger statement will behave differently depending on the dialect of the database.

create trigger blah_trigger for blah
  before insert

This statement relies on the fact that InterBase allows double quotes to be used for string literals. However, 6.0 introduces the concept of delimited identifiers. With delimited identifiers all string literals must use single quotes and double quotes are left for SQL identifiers. Here are the results of executing this statement:

DB Dialect Results of executing the create trigger statement
1 Successful
Statement failed, SQLCODE=-104

Dynamic SQL Error
-SQL error code=-104
-a string constant is delimited by double quotes
Statement failed, SQLCODE=-206

Dynamic SQL Error
-SQL error code=-206
-Column unknown

Dialect 1 was successful because this create trigger statement is accepted behavior for pre-6.0 servers. Dialect 2 failed with a warning error because it detected double quotes. Dialect 3 failed because all double quotes are interpreted as delimited identifiers and "blah" doesn't correspond to any valid column in the table.

Client Dialect

Where the database dialect deals with metadata, the client dialect deals with DML statements. Like the database dialect, the client dialect controls the functionality that is available to DML statements.

The following example demonstrates how the client dialect affects a particular DML statement. Executing the following statement:

insert into foo values (22.22, "now");

yields the following results:

Client Dialect Results of executing insert statement
1 successfully inserted record
Statement failed, SQLCODE=-104

Dynamic SQL Error
-SQL error code=-104
-a string constant is delimited by double quotes
Statement failed, SQLCODE=-206

Dynamic SQL Error
-SQL error code=-206
-Column unknown

The results are almost identical to the previous database dialect example. Once again, with dialect 1 the statement is successful. This insert statement is legal in pre-6.0 InterBase kits, thus it works on 6.0 with dialect 1. Dialect 2 once again detected the use of a feature that changed functionality in 6.0 so it flagged it as an error. In this case the use of double quotes has changed functionality in 6.0 so the -104 SQL error was generated. Dialect 3 errored out because it tried to interpret "now" as an SQL identifier and assumed that it was a column reference. There is no such column, thus the "Column unknown" error.


One DDL statement that is affected by the client dialect is the Create Database statement. The dialect for the new database is set to whatever the client dialect is. For example, running the following isql script will create a new database with a dialect of 1.

set sql dialect 1;
create database 'foo.gdb';

All subsequent DDL statements will adhere to the dialect of the database.

Note: the statement that the database dialect deals with DDL and the client dialect with DML is a generality. It is just a generalization to help you understand what each dialect governs.

Why are Dialects Needed?

Dialects were introduced so you weren't forced to migrate your database and client applications to utilize the new 6.0 features. The migration process may be extensive for some. We want to ensure that everyone can install InterBase 6.0 and continue to run without having to upgrade their applications. Using dialects allows for a smooth migration process. You can migrate at your own pace. Additionally, with the database and client dialect you can migrate pieces of your application without having to migrate the whole thing. You can migrate the database or the client application first and deal with the other half later.

Where are Dialects Used?

Now that we've discussed what a dialect is, we need to proceed with how to set them for databases and applications.

Working with Database Dialect

Setting Database Dialect

I will start with the database dialect. This section will NOT cover the migration process. Instead it will cover how to actually set the database dialect for a particular database. So... how do you set the database dialect, you ask?

To change the dialect for an existing database you must use gfix. Here is the syntax for the new gfix parameter to change the dialect for a database:

gfix -SQL_dialect x dbname

where x is the dialect number and dbname is the name of the database to change the dialect for. Setting a database's dialect doesn't change the database objects that currently exist (more on this later), but governs how future database objects are created and dealt with.

You can use this method for new databases as well. Create the database and set the dialect after. However, most customers have SQL scripts that create the database and all their database objects (tables, triggers, ...). So, how does one run this creation script so that all the database objects are created as dialect 3 objects?

Well, this is where it gets a little tricky. When creating a new database the initial dialect for the database is set to whatever the client dialect is when the create database statement is executed. For example, if you are using ISQL to execute your script (which creates the database), the client dialect that ISQL is using is what is used for the database's initial dialect.


It seems logical that gbak would allow you to set the database dialect when it is restoring. Sadly, this is not the case. You must use gfix.

Showing Database Dialect

Now that we know how to set the database dialect, how do we see what a database's dialect is? Retrieving a database's dialect can be done several ways.

ISQL has a new show command that will display the database's dialect (it also shows the client dialect). The syntax for the new show command is:


Here is the output from an ISQL session executing the show sql dialect command:

D:workinterbasemigration>isql oldtypes.gdb
Database: oldtypes.gdb
SQL> show sql dialect;
Client SQL dialect is set to: 2 and database SQL dialect is: 2

Programmatically you can use the API function isc_database_info and pass it a new info parameter: isc_info_db_SQL_dialect. Several of our client interfaces (i.e. IBX) have a property or method that wrap this function call and make it easier for the programmer to retrieve the database's dialect.


Neither gfix or gstat will give you a database's dialect.

Working with Client Dialect

This section will detail how to set the client dialect from the different client interfaces that we have.

Setting Client Dialect in ISQL

There are two ways to set the SQL dialect in ISQL. You can set it as a command-line option when you start ISQL or you can set it with an ISQL set statement.

The command-line parameter -s is used to set the sql dialect. Here is an example connecting to employee.gdb with a client dialect of 3:

ISQL -s 3 employee.gdb

Here is an example running a script using client dialect 1. It will run all statements in the script using client dialect 1 unless the dialect is changed in the script:

ISQL -s 1 -i input.sql

You can also set the client dialect after ISQL has started. To set it you use the syntax:


ISQL will default to using a client dialect that is the same as the database's dialect. The following statements demonstrate this. I first connect to the database using ISQL without specifying a client dialect. When the show sql dialect statement is executed it shows the client dialect equals the database dialect. Next I use gfix to change the database dialect to 3. Reconnecting with ISQL shows that ISQL now uses a client dialect of 3 (to match the database's dialect).

D:workinterbasemigration>isql oldtypes.gdb
Database: oldtypes.gdb
SQL> show sql dialect;
Client SQL dialect is set to: 2 and database SQL dialect is: 2
SQL> exit;

D:workinterbasemigration>gfix -SQL_dialect 3 oldtypes.gdb
D:workinterbasemigration>isql oldtypes.gdb
Database: oldtypes.gdb
SQL> show sql dialect;
Client SQL dialect is set to: 3 and database SQL dialect is: 3


ISQL defaults to dialect 2 for new databases. The following example is a script that just creates a new database and shows the database's dialect. So, be careful when running ISQL scripts.

D:workinterbasemigration>isql -i isql_default.sql
Use CONNECT or CREATE DATABASE to specify a database
create database "isql_default.gdb";
show sql dialect;
Client SQL dialect is set to: 2 and database SQL dialect is: 2

Showing Client Dialect in ISQL

As mentioned previously the way to show a client dialect (and database dialect) in ISQL is to use the new show command. Again, the syntax for this show command is:


This will give you both the client and the database dialects.

Gpre applications

Gpre is similar to ISQL in the ways that you can set the SQL dialect. Just like ISQL there is a command-line parameter to set the client dialect. The syntax for using it is:

gpre <current options> -sqldialect x infile [outfile]

For example:

gpre -z -e -m -n -sqldialect 3 blah.e

Additionally, you can set the dialect via a set statement. Here is an example setting the client dialect to 2


API applications

For API applications the client dialect is set as a parameter to many of the isc_dsql_* functions. The new functions that take the dialect as a parameter are:

  • isc_dsql_describe
  • isc_dsql_describe_bind
  • isc_dsql_execute
  • isc_dsql_execute2
  • isc_dsql_execute_immediate
  • isc_dsql_execute_immed2
  • isc_dsql_fetch
  • isc_dsql_fetch2
  • isc_dsql_prepare

These API functions haven't changed, the programmer must just pass a different value for the dialect parameter of each one.

The 5.5 API guide says to set the value of the dialect parameter to 1. This is because pre-6.0 there was only dialect 1 available. Well, you can now set it to the value of the client dialect that you wish to use. Here is an example that prepares and executes a statement and fetches the results. Notice the 3 passed into each function. This is the client dialect that I am using in this example

isc_dsql_prepare(isc_status, &trans, &stmt, 0, queryString, 3, outSqlda);
isc_dsql_describe(isc_status, &stmt, 3, outSqlda);
isc_dsql_execute(isc_status, &trans, &stmt, 3, NULL);
while((retcode=isc_dsql_fetch(isc_status, &stmt, 3, outSqlda))==0)
if(retcode !=100L)
  print_error(isc_status, "SELECT(fetch)");


the client dialect isn't used when connecting to a database. You don't have to specify any dpb parameters or anything else. The only place you need to worry about the client dialect is with the API functions listed above.

IBX applications

To set the client dialect in IBX you just need to set the SQLDialect property for your TIBDatabase objects. All database access that is done through this TIBDatabase component will use the dialect that is set in this property. For example if you have two TIBQuery components connected to your TIBDatabase, both will use the TIBDatabase.SQLDialect property as their client dialect when interacting with the database.

To retrieve the dialect of the database you need to use the TIBDatabaseInfo component. This component has a property, DBSQLDialect, that you can access to retrieve the connected database's dialect. Here is a code example that retrieves the connects to a database and retrieves its dialect for display.

// set database to connect to
ibdatabase1.DatabaseName :='employee.gdb';

// connect to database
ibdatabase1.Connected :=true;

// retrieve database dialect and display in edit box
Edit1.Text :=IntToStr(ibdatabaseinfo1.DBSQLDialect);

BDE applications

Updated 3rd January 2006, thanks to Mirco Malaguti

If the BDE has been updated to at least version, it will support both dialect 1 and dialect 3 via the SQL Links.

Here's a note extracted from

Verifying that the BDE supports Interbase SQL dialects Before you can specify the SQL dialect of your BDE aliases, you need to verify that BDE supports the required setting. In our experience, the easiest way to do this is with the BDE Administrator itself:

  1. From Control Panel, start the BDE Administrator.
  2. When the Administrator opens, select the Configuration tab.
  3. Use the left panel to select Configuration | Drivers | Native | INTRBASE.
  4. Search the settings listed in the Definition panel for one called SQLDIALECT.

If the Definition panel contains SQLDIALECT, BDE is configured to support Interbase's SQL dialects and you can skip to the next section. If you don't find a SQLDIALECT setting, you need to add it to the Registry and re-create your existing alias with new one. (This would be a good time to record the settings of your current Interbase alias and then delete it. This would also be a good time to backup your Registry.) To add the SQLDIALECT setting to BDE:

  1. Close all BDE clients, include the BDE Administrator, Paradox for Windows, and any other application using BDE to connect to databases.

  2. From the Start menu, choose Run, type regedit and then press Enter.

  3. When the Registry Editor appear, use the left panel to locate the following settings:

    SOFTWARE\Borland\Database Engine\
  4. Right-click the right panel and then choose New | String Key from the shortcut menu. This adds a value called "New Value #1." Note: The specific command varies between versions of Windows. For example, it's "String Key" in Windows XP and "String" in Windows 2000. Choose the command appropriate for your version of Windows.

  5. Rename the new value to SQLDIALECT (Note that this should be capitalized and should not contain spaces.)

  6. After changing the name, make sure SQLDIALECT is selected and then press Enter to display the Edit String dialog box.

  7. In the Value data edit box, type 1, 2, or 3 (the specific value should be the dialect you use most frequently; if you're not sure, type 1) and then choose OK.

  8. Close the Registry Editor to save your changes.

At this point, BDE supports Interbase SQL dialects. Now you need to verify that the used to connect to your database uses the dialect assigned to your database, as discussed in the next section


If you've just added the SQLDIALECT setting, you may need to delete all existing Interbase aliases and re-create them before you can use this setting in your aliases, for BDE does not refresh the settings of existing aliases.

How do I migrate a database to dialect 3?

This section will explain a high level set of steps to migrate a database.

There are two ways to migrate your database to use the new 6.0 features. The first is to fix up your database and change the dialect to 3. The second is to create a new database in dialect 3 and copy over your data. Both methods are fairly clear and don't need further explanation. The next two sections will highlight major areas that need to be address and kept in mind when migrating databases.

How to Find Migration Areas

The general way to find areas in your database that will need migration is to use dialect 2. Remember, dialect 2 will generate an error for any functionality that has changed for 6.0 (even if user is using it correctly).

The basic approach to migrating a database is to extract the metadata for the database and run the script with ISQL using dialect 2. This will give you a list of errors where the engine found potential trouble spots. For example, it will generate an error for each instance of a DATE datatype. The user can then look at each of these errors and determine whether this object needs to be updated or not.


There is no magical or automated way of migrating a database. A lot of the work is placed on the customer to find the trouble spots and migrate to the new features.

Areas to Look For When Migrating

Here are the major areas to look for when migrating a database to dialect 3:

  • Use of double quotes for string literals
  • Use of date fields (Do you really need a TIMESTAMP or will a DATE or TIME suffice)
  • Do you need an int64 datatype for your numeric(15,x) fields
  • Are you using any of the new keywords introduced in 6.0?

Migrating vs Creating New

DataTypes Are NOT Migrated

When you migrate a database to dialect 3 it will NOT do any conversion of datatypes to accommodate the new datatypes. This means, for example, that all columns of type numeric(15,2) will still be stored as doubles. However, all new columns defined as numeric(15,2) will be stored as int64. This fact will lead to a lot of confusion. When you show a table in ISQL both types will show up as numeric(15,2). It is just the underlying storage type that is different. Here is a query that will return the datatype used to store the column:

select f.rdb$field_type
from rdb$fields f, rdb$relation_fields r
where r.rdb$relation_name='<tablename>' and

This query will return a datatype number that can then be looked up in rdb$types.

Also, the old DATE datatype will now show up as TIMESTAMP if you show table in ISQL. If you create a DATE column in dialect 1 and change the database dialect to 3 it will then show up as a TIMESTAMP. However, if you create a DATE column in dialect 3 it will actually be stored as a DATE (no time information).

Here are a couple of datatypes for your convenience:

DataType Number
Double 27
TimeStamp 35
Int64 16
Date 12

Database Objects Will Work Even With Invalid Dialect 3 Syntax Another thing I discovered is that certain database objects (I've tried this with triggers and stored procedures) will continue to work in dialect 3 even though they are using functionality of dialect 1. Here is an example trigger that clearly shouldn't work in dialect 3:

create trigger blah_trigger for blah
  before insert

This trigger is valid in dialect 1. The double quotes are allowed for string literals. However, you will get an error if you try to create this trigger in a dialect 3 database. You will get the "Column unknown" error. But, this is where it gets interesting...

If this trigger was created in a dialect 1 database and migrated to a dialect 3 database (changing the database dialect via gfix), the trigger will continue to work. Why you ask...

The reason it continues to work with the string literal in double quotes is because when it was created it was compiled into blr. Blr doesn't know about strings. All text is dealt with as a series of characters. If you print the blr for this trigger you get:

           blr_literal, blr_text2, 127,0, 4,0, 'b','l','a','h',
           blr_field, 1, 2, 'V','1',

As you can see there isn't any double quotes here and the string blah is converted to a series of characters. Blr is a character based language so everything is a series of characters.

However, if you show the trigger you still get the string literal surrounded by double quotes. Herein lies the problem. If this trigger is ever extracted it will not successfully complete if run against a dialect 3 database. So, you are left with a trigger that works as is, but can't be extracted and used elsewhere.