Click here to Skip to main content
13,596,176 members
Click here to Skip to main content
Add your own
alternative version


38 bookmarked
Posted 4 Feb 2005

MS SQL Server to FireBird migration

, 4 Feb 2005
Rate this:
Please Sign up or sign in to vote.
A real example of database migration (MS SQL -> FireBird).


Recently I read an article Embedded Firebird at CodeProject and decided to try FireBird. I like the idea of embedded database. FireBird allows .NET developers to put a DLL into bin directory, create database file and use usual database access. It sounds very attractive and simple in theory. So I decided to migrate from SQL Server to FireBird.

Database Creation and Tools

Embedded version of FireBird is very light, just 1.5 Mb. FireBird Data Provider for .NET is also not so big - 0.7 Mb. I downloaded all these stuffs and put required DLLs into bin directory. Then I started database creation. However, FireBird Embedded distributive does not include any tool for database administration. Well, I downloaded standard distributive (4 Mb) and installed full featured SuperServer on my workstation. I dug into Program Files in the hope of finding a DB administration tool and found one. It was an ISQL console tool. "OK, no GUI" - I thought. - "This database is for real geeks". I opened QuickStart guide, found a section about database creation and tried to create one. But, as you might guess, without success. There was strange behavior, the ISQL refused to believe me that I was really creating new database and told that database is unavailable.

Fighting with console for awhile, I decided to find a GUI tool. There were several free tools at IBPhoenix web site. First I downloaded IBSQL (1Mb). It ran, but, when I tried to create new database, showed an error "gds32.dll was not found". I hunted around and found out that original DLL could be renamed and put into the System32 directory. I started IBSQL and it worked! A new database was created!

Do you think the migration is close to completion now? Not at all. Schema importing became a really tough action. At the very beginning, I discovered that FireBird does not support IDENTITY keyword. That's not a problem, I removed it. Then things became weird. IBSQL tool did not execute query popping up the error in the line with second's table CREATE statement. I tried to correct statements and understood that the tool was unable to run several statements! There were about 40 statements to execute, and I was not very enthusiastic about doing that one by one. "Fine, let's find something more user friendly". I found FeniSQL - very light (0.5Mb) tool. The same bug! I was slightly tired of that. I found several advices in newsgroups about IBOConsole tool and got them (2Mb). Yes, this tool is really great, has appealing GUI and useful features. And has no silly restrictions on statements' execution.

SQL compatibility

However, SQL was still wrong for FireBird. I used columns named "password", "value" and "time". These are reserved keywords. MS SQL Server eats them, but not FireBird. As you understand, renaming several columns is an issue, but I want to set up database at least. So I renamed fields in schema and ran SQL script again. The database schema was created successfully.

Do you think the migration is over? I thought so in that moment, but FireBird converted all tables and columns names in upper case. And the application refused to query database correctly. Ok. Google newsgroups are helpful and I found out that names should be quoted:

CREATE TABLE "releases"
            "goal" VARCHAR(500),
            "finish_date" DATE,
            "start_date" DATE,
            "name" VARCHAR(150) NOT NULL,
            "project_plan_id" INTEGER NOT NULL,
            "release_id" INTEGER  NOT NULL,
             CONSTRAINT "pk_releases" PRIMARY KEY ( "release_id" )

I changed SQL and… Yes! I got it! I run the application and all screens seem to be working. Then I tried to add a new Project and… Yes, I got an error (I was not surprised in fact). FireBird does not support autoincremental columns. There is a workaround using Triggers and Generators. The main idea is to create generator and invoke it before insertion to increment primary key. Something like that:

CREATE GENERATOR "project_id_gen";
   NEW."project_id" = GEN_ID ("project_id_gen", 1);

As I understand, this had to work properly. But it wasn't.


I spent four hours, downloaded six distributives, several times queried Google and almost resolved all problems (the keyword is almost). Well, maybe I am not a DBA guru, but have a good experience with several major databases and never encountered such difficulties with them. FireBird's behavior is illogical for me in some cases, but I am going to complete migration today. No, I am not a stubborn donkey, just because of the embedding. This way is agile and should be simple, but it is hard. That's sad.

Today I played with GENERATORS again. And (finally!) I found just one post about ID_GENERATOR problem. The solution is to create table id_generator with two fields: id (int), next (int). I created ID_GENERATOR table, changed trigger to:

NEW."project_id" = GEN_ID (ID_GENERATOR, 1);

And new Project action has been fixed. FireBird is very tricky you know...

About the author

Michael Dubakov is a leader of TargetProcess project - XP Planning and Bug Tracking Software.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Belarus Belarus
Lead of TargetProcess Project (
TargetProcess is an integrated Project Management and Bug Tracking software

You may also be interested in...


Comments and Discussions

GeneralSee also Pin
Dan Letecky4-Feb-05 11:37
memberDan Letecky4-Feb-05 11:37 
General4 Hours for a database migrations... Pin
Colin Angus Mackay4-Feb-05 8:42
memberColin Angus Mackay4-Feb-05 8:42 
GeneralRe: 4 Hours for a database migrations... Pin
firefalcon4-Feb-05 10:22
memberfirefalcon4-Feb-05 10:22 
We are using NEO O/R mapping tool. So migration is not a problem at all. The main problem was to tune FireBird Smile | :)

XP Planning & Bug Tracking Software
GeneralSend an email to Pin
Anonymous4-Feb-05 8:00
sussAnonymous4-Feb-05 8:00 
GeneralFirst -Read the manual Pin
Anonymous4-Feb-05 7:36
sussAnonymous4-Feb-05 7:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04-2016 | 2.8.180621.3 | Last Updated 4 Feb 2005
Article Copyright 2005 by firefalcon
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid