Firebird SQL

Firebird 1.5 supports a fair amount of useful SQL not supported by Interbase 5, and, therefore, not in the Delphi 5 (Interbase 5) online manuals - This just documents some of it.


Debugging Scripts

I use isql.exe to run scripts - but it normally only shows errors. The following command permits isql to show each command as it executes and, therefore, which command failed.


Alter Table

Alter table is standard sql, but Alter column is new to Firebird 1.5 In the example, d1 is a domain name. As shown, you can also convert some columns from numeric to character types.

There are some limitations to modifying columns if they are used as part of a constraint, view, etc.


SET SQL DIALECT n

By default, Firebird 1.5 creates new databases using dialect 3 - this is a good thing. The Interbase DataPump automatically places the following non-standard sql in the build scripts if you specify that you will use that dialect. In practice, this does nothing (since it is the default) ... setting the dialect to 1 will change how things work.

Firebird 1.0 used only dialect 1.

When a database is opened, the default dialect is dependent on the Firebird version used to create the table.


Date literals

Date literals are string values that can be used instaed of date values in SELECT, INSERT, and UPDATE statements.


First & Skip

This will limit the number of rows returned. Notice that distinct must be after first and skip.

(I don't know if Interbase supports this)


Various Functions

These are some of the functions that can be used in various SQL statements - including SELECT, INSERT, and UPDATE - As far as I know, the function names are NOT case sensitive.

Reference - several functions listed here do NOT work with Firebird 1.5.3.


References

On-line references to this information are pretty poor (that's why I created this page).

This information is based in part on "The Firebird Book" by Helen Borrie.


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / Databases / Firebird / SQL.html