Using Firebird ISQL to create embedded database files

ISQL is a command line tool that enables the execution of SQL statements either interactively or by input script file.

I was struggling to get ISQL to work with my Firebird Embedded database files until I stumbled upon a little nugget of information in one of my many google searches.

The problem was that ISQL wasn't using the embedded client! Doh!

The fix was simple! To get ISQL working the way I needed it to all I had to do was:

• Copy isql.exe into the Firebird embedded directory.

• Rename fbembed.dll to fbclient.dll

Now I can create and connect to embedded database files using the ISQL command line tool.

isql.exe" -q -i myDatabase.sql

where the file myDatabase.sql contains:
CREATE DATABASE 'TEST.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET NONE;

CREATE ROLE RDB$ADMIN;

CREATE TABLE MYTABLE(
ID INTEGER NOT NULL,
NAME VARCHAR(500) NOT NULL
);

ALTER TABLE MYTABLE
ADD CONSTRAINT PK_MYTABLE_1
PRIMARY KEY (ID);

COMMIT;

Why did I want to do this?
My want was driven by a desire to have my build server create/modify database files for our product install. Treating the database creation scripts like code negates the need for a "Golden" database file that no one, other than myself, knows how to create.

Firebird embedded with .Net

Here's a simple getting started with Firebird embedded

Step one: Download FireBird embedded and the .Net provider

There are numerous options available for download but I opted for the zip packages as only the assemblies and supporting files are needed.
http://www.firebirdsql.org/en/server-packages/
http://www.firebirdsql.org/en/net-provider/
http://www.firebirdsql.org/manual/ufb-cs-embedded.html#ufb-cs-embedded-windows

Step two: Create your database file

There are many administrative tools available for FireBird but I opted to use FlameRobin as its free. http://www.flamerobin.org/

In order to get flame robin to communicate with an embedded FireBird database there are a couple of steps required.
Copy the following FireBird embedded client files to the FlameRobin installation folder (Default C:\Program Files\FlameRobin)
fbembed.dll (Rename to fbclient.dll)
firebird.msg
icudt30.dll
icuin30.dll
icuuc30.dll

Register Server

Display name
leave hostname and port empty
Create a new database







Step 3: Create your .Net project.
Add a reference to the .Net Provider.
FirebirdSql.Data.FirebirdClient.dll
The files icudt30.dll and icuuc30.dll need to be in the build directory. Add them to the project (I added as a link as all my third party components live in a single place) and set to copy to output build directory.

Step 4: Write the code.

using System;
using FirebirdSql.Data.FirebirdClient;

namespace FirebirdClient
{
class Program
{
static void Main(string[] args)
{
var builder = new FbConnectionStringBuilder
{
UserID = "SYSDBA",
Password = "masterkey",
ServerType = FbServerType.Embedded,
Dialect = 3,
Database = @"C:\MyDatabases\MyDB1.fb"
};

using (var conn = new FbConnection(builder.ConnectionString))
{
conn.Open();

using (var cmd = new FbCommand("select * from MyTable", conn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(string.Format("{0} {1}", reader["MyCol1"], reader["MyCol2"]));
}
}
}

Console.ReadLine();
}
}
}