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
as
begin
new.v1 = "blah";
end!!
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 |
| 2 |
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-a string constant is delimited by double quotes |
| 3 |
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 |
| 2 |
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-a string constant is delimited by double quotes |
| 3 |
Statement failed, SQLCODE = -206
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-now |
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.
Important: 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.
Note: 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:
SHOW SQL DIALECT;
Here is the output from an ISQL session executing the show sql dialect
command:
D:\work\bbandy\interbase\migration>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.
Important: 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:
SET SQL DIALECT x
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:\work\bbandy\interbase\migration>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:\work\bbandy\interbase\migration>gfix -SQL_dialect 3 oldtypes.gdb
D:\work\bbandy\interbase\migration>isql oldtypes.gdb
Database: oldtypes.gdb
SQL> show sql dialect;
Client SQL dialect is
set to: 3 and database SQL dialect is: 3
important: 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:\work\bbandy\interbase\migration>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:
SHOW SQL DIALECT;
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
EXEC SQL SET DIALECT 3;
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);
rowTotals = 0;
while((retcode = isc_dsql_fetch(isc_status, &stmt, 3, outSqlda))
== 0)
{
rowTotals++;
}
if(retcode != 100L)
{
print_error(isc_status, "SELECT(fetch)");
return;
}
Important: 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
BDE applications all use client dialect 1. There is currently
no way to change the client dialect used. Additionally, there
is no way to retrieve the database's dialect.
Essentially, BDE applications cannot access the new features introduced
into 6.0. The BDE will have to be updated to utilize the client dialect
and access the new 6.0 features.
Important: Customers wanting to use the new 6.0 features must
convert their BDE applications to use IBX.
How do I migrate a database to dialect 3?
This section will explain a high level set of steps to migrate a database.
There is a
migration document available that explains the migration process in detail.
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.
Important: 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
r.rdb$field_source = f.rdb$field_name;
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
as
begin
new.v1 = "blah";
end!!
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_version4,
blr_begin,
blr_begin,
blr_begin,
blr_assignment,
blr_literal, blr_text2, 127,0, 4,0, 'b','l','a','h',
blr_field, 1, 2, 'V','1',
blr_end,
blr_end,
blr_end,
blr_eoc
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 asis, but
can't be extracted and used elsewhere.
