Accessing databases that support ODBC 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 (refer to 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.
Contents
- PEAR DB architecture
- PEAR DB and Linux
- PEAR DB and ODBC prerequisites
- Installing PEAR DB
- Connecting to ODBC Databases through PEAR DB
- An example PEAR DB script
- Further information
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:
- PHP 5.2.3 and PEAR DB 1.80 on Ubuntu.
- PHP 4.2.2 and PEAR DB 1.7.6 on RedHat.
However, Easysoft ODBC drivers should work with PHP and PEAR DB on any recent Linux distribution (CentOS, Fedora, Mandrake, SUSE, and so on).
PEAR DB and ODBC prerequisites
- PEAR DB is compatible with PHP 4 (version 4.0.4 or later) and PHP 5 but not PHP 3.
- PEAR DB layers itself on top of PHP's existing database extensions. To use PEAR DB's ODBC driver, you need the PHP ODBC extension (Unified ODBC).
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:
- Do one of the following:
- If you are using PHP 4.3.1 or later, run:
pear list
- If you are using PHP 4.3.0 or earlier, run:
pear list-installed
To check what version of PHP you have, run:
php -v
- If you are using PHP 4.3.1 or later, run:
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, refer to the PEAR documentation.
If you're using PHP 4.3.1 or later, to install PEAR DB:
- As root, run:
pear install DB
The
pear
command line installer opens an HTTP connection to the PEAR package server, loads the package and then installs it on your system.
If you are using PHP 4.3.0 or earlier, to install PEAR DB:
- 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 don't 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 savedgo-pear.php
, and then run:php go-pear.php
- If you have the lynx web browser installed, as root, run:
- 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, typen
, 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're using a version of PEAR DB that's 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, refer to 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:
- References
DB.php
to access the PEAR DB module. - Connects to the ODBC database by calling
connect()
with a DSN that specifies the DB ODBC driver and an Easysoft ODBC driver data source. - Uses the new DB connection object that
connect()
creates to execute a SQL query and obtain a DB result object. - Retrieves the data returned by the query from the result object.
- Closes the connection to the database when the connection object is no longer needed.
<?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(); ?>
Further information
- Enabling ODBC support in PHP under Apache — Easysoft tutorial for this tutorial.
- PEAR DB documentation:
- PEAR DB tutorials: