| 7.4. |
Firebird database server. |
| 7.4.0. |
Firebird distributions. |
| |
|
"Firebird is an open source, free database server that has been in use and developed for decades. It developed out of the Interbase 6 database that was open
sourced by Borland. The database requires very little manual DBA work once it is set up, making it ideal for small business use or embedded use. It can grow
to terabyte scale given proper tuning, although PostgreSQL may be a better choice for such large environments." This is how Firebird is described on the
Firebird page of the Free Pascal wiki.
|
|
Firebird offers both embedded (file-based, as for example SQLite) and client-server (as MySQL, MariaDB, PostgreSQL...) databases. This tutorial is about
client-server (TCP/IP based) databases only. It describes the installation of Firebird 4.0.2 (64bit) on
Windows 10. Installation on other Windows releases should be similar. Concerning older versions of Firebird, the content of this tutorial might not always
apply.
|
|
Firebird may be downloaded free of charge from the Firebird Download
page. I myself have downloaded the Windows 64bit installer, filename: Firebird-4.0.2.2816-0-x64.exe.
|
|
| 7.4.1. |
Installing Firebird. |
| |
|
Double-click the installer to launch the setup wizard. After the choice of the language, the license agreement, the display of the installation instructions
and the choice of the installation folder (default for Firebird 4.0: C:\Program Files\Firebird\Firebird_4_0), you come to the
Components selection window (screenshot on the left). In our case, the default option Full installation of Server
and development tools is all ok. The next window allows you to configure Additional tasks (screenshots on the right), in
particular to choose between 3 different server architectures. For "normal" use, the default option SuperServer mode should be
selected. It's possible to run Firebird as an application, but you should let the default option to run it as a service. Starting it with Windows or not depends
on your personal preferences. Copy Firebird client library to <system> directory should be checked (this will, for example, allow to
develop a Lazarus application that accesses a Firebird database without having to copy the client into the project output folder). Legacy Interbase support
is normally not needed (let the checkbox unselected).
|
|
|
|
In the next window, you are asked to set the password of the Firebird super-user, who is actually called SYSDBA, then you pass to
the Ready to install window, where a summary of your installation choices is displayed. Push the Install
button to do the installation. After a window showing further information about the installed version of Firebird, you come to the last screen, where you
can choose to now start or not the server. Push the Finish button to terminate the setup wizard.
|
|
You can start and stop the Firebird server in Windows Services. Here you can also change the server's properties, in particular if
you want to start it automatically with Windows or manually.
|
|
|
|
| 7.4.2. |
ISQL and third-party GUI clients. |
| |
|
Firebird ships with the command line client ISQL (Interactive SQL utility). You can use it to enter SQL statements concerning
administration (e.g. creating users), data definition (e.g. creating a database or its tables), data manipulation (e.g. selecting or inserting some records),
and other. The connection is done either by the command CONNECT (for an existing database), or the command
CREATE DATABASE. This is a particularity of Firebird (compared to MySQL and other RDBMS): The client connects to a given database
rather than to the server. With either of these two commands, a user name and a password have to be
specified. It is also possible to specify a given role (cf. section 9.4).
|
The typical command to connect with ISQL to a database (here with the super-user to the "employee" database):
CONNECT employee USER SYSDBA PASSWORD password;
|
|
This is in fact a simplification, as with the "complete" command you also specify the way how you want the client to connect (cf. the Firebird documentation for
details).
|
|
Please, note that the commands in ISQL are multiple line statements. Hitting ENTER does not automatically execute the statement, but
does a line feed and in the new line (having the prompt CON> instead of SQL> that indicates the first line of the statement) where you can continue the statement
text. To terminate a statement, use a semicolon (;). If now you hit ENTER, the statement is executed. To exit the ISQL utility, use
QUIT; or EXIT;. The difference is that with EXIT all open transactions are committed before the program
terminates.
|
|
The screenshot below shows the execution of some SQL statements in ISQL.
|
|
|
|
Note: In ISQL, transactions are normally not automatically committed. So after, for example,
executing an UPDATE statement, you have to execute the statement COMMIT; to physically change the database content.
|
|
There is no GUI client available from the Firebird developers themselves, but there are several third party desktop and web applications. I did not take the time
to try to evaluate them, but more or less randomly selected one of them to install, actually FlySpeed SQL Query. "With FlySpeed
SQL Query you can browse and edit data in grid form or via the customizable form view, quickly find and filter data, and set up master-detail views. The
visual query designer lets you create new SQL queries by drag'n'drop, and represent off-the-shelf queries visually. It makes SQL query analysis, creation,
and modification much simpler. Using FlySpeed SQL Query, you can build parameterized queries, and browse data from linked tables via foreign keys.", they
describe the application on their website. You can download FlySpeed SQL Query for free from the
ActiveDBsoft website).
|
|
The first time that you launch the application, you are asked for the connection parameters. In the Selecting the connection type
window (screenshot on the left), select Direct database server connection (other possibilities are OLE DB and ODBC) and choose
Firebird as connection type (FlySpeed SQL Query may be used with most actual databases). In the Connection
properties window (screenshot on the right), select the TCP/IP protocol, set the host to localhost
and the port to port 3050 (Firebird default port). Enter SYSDBA as user name and also enter their password. Trying out the connection
with these settings will fail with the message I/O Error during "CreateFile (open)". The reason for this is that it's
mandatory to specify a database; "employee" in our case.
|
|
|
|
In the last window of the New connection wizard, you can specify a name for the connection; for example: "Firebird (employees)",
and choose if you want to open this connection automatically when the application starts. In the application start-up window, you can create a new connection
and edit an existing one using the items of the Connection menu. You can choose a given connection from the drop-down list, and then
clicking the Connect link to connect. If the password has not been stored with the other connection properties, you'll have to
enter it now.
|
|
|
|
Double-clicking a table in the left pane opens it in Query Builder. A visual tool to build queries, they say ... I didn't try out
this feature, but I entered all my SQL manually, by choosing Query > New text query from the menu.
|
|
|
|
Clicking the Execute button executes the query, the default being a SELECT * FROM <table>. You can
work on the displayed dataset a little bit in the same way as in MySQL Workbench or similar applications.
|
|
|
|
| 7.4.3. |
Creating Firebird users. |
| |
|
User management of a Firebird database isn't so simple as it is with MySQL, where you can give a user any global, database or table related privileges
by doing the corresponding selections in the administration part of a graphical interface such as MySQL Workbench or phpMyAdmin. With Firebird, you'll
have to create the users and define their privileges by entering the corresponding SQL statements. This requires that you understand the basics of Firebird
security. Here a (largely simplified) introduction to Firebird security; for details, cf. the Firebird Language Reference, that
may be downloaded in PDF format from the Firebird website.
|
|
As we have seen, the Firebird super-user is called SYSDBA. This user has full privileges on all databases, including those where
security related data is stored. You can say that SYSDBA can "do what they want", what is practical, of course, but on the other hand is dangerous because, if
you don't pay attention, you can easily corrupt or destroy entire databases.
|
|
Regular users must be created using the CREATE USER ... statement. Once created, a user can connect to a
database by specifying their password. However, this is all they can do. What I mean is that at this stage the user can't access any object in the database.
To access an object in the database, the user must have privileges for this object. These privileges may be to only read data
(SELECT), to change data (INSERT, UPDATE, DELETE), to create, change and drop database objects (CREATE DATABASE, CREATE TABLE, ALTER ...), and others.
You give privileges to a user using the GRANT ... TO USER ... statement.
|
So, basically a standard user has only those privileges that they have been granted. In other words: A newly created user has no privileges until they have been
granted to them. However, it's a little bit more complicated:
- The database owner (user, who created the database) has automatically full administrative privileges
on that database.
- All users are automatically granted the privileges of a special user called PUBLIC. So, if user PUBLIC
has full access to a given database (what should, of course, never be the case in a real life situation), then creating a new user without specifying any grants,
will nevertheless give them full administrative privileges on that database.
|
Another important thing to understand is the concept of roles. A role is a set of privileges, a user has, when connecting to a
database. Here an example: Suppose that we had created a user called "user1" and a role called "access_employees" and that this role has been granted the
privileges SELECT, INSERT, UPDATE, DELETE on all tables of the "employee" database. Also suppose that we had granted the role "access_employees" to user
"nemo". In order to manipulate the records in any table of the "employee" database, we could connect in ISQL using the command:
CONNECT employee USER nemo PASSWORD password ROLE access_employees;
|
|
What do you think will happen if we connect without explicitly specifying the role? As "nemo" has been granted the "access_employees" role, would they still
be authorized to manipulate the data in the "employee" database? No! If they tried, Firebird would answer with a no permission error
message. This seems not logical, but it's just a question of correctly understanding the Firebird security concepts. Granting a role to s user means not less and
not more than that this user is authorized to connect with this role. It doesn't tell anything about the connection itself. Thus, if "nemo" has been granted
the "access_employees" role and connects with this role (for example by specifying ROLE access_employees in the
connect command), they may access the data. If no role is specified, "nemo" is not authorized to access the data, even if they
have been granted the "access_employees" role. Except, and that's a nice thing up from Firebird version 4, the "access_employees" role has been granted as
default role.
|
Summary:
|
A regular, authenticated user has no privileges on any database object until they are explicitly granted to that individual user, to a role granted
to the user as a default role, or to all users bundled as the user PUBLIC.
|
|
|
Now its time to create the users for our "employee" database (you can do it using ISQL or FlySpeed SQL Query). As I usually do with MySQL databases, I also
did with Firebird: Creating a read-only user "nemo", who can read (select) all data from all databases, and a
read-write user "allu", who can manipulate (select, insert, update, delete) all data from all databases.
|
The important thing to know is that user PUBLIC has been granted full access to the "employee" database, so any user created would automatically inherit these
grants. Thus, the first step is to revoke the privileges of user PUBLIC:
REVOKE ALL ON ALL FROM USER PUBLIC;
|
Second step: Lets create the two users:
CREATE USER nemo PASSWORD 'nemo';
CREATE USER allu PASSWORD 'Password';
|
Third step: Lets create two roles "ro_user" and "rw_user" and grant them the privileges to select resp. fully access all records
in the database. These privileges may be granted using two predefined system privileges:
CREATE ROLE ro_user
SET SYSTEM PRIVILEGES TO SELECT_ANY_OBJECT_IN_DATABASE;
CREATE ROLE rw_user
SET SYSTEM PRIVILEGES TO ACCESS_ANY_OBJECT_IN_DATABASE;
|
And forth and last step: Lets grant these roles as default roles to our users:
GRANT DEFAULT ro_user TO USER nemo;
GRANT DEFAULT rw_user TO USER allu;
|
|
The screenshot shows the connection of user "nemo", the successful selection of some data from the "customer" table, but a "no permission" error message
when they try to update a record in this table.
|
|
|
|
| 7.4.4. |
Accessing Firebird from PHP. |
| |
|
When writing this tutorial, I used Apache Lounge 2.4.46 and PHP 8.0.0; the tutorial may not apply
to older (or newer) versions of PHP. If you need more information or help with Apache or PHP, please have a look at
part 1 resp. part 2 of this tutorial.
|
Connecting to a Firebird database using PHP is really easy. The client DLL php_pdo_firebird.dll is included with the PHP
distribution and all you have to do is to enable the extension in php.ini. Just uncomment the following line:
extension=pdo_firebird
Be sure to restart Apache to make the changes take effect!
|
|
Here a simple script to test the connection. I called it firebird.php and placed it in the /php
directory of my webserver.
|
<html>
<head>
<title>PHP-Firebird test</title>
</head>
<body><p>
<?php
$dsn = 'firebird:dbname=localhost:employee'; $username = 'nemo'; $password = 'nemo';
try {
$dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
$sql = "SELECT count(*) FROM customer";
$query = $dbh->query($sql);
$count = 0;
if ($row = $query->fetch(\PDO::FETCH_OBJ)) {
$count = $row->COUNT;
}
echo "Number of customers in database 'employee' = $count";
$query->closeCursor();
}
catch (\PDOException $e) {
echo $e->getMessage();
}
?>
</p></body>
</html>
|
|
To run the script, type localhost/php/firebird.php in your webbrowser's address field. The screenshots below show the situation where
I forgot to restart Apache after having enabled the Firebird extension (on the left), a failure of the connection, because the Firebird server was offline (in
the middle), and a successful connection with return of the number of customers in the "employee" database (on the right).
|
|
|
|
| 7.4.5. |
Accessing Firebird from Perl. |
| |
|
There is a DBD driver available for Firebird, however I did not succeed to install it.
|
The screenshots show my trial to install the Firebird DBD driver from CPAN: On the left, you can see, how I searched for the driver and tried to install
one of the search results, using the commands:
i /firebird/
install DAM/DBD-Firebird-1.34.tar.gz
On the right, you see the last screen of the installation process: Failure during the make_test command!
|
|
|
|
Must the make_test fail because the embedded version of Firebird is not installed? Or, is it my fault because I omit something (DBI version, setting the
password to access the databases created during test)? I did not further investigate the problem. First, Firebird is just a try-out (for doing real things,
I use MySQL), and second, I succeeded to access Firebird from Perl using ODBC.
|
|
| 7.4.6. |
Accessing Firebird from Python. |
| |
There are several Python drivers for Firebird available. I actually use FDB, version 2.0.2 (have a look at the
FDB website for details). You can install it using PIP. In command prompt, enter:
python -m pip install fdb
|
|
|
|
Here an elementary Python script (I named it firebird.py and placed it in my /cgi-bin directory) to test
the connectivity to the "employee" database. Please, note that the script doesn't do any error handling. If the server is offline (and also in other error
conditions), a white page will be displayed in the web browser.
|
import fdb
print("Content-type: text/html")
print()
print("<html>")
print("<head><title>Python-Firebird test</title></head>")
print("<body>")
db = fdb.connect(
host='localhost', database='employee',
user='nemo', password='nemo'
)
cursor = db.cursor()
cursor.execute("SELECT count(*) FROM customer")
count = cursor.fetchone()[0]
print("<p>Number of customers in database 'employee' = ", count, "</p>")
print("</body>")
print("</html>")
db.close()
|
|
To run the script, type localhost/cgi-bin/firebird.py in your browser. The screenshot shows a successful connection with the display of
the number of customers.
|
|
|
|
| 7.4.7. |
Accessing Firebird from Lazarus/Free Pascal. |
| |
|
| 7.4.8. |
Accessing Firebird using ODBC. |
| |
|
An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database
management systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximum interoperability, i.e. a single application can access different
DBMS. Application end users have just to add ODBC database drivers to link the application to their choice of DBMS, for example a Firebird ODBC driver for
Firebird. Official ODBC documentation can be found at the MSDN ODBC site.
|
To connect to a Firebird database using ODBC, we need:
- An ODBC Driver Manager.
- A Firebird ODBC driver.
The driver manager is part of Microsoft Windows (cf. below), the driver has to be installed manually.
|
|
You can find links to download an ODBC driver for Firebird on the Firebird ODBC page.
I actually use Firebird ODBC 2.0.5.156 (64bit). Download the full installer and run it. In the Select components window, choose
Developer install.
|
|
|
|
MS Windows includes the ODBC Data Source Administrator, a built in ODBC Driver Manager, which allows DSNs to be set up, and other
configuration. You can find it in Control Panel > Administrative Tools. There are two ODBC Driver Managers available: one for 32bit
and one for 64bit data sources.
|
|
|
|
Open ODBC Data Sources (64bit). In the Drivers tab, you should see the Firebird driver that we've just
installed.
|
|
|
|
One of the ways, an ODBC driver manager provides to make shortcuts for a set of parameters, is a DSN (DataSource Name): a system
or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator (or by manually editing the
odbc.ini file or the registry). The parameters in a DSN can always be combined with additional parameters in the connection string (for example to specify
a password). By setting up a DSN, the connection details can be verified to work within the manager dialog, and then the named DSN is all, that is needed,
to use the connection later. The connection details are decoupled from the applications, as only the DSN name is used – the DSN
acting as a go-between.
|
|
To set up a user DSN for our Firebase database "employee" (as a difference with other RDBMS, you'll have to create a DSN for each database – at least as far
as I have understood), in ODBC Data Source Administrator, choose the User DSN tab and click the Add button.
Select the Firebase/Interbase(r) driver.
|
|
|
|
In the Firebird ODBC setup window, choose a name for the connection (DSN); I chose "Firebird_employee". Enter a description if
you like, enter the database name ("employee") and browse for the client DLL (fbclient.dll); you must use the DLL located in System32,
the one located in SysWOW64 does not work (?). To test the connection, enter an username and their password, then push the
Test connection button.
|
|
|
|
Back in the Firebird ODBC setup window, remove user name and password (we will provide these information within the application)
and push the OK button. The new connection Firebird_employee should now be listed with the other user DSN.
|
|
ODBC connection from Perl.
|
|
Connecting from Perl using ODBC requires the module DBI::ODBC. No need to install it; it is included with DBI. The connection from Perl is somewhat special,
as you have to indicate the driver name, not as expected the DNS. Here the code of a simple script (I called it
firebird.pl and placed it in my /cgi-bin directory) that reads the number of customers in the "employee"
database.
|
use strict; use warnings;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
my $driver='Firebird/InterBase(r) driver'; my $database='employee'; my $username = 'nemo'; my $passwd = 'nemo';
print header;
my $dbh = DBI->connect("dbi:ODBC:driver=$driver;Database=$database", $username, $passwd, { AutoCommit => 1, RaiseError => 1 })
or die "Failed to connect to database: $DBI::errstr";
my $sql = "SELECT count(*) FROM customer";
my ($count) = $dbh->selectrow_array($sql);
print "<html>";
print "<head><title>Perl-Firebird test</title></head>";
print "<body><p>Number of customers in database 'employee' = $count</p></body>";
print "</html>";
$dbh->disconnect();
|
|
To run the script, enter localhost/cgi-bin/firebird.pl in your web browser's address field. The screenshot on the left shows the
situation, where the Firebird server is offline. The screenshot on the right shows a successful query.
|
|
|
|
ODBC connection from Lazarus/Free Pascal.
|
|
You can use the sample application, described in my tutorial Using Firebird databases with
Lazarus/Free Pascal as a template to create the ODBC based application. All you'll have to do is to replace the TIBConnection object by a
TODBCConnection object. You probably would like to give this object another name as the one used with TIBConnection, the
corresponding statements in the methods TfFbSQL.btQueryClick and TfFbSQL.btExitClick having to be changed. Concerning the method TfFbSQL.btConnectClick,
here is the code of the ODBC version (dbODBCConnection being the name of my TODBCConnection object):
|
procedure TfFbSQL2.btConnectClick(Sender: TObject);
begin
if dbODBCConnection.Connected then
dbODBCConnection.Close;
dbODBCConnection.DatabaseName := 'Firebird_employee';
dbODBCConnection.UserName := 'nemo';
dbODBCConnection.Password := 'nemo';
dbODBCConnection.Params.Add('AUTOCOMMIT=1');
try
dbODBCConnection.Open;
edMess.Text := 'Connection to Firebird database "employee" = OK!';
except
on E: ESQLDatabaseError do
edMess.Text := E.Message;
end;
end;
|
|
The important thing here is that the value of <ODBCConnection-object>.DatabaseName has to be set to the name of the User DSN,
not the name of the database (as a difference with ODBC for MySQL and other RDBMS, the database name is not required here, as it has been set in the properties
of our Firebird User DSN).
|
|
The screenshots below show the situation after the "Connect" button has been pressed: On the left connection failure because the server is offline, on
the right successful connection.
|
|
|
|
