SQL Dialect Support in Gpre - Functional Specification

Description

InterBase 6.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 gpre support part for the SQL dialect. Support for Exact Numeric, Delimited identifiers is not a part of this of this document.

User Interface/Usability

The user can specify the SQL dialect in gpre either by using a command line option or by using SET command within the user code. A new command line switch -sqldialect is added. Valid values for this switch are 1, 2 or 3. Also the switch -SQLDA is retired. This is now a deprecated feature and we only use the new SQLDA i.e. XSQLDA. The user can also embed Sql statement EXEC SQL SET DIALECT n to specify a dialect. Valid values for n could be 1,2 or 3. The default dialect is set to that of the compile time database specified. If the default dialect is different than the one specified by the user then a warning is generated and the default dialect is set to the user specified value. This is a SQL feature only. GDML statements will not be using the dialects. An embedded statement SET DIALECT will take precedence over the command line switch -sqldialect.

SET DIALECT statement is a compile time directive to gpre hence there is no semblance of scope for these statements. For e.g.:

 EXEC SQL set dialect 1
 sql statements.....
 if  (A)
        {
        EXEC SQL set dialect 2
        sql statements......
        }
 else
        {
        EXEC SQL set dialect 3
        sql statements.....
        }

sql statements....

In the code snippet shown above, The SQL statements after the if/else clause will be executed in dialect 3 because that was the last set dialect value gpre processed. If the user wanted that part of the code to be in dialect 1 then he would have to specify it once more after the else block.

GPRE will generate error if the dialect specified is not in the range of 1 - 3.

Requirements and Constraints

General InterBase requirements.

Migration Issues

Since the SQLDA switch is deprecated all the embedded sql codes that used old-style SQLDA structures should be re-written using XSQLDA.