SQL syntax

<< Data types | Migration from MS SQL to Firebird | Server-side SQL >>

Using database basics
Using variables
Flow control
Standard statements
Using transactions
Using cursors

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.

MS SQL 7 and above supports modification on joins (update, delete, insert). Firebird has no such syntax. Data types are, of course, different for the different databases. 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 statements. This can be the source of some tricky bugs, specially when using many parentheses. 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. For example, compare the following code snippets.

 /* MS SQL */
 CREATE PROCEDURE my_procedure
 AS
 DECLARE @my_variable int
 SET @my_variable = 5

 /* Firebird */
 CREATE PROCEDURE my_procedure
 AS
 DECLARE VARIABLE my_variable int;
 BEGIN
   my_variable = 5;
 END

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.

 IF (something = 'unknown')
   THEN something = 'uuhhh.....';
   ELSE something = 'I know! I know!';

`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

 /* This is how it can be converted to Firebird. */
 /* Isolate the CASE statement. */
 CREATE PROCEDURE get_state_name ( state_code char(2) )
 RETURNS ( state_name varchar(64) )
 AS
 BEGIN
   IF (state_code = 'CA') THEN state_name = 'California';
   ELSE IF (state_code = 'UT') THEN state_name = 'Utah';
   ELSE state_name = 'unknown';
 END 

 /* This is the selectable stored procedure. */
 CREATE PROCEDURE list_states
 RETURNS (state varchar(64))
 AS
 DECLARE VARIABLE short_state CHAR(2);
 BEGIN
   FOR SELECT state FROM authors INTO :short_state DO
   BEGIN
     EXECUTE PROCEDURE get_state_name :short_state
     RETURNING_VALUES :state;
     SUSPEND;
   END
 END

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 keyword after the WHILE condition. Compare the following equivalent snips.

 /* Firebird syntax. */
 WHILE (i < 3) DO
 BEGIN
   i = i + 1;
   j = j * 2;
 END

 /* MS SQL syntax. */
 WHILE (i < 3)
 BEGIN
   SET @i = @i + 1
   SET @j = @j * 2
 END

`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 extensions 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.

 /* MS SQL syntax to delete all records in my_table. */
 TRUNCATE TABLE my_table /* ...or... */
 DELETE FROM my_table

 /* Firebird syntax. */
 DELETE FROM my_table

The biggest threat are our fumbling fingers. More data has been destroyed by "delete from xxx" "oops" than by a 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 is 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 multi-generational 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.