Accessing ODBC Databases with PHP and PEAR DB

PEAR (PHP Extension and Application Repository) is a framework and distribution system for reusable PHP classes, libraries and modules. One of its most popular packages is PEAR DB, the database abstraction layer created by this project. DB provides portability features that enable programs written for one database to work with other databases.

The PEAR DB module’s ODBC backend lets you access databases for which an ODBC driver is available. This tutorial shows how to use PEAR DB with an ODBC driver, which you can download from this site. You can then access remote database such as Microsoft SQL Server, Oracle, Microsoft Access, Sybase ASE and InterBase from PEAR DB on Linux and Unix.

Note Pear DB has been superceded by Pear MDB2, which is a merge of the DB and Metabase PHP database abstraction layers. However, it is not currently possible to access a database from PEAR MDB2 through ODBC (see PEAR :: Request #8413 :: ODBC driver). If you want to access your database through ODBC, you need to use PEAR DB rather than PEAR MDB2 therefore.

PEAR DB Architecture

The PEAR DB architecture has two levels. The top level, DB.php, provides a database independent interface for interacting with databases. The lower level driver files translate between the top-level abstract interface and the database-specific extensions that DB layers itself on top of. In the case of PEAR DB’s ODBC driver, the underlying database extension is Unified ODBC.

Code shared by the drivers is stored in DB/common.php. The default data access methods in common.php are overridden as necessary by the drivers.

The PEAR DB structure insulates your PHP applications and scripts from database-specific details letting you switch from one database to another without having to change your PHP code.

PEAR DB and Linux

Easysoft ODBC drivers have been tested with:

However, Easysoft ODBC drivers should work with PHP/PEAR DB on any recent Linux distribution (CentOS, Fedora, Mandrake, SUSE and so on).

PEAR DB and ODBC Prerequisites

Installing PHP, Unified ODBC and Easysoft ODBC Drivers

The Easysoft tutorial Enabling ODBC support in PHP under Apache contains installation instructions for the software you need to install before using the PEAR ODBC driver. The tutorial describes how to install, configure and test Apache, PHP, the PHP ODBC extension and an Easysoft ODBC Driver. Note that if you want to use PHP as a standalone program rather than an Apache module, the tutorial is still relevant. Skip the sections in the tutorial that relate to Apache.

After you have completed the Enabling ODBC support in PHP under Apache tutorial, you will have the necessary prerequisite software for the PEAR DB ODBC database driver. Until you have done this, you will not be able to access an ODBC data source from PEAR DB.

Installing PEAR DB

The base PEAR installation included in the PHP distribution provides a program named pear that lets you check what PEAR packages are installed. To check whether the PEAR DB package is installed:

If DB is not listed in the command output, the package is not installed. The following instructions show how to install PEAR DB using the simplest method, the pear command line installer. For information about all the available methods for installing PEAR packages, see the PEAR documentation.

If you are using PHP 4.3.1 or later, to install PEAR DB:

If you are using PHP 4.3.0 or earlier, to install PEAR DB:

  1. You need to install PEAR Package Manager before running pear to install PEAR DB. To do this, do one of the following:
    • If you have the lynx web browser installed, as root, run:
      lynx -source http://go-pear.org | php
    • If you do not have lynx installed, use another web browser to go to this address: http://go-pear.org. In your browser, save the output to a local file named go-pear.php. As root, change to the directory where you saved go-pear.php, and then run:
      php go-pear.php
  2. Follow the onscreen instructions. When prompted whether to install the PEAR packages, type "y" if you want to install all the packages in the list. If you only want to install PEAR DB, type "n", and then when the go-pear script has finished, as root, run:
    pear install DB

Connecting to ODBC Databases through PEAR DB

Accessing PEAR DB Classes

To access the classes provided by the PEAR DB library, your PHP script needs to include the file DB.php:

require_once "DB.php";

Using the require_once() statement ensures that DB.php will only be included once even if other files that your script includes also reference DB.php.

Note that if you get the error Failed opening 'DB.php' for inclusion when running your script, you need to edit your include_path directive to specify the directory where DB.php is located. To do this, edit the include_path entry in your php.ini file. For example, if DB.php is installed in /usr/share/pear:

include_path = ".:/php/includes:/usr/share/pear"

Note that on the platform we used for testing, Red Hat Linux, the default include_path value already specified the DB.php location and so editing php.ini was not necessary. To check the default value for include_path, use phpinfo().

Creating the PEAR DB DSN

To access a database through PEAR DB, you have to create a data source name (DSN) that specifies the appropriate PEAR DB backend for your database and the parameters necessary to connect to the database. To access an ODBC database through PEAR DB, create a DSN that uses the DB ODBC database backend and specifies the Easysoft ODBC driver data source that you have set up for the database.

Use this DSN syntax to connect to an ODBC database:

odbc:///odbc_data_source_name

For example, you have added an Easysoft ODBC driver data source named my_easysoft_dsn to the /etc/odbc.ini file that connects to your database. To access your database from PEAR DB by using the Easysoft ODBC driver, use:

$dsn = "odbc:///my_easysoft_dsn"

If your database requires a username and password to authenticate the connection, you have the option to specify these settings in the PEAR DB DSN. If your DSN connection string contains the database username and password, the values will override any specified in the Easysoft ODBC data source. Otherwise, PEAR DB will use the database username and password specified in the data source.

Note For SQL Server, you must specify the database username and password in the DSN rather than the ODBC data source.

Use this DSN syntax:

odbc://database_username:database_password@/odbc_data_source_name

For example:

$dsn = "odbc://my_database_username:my_database_password@/my_easysoft_dsn"

The parseDSN() function that PEAR DB uses to pass DSN values to the DB database driver relies on the @ symbol being present in the DSN to find the username and password. The DSN documentation mentions two further settings that can be specified after the database username and password: protocol and hostspec. The PEAR DB ODBC driver does not use the protocol setting and so you do not need to include it. For backwards compatibility, the hostspec setting can be used to specify the Easysoft ODBC data source name. If you are using a version of PEAR DB that is earlier than 1.6.0 (to check the version, use pear info DB), you must specify the ODBC data source name with hostspec:

odbc://database_username:database_password@odbc_data_source_name

For example:

// DSN for PEAR DB versions earlier than 1.6.0. If your database does
// not require a username or password or they are already specified
// in odbc.ini, omit my_database_username:my_database_password@
$dsn = "odbc://my_database_username:my_database_password@my_easysoft_dsn"

An additional setting, dbsyntax, is used to specify database-specific issues such as the quoted identifier character and transaction support. For more information, see the PEAR DB ODBC driver documentation.

An Example PEAR DB Script

Use this DB code sample to access your ODBC database from PEAR and retrieve some data. The script:

<?php
// Include the PEAR DB library.
require_once "DB.php";

// Use PEAR DB's ODBC driver as the backend.
$dbbackend = "odbc";

// For SQL Server, you must specify the database username and
// password here. Otherwise, leave $dbuser and $dbpassword
// blank if not required by your database or already specified
// in the ODBC data source.
$dbuser = "";
$dbpassword = "";

// Replace my_easysoft_dsn with the name of your Easysoft ODBC data
// source.
$dbname = "my_easysoft_dsn";

// Replace my_table with the name of a table in your database.
$stmt = "SELECT * FROM my_table";

// This DSN connects to an Easysoft ODBC data source.
if ($dbuser == "") {
    $dsn = "$dbbackend:///$dbname";
} else {
    $dsn = "$dbbackend://$dbuser:$dbpassword@/$dbname";
}

// Create a new DB connection object and connect to the ODBC database.
$dbh =& DB::connect($dsn);
if (DB::isError($dbh)) {
    die("Unable to connect to database: " . $dbh->getMessage() . "\n"
                                          . $dbh->getDebugInfo() . "\n");
}

// Use the database connection object to execute a SQL query.
$sth =& $dbh->query($stmt);
if (DB::isError($sth)) {
    die("Unable execute query: " . $sth->getMessage() . "\n"
                                 . $sth->getDebugInfo() . "\n");
}

// Retrieve each row from the DB result object.
while ($row =& $sth->fetchRow()) {
    print_r($row);
}

// Release the result set.
$sth->free();

// Close the connection object.
$dbh->disconnect();
?>

See Also

Appendix A: Resources