InterBase 6.0 SQL Dialects

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.

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: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.

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: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

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:

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;
}

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 5.2.0.2, it will support both dialect 1 and dialect 3 via the SQL Links.

Here's a note extracted from http://www.techtricks.com/paradox/ibdialect.php

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:

   My Computer\HKEY_LOCAL_MACHINE\
   SOFTWARE\Borland\Database Engine\
   Settings\DRIVERS\INTRBASE\DB OPEN
   

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.

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.

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:

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 as is, but can't be extracted and used elsewhere.