Once in a while I write desktop database applications. Last time it was about 5 years ago, and it was BDE application. Now BDE is obsolete and I was forced to choose something else for my new database project. After thinking a little I have chosen Firebird as a database server and FibPlus as a programmer’s library to access the database. I was absolutely new to Firebird and FibPlus and I had a lot of questions for the first time. Now I am feeling quite comfortable with these technologies (though I can’t say I am a Firebird professional) so I decided to write a series of articles about creating native database applications in Delphi for Windows using Firebird and FibPlus for the absolute beginner.
The first thing one should do is to download and install the latest stable Firebird version from http://www.firebirdsql.org . For my case it was Firebird 2.1.3. The installation process is easy and straightforward. The two things you should take care of are:
• Do not install Firebird in “Program Files” directory to avoid access rights problems on Vista and above Windows systems. I have chosen “C:\Firebird\Firebird_2_1” directory
• Make notice are you installing Firebird server+client or just a client alone. For the latter case uncheck the “Server components” checkbox. For the purpose of this post we need both server and client on a developer system.
Now we are ready to create our first Firebird database. For the illustration purposes I have created a sample “Electronic Library” database with two tables – “BOOK” and “AUTHOR” linked by “many-to-many” relation. Here is the SQL script (ellib.sql) to create the database:
SET SQL DIALECT 3; SET NAMES UTF8; CREATE DATABASE 'localhost:D:\ElLib\ElLib.fdb' user 'SYSDBA' password 'masterkey' DEFAULT CHARACTER SET UTF8; CREATE TABLE AUTHOR ( AUTHOR_ID INTEGER NOT NULL, AUTHOR_NAME VARCHAR(40), CONSTRAINT PK_AUTHOR PRIMARY KEY (AUTHOR_ID) ); CREATE TABLE BOOK ( BOOK_ID INTEGER NOT NULL, BOOK_NAME VARCHAR(40), CONSTRAINT PK_BOOK PRIMARY KEY (BOOK_ID) ); CREATE TABLE LINK ( LINK_AUTHOR INTEGER NOT NULL, LINK_BOOK INTEGER NOT NULL, CONSTRAINT PK_LINK PRIMARY KEY (LINK_AUTHOR, LINK_BOOK) ); ALTER TABLE LINK ADD CONSTRAINT FK_LINK_AUTHOR FOREIGN KEY(LINK_AUTHOR) REFERENCES AUTHOR(AUTHOR_ID); ALTER TABLE LINK ADD CONSTRAINT FK_LINK_BOOK FOREIGN KEY(LINK_BOOK) REFERENCES BOOK(BOOK_ID); CREATE GENERATOR GEN_AUTHOR_ID; CREATE GENERATOR GEN_BOOK_ID; SET TERM ^ ; CREATE TRIGGER BI_AUTHOR FOR AUTHOR ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.AUTHOR_ID IS NULL) THEN NEW.AUTHOR_ID = GEN_ID(GEN_AUTHOR_ID, 1); END^ CREATE TRIGGER BI_BOOK FOR BOOK ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.BOOK_ID IS NULL) THEN NEW.BOOK_ID = GEN_ID(GEN_BOOK_ID, 1); END^ SET TERM ; ^ COMMIT;
The script is obvious for everybody who ever created Firebird or Interbase databases. I have chosen UTF8 character encoding since unicode is a worldwide standard nowadays. Both “AUTHOR” and “BOOK” tables have autoincrement primary keys (AUTHOR_ID and BOOK_ID). The autoincrement fields in Firebird are implemented using generator and trigger database objects. The trigger code allows a client application to fill the autoincrement field or leave it empty so that it would be filled on server. The LINK table implements “many-to-many” relation between authors and books.
Now we must execute the script. There are several third-party GUI tools like IBAccess for the purpose. They are useful for debugging the SQL scripts, but in our case the console Firebird ISQL Tool utility is sufficient. The utility can be found in \bin subdirectory of the Firebird installation directory (“C:\Firebird\Firebird_2_1\bin\isql.exe”):
Every command in ISQL ends with semicolon “;”. A good command for a novice is “help”:
We see that we need “in” command to execute a SQL script:
The absence of any ISQL messages after the command means that the execution was successful and we are ready to work with our newly created database (d:\ellib\ellib.fdb).
The alternative way to execute SQL script with ISQL is to use -i command line parameter. That is useful for executing the scripts from command (.bat) files. I have written a demo ellib.bat file to create the ellib.fdb database:
@echo off cls echo Creating ellib.fdb echo ------------------ echo. set ISQLEXE=C:\Firebird\Firebird_2_1\bin\ISQL.exe set ISQLSCRIPT=ellib.SQL if exist %ISQLEXE% goto findSQL echo %ISQLEXE% not found. goto abort :findSQL if exist %ISQLSCRIPT% goto execSQL echo %ISQLSCRIPT% not found. goto abort :execSQL %ISQLEXE% -i %ISQLSCRIPT% goto fin :abort echo _ echo Execution failed :fin set ISQLEXE= set ISQLSCRIPT= echo. pause
The result of successful execution of the command file is shown below: