See my recent article on CodeProject.

The Code Project - Embedded Firebird: Full-Featured Embedded Database with 2 MB Runtime - C# Database

Of those surveyed, 23 percent of developers picked Firebird for use in "edge" databases—in other words, those that are embedded in systems or in devices, such as a point-of-sale system in a retail outlet or a network device. Runners-up included Microsoft Corp.'s Access, at 21 percent, and Microsoft's SQL Server, at 13 percent.

Enterprises Warming Up to Firebird Open-Source Database

I mentioned already in Migration from MySQL I. that in Firebird you should use a generator instead of AUTO_INCREMENT:

CREATE GENERATOR GEN_MYTABLE_ID;

SET TERM ^ ;
CREATE PROCEDURE SP_MYTABLEINSERT (
MYTEXT VARCHAR(20))
AS
DECLARE
VARIABLE ID INTEGER;
BEGIN
ID = GEN_ID(GEN_MYTABLE_ID,1);
INSERT INTO MYTABLE (ID, MYTEXT) VALUES (:ID, :MYTEXT);
END^
SET TERM ; ^

In that example we used a stored procedure SP_MYTABLEINSERT to insert the data. If you need to get the number that was returned by the generator you can modify this stored procedure to return the generator value:

SET TERM ^ ;
CREATE PROCEDURE SP_MYTABLEINSERT (
MYTEXT VARCHAR(20))
RETURNS (
ID INTEGER)
AS
BEGIN
ID = GEN_ID(GEN_MYTABLE_ID,1);
INSERT INTO MYTABLE (ID, MYTEXT) VALUES (:ID, :MYTEXT);
SUSPEND;
END^
SET TERM ; ^

We increase the generator value by one and store the result in ID variable. After modifying the stored procedure header we now return the inserted id.

This is a basic comparison of Firebird 1.5 and Microsoft MSDE 2000 that stresses the advantages of Firebird. The whole matter is not so simple, especially when you are trying to compare Embedded Firebird (that is not a standalone server) and not Firebird Server. I'm going to expand this comparison later (and to bring a Firebird/SQL Express 2005 comparison as well).

Feature	Embedded Firebird 1.5	Microsoft MSDE 2000
Deployment and Administration
Single database file		No
Embeddable	Details...	Limited*
XCOPY data deployment	Details...
Hot backup (24x7 availability is possible)
Can run on Windows 98
Scalability
Database file size limit	Unlimited***	2 GB
Multiple concurrent connections		Limited**
Engine Features
Transactions
Views
Autoincrement fields
Stored procedures and triggers
BLOB fields
Unicode support
National character sets support (including sorting)
ADO.NET Provider
Licensing
Open-source license	Details...
License allows use in commercial applications	Details...

* Embedding. MSDE can be installed with the host application but it still is a separate server. You need to accept MSDE EULA before redistribution. Firebird can be installed as a server or can be used as as a part of the application (just a single DLL copied into the application directory that doesn't expose any TCP/IP or other interface to other applications).

** Multiple concurrent users. MSDE contains a workload governor which adds performance penalty when there are more than 5 concurrent connections working (NB working not just open).

*** Database file size limitation. Firebird is limited only by file system (e.g. 16+ TB on NTFS).

To summarize: Embedded Firebird is a clear win when you need a fully embedded database that users are not aware of. Comparing standalone Firebird server is a different story and we will come back to it later. The clear advantages of Embedded Firebird are:

• Licensing. You can't beat Firebird's open-source license that allows use in commercial applications without any viral effect (i.e. no need to open your proprietary code). Any kind of a "free redistribution" license is just too far from that.
• XCOPY Data Deployment. A Firebird database is a single file. Just copy it somewhere on disk and open it. Compress it and send it by e-mail. Use your own file extension and associate it with your application.
• XCOPY Runtime Deployment. Not only the data but the runtime (which is a single DLL and optional supporting files) can be just copied to your application's directory.
• Runtime size. Compare the MSDE 40+ MB download with the 2 MB Firebird runtime.
• No performance limitations. With Firebird you can have a database of any size and open multiple connections without any penalty.
• Real embedding. Users and administrators don't need to be aware that your application is using Embedded Firebird because it is only accessible from your application.

Related

• Embedded Firebird and Microsoft SQL Server 2005 Express Feature Comparison
• Firebird and Microsoft Jet Feature Comparison
• Firebird Can Be Easily Embedded
• Create a Database Programmatically

MySQL and Firebird battle for database top spot - ZDNet UK News

Survey results (what open source database developers deployed):

• MySQL: 40%
• Firebird: 39%
• PostgreSQL: 11%

Via Firebird Weekly News.

Firebird ADO.NET Provider API Documentation is now available online on http://www.dotnetfirebird.org:

• Firebird ADO.NET Provider 1.6 API Documentation
• Firebird ADO.NET Provider 1.7 API Documentation [beta]

Each Firebird instance creates a REG_SZ value in the registry under the key

HKEY_LOCAL_MACHINE\SOFTWARE\Firebird Project\Firebird Server\Instances

The name for the default instance is "DefaultInstance". The value data contains the server root directory (e.g. "C:\Program Files\Firebird\Firebird_1_5\"). You can detect the server settings by reading firebird.conf (e.g. the TCP port) and aliases.conf (aliased databases).

If you need to join strings with other values in Firebird you need to use || (double pipe) operator:

SELECT 'col1: ' || col1 || ', col2: ' || col2 FROM mytable;

The problem is that if any of col1 or col2 fields are NULL, the whole joined string will be also NULL. If this is not the intended behavior (and it usually is not) you can use COALESCE function to replace the NULL value with another value:

SELECT 'col1: ' || COALESCE(col1, 'NULL') || ', col2: ' || COALESCE(col2, 'NULL') FROM mytable;

See Expressions involving null.

The possible reasons of Firebird database corruption:

• Abnormal termination of server computer (e.g. electric power interruption)
• Server hardware failure (HDD, etc.)
• Accessing a database file when server is running and using that file
• Full disk
• Restoring a backup to a running database
• Allowing users to log in during a restore
• Direct modification of metadata tables
• Having Forced Writes off on Windows

See

• How to corrupt a database
• Interbase and Firebird database recovery guide
• Interbase and Firebird errors and their reasons

If you are missing a tool like phpMyAdmin when coming from MySQL, you should try ibWebAdmin.

• Screenshots
• Download
• Documentation

Firebird Weekly News: FireBird is the most used open source database for Enterprise applications

It seems that Firebird is used much more often than is disclosed publicly...

Imports and exports CSV, INSERTs and a proprietary format. It comes with a GUI and a command line - that comes handy when executing batch jobs (that is not possible with the popular IBExpert).

http://fbexport.sourceforge.net/

It seems that Firebird can also run really big databases: http://sourceforge.net/mailarchive/message.php?msg_id=5153028.

See this (a bit older, I know) report of a large Firebird deployment project.

• 24h operation on 6 or 7 days a week
• operation on a fail safe cluster system
• about 25 millions of blobs per year
• about 50 millions of records per year
• 100% logging and archiving of all data

Firebird doesn't support fulltext search. You need to rely on third party tools. That seems odd, but it doesn't have to be so bad as it looks at first.

I am using DotLucene. It is an open source .NET library (ported from Java) that can index any data (structured or unstructed) that you are able to convert to raw text.

On a server, it is no problem to store the index in a separate directory (you can also load it to RAM to make your searches super fast - if you have enough RAM, of course). In a desktop application, it might be useful to store the index in a Firebird database.

For example: MySQL fulltext search has these drawbacks (compared to DotLucene):

• You can use it only in MyISAM tables (i.e. no transactions)
• You can't browse the index (see Luke)
• You need to store transformed text in the DB (i.e. HTML without tags)
• It doesn't support highlighting of the query words in the result
• You will hardly modify the sources to do custom changes
• The license doesn't allow to use it in commercial application for free
• It is reported to be slow on large data sets

Incomplete and biased comparison of Firebird 1.5 and Microsoft Jet 4.0:

Feature	Firebird 1.5	Microsoft Jet 4.0
Deployment and Administration
Single database file
Embeddable	Details...
XCOPY data deployment	Details...
Hot backup (24x7 availability is possible)
Can run on Windows 98
Scalability
Database file size limit	Unlimited*	2 GB
Multiple concurrent users		It doesn't scale
Engine Features
Transactions
Views
Autoincrement fields
Stored procedures and triggers
BLOB fields
Unicode support
National character sets support (including sorting)
ADO.NET Provider		(OLEDB)
Licensing
Open-source license	Details...
License allows use in commercial applications	Details...

* Database file size limitation: Firebird is limited only by file system (e.g. 16+ TB on NTFS).

Related

• Embedded Firebird and Microsoft SQL Server 2005 Express Feature Comparison
• Embedded Firebird and Microsoft MSDE 2000 Feature Comparison

The maximum you can specify:

Hashtable parameters = new Hashtable();
parameters.Add("User", "SYSDBA");
parameters.Add("Password", "masterkey");
parameters.Add("Database", @"c:\database.fdb");
parameters.Add("Dialect", 3);
parameters.Add("DataSource", "localhost");
parameters.Add("Port", "3050");
parameters.Add("Charset", "NONE");
parameters.Add("PageSize", "8192");
parameters.Add("ForcedWrite", true);
FbConnection.CreateDatabase(parameters);

The miminum you must specify:

Hashtable parameters = new Hashtable();
parameters.Add("User", "SYSDBA");
parameters.Add("Password", "masterkey");
parameters.Add("Database", @"c:\database.fdb");
FbConnection.CreateDatabase(parameters);

See also:

• Batch SQL/DDL execution

You can fill an untyped DataSet using the following code:

DataSet ds = new DataSet();
FbDataAdapter da = new FbDataAdapter("SELECTINGSTOREDPROCEDURE", connectionString);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@firstparam", "firstparamvalue");
da.Fill(ds, "myTableNameInDataset");

This code is using a default transaction, i.e. new transaction is started just for this stored procedure call and is committed immediately after. If you are using a FbDataAdapter to load the data there is no need to open and close the connection.

To call a stored procedure (that doesn't have return values) using Firebird ADO.NET Provider you can use the following code:

FbCommand cmd = new FbCommand("STOREDPROCEDURENAME", new FbConnection(connectionString));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@first", "paramvalue");
cmd.Parameters.Add("@second", "anotherparamvalue");
cmd.Connection.Open();
try 
{
	cmd.ExecuteNonQuery();
}
finally 
{
	cmd.Connection.Close();
}

This code is using a default transaction, i.e. new transaction is started just for this stored procedure call and is committed immediately after.

Why you should do that:

• stored procedures support
• views support
• transactions (these are also supported in InnoDB tables in MySQL)
• friendly open source license that allows commercial use and embedding for free
• embeddable (with a small runtime)
• hot backup

How to:

1) Autoincrement fields

There are no autoincrement fields in Firebird. You need to use a generator. It is a server variable that stores the last number used. You need to call it when inserting a new row:

• in an inserting stored procedure
• in a trigger

Given that we have a table

CREATE TABLE mytable (
  id INTEGER,
  mytext VARCHAR(20)
);

the generator would look like this:

CREATE GENERATOR GEN_MYTABLE_ID;

the trigger would look like this:

CREATE TRIGGER MYTABLE_BI FOR MYTABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
  NEW.ID = GEN_ID(GEN_MYTABLE_ID,1);
END

and the inserting stored procedure like this:

SET TERM ^ ;

CREATE PROCEDURE SP_MYTABLEINSERT (
  MYTEXT VARCHAR(20))
AS
DECLARE VARIABLE ID INTEGER;
BEGIN
  ID = GEN_ID(GEN_MYTABLE_ID,1);
  INSERT INTO MYTABLE (ID, MYTEXT)  VALUES (:ID, :MYTEXT);
END^

SET TERM ; ^

2) NOW() MySQL:

SELECT * FROM mytable WHERE mydate = NOW();

Firebird:

SELECT * FROM mytable WHERE mydate = CURRENT_TIMESTAMP;

There are three special variables for current date and time:

• CURRENT_TIMESTAMP (date and time, TIMESTAMP type)
• CURRENT_DATE (date, DATE type)
• CURRENT_TIME (time, TIME type)

3) LIMIT x, y (return first y rows starting at offset x)

In Firebird it looks like this :

SELECT FIRST y SKIP x * FROM mytable;

LIMIT x (take first 10 rows) looks like this:

SELECT FIRST x * FROM mytable;

See also http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;IBPHOENIX.FAQS;NAME=.

I have created an add-on for DotLucene search engine that allows storing the Lucene index in a Firebird database. You can download the source code here. It is licensed under Apache Software License 2.0 so you can use it freely in your applications. You are only required to put an acknowledgement there (see NOTICE file). It should work with DotLucene 1.3 (tested) and 1.4 (it uses the same interfaces). But remember that the index file format has changed in 1.4.

Performance tips:

1. Use FSDirectory instead.... No, really, the performance goes down if you store the index in the database (my results are that database is twice slower that filesystem). Use the database storage only when you have no other choice.
2. Use compound index format (writer.SetUseCompoundFile(true);). This is default in DotLucene 1.4 but in 1.3 you have to do it manually.
3. Create the index in memory, optimize, then save it on disk using FbDirectory.Copy(); This will only help you if you are rebuilding the whole index from scratch.
4. If you are adding a document to the index from a desktop application, do it in background (in a separate thread). You are still able to search while you are adding a new document.

When I started using Firebird I was calling raw SQL from the code and only when there was no other choice I was calling a stored procedure. When there was a too complicated SELECT command I saved it as a view.

Now I am more and more relying on stored procedures. The Firebird ADO.NET provider makes this easier by providing a Microsoft-SQL-style stored procedure calling:

FbCommand cmd = new FbCommand("SP_INSERTDOCUMENT", new FbConnection(this.ConnectionString));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@guid", guid);
cmd.Parameters.Add("@parent", parent);
...

It makes the .NET code much easier to read. It has also other advantages:

• Now I have a clean database interface (I am trying to do every DB call through a SP).
• I can easily do later optimizations inside a selecting SP. This was not possible with raw SELECTs or views.

I also had some problems with this approach:

• When changing a table field type I need to change the variables in all related stored procedures (this already caused me some headaches - after making the VARCHAR field bigger the SP still wasn't working properly because I forgot to change it there).
• Stored procedures can't use the type domains.

One of the cool Firebird features is that the database can be deployed just by copying the database file (although you can have a database in multiple files, by default it is in a single one). This works not only with the embedded Firebird, but also with the server.

The convention is that the database files have .fdb extenstion but you can use any filename you want. You are probably not going to change that but it's nice to have the possibility.

Cut and paste date functions. See Ivan Prenosil's site.

Day of week (American format: week starting on Sunday, Sunday is 0):

EXTRACT(WEEKDAY FROM D)

Day of week (ISO 8601 format: week starting on Monday, Monday is 1):

EXTRACT(WEEKDAY FROM D-1)+1

First day of a month:

D - EXTRACT(DAY FROM D) + 1;

Last day of a month:

D - EXTRACT(DAY FROM D) + 33 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32)

Number of days in a month:

EXTRACT(DAY FROM (D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32))

Week of a year (ISO 8601) stored procedure:

CREATE PROCEDURE YearWeek (D DATE)
  RETURNS (WEEK_NO VARCHAR(8)) AS
  DECLARE VARIABLE W INTEGER; /* week number */
  DECLARE VARIABLE Y INTEGER; /* year the week belongs to */
  BEGIN
    W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
    Y = EXTRACT(YEAR FROM D);

    IF (W=0) THEN BEGIN
      Y = Y - 1;
      D = D - EXTRACT(YEARDAY FROM D) - 1; /* last day of previous year; D is used as temporary variable here */
      W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
    END
    ELSE
      IF (W=53 AND 4>EXTRACT(WEEKDAY FROM (D - EXTRACT(DAY FROM D) + 31))) THEN BEGIN
        Y = Y + 1;
        W = 1;
      END

    /* This is just formatting; you may prefer to make W and Y return parameters instead. */
    IF (W<10) THEN WEEK_NO = '0'; ELSE WEEK_NO = ''; 
    WEEK_NO = Y  '/'  WEEK_NO  W;
    SUSPEND;
END

Is leap year stored procedure:

CREATE PROCEDURE Is_LeapYear (D DATE) RETURNS (LY INTEGER) AS
BEGIN
  IF ( 2 = EXTRACT(MONTH FROM (D - EXTRACT(YEARDAY FROM D) + 59)) ) THEN
    LY = 1;  /* leap year */
  ELSE
    LY = 0;  /* normal year */
END

When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where

• the integral fraction is the number of days
• the decimal fraction is the part of a day

For example:

• Two days: 2.0
• One hour: 1.0/24.0
• One minute: 1.0/1440.0
• One second: 1.0/86400.0

Extracting the smaller units from a TIMESTAMP value:

• Number of seconds: VALUE*86400.0
• Number of minutes: VALUE*1440.0
• Number of hours: VALUE*24.0

Don't forget the decimal point in the numbers (e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.

The license for the Firebird itself and the ADO.NET provider is very flexible: Initial Developer's PUBLIC LICENSE Version 1.0 and INTERBASE PUBLIC LICENSE Version 1.0. Both are modified versions of Mozilla Public License v.1.1. That means you can use it freely in your commercial applications:

You can include the binaries in your commercial product but you have to tell your users where they can get the sources (of Firebird/provider). Any changes you make to Firebird's source code must be made available in source code under the same license. Your proprietary code can remain proprietary and closed.

Consult your counsel for more details or read the license in detail.

Firebird has a fully featured managed ADO.NET provider with rich set of features:

• Connection pooling
• MSSQL-style named parameters ("@parameter") as well as ODBC-style parameters ("?")
• Implicit transactions
• CommandBuilder
• VS.NET visual design support
• StoredProcedure CommandType support
• Transparet text BLOB support
• Metadata retrieval support
• Database service API support (backup, restore, statistics, configuration)

You can download the ADO.NET provider here.

Embedded Firebird has an incredibly small runtime. At minimum it requires two files:

• fbembed.dll (Embedded Firebird) - 1.44 MB
• FirebirdSql.Data.Firebird.dll (ADO.NET Provider) - 380 kB

Embedded Firebird features:

• Supports multiple concurrent connections
• Locks the database file (it can't be open by other application at the same time)
• No TCP/IP port exposed

To start using the embedded Firebird you need to:

• Download the latest Embedded Firebird Server for Windows from sourceforge.
• Download the latest Firebird ADO.NET Provider from sourceforge.
• Add a reference to FirebirdSql.Data.Firebird.dll to your VS.NET project.
• Copy fbembed.dll to your application's directory.
• Use a connection string with a ServerType property set to 1, e.g. "ServerType=1;" + "User=SYSDBA; " + " Password=masterkey;" + "Dialect=3;" + "Database=c:\\data\\employees.fdb". You still need to specify the User and Password although the security doesn't require that. The Database path can be relative.
• The database file doesn't need to have the .fdb extension, you can rename it as you want.

Later posts and documentation entries:

• How to Use Embedded Firebird in ASP.NET Applications
• Embedded Firebird in .NET FAQ
• Embedded Firebird Documentation

Related

• Fulltext search in Firebird
• Creating a database programmatically
• XCOPY data deployment
• Firebird is open source

Firebird SQL provides full set of DB features:

• Full SQL 92 Entry Level 1 support with most of the SQL-99 standard support
• All operations are executed in transactions
• Transaction savepoints (nested transactions) are supported
• Stored procedures are supported
• Views are supported
• Triggers for insert/update/delete operations are supported (can be executed before or after the command)
• Field type domains are supported
• BLOB fields (text and binary) are supported
• UNICODE as well as many national character sets are supported
• Autoincrement fields are supported through generators

Firebird SQL is a powerful open source database that can be easily used in .NET. I believe it has a big potential (it is full-featured, requires zero administration, can be embedded, etc.). However this potential has not been fully discovered by .NET developers who rely on Microsoft SQL Server heavily. I am working with Firebird in .NET and this site is a side effect - I am going to note my experience here so it can be reused by the others. The more users Firebird has the easier my work will be so I have started this site for my own good ;-).