Delphi, Firebird, FibPlus. Part 1 – Introduction

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:

You should delete the previously created ellib.fdb file to see this screenshot. Otherwise you will see ISQL error message: