MS SQL vs Firebird SQL by wuyunyi


									                             MS SQL vs Firebird SQL
There are 532 SQL statements in the FieldWorks C++/C# application code (as at Nov
2003). Each SQL statement has been examined for the presence of the following
features/syntax, not supported in Firebird:

1. Locking hints (eg. readuncommitted) (186 statements): Firebird's provision for multi-
user database access differs significantly from that of SQL Server. Firebird uses versioning
whereas SQL Server uses locking* (see,1410,27007,00.html). Unlike SQL Server, Firebird
requires all SQL statements to be executed within the context of a transaction. In Firebird,
the isolation level is established when a transaction begins and applies to all SQL
statements executed during that transaction. It is not possible, as it is with SQL Server, to
provide locking hints that apply only to an individual SQL statement. Any locking hints must
be omitted from Firebird SQL statements.

* SQL Server 2005 Beta 2 introduces "a new snapshot isolation level" described as "a row
versioning mechanism" (see

It is likely that this snapshot isolation level will improve concurrency in a multi-user
environment. Firebird also has a snapshot isolation level.

Recent work by John Thompson and Steve McConnel on the Undo/Redo mechanism, with
the intention of reducing transaction duration, may mean that the readuncommitted locking
hint becomes less widely used. In any case, Firebird does not support 'dirty' reads.

See fw/Doc/Database/Readuncommitted.htm, fw/Doc/Database/ActionHandler.htm and
fw/Doc/Database/UndoRedoOptions.htm for further information.

2. enclosure of object names in (square) brackets (187 statements): SQL Server requires
delimited object identifiers to (a) permit spaces within object names; (b) permit special
characters* within object names; and (c) allow keywords to be used as object names.
Elsewhere, brackets were included to adhere to an LSDev SQL coding standard, but were
not needed by SQL Server. More recently, there has been a tendency to omit the brackets
(where possible) from new code and to remove them from existing code as it is amended,
thus improving readability. Firebird uses double quotes, when necessary, to delimit object
names. SQL Server also accepts double quotes, as an alternative to brackets, for this

Unfortunately, Firebird treats delimited object names differently from SQL Server with
respect to case-sensitivity. For example, SELECT "columnName" FROM ... will succeed only
if the table was created with a delimited column name and with exactly the same internal
capitalisation. If the double quotes are omitted from the column name when the table is
created, then SELECT columnName FROM ... will execute successfully, as will SELECT
columnname FROM ... or SELECT COLUMNNAME FROM .... But, for reasons (a), (b) and (c)
above, certain column names must be enclosed in double-quotes when the table is created.
In these cases, care must be taken to ensure consistent use of double-quotes and case
throughout the application. However, when tables are created using delimited, upper-case
column names, all non-delimited names in DML statements remain case-insensitive, with
only those column names that must be delimited (for reasons (a), (b) and (c)) needing to
be coded in upper-case.
Ideally, all object names would omit spaces, special characters, and would not duplicate
keywords. Then there would be no need for delimiters and all object names would be case-

* Firebird object names can, ordinarily, contain only the following characters: A-Z, a-z, 0-9,
$ and underscore.

3. Unicode literals coded as N'text' (9 statements): As all text will be stored as Unicode
(UTF-8) in the Firebird database, there is no need to distinguish between Unicode and non-
Unicode text. Firebird requires all text literals to be coded simply as 'text' (omitting the N).

4. TOP n used in SELECT statements (6 statements): Rather than TOP, Firebird uses the
FIRST keyword.

5. EXEC used to invoke stored procedures (126 statements): Firebird uses either EXECUTE
PROCEDURE or SELECT to invoke stored procedures, depending on the way the procedure is
coded and how it is intended to be used.

In Firebird, there are two types of stored procedures (SPs): selectable procedures (invoked
with SELECT) return multiple rows, whereas executable procedures (invoked with EXECUTE
PROCEDURE) optionally return one or more output parameters. Most, if not all, FieldWorks
SPs will become executable procedures in Firebird. They will return data, where required, by
means of output parameters. Firebird selectable procedures are likely to be used to create
'virtual tables', compensating for Firebird's lack of temporary tables (see 15 below).

6. UPDATE ... FROM used to supply column values for the table being updated directly from
column values in a joined table (2 statements): For example, the following SQL statement
returns an error in Firebird:

update UserViewField
    set WsSelector = pssl.WsSelector,
        WritingSystem = pssl.WritingSystem
    from CmPossibilityList pssl, UserViewField uvf
    where pssl.Id = %d and uvf.PossList = %d

But, when rewritten to use subqueries, the statement executes successfully:

update UserViewField
     set WsSelector = (select WsSelector from CmPossibilityList where Id =
         WritingSystem =
                   (select WritingSystem from CmPossibilityList where Id = %d)
     where PossList = %d

7. Two or more SQL statements submitted for execution at the same time (29 statements):
The Firebird C API does not support this (unlike that of SQL Server and PostgreSQL).
OleDbEncap will need to forward to the server one SQL statement at a time, managing
multiple result sets arising from execution of a command, so that they can subsequently be
returned to the client application upon each GetRowset() call.

Note that many of these 'compound' commands will be of the form "DECLARE variable ...
EXEC procedure ... SELECT variable". These will need to be changed anyway (see 8 below).
8. DECLARE variable (23 statements) and SET variable (8 statements): It's not possible to
define and use SQL variables in Firebird client-side SQL.

In some cases, a SQL variable is declared, is assigned the return code when a stored
procedure is executed, and this is then returned to the application by executing a SELECT
statement. For example:

declare @err integer; exec @err = DeleteObj$ %d; select @err

This sequence of SQL statements could be replaced by the following, in Firebird, where the
DeleteObj$ stored procedure returns an integer output parameter:

execute procedure DeleteObj$ %d

It will also be necessary for the application to obtain the output parameter value (using
SetParameter/GetParameter) instead of processing a rowset (using

In general, both the SQL statements and the surrounding C++/C# code will need to be
changed to eliminate the SQL variables. Except for the simplest cases, such as that
described above, concatenated SQL statements will need to be presented individually to the
server, with intermediate values being stored in C++/C# variables rather than SQL
variables. Or, where performance becomes an issue, a sequence of SQL statements could
be executed within a stored procedure.

9. IF … ELSE used in client-side SQL (13 statements): Conditional statements are not
supported in client-side Firebird SQL.

if exists
     (select * from %s_%s cf (readuncommitted)
          join StText_Paragraphs sp (readuncommitted) on sp.src = cf.dst
          join StTxtPara stp (readuncommitted) on = sp.dst
               where stp.contents like N'%[^'+nchar(0xA0)+nchar(0x2000)+'-'
                    +nchar(0x3000)+' ]%' )
     select cast(1 as tinyint) fDataExists
     select cast(0 as tinyint) fDataExists

As for 8, above, these statements must either be moved into the surrounding C++/C#
code, or the group of SQL statements will need to be encapsulated in a stored procedure.

10. BACKUP and RESTORE commands (5 statements): Backup and restore of Firebird
databases is performed by using a separate command line utility program (gbak). SQL
statements effecting backup and restore will need to be replaced by shell scripts capable of
running gbak.

11. SET LOCK_TIMEOUT command (1 statement): The Firebird DeadlockTimeout period is a
parameter setting in the Firebird Configuration File (firebird.conf). Unless there is a need to
vary the lock timeout period per connection, the statement can simply be removed.

12. SET IDENTITY_INSERT CmObject ON/OFF (12 statements): According to MSDN, SET
IDENTITY_INSERT ... ON "Allows explicit values to be inserted into the identity column of a
table". These commands, invalid in Firebird, will need to be removed. The same effect can
be achieved in Firebird by using a BEFORE INSERT trigger for the CmObject table that will
generate a primary key value only when the supplied value is NULL.

13. There is no XML support in Firebird (14 statements): Several SQL statements execute
system stored procedures sp_xml_preparedocument or sp_xml_removedocument, or SIL-
developed stored procedures that receive input or create output parameter data in XML
format. The affected, SIL-developed stored procedures are: DeleteObj$, DisplayName_Msa,
DisplayName_MoForm, DisplayName_PhEnvironment, DisplayName_LexEntry,
GetOrderedMultiTxtXml$, ReplaceRefColl$ and ReplaceRefSeq$.

14. SQL statements with embedded newline characters (12 statements): Firebird does not
permit embedded newline (ie. \n) characters. However, the majority of SQL statements that
include newline characters do so by including '%n' in the statement string when calling a
StrUni method (eg. Format()) (44 statements). This latter approach has the advantage that
the behaviour of the StrUni methods can accommodate the requirements of the underlying
database when substituting a value for '%n'.

15. Firebird 1.5.x does not provide temporary tables (11 statements): Only one temporary
table is referred to within the FieldWorks client-side SQL. #ObjInfoTbl$ appears in
AfDbInfo.cpp and MiscDlgs.cpp (both in Appcore). The stored procedures GetLinkedObjs$
and DeleteObj$ (both in Cellar/FwCore.sql) also refer to the same temporary table.

Temporary tables will appear in a later release of Firebird. In the interim, there are two
possible alternatives:

     if only one query is to be run against the temporary table, then a selectable stored
      procedure could be used to produce a result set from a 'virtual table'.

     if several queries must be run against the same temporary table, then a ‘permanent’
      table could be created with, for example, transaction id being included in the table

There are 100 out of 532 statements that will execute unchanged on Firebird. Of those 432
statements that need to be changed, some will require only minor modification, but others
will involve substantial refactoring of the application code and/or new stored procedures to
be developed.

I'm open to suggestions as to how we can support both Firebird and SQL Server from a
single code base. There will certainly be some C++/C# tricks that we can use to achieve
this (eg. conditional compilation, macros), but I wonder if there's a chance that judicious
use of stored procedures could hide some of the SQL differences from the application.


Although the scale of the problem (or potential problem) is unknown, the following SQL
differences have also been noted:

16. In Firebird, object names are limited to 31 characters (27 characters for constraint
names). The limit in SQL Server is 128 characters, and several FieldWorks table names are
known to exceed the Firebird limit. The names exceeding 31 characters could be
abbreviated as a one-off exercise, or a stored procedure could be used at runtime to
translate excessively long table names into a shortened form acceptable to Firebird.

17. Firebird does not permit joins between tables in separate databases.

18. SQL Server allows named input parameters to be supplied to a stored procedure, any
omitted parameters taking default values: A Firebird stored procedure must be called by a
statement that supplies values for all of its input parameters. The parameters are not
identified by name, but by position only.

One possible approach, that keeps the default values within the stored procedures, is to
have the application provide a value of NULL for any parameter that must take a default
value. The stored procedure can then replace each NULL with the appropriate default.

19. Firebird does not allow use of regular expressions in search criteria (see the example
given in 9, above).

20. OleDbEncap uses Firebird's dynamic SQL (DSQL) API. DSQL functions impose the
following limitations on OleDbEncap:

     database creation is restricted to CREATE DATABASE statements executed using either
      the isc_dsql_execute_immediate() or isc_dsql_exec_immed2() API functions.
      OleDbEncap currently uses the isc_dsql_execute2() function to execute all SQL
      statements. If OleDbEncap is required to execute CREATE DATABASE statements, then
      the ExecCommand() method's statement type parameter could be used to indicate
      that alternative processing is needed. Or, OleDbEncap’s interface could be changed to
      provide a CreateDatabase() method.

     initiating a transaction with SET TRANSACTION must be executed using either the
      isc_dsql_execute_immediate() API function. OleDbEncap currently uses the
      isc_dsql_execute2() function to execute all SQL statements. But, since transactions
      can be initiated using OleDbEncap's BeginTrans() method, there is no need to support

     none of the following cursor-related commands are acceptable: DECLARE CURSOR,
      OPEN, FETCH or CLOSE. So far, I have not seen any cursor processing in FieldWorks
      client-side SQL.

     database deletion cannot be achieved by executing a DROP DATABASE statement.
      Instead, the isc_drop_database() function must be used. This would necessitate a
      change to the OleDbEncap interface, introducing a new DropDatabase() method.

Andrew Weaver (27 May, 2005)

To top