Click here to Skip to main content
Click here to Skip to main content

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).

Introduction

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";
 
CREATE TRIGGER SET_ID FOR "projects";
BEFORE INSERT
AS BEGIN
   NEW."project_id" = GEN_ID ("project_id_gen", 1);
END

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

Conclusion

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.

License

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

firefalcon

Belarus Belarus
Lead of TargetProcess Project (www.targetprocess.com).
TargetProcess is an integrated Project Management and Bug Tracking software

Comments and Discussions

 
GeneralThanks for sharing... Pinmemberzmariow13-Dec-07 10:57 
QuestionHow to connect from asp.net to a firebird db on a different machine PinmemberSumith P D11-Aug-05 1:55 
GeneralRight and Wrong.... PinmemberRicalawaba11-May-05 10:40 
GeneralI can't belive to what you wrote! Pinmemberretekmaster2-Apr-05 1:24 
You busy us because you spent 4 hour for migrate to another DBMS? Are you an ET? Haw much are 4 hour!! Oh my god i can't belive it..
 
Pour me..
GeneralQuoting Pinmember61733651213-Feb-05 21:50 
GeneralRe: Quoting PinmemberariesT16-Feb-05 12:04 
GeneralSounds just like Oracle PinmemberGX9-Feb-05 2:44 
GeneralGenerators PinsussAnonymous7-Feb-05 13:50 
GeneralPain PinsussAnonymous7-Feb-05 4:49 
GeneralMS-SQL to Firebird Migration Guide PinmemberPaul Vinkenoog7-Feb-05 0:59 
GeneralRe: MS-SQL to Firebird Migration Guide PinmemberPaul Vinkenoog7-Feb-05 1:04 
QuestionBug? PinsussAnonymous7-Feb-05 0:31 
GeneralQuoted & conversion tip :-) PinsussMartijn Tonies7-Feb-05 0:14 
GeneralMS SQL's fault - not FireBird's ! PinmemberMarc Scheuner6-Feb-05 1:11 
GeneralRe: MS SQL's fault - not FireBird's ! Pinmemberfirefalcon6-Feb-05 22:57 
GeneralRe: MS SQL's fault - not FireBird's ! Pinmemberchriswa22-Mar-05 8:42 
GeneralHere is your solution:) PinmemberUnruled Boy5-Feb-05 20:11 
Generalother alternative: advantage database server Pinmemberchristoph braendle5-Feb-05 3:57 
GeneralRe: other alternative: advantage database server PinmemberFZelle5-Feb-05 6:45 
GeneralRe: other alternative: advantage database server Pinmemberchristoph braendle6-Feb-05 2:26 
Generalsql2gdb tool Pinmembergof4-Feb-05 22:57 
GeneralOne Comment PinsussAnonymous4-Feb-05 19:24 
GeneralAmen. PinmemberPeter M. Lemmen4-Feb-05 12:01 
GeneralSee also PinmemberDan Letecky4-Feb-05 11:37 
General4 Hours for a database migrations... PinmemberColin Angus Mackay4-Feb-05 8:42 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 4 Feb 2005
Article Copyright 2005 by firefalcon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid