• You are here:
• Home
• >
• Firebird resources
• >
• Documentation
• >
• Function & Design
• >
• Document 152

Delimited SQL Identifiers - Functional Specification

Description

InterBase currently does not support delimited SQL identifiers. Due to the lack of this support, applications that use InterBase reserved words as SQL identifiers require some work to be ported to InterBase. It also makes applications written for InterBase less portable. A number of requests have been filed to InterBase requesting the support of this feature.

The purpose of supporting this feature is to:

• Enable the use of reserved word as SQL identifiers.
• Conform to SQL, ODBC, and JDBC standards.
• Enable the use of non ASCII identifiers as SQL identifiers (next phase of the project).
• Enable to port non InterBase applications more easily.
• Allow existing 3rd party JDBC or OBDC non InterBase but portable applications to work on InterBase.
• Enable InterBase applications to be more portable.

The requirements of this project:

• This new feature should not break existing applications (ISQL nor GPRE).
• Provide greatest flexibility to customers to mix the new feature with the current functionality within one application.
• Customer should not make any changes to existing applications when migrating to the new feature. But the user is required to add a command line option to the existing command line option in GPRE or ISQL if he/she would like to turn off the new features.

InterBase definition of a string constant is not compliant with the SQL standard. InterBase allows a string constant to be delimited by single quotes or by double quotes.

In the ANSI SQL standard, a string constant can only be delimited by single quotes and a SQL delimited identifier is delimited by double quotes. SQL delimited identifier is case sensitive. For example, table "foo" is different from from table "FoO". But in the case of regular SQL identifier, table fOo is same same table FOO and it is also the same as table "FOO".

This document describes the InterBase engine support for delimited identifiers. Delimited identifiers are among the many new features that InterBase 6.0 supports. The InterBase engine now has to understand the difference between single quoted and double quoted strings and interpret them correctly

User Interface/Usability

In InterBase 6.0, Any thing that is delimited by single quotes is treated as a string constant. But any thing that is delimited by double quotes is processed based on the following table.

SQL statements that are impacted:

1. create/alter/connect database --- InterBase treats database name as file name and processed as a string constant, therefore it must be delimited by single quotes.
2. create shadow --- InterBase treats file names as string constants, therefore it must be delimited by single quotes.
3. create/alter/drop/show domains.
4. create/alter/drop/show tables.
5. constraint name.
6. correlation/alias names.
7. select/insert/update/delete with delimited table/column name.
8. create/alter/drop/show exceptions.
9. create/alter/drop/show procedures.
10. create/alter/drop/show triggers.
11. create/alter/drop/show/set statistic indexes.
12. create/alter/drop views.
13. create/set generators.
14. create/drop roles.
15. in order to support SQL Roles, the passing of the role information by the CONNECT statement is changed as the following:
    CONNECT ... ROLE role_name where the role_name is not case sensitive.
    CONNECT ... ROLE "role_name" where the role_name is case sensitive.
16. grant/revoke.
17. isql -extract.
18. declare tables (handled by GPRE).
19. declare/prepare/describe/execute statements (handled by GPRE).
20. declare/open/fetch/close cursors (handled by GPRE).
21. events (handled by GPRE).
22. set transactions (handled by GPRE).
23. UDF/filters (N/A).
24. shadow/external files (N/A).

New error messages:

Migration Issues

Existing applications (embedded or API applications) will run as it is today without any changes. But if an existing embedded application needed to be precompiled, 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. pre ODS 10 databases must be backed up and restored with IB V6.0 GBAK utility.
2. fix up the old applications:

   • Modify any string constants that is delimited by double quotes to be delimited by single quotes.
   • 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 and isc_dsql_prepare statements.
   • add '-sqldialect 3' to the command line option stream (for embedded and ISQL script applications only)

3. Any databases created by InterBase V6.0 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.
4. 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)

NOTE:
This project assumes using only XSQLDA. If we introduce ANSI SQLDA, then this feature has to be revisited.