Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Web Applications with PHP and Firebird → Interfacing PHP and Firebird
Firebird Home Firebird Home Prev: Developing Web Applications with PHP and FirebirdFirebird Documentation IndexUp: Developing Web Applications with PHP and FirebirdNext: Choosing a Framework for Building a Web Application

Interfacing PHP and Firebird

Table of Contents

PHP Drivers for Firebird
Comparing the Drivers

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

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.

Note to Win32/Win64 users

For the drivers to work with the Windows PATH system variable, the fbclient.dll DLL file must be available. Copying the DLL file from the PHP directory or a Firebird installation to the Windows system folder would work, because the system directory is in the PATH variable by default. However, it is not recommended. The more robust way to do it is to prepend the file path to the PATH variable explicitly yourself, using the Windows advanced administration tool.

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
            

Tip

You might need to enable third party repositories if you find you have unresolvable dependency problems.

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 http://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 http://php.net/persistent-connections.

Need to know

Many ibase_ functions cannot accommodate the identifier of a connection, transaction or prepared query. Those functions use the identifier of the last established connection or last started transaction instead of the relevant identifier. Ii is not a recommended practice, especially if your web application can use more than one connection.

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.

Note

The default transaction parameters are good for most cases and it is really rarely that you need to change them. A connection to the database, along with all resources allocated to it, exists for no longer than it takes for the PHP script to complete. Even if you use persistent connections, all allocated resources will be released after the ibase_close function is called. Even so, I strongly recommend releasing all allocated resources explicitly by calling the corresponding ibase_ functions.

I advise strongly against using the ibase_commit_ret and ibase_rollback_ret functions because they have no place in a web application. The purpose of COMMIT RETAIN and ROLLBACK RETAIN is to keep cursors open in desktop applications when a transaction ends.

$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);
          

Warning

ibase_ functions raise no exception if an error occurs, although an error will cause some to return False. Note that it is essential to use the === strict relational operator to compare the result to False. Calling any ibase function could result in an error.

The function ibase_errmsg is available to discover an error message and the function ibase_errcode can provide the error code.

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
          

Note

This step is optional for PHP versions 5.3 and higher because DLLs are no longer needed for PDO to work.

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();
}
            

Persistent connections

For PDO to use persistent connections, the array of attributes must be passed to the PDO constructor with PDO::ATTR_PERSISTENT => true.

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();
}
              

Note

In order to support named parameters, PDO preprocesses the query and replaces parameters of the :paramname type with "?", retaining the array of correspondence between the parameter names and their left-to-right positions in the query. For that reason, the EXECUTE BLOCK statement will not work if there are colon-prefixed variables. Currently, PDO offers no workaround to support a parameterized EXECUTE BLOCK statement, such as by specifying an alternative prefix for parameters as has been implemented in some access components.

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();
}
                

Caution

The numbers associated with unnamed parameters for the bindParam and bindValue methods start from 1.

$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);
              

Comparing the Drivers

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

Table 6.1. Comparing the Firebird/InterBase and PDO Drivers

Capability Fb/IB Extension PDO Extension
Programming paradigm Procedural Object-oriented
Supported database engines Firebird and InterBase; or clones of either Any database engine for which there is a PDO driver, including Firebird
Handling query parameters Only unnamed parameters, not very convenient because the functions used allow the number of parameters to be variable Can work with both named and unnamed parameters. Very convenient although some Firebird features (the EXECUTE BLOCK statement) do not work.
Error handling Requires checking the results of the ibase_errmsg, ibase_errcode functions. An error may occur after any ibase_ function call without raising any exception. An optional mode is provided to raise exceptions on any error
Transaction management Allows transaction parameters to be specified Does not allow transaction parameters to be specified. Workaround: execute the SQL statement SET TRANSACTION.
Firebird-specific features Supports work with the Services API (backup, restore, statistics, etc.) and with database events Does not support any database-specific feature that cannot be implemented directly using an SQL statement


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

Prev: Developing Web Applications with PHP and FirebirdFirebird Documentation IndexUp: Developing Web Applications with PHP and FirebirdNext: Choosing a Framework for Building a Web Application
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Web Applications with PHP and Firebird → Interfacing PHP and Firebird