SQL Dialects - Functional Specification

Description

InterBase V6.0 introduces new features like Exact Numeric, Delimited Identifiers, SQL date time, etc. This also introduces ambiguity between pre 6.0 and 6.0 SQL syntax. In order to provide a smooth migration for the user, SQL dialect has been introduced. This document describes the support for the SQL dialect feature.

New SQL dialect values 2 and 3 are introduced for 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 and isc_dsql_prepare APIs. The new SQL dialect values are being used by Delimited SQL Identifier project, SQL date project and Exact numeric type project. The following are the definitions of SQL dialect:

• 0 - SQLDA (obsolete in V6.0).
• 1 - XSQLDA (no changes from V5.0 behaviour).
• 2 - XSQLDA, transitional flagger (default behavior for create database only). The purpose of SQL dialect 2 is to catch changes when going from SQL dialect 1 to SQL dialect 3.
• 3 - XSQLDA, InterBase V6.0 SQL Synmatics. InterBase parser will process anything that is delimited by double quotes as SQL delimited identifiers, DATE as date and 64-bit numeric.

A new RDB$FIELD_PRECISION column is added to ODS 10 databases' RDB$FIELDS system table and is to be used by the 64-bit numeric project.

A new command line option -s[qldialect] n is introduced to ISQL:

• -s[qldialect] 1 - InterBase parser processes as in InterBase V5
• -s[qldialect] 2 - transitional flagger (default behavior for create database only). The purpose of SQL dialect 2 is to catch changes when going from SQL dialect 1 to SQL dialect 3.
• -s[qldialect] 3 - InterBase V6.0 SQL Synmatics. InterBase parser will process anything that is delimited by double quotes as SQL delimited identifiers, DATE as date and 64-bit numeric.

GPRE support for SQL Dialect please see the article entitled SQL Dialect Support in Gpre

New InterBase SET statements 'SET SQL DIALECT n' where n is 1, 2 or 3 will also be introduced to ISQL, GPRE, and WISQL as GUI control. 'SET SQL DIALECT n' will overwrite any -s[qldialect] n command line option.

• When SET SQL DIALECT 1, all the subsequent SQL statements will be processed as it is today. The function behaves as same as in ISQL -s[qldialect] 1.
• When SET SQL DIALECT 2, all the subsequent SQL statements will be processed according to the rules of flagger. The function behaves as same as in ISQL -s[qldialect] 2.
• When SET SQL DIALECT 3, all the subsequent SQL statements will be processed according to the rules of InterBase V6.0 SQL Synmatics. The function behaves as same as in ISQL -s[qldialect] 3.
• In WISQL, a new Dialog Box will be added to the Advanced Settings Panel. Selecting the dialog item in the Advance Setting Panel will set the dialect to 1, 2 or 3. A new Dialog Box will also be added to the WISQL's Main Dialog Panel. A click on that dialog item will set the dialect to 1, 2 or 3. The rest of the processing logic is the same as described for ISQL.

A concept of client SQL dialect and database SQL dialect is introduced in this project. client SQL dialect is the SQL dialect that the user intent to use for processing the SQL statements. database SQL dialect is SQL dialect that was used to create database and all its meta data. Sql Dialect Summary and Sql Dialect Matrix explain how the client SQL dialect behaves with different versions of InterBase servers, and databases with different SQL dialects.

New SHOW SQL DIALECT statement is added to show current Client SQL Dialect setting and Database SQL Dialect value.

New isc_info_db_SQL_dialect parameter is added to InterBase.

A new command option -set_db_SQL_dialect is added to GFIX. This command line option can be used to set an ODS 10 database speaking SQL dialect V6 1 or 3 only, and can be used with isc_database_info API to query the database's dialect.

Examples on how to use the new dialect values in isc_dsql_prepare:

• Today:

  o isc_dsql_prepare (isc_status,
                                &tr_handle,
                                &stmt_handle,
                                x_length,
                                sql_string,
                                1,    <--- dialect, indicates pre V6.0 functionalities
                                 x_sqlda);
  

• New:

  o isc_dsql_prepare (isc_status,
                                &tr_handle,
                                &stmt_handle,
                                x_length,
                                sql_string,
                                3,    <--- dialect,  indicates V6.0 functionalities
                                x_sqlda);
  

The rationale for choosing default behavior as transitional flagging for create database:

1. To catch any usage of SQL statements that is not compliant to SQL standard definition of DATE, exact numeric type, and string constants.
2. To warn the users that the InterBase engine will process those statements differently than in the past.
3. To make the users aware that they have to correct these non compliances and get ready for migrating to the future capabilities of InterBase.

User Interface/Usability

1. Two new SQL syntax statements will be added to ISQL, WISQL, and GPRE

   • SET SQL DIALECT n where n is 1, 2, or 3
   • SHOW SQL DIALECT
   • Dialog Box is added to the Advance Setting Panel and a new Dialog Box is also added to the WISQL's Main Dialog Panel.

2. New dialect values are introduced to APIs (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 and isc_dsql_prepare). The new dialect values are: * 2 - XSQLDA, InterBase migration flag on * 3 - XSQLDA, InterBase V6.0 new syntax flag on

3. New command line option is added to ISQL and GPRE

   • -s[qldialect] n, where n is 1, 2, or 3

4. A new column RDB$FIELD_PRECISION is added to ODS 10 databases' RDB$FIELDS system table. PRE ODS 10 databases must be backed up and restored with IB V6.0 GBAK to have this new column added to RDB$FIELDS system table.

5. a new public dpb parameter - isc_dpb_SQL_dialect is a dialect information for creating a database. This dpb parameter is used by isc_create_database.

6. new public dpb parameter - isc_info_db_SQL_dialect for finding out which dialect that the database speaks. This dpb parameter is used by isc_database_info.

7. New isc_database_info parameter - isc_info_db_SQL_dialect for any users to query the database's SQL Dialect.

8. New #define constructs in ibase.h:

   * #define SQLDA_VERSION1                  1
   * #define SQLDA_VERSION2                  2
   * #define SQL_DIALECT_V5                  1
   * #define SQL_DIALECT_V6_TRANSITION       2
   * #define SQL_DIALECT_V6                  3
   * #define SQL_DIALECT_CURRENT             SQL_DIALECT_V6
   * #define isc_dpb_SQL_dialect             6           3
   * #define isc_info_db_SQL_dialect         6           2
   

9. New messages are added to the message database:

Migration Issues

Existing applications (embedded or API applications) will run as in InterBase V5 without any changes. But if an existing embedded application needed to be recompiled, it must be recompiled with -sqldialect 1. If an existing ISQL script needed to be rerun, it must be executed with -sqldialect 1 or SET SQL DIALECT 1 statement must be added to the beginning of the ISQL script file. Nothing to be added for recompiling an existing API application. backing up and restoring pre ODS 10 databases is not required.

To get the new features:

1. Create the database with SQL dialect 3
2. For old applications:
3. Pre ODS 10 databases must be backed up and restored with InterBase V6.0 GBAK utility.
4. Fix up the old applications:
5. Fix up the meta data in the database
6. For API applications, the dialect value 1 must be changed to dialect value 3 on 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 and isc_dsql_prepare statements.
7. Mark the database as SQL dialect 3 database with GFIX -set_db_SQL_dialect 3

Any databases created by InterBase V6.0 with client SQL dialect 1 will work with any existing applications. But if there is a need to recompile the application, it must be recompiled with -s[qldialect] 1 precompiler option.

InterBase will not provide any tool for converting old format of meta data definitions to the new format of meta data definitions. It is the user responsibility to convert his/her meta data definitions from the old format to the new format. But it is not the migration requirement for activating SQL delimited identifiers feature.

Migration matrix (as migrating SQL delimited identifier project):

Issues That Were Not Resolved

Unicode support of delimited identifier will be dealt as an separate issue after the feature is implemented (due to current SQLDA->relname is only 31 bytes long and is not large enough to hold non-ASCII SQL identifier. The support of non-ASCII SQL delimited identifier will be dealt when implementing the ANSI SQLDA)

Since WISQL is going away and we may bring in 3rd party tool to replace WISQL, the open issue is who is going to do the support of this feature in "WISQL" ?