SQL Syntax

This section describes differences in the SQL syntax used by Firebird and MS SQL in
general.

Firebird and MS SQL can both use object names (table names,
field names, etc.) directly, when they have no whitespace or other symbols. To include
whitespace and otherwise escape object names, MS SQL uses brackets, [ and ], while
Firebird uses double quotes, ". Another thing to bear in mind is that
MS SQL accepts a database.username.objectname syntax to name objects, which
Firebird does not.

Warning

Bear in mind that MS SQL is case-sensitive in its object naming if it was installed with
the case-sensitive option; otherwise it's case insensitive. Fun. Not.

Tip

MS SQL also accepts quoted identifiers, but by default it is set only when accessed
through OLE DB and ODBC, and not when accessed through the DB-Library. In general, therefore,
this practice is discouraged.

Data types are, of course, different for the different database. Both support a common
subset with the most-used types; this is rarely an issue.

There are different built-in functions. Most of MS SQL functions can be replaced and
extended by UDFs in Firebird.

There are different formats for specifying date constants. In general,
Firebird will accept different formats independently of the
underlying platform - MS SQL, on the other hand, uses a mixture of server-independent,
server-side platform and per-client-connection formats. In addition to this, the MS SQL access
methods typically introduce one or two additional layers where a string constant may be changed
one way or another into a date.

MS SQL has more environment variables than Firebird does, but
the most common ones (identity retrieval and user name retrieval) can be found. The only
important variable missing is the row count of the latest operation.

An important difference is that Firebird 1.0 does not support
the MS SQL CASE statement. You can sometimes use a stored procedure in its
stead, which promotes reusability and eases maintenance. Starting at 1.5, Firebird fully
supports CASE.

A minor difference is that MS SQL does not use a delimiter between statement. This can be
the source of some tricky bugs, specially when using many parenthesis.
Firebird requires that every statement end in a semicolon ; so errors
are easier to spot.

Both MS SQL and Firebird support comments using the /* and */
delimiters. MS SQL also supports the -- syntax to comment a single line. Some client-side
Firebird tools also support this syntax, but it is not supported
in-line.

Using Database Basics

MS SQL allows clients to use many databases from a single connection. To do this, you
can use the dbname.user.syntax, or execute an explicit USE statement.

Firebird does not allow you to use different databases in the
same SQL statement, but it does allow you to perform transactions spanning multiple
databases.

There are many generic tools to enter SQL commands to your database, in both platforms.
Note that for Firebird, you do not need to use
GO to delimit T-SQLbatches; rather, you manage transactions explicitly. You
can also use the default of commit-every-statement on both servers.

Warning

If you MS SQL and Firebird setup on the same computer, be
careful with the isql command. If you do not reference them by the full
path, the one which is first on your system path will be used, and both MS SQL and
Firebird have a command-line isql
program.

Using variables

Variable handling is similar on both platforms. Variables must be declared before being
used, specifying their types. However, bear in mind that variables names in Firebird need not
be prefixed with a @ character, and they do need to be declared before the procedure or
trigger body.

Under both database servers, parameters are considered normal variables, set to an
initial value.

Flow Control

BEGIN ... END

Under both database servers, the BEGIN and END
keywords can be used to group multiple statements, for example, inside an
IF branch.

GOTO

Firebird has no GOTO statement. However, this usually turns for the
better. GOTO statements are usually used in MS SQL because errors do not
roll back transactions by default (the @@ERROR variable must be examined after every
statement); GOTO is used to group error handling statements. In Firebird,
there is a better error-handling mechanism: the WHEN...DO statements.

Of course, GOTO statements can be used for other purposes. In these
cases, using stored procedures correctly will usually improve the database design.

IF

The IF..ELSE statement exists on Firebird with the same semantics.
However, Firebird syntax requires a THEN after the IF condition clause.

CASE

Firebird 1.0 doesn't have a CASE statement, so you'll need to do
some manual conversion work if it is used in your MS-SQL database.

You can skip this section if
you're using Firebird 1.5 or up, since these versions fully support the
CASE syntax.

The CASE statement can be used as a switch statement
in C or a case statement in Pascal to change one value for another. This can
usually be translated to Firebird 1.0 as a stored procedure
returning some value.

/* This is the original MS SQL
statement, using the * traditional pubs database. */
CREATE PROCEDURE list_states
AS
SELECT
CASE state
WHEN 'CA' THEN 'California'
WHEN 'UT' THEN 'Utah'
ELSE 'unknown'
END
FROM authors

Three things should be noted from the example above. First, the conversion is trivial.
Second, it is however quite verbose. Third, using a stored procedure allows for greater
flexibility, and makes maintenance easier. Suppose the CASE statement
for the state occurs in twelve different procedures, and a new state was added; or that you
misspelled a state name; or any other change. It is clearly beneficial to abstract this
conversion, trivial as it may seem, into its own stored procedure.

Again: as from version 1.5, Firebird fully supports the CASE
statement, so no conversion is needed there.

WHILE

WHILE exists in Firebird as it does in
MS SQL, with some differences. There are no BREAK or
CONTINUE statements, but these can be emulated with additional controls
and variables. There's also a small difference in syntax;
Firebird requires a DO keywords after the
WHILE condition. Compare the following equivalent snips.

RETURN

The RETURN statement in MS SQL will return an output integer
variable and stop execution. Firebird supports the EXIT statement, which
will jump to the final END in stored procedures. However, there is no
implicit output variable, so if you need to return a code (it's optional in MS SQL), you
will need to declare an output variable in the procedure.

WAITFOR

The WAITFOR statement in MS SQL will suspend execution for an
amount of time, or until a specified time is reached. Something like this could be done with
a UDF; however, under both database servers, an alternative would be very much preferred, as
the connection from the client remains suspended, too.

Standard Statements

The standard statements which can be found in all databases are
SELECT, INSERT, UPDATE and
DELETE. Firebird and MS SQL support them, but
there are some non-standard MS SQL extension to consider if they are being used.

The SELECT statement in Firebird does not allow the INTO clause to create a new table on
the fly. Instead, it is used to bind a result into a variable.

/* MS SQL syntax to get field values into a variable. */
SELECT @my_state = state
FROM authors
WHERE auth_name = 'John'

/* Firebird syntax. */
SELECT state INTO :state /* --> note the ":" before the name */
FROM authors
WHERE auth_name = 'John'

In MS SQL 7 and above, the SELECT clause can take a
TOP specifier to limit the number of rows returned. This feature is
currently under development for the Firebird engine.

Both MS SQL and Firebird support the normal
INSERT syntax and the INSERT..SELECT syntax.

Both MS SQL and Firebird support the normal
UPDATE. MS SQL also supports a form of UPDATE in which a
join is performed, and one side of the join is updated. Think of this as a
WHERE on steroids. If this feature is absolutely required, it can be
implemented using views.

Both MS SQL and Firebird support the normal DELETE. MS SQL also supports the TRUNCATE
TABLE statement, which is a more efficient (but dangerous) form of DELETE.

The biggest threat are our fumbling fingers. More data has been destroyed by "delete
from xxx" "oops" than deliberate "delete rdb$pages".

--Jim Starkey

Using Transactions

Transactions are rarely used directly in Firebird when using
DSQL. Named transactions are not supported in this case. Both syntaxes accept the
WORK keyword for compatibility.

This should not present a problem in most situations, as MS SQL's explicit transaction
control is usually in place because there no support for using exception handlers.

Tip

MS SQL has a XACT_ABORT global variable, to manage whether
transactions are rolled back on run-time errors. Otherwise, the @@ERROR
variable must be examined after each statement.

In general, most discussions about isolation level problems found in MS SQL environments
are void when taken to a Firebird database server. Contention
between readers and writers is minimal and is resolved by the multigeneration
architecture.

Using Cursors

MS SQL uses cursors mostly to iterate over query results to perform activities. Other
than syntax, there is little difference in what can be accomplished in either database.
Although there are many options for iterating backwards and forwards, in practice the only
cursor used is the forward-only cursor.

/* MS SQL syntax. */
DECLARE my_cursor CURSOR
FOR SELECT au_lname FROM authors ORDER BY au_lname
DECLARE @au_lname varchar(40)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
/* Do something interesting with @au_lname. */
FETCH NEXT FROM my_cursor
END
CLOSE my_cursor
DEALLOCATE my_cursor

/* Firebird syntax. */
DECLARE VARIABLE au_lname VARCHAR(40);
...
FOR SELECT au_lname FROM authors
ORDER BY au_lname INTO :au_lname DO
BEGIN
/* Do something interesting with au_lname. */
END

Note that MS SQL can place cursors in variables and pass them around; this cannot be
performed in Firebird.

Warning

Different versions of MS SQL change the default scope for cursor variables. Be careful
with how you use them and bear this in mind when reading code to convert it.