Understanding SQL Dialects

Go Up to Migration Issues

Below are explanations of server and client behavior with SQL dialects 1, 2, and 3.

Dialect 1 Clients and Databases

In dialect 1, the InterBase 6 and later servers interpret transition features exactly as an InterBase 5 server does:

• Double quoted text is interpreted as a string literal. Delimited identifiers are not available.
• The DATE data type contains both time and date information and is interpreted as TIMESTAMP; the name has changed but the meaning has not. Dialect 1 clients expect the entire timestamp to be returned. In dialect 1, DATE and TIMESTAMP are identical.
• The TIME data type is not available.
• Dialect 1 databases store DECIMAL and NUMERIC data types with precision greater than 9 as DOUBLE PRECISION, not INT64.
• Dialect 1 clients expect information stored DECIMAL and NUMERIC data types to be returned as double precision; such clients cannot create database fields to hold 64-bit integers.

InterBase 6 and later servers recognize all the other InterBase features in dialect 1 clients and databases.

Dialect 2 Clients

Dialect 2 is available only on the client side. It is intended for assessing possible problems in legacy metadata that is being migrated to dialect 3. To determine where the problem spots are when you migrate a database from dialect 1 to dialect 3, you extract the metadata from the database, set isql to dialect 2, and then run that metadata file through isql. isql issues warning whenever it encounters double quotes, DATE data types, or large exact numerics to alert you to places where you might need to change the metadata in order to make a successful migration to dialect 3.

To detect problem areas in the metadata of a database that you are migrating, extract the metadata and run it through a dialect 2 client, which will report all instances of transition features. For example:

isql -i v5metadata.sql

Do not assign dialect 2 to databases.

Dialect 3 Clients and Databases

In dialect 3, the InterBase server interprets transition features as InterBase 6 SQL 92-compliant:

• Double quoted strings are treated as delimited identifiers.
• Dialect 3 DATE data type fields contain only date information. Dialect 3 clients expect only date information from a field of data type DATE.
• The TIME data type is available, and stores only time information.
• Dialect 3 databases store DECIMAL and NUMERIC data types with precision greater than 9 as INT64 if, and only if, they are in columns that were created in dialect 3.
• Dialect 3 clients expect DECIMAL and NUMERIC data types with precision greater than 9 to be returned as INT64.

To learn how to migrate older data to INT64 storage, see Do you really need to migrate your NUMERIC and DECIMAL Data Types? and Migrating NUMERIC and DECIMAL Data Types.

Advance To:

• Setting SQL Dialects