Understanding SQL Dialects
Go Up to Migration Issues
Below are explanations of server and client behavior with SQL dialects 1, 2, and 3.
Contents
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 asTIMESTAMP
; the name has changed but the meaning has not. Dialect 1 clients expect the entire timestamp to be returned. In dialect 1,DATE
andTIMESTAMP
are identical. - The
TIME
data type is not available. - Dialect 1 databases store
DECIMAL
andNUMERIC
data types with precision greater than 9 asDOUBLE PRECISION
, notINT64
. - Dialect 1 clients expect information stored
DECIMAL
andNUMERIC
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 typeDATE
. - The
TIME
data type is available, and stores only time information. - Dialect 3 databases store
DECIMAL
andNUMERIC
data types with precision greater than 9 asINT64
if, and only if, they are in columns that were created in dialect 3. - Dialect 3 clients expect
DECIMAL
andNUMERIC
data types with precision greater than 9 to be returned asINT64
.
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.