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 back end 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 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:

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 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 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 back end 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 back end 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 user name 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 user name and password, the values will override any specified in the Easysoft ODBC data source. Otherwise, PEAR DB will use the database user name and password specified in the data source.

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

Use this DSN syntax:

odbc://database_user_name:database_password@/odbc_data_source_name

For example:

$dsn = "odbc://my_database_user_name: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 user name and password. The DSN documentation mentions two further settings that can be specified after the database user name 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_user_name: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 user name or password or they are already specified
// in odbc.ini, omit my_database_user_name:my_database_password@
$dsn = "odbc://my_database_user_name: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 back end.
$dbbackend = "odbc";

// For SQL Server, you must specify the database user name 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 manual
  • PEAR DB class reference
• PEAR DB tutorials:
  • Quick Start Guide to Pear DB
  • Writing Scripts with PHP's PEAR DB Module

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

• 
• 
•