Keywords Used for Specifying Data Type - Firebird

The keywords for specifying the data type in DDL statements are provided here for quick reference. For exact syntax, refer to the relevant chapter in this part of the guide for the data type in question.

Special Migration Topic: SQL Dialects

If you are an ex-InterBase user, or you have used outdated migration tools to convert another RDBMS to InterBase, then SQL dialects are likely to affect several aspects of the new life of your databases and applications under a Firebird server.

On-Disk Structure and Dialect

On-disk structure (ODS) identifies a database with respect to the major release version of a Firebird or an InterBase server that created or restored it. The ODS of a database affects its compatibility with server versions. A file suitable for upgrading the ODS can be created by backing up the database under the server version that created it, using the gbak utility for that old version, with the – t[ransportable] switch. When that backup file is restored, using the gbak version distributed with the target server, the restored database will have the new ODS. It is not possible to “downgrade” the ODS of any database.

ODS Upgrade Does Not Change the Dialect

Upgrading the ODS does not make any difference to the SQL dialect of the database: a dialect 1 database will remain a dialect 1 database.

Firebird Databases

Firebird 1.0.x has an ODS identified as ODS-10. Firebird 1.5 has ODS-10.1. To make an ODS-10 database created in Firebird 1.0.x into ODS-10.1, you merely have to back it up and restore it using the Firebird 1.5 gbak. By default, Firebird servers 1.0.3+ and 1.5 create dialect 3 databases. To check your databases, see the section “How to Determine Dialect” later in this chapter.

InterBase 6.0.x Databases

The “open source” InterBase 6.0.x versions have ODS-10. However, to upgrade IB 6.0.x to any Firebird version, it is advisable to use the InterBase 6.0 version of gbak, using the –t[ransportable] switch. The backup file should then be restored using the gbak appropriate to the target Firebird server version.

If the IB 6.0 database was created under default settings, it is probably dialect 1. See the section “How to Determine Dialect” later in this chapter.

InterBase 5.x Databases

InterBase 5 databases have ODS-9 or “9-point-something.” Firebird servers can open them, read them as dialect 1 databases, and update them without altering their on-disk structure, enabling them to be returned to an IB 5.x server environment at any time.

There is no such thing as an ODS-9 dialect 1 or dialect 3 database. To upgrade an ODS-9 database to Firebird, use the IB 5.x gbak program, running under the IB 5.6 server with the –t[ransportable] switch. Upgrading an IB 5. x database to Firebird does not convert it to a dialect 3 database. Its SQL dialect will be 1 and the upgrade is irreversible.

Where Dialect Counts

The dialect concept distinguishes the data type support and language features available to ODS-9 databases (dialect 1), and ODS-10 and higher (dialect 3). The server itself has no “dialect”—the dialect of the database is stored as a database attribute. It is the client interface that determines which set of features to request on behalf of the database. Under some conditions, if you as application developer or admin tool user get it wrong, you will cause erroneous data to be posted and corrupt the database.

It is convenient to refer here to an instance of a client connection, whether it be through the API library itself or through a customized language driver such as JayBird (Java), ODBC, or a .NET provider, as “a dialect 1 client” or “a dialect 3 client.” What it means is that the client interface has been set up to request dialect 1 or dialect 3 features.

What Can Break

The following items illustrate some of the ways in which dialect 1 and dialect 3 differ:

  • Dialects 1 and 3 store large scaled numbers differently. All dialect 3 fixed decimal types —NUMERIC and DECIMAL —having precision higher than 10 are 64-bit integers with field descriptions that include some attributes to determine precision and scale. Dialect 1 fixed numeric types are stored as 8-, 16-, or 32-bit integers, and those with precision exceeding 10 are converted to a 64-bit floating-point DOUBLE PRECISION type for storage. Your data is likely to throw overflow errors if a dialect 3 client submits requests to store number data in a dialect 1 database, or generate wrong results when a dialect 1 client submits requests for number operations to a dialect 3 database.
  • Dialect 3 generators are 64-bit integers, whereas dialect 1 generators are 32-bit.
  • Dialect 3 arithmetic operations were brought up to SQL-92 standard, whereas dialect 1 uses non-standard rules. For example, integer-by-integer division in dialect 3 returns a truncated integer, whereas in dialect 1 it returns a double-precision floating-point number. If your application stores the result of an expression involving this arithmetic operation, “wrong” results will be stored without throwing an exception.
  • Both dialects have a date/time type named DATE, but they are different types. The dialect 1 DATE is equivalent to the dialect 3 TIMESTAMP, and the dialect 3 DATE is a date-only type not supported in dialect 1.
  • Dialect 3 supports a TIME (time-of-day) type, which is unsupported in dialect 1.
  • In dialect 3 databases, Firebird supports the ANSI SQL convention for optionally delimiting identifiers by enclosing them in double quotes, enabling the use of identifier strings that are illegal in dialect 1. Mismatching the client and data-base dialects will cause exceptions and breakages.
  • The SQL string delimiter is the single quote (apostrophe). Dialect 1 permits the alternative use of double quotes for delimiting strings. Dialect 3 forbids it, for obvious reasons. Again, connecting with the wrong client dialect will cause exceptions and breakages.
  • Dialect 3 has more reserved keywords than dialect 1. Existing dialect 1 databases that use the new keywords as identifiers will not work with a dialect 3 client.
  • Dialects 1 and 3 behave differently with regard to implicit type conversion. This will be an issue if you wish to convert an existing database to dialect 3 and update its supporting applications.

Dialect 2

There is no such thing as “a dialect 2 database.” Dialect 2 is a client setting that you can use for performing the data type transitions required to convert a dialect 1 database to dialect 3. Inprise Corporation (now Borland) released a Migration Guide document with IB 6.0 in 2000 that describes the full sequence of tasks involved in converting a dialect 1 database to dialect 3. It is available from several Firebird community websites as a PDF document.

How to Determine Dialect

Go to a console (command window) and get to the /bin directory where the Firebird command-line tools are located. Start the isql utility. Now, connect to your database:

Then enter this ISQL command:

This is good. If you find there is a mismatch, it will not do harm as long as you do not try to insert or alter data. You must take steps to ensure that the client will use the correct dialect.

Changing the Client Dialect in isql

Suppose that, now in isql , you want to close down your connection to this database and connect to another one, which you know is dialect 1. This is what you do:

That is OK, because you are just going to connect to a dialect 1 database:

Many of the free and commercial GUI admin tools provide the ability to set the client dialect interactively. Database access components and drivers provide properties or other mechanisms to pass the dialect in the API connection structure.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics