Question: Paradox displays an "Unable to open table" error when I try to open a table from an Interbase database. What's causing this and how do I fix it?

Answer: If you're using a recent version of Interbase (or Firebird, its open-source sibling), your BDE alias is probably not using the SQL dialect of your InterBase alias. To fix it, you need to verify that:

• This is the problem you've run into.

• BDE is properly configured to support the SQLDIALECT setting for Interbase aliases.

• Your current alias uses the correct dialect for your Interbase database.

While this sounds like a bit of work, it goes quickly and is detailed in the following sections.

Verifying the problem

The easiest way to verify this as your actual problem, review the extended information provided by the Paradox Error dialog. To do this, simply try to open a table in your database and wait for Paradox to display the error message. When it appears, click the [>>] button and see if the extended details are similar to the following:

   Column unknown
   [Column name]
   Client SQL dialect 1 does not support reference to DATE datatype

The phrase "Client SQL dialect" is the pay-off; it indicates that your BDE alias is using a different SQL dialect than your database.

Note: In BDE terminology, "client" refers to any application using BDE to connect to databases, such as Paradox for Windows, dBASE for Windows, custom Delphi/C++ Builder applications, and so on.

To fix the problem, you need to change the alias to support the proper dialect. Before you can do that, however, you need to verify that BDE supports the required setting. (It is not installed by default.)

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

Note: If you've just added the SQLDIALECT setting, you may need to delete all existing Interbase aliases and re-create them before you can use this setting in your aliases, for BDE does not refresh the settings of existing aliases.

Verifying BDE Interbase aliases use the correct dialect

To verify that your BDE alias uses the correct dialect, you need to know the dialect used by your Interbase database. Here's one way to do it:

1. Start IBConsole and log into your Interbase server.

2. Connect to your Interbase database by double-clicking its name from the list of registered databases. (If your Interbase database isn't listed, you need to register it.)

3. Once you've connected to the database, you can determine its dialect in one of several ways:

   • The Properties action displays the dialect on the General tab of the Database Properties dialog. Look for a setting called "Database dialect."

   • The Database Statistics action lets you choose from several different statistics. Choose either All Options or Header Information. Again, look for a setting called "Database dialect."

   • The View Metadata action displays the current dialect as the first line of metadata.

   Each option provides the value you need for SQLDIALECT.

Next, verify that your BDE alias uses the correct SQL dialect:

1. Use the Control Panel to start the BDE Administrator.

2. From the Databases tab, choose your current Interbase alias. Be sure to log with an administrative password.

3. After connecting the alias, verify that the SQLDIALECT setting matches the value used in your database.

4. Apply any edits and then restart all BDE client applications for your changes to take effect.

When the dialect of your BDE alias matches the dialect of your Interbase database, you should be able to open tables with no further problems.