6.1. Interfacing PHP and Firebird

To communicate with a Firebird database, you will need a driver.

6.1.1. PHP Drivers for Firebird

Two free, downloadable drivers are available for interfacing with Firebird:

• The Firebird/Interbase extension (ibase_ functions)

• The PDO driver for Firebird

Firebird Client Library

Both drivers require that you have the fbclient.dll client library installed (fbclient.so for POSIX systems). Make sure it is for the correct CPU register width (32-bit or 64-bit) to match that of your web server/PHP installation. For example, if you have a 64-bit machine running 64-bit Firebird and 32-bit Apache/PHP then you need the 32-bit driver.

Make sure you have the matching release version of the Firebird client for your Firebird server.

The Firebird/InterBase Extension

The Firebird/Interbase (“Fb/IB”) extension predates the PDO driver and is regarded as the more proven solution of the two.

To install the extension, uncomment this line in the php.ini configuration file:

extension=php_interbase.dll

or this line on Linux and other POSIX systems:

extension=php_interbase.so

Installing the Fb/IB Extension on Linux

In Linux, one of the following commands should work. The one you use depends on the distribution package and the versions it supports:

apt-get install php5-firebird
rpm -ihv php5-firebird
yum install php70w-interbase
zypper install php5-firebird

Programming Style

The Firebird/InterBase extension uses a procedural approach to developing programs. Functions with the ibase_ prefix can return or accept the identifier (ID) of a connection, transaction, prepared query or cursor (the result of the SELECT query) as one of their parameters. This identifier is a server-allocated resource which, like all allocated resources, should be released immediately it is no longer needed.

The PHP functions will not be described in detail here. You can study their descriptions at https://php.net/ibase. Several small examples with comments will be provided instead.

<?php
$db = 'localhost:example';
$username = 'SYSDBA';
$password = 'masterkey';
// Connect to database
$dbh = ibase_connect($db, $username, $password);
$sql = 'SELECT login, email FROM users';
// Execute query
$rc = ibase_query($dbh, $sql);
// Get the result row by row as object
while ($row = ibase_fetch_object($rc)) {
  echo $row->email, "\n";
}
// Release the handle associated with the result of the query
ibase_free_result($rc);
// Release the handle associated with the connection
ibase_close($dbh);

The ibase_ Connect Functions

The ibase_pconnect function, that creates so-called “persistent connections”, could be used instead of ibase_connect. A call to ibase_close on this style of connection does not close it but all resources allocated to it will be released. The default transaction is committed, while any others are rolled back. This type of connection can be re-used in another session if the connection parameters match.

Persistent connections can increase the performance of a web application, sometimes considerably. It is especially noticeable if establishing a connection involves a lot of traffic. They allow a child process to use the same connection throughout its entire lifetime instead of creating a connection every time a page interacts with the Firebird server. Persistent connections are not unlike working with a connection pool.

You can find more details about persistent connections at https://php.net/persistent-connections.

ibase_query

The ibase_query function executes an SQL query and returns the identifier of the result or True if the query returns no data set. Along with the connection or transaction ID and the text of the SQL query, this function can accept a variable number of parameters to populate the SQL query parameters. For example,

// …
$sql = 'SELECT login, email FROM users WHERE id=?';
$id = 1;
// Execute query
$rc = ibase_query($dbh, $sql, $id);
// Get the result row by row as object
if ($row = ibase_fetch_object($rc)) {
  echo $row->email, "\n";
}
// Release the handle associated with the result of the query
ibase_free_result($rc);
// …

Parameterized queries are typically used multiple times with fresh sets of parameter values each time. Prepared queries are recommended for this style of usage. The identifier of a query is returned by the function ibase_prepare and then the prepared query is executed using the function ibase_execute.

// …
$sql = 'SELECT login, email FROM users WHERE id=?';
// Prepare statement
$sth = ibase_prepare($dbh, $sql);
$id = 1;
// Execute statement
$rc = ibase_execute($sth, $id);
// Get the result row by row as object
if ($row = ibase_fetch_object($rc)) {
  echo $row->email, "\n";
}
// Release the handle associated with the result of the query
ibase_free_result($rc);
// Release the prepared statement
ibase_free_query($sth);

Prepared queries are very often used when a large amount of data input is anticipated.

// …
$sql = 'INSERT INTO users(login, email) VALUES(?, ?)';
// Prepare statement
$sth = ibase_prepare($dbh, $sql);
$users = [["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"]];
// Execute statement
foreach ($users as $user)) {
  ibase_execute($sth, $user[0], $user[1]);
}
// Release the prepared statement
ibase_free_query($sth);
// …

It is actually a disadvantage of this extension that functions can take a variable number of parameters. It less than ideal for parameterized queries, as the last example demonstrates. It is especially noticeable if you try to write a universal class for executing any query. It would be much more useful to be able to send parameters in one array.

This would be one way to get around it:

function fb_execute ($stmt, $data)
{
  if (!is_array($data))
    return ibase_execute($stmt, $data);
  array_unshift($data, $stmt);
  $rc = call_user_func_array('ibase_execute', $data);
  return $rc;
}

The Fb/IB extension does not support named parameters in queries.

ibase_trans

By default, the Fb/IB extension commits the transaction automatically after executing each SQL query, making it necessary to start a transaction with the function ibase_trans if you need to control transactions explicitly. An explicit transaction is started with the following parameters if none are provided: IBASE_WRITE | IBASE_CONCURRENCY | IBASE_WAIT. You can find the description of predefined constants for specifying the parameters of a transaction here. A transaction must be completed by either ibase_commit or ibase_rollback.

This extension supports the COMMIT RETAIN and ROLLBACK RETAIN parameters directly if you use the functions ibase_commit_ret or ibase_rollback_ret, respectively, instead.

$sql = 'INSERT INTO users(login, email) VALUES(?, ?)';
// Prepare statement
$sth = ibase_prepare($dbh, $sql);
$users = [["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"]];
$trh = ibase_trans($dbh, IBASE_WRITE | IBASE_CONCURRENCY | IBASE_WAIT);
try {
  // Execute statement
  foreach ($users as $user)) {
    $r = ibase_execute($sth, $user[0], $user[1]);
    // If an error occurs, throw an exception
    if ($r === false)
      throw new \Exception(ibase_errmsg());
  }
  ibase_commit($trh);
}
catch(\Exception $e) {
  ibase_rollback($trh);
  echo $e->getMessage();
}
// Release the prepared statement
ibase_free_query($sth);

Services API Calls

The Fb/IB extension can interact with the Firebird server by way of functions that wrap calls to the Services API: ibase_service_attach, ibase_service_detach, ibase_server_info, ibase_maintain_db, ibase_db_info, ibase_backup, ibase_restore. They can return information about the Firebird server, initiate a backup or restore or get statistics. We are not examining them in detail, since they are required mainly to administer a database, a topic that is outside the scope of this project.

Firebird Events

The Firebird/Interbase extension also supports working with Firebird events by means of a set of functions: ibase_set_event_handler, ibase_free_event_handler, ibase_wait_event.

The PDO (Firebird Driver)

The PDO extension is a common interface for accessing various types of databases. Each database driver that implements this interface can provide database-specific features in the form of standard extension functions.

PDO and all basic drivers are built into PHP as extensions. To use them, just enable them by editing the php.ini file as follows:

extension=php_pdo.dll

Firebird-specific Library

The other requirement is for database-specific DLLs to be configured; or else loaded during execution by means of the dl() function; or else included in php.ini following php_pdo.dll. For example:

extension=php_pdo.dll
extension=php_pdo_firebird.dll

These DLLs must be in the directory extension_dir.

In Linux, one of the following commands should work. The one you use depends on the distribution package and the versions it supports:

apt-get install php5-firebird
rpm -ihv php5-firebird
yum install php70w-firebird
zypper install php5-firebird

Programming Style

PDO uses an object-oriented approach to developing programs. The DSN (Data Source Name), a.k.a. connection string, determines which specific driver will be used in PDO. The DSN consists of a prefix that determines the database type and a set of parameters in the form of <key>=<value> separated by semicolons. The valid set of parameters depends on the database type.

To be able to work with Firebird, the connection string must start with the firebird: prefix and conform to the format described in the PDO_FIREBIRD DSN section of the documentation.

Making Connections

Connections are established automatically during creation of the PDO from its abstract class. The class constructor accepts parameters to specify the data source (DSN) and also the optional username and password, if any. A fourth parameter can be used to pass an array of driver-specific connection settings in the key=value format.

$dsn = 'firebird:dbname=localhost:example;charset=utf8;';
$username = 'SYSDBA';
$password = 'masterkey';
try {
  // Connect to database
  $dbh = new \PDO($dsn, $username, $password,
                  [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
  $sql = 'SELECT login, email FROM users';
  // Execute query
  $query = $dbh->query($sql);
  // Get the result row by row as object
  while ($row = $query->fetch(\PDO::FETCH_OBJ)) {
    echo $row->email, "\n";
  }
  $query->closeCursor();
} catch (\PDOException $e) {
  echo $e->getMessage();
}

Exception Handling

The PDO driver is much more friendly than the Firebird/InterBase extension with respect to exception handling. Setting the \PDO::ATTR_ERRMODE attribute to the value \PDO::ERRMODE_EXCEPTION specifies a mode in which any error, including a database connection error, will raise the exception \PDOException.

This is superior to the laborious procedure of checking whether an error has occurred each time an ibase_ function is called.

Querying

The query method executes an SQL query and returns the result set in the form of a \PDOStatement object. A fetch to this method can return the result in more than one form: it could be a column, an instance of the specified class, an object.

The various ways of calling query can be found in the documentation.

Queries with No Data Set

For executing an SQL query that returns no data set, you can use the exec method that returns the number of affected rows.

Executing prepared queries is not supported by exec.

Parameterized Queries

If there are parameters in the query, prepared queries must be used. For this, the prepare method is called instead of the query method. The prepare method returns an object of the \PDOStatement class that encapsulates methods for working with prepared queries and their results. Executing the query requires calling the execute method that can accept as its parameter an array of named or unnamed parameters.

The result of executing a SELECT query can be obtained with one the following methods: fetch, fetchAll, fetchColumn, fetchObject. The fetch and fetchAll methods can return results in various forms: an associative array, an object or an instance of a particular class. The class instance option is quite often used in the MVC pattern during work with models.

$dsn = 'firebird:dbname=localhost:example;charset=utf8;';
$username = 'SYSDBA';
$password = 'masterkey';
try {
  // Connect to database
  $dbh = new \PDO($dsn, $username, $password,
                  [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
  $sql = 'INSERT INTO users(login, email) VALUES(?, ?)';
  $users = [
    ["user1", "user1@gmail.com"],
    ["user2", "user2@gmail.com"]
  ];
  // Prepare statement
  $query = $dbh->prepare($sql);
  // Execute statement
  foreach ($users as $user)) {
    $query->execute($user);
  }
} catch (\PDOException $e) {
  echo $e->getMessage();
}

An example using named parameters:

$dsn = 'firebird:dbname=localhost:example;charset=utf8;';
$username = 'SYSDBA';
$password = 'masterkey';
try {
  // Connect to database
  $dbh = new \PDO($dsn, $username, $password,
                  [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
  $sql = 'INSERT INTO users(login, email) VALUES(:login, :email)';
  $users = [
    [":login" => "user1", ":email" => "user1@gmail.com"],
    [":login" => "user2", ":email" => "user2@gmail.com"]
  ];
  // Prepare statement
  $query = $dbh->prepare($sql);
  // Execute statement
  foreach ($users as $user)) {
    $query->execute($user);
  }
} catch (\PDOException $e) {
  echo $e->getMessage();
}

Another Way to Do It

An alternative way to pass parameters to a query is by using “binding”. The bindValue method binds a value to a named or unnamed parameter. The bindParam method binds a variable to a named or unnamed parameter. The bindParam method is especially useful for stored procedures that return a value via the OUT or IN OUT parameter, which is different to the mechanism for returning values from stored procedures in Firebird.

$dsn = 'firebird:dbname=localhost:example;charset=utf8;';
$username = 'SYSDBA';
$password = 'masterkey';
try {
  // Connect to database
  $dbh = new \PDO($dsn, $username, $password,
                  [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
  $sql = 'INSERT INTO users(login, email) VALUES(:login, :email)';
  $users = [
    ["user1", "user1@gmail.com"],
    ["user2", "user2@gmail.com"]
  ];
  // Prepare statement
  $query = $dbh->prepare($sql);
  // Execute statement
  foreach ($users as $user)) {
    $query->bindValue(":login", $user[0]);
    $query->bindValue(":email", $user[1]);
    $query->execute();
  }
} catch (\PDOException $e) {
  echo $e->getMessage();
}

$dsn = 'firebird:dbname=localhost:example;charset=utf8;';
$username = 'SYSDBA';
$password = 'masterkey';
try {
  // Connect to database
  $dbh = new \PDO($dsn, $username, $password,
                  [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
  $sql = 'INSERT INTO users(login, email) VALUES(?, ?)';
  $users = [
    ["user1", "user1@gmail.com"],
    ["user2", "user2@gmail.com"]
  ];
  // Prepare statement
  $query = $dbh->prepare($sql);
  // Execute statement
  foreach ($users as $user)) {
    $query->bindValue(1, $user[0]);
    $query->bindValue(2, $user[1]);
    $query->execute();
  }
} catch (\PDOException $e) {
  echo $e->getMessage();
}

Transactions

By default, PDO commits the transaction automatically after executing each SQL query. If you want to control transactions explicitly, you need to start a transaction with the method \PDO::beginTransaction. By default, a transaction is started with the following parameters: CONCURRENCY | WAIT | READ_WRITE. A transaction can be ended with the \PDO::commit or \PDO::rollback method.

$username = 'SYSDBA';
$password = 'masterkey';
try {
  // Connect to database
  $dbh = new \PDO($dsn, $username, $password,
                  [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
  // Start the transaction to ensure consistency between statements
  $dbh->beginTransaction();
  // Get users from one table
  $users_stmt = $dbh->prepare('SELECT login, email FROM old_users');
  $users_stmt->execute();
  $users = $users_stmt->fetchAll(\PDO::FETCH_OBJECT);
  $users_stmt->closeCursor();
  // And insert into another table
  $sql = 'INSERT INTO users(login, email) VALUES(?, ?)';
  // Prepapre statemenet
  $query = $dbh->prepare($sql);
  // Execute statememt
  foreach ($users as $user)) {
    $query->bindValue(1, $user->LOGIN);
    $query->bindValue(2, $user->EMAIL]);
    $query->execute();
  }
  // Commit transaction
  $dbh->commit();
} catch (\PDOException $e) {
  // Rollback transaction
  if ($dbh && $dbh->inTransaction())
    $dbh->rollback();
  echo $e->getMessage();
}

Changing Transaction Parameters

Unfortunately, the beginTransaction method does not permit transaction parameters to be changed, but you can do the trick by specifying transaction parameters in the SQL statement SET TRANSACTION.

$dbh = new \PDO($dsn, $username, $password);
$dbh->setAttribute(\PDO::ATTR_AUTOCOMMIT, false);
$dbh->exec("SET TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED NO WAIT");
// Perform actions in the transaction
// …
$dbh->exec("COMMIT");
$dbh->setAttribute(\PDO::ATTR_AUTOCOMMIT, true);

6.1.2. Comparing the Drivers

The following table summarises the capabilities offered by the two drivers for working with Firebird.

From these comparisons we can conclude that PDO is better equipped than the FB/IB extension for most frameworks.