Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Enabling ODBC support in PHP under Apache

Contents

Introduction

This document contains instructions on making ODBC support in PHP work under Apache,

We have tested our ODBC drivers with Apache 1.3.x–2.0.50 and PHP 3.0.0–5.0.0 and we expect them to be compatible past these releases.

There are many different ways to build or install Apache and PHP with ODBC support. We have attempted to document these methods in order of popularity. If you're using an older copy of Apache or PHP, refer to Building and installing Apache 1.x and PHP 3.x or 4.

This document covers the Unified ODBC PHP extension. For information about using an Easysoft ODBC driver with PHP Data Objects (PDO) or PDO_ODBC, refer to this Easysoft tutorial.

PHP 5.x and Apache 2.0.x

For Apache 2.0.x and PHP 5.x, the recommended method is detailed here. If you're building Apache 1.x and PHP 4.x, refer to Building and installing Apache 1.x and PHP 3.x or 4.

Apache supports DSO (Dynamic Shared Objects) on most platforms now. PHP supports loading PHP modules (under PHP) by using extension entries in the php.ini file.

Building PHP 5.x and Apache 2.0.x yourself

This section describes how to build PHP 5.x and Apache 2.0.x yourself. You may need to do this because binary distributions for your platform are not available or are not built with the options you require.

Untar Apache, cd into the apache directory.

Always run configure --help with Apache to find out what other options you may want to include. This example uses all the defaults.

Build Apache with --enable--so. This turns on the DSO module that allows Apache to dynamically load modules (like PHP) at run time.

Run:

./configure --prefix=dir_where_you_want_apache --enable-so

Run make.

Run make install.

You should now have Apache installed in dir_where_you_want_apache.

Read PHP installation and configuration.

Basically, you need to make sure:

  1. LoadModule php5_module modules/libphp5.so is uncommented (or added) in the httpd.conf.
  2. AddType application/x-httpd-php .php .phtml is entered into the httpd.conf.

Untar PHP, cd into the PHP directory.

For PHP, always run ./configure --help with PHP to find out what other options you may want to include.

Read php-5.x.x/INSTALL, which refers to PHP Installation and configuration.

Run:

./configure --with-apxs2=/dir_where_you_want_apache/bin/apxs
--with-unixODBC=shared,/usr/local/easysoft/unixODBC

The shared says unixODBC should be built as a shared extension and the directory path points PHP's configure to where to find unixODBC.

What path you set in --with-unixODBC=shared,path depends on whether you are using the unixODBC Driver Manager that comes with the Easysoft products or whether you have built and installed unixODBC yourself. When installing Easysoft products, unixODBC is installed in /usr/local/easysoft/unixODBC but if you built unixODBC yourself with the default configure prefix, it will be in /usr/local. In addition, if you downloaded a package then unixODBC is probably in /usr. You must modify the --with-unixODBC appropriately.

Build php with make then run make install.

You should now have libphp.xx in /dir_where_you_want_apache/modules and PHP extensions (like odbc) in /usr/local/lib/php/extensions/no-debug-non-zts-2004041 (where the last subdirectory name depending on your PHP extension).

Copy the php.ini-dist file to /usr/local/lib/php.ini,

Now edit /usr/local/lib/php.ini and make sure:

  1. extension_dir is set to where your PHP extensions exist. For example: 
    extension_dir = 
"/usr/local/lib/php/extensions/no-debug-non-zts-20040412"
  2. extension is set to "extension=odbc.so", where the extension depends on the platform, but will be a file in extension_dir. This causes odbc.XX to be loaded automatically. The extension is platform dependent, but on UNIX System V machines is always .so (typically .sl on HP-UX and .a on AIX).

Now restart Apache. For example:

dir_where_you_want_apache/bin/apachectl restart

You should now be able to write a .phtml file and put it in dir_where_you_want_apache/htdocs. Refer to 5.0 Testing ODBC in PHP

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Apache 2 note

Apache 2 runs in a hybrid thread-process mode on any platform that supports both threads and processes. When running multi-threaded, you need to use a thread-safe ODBC-ODBC Bridge distribution and you are advised to build PHP with the unixODBC Driver Manager included with the ODBC-ODBC Bridge. Thread-safe ODBC-ODBC Bridge distributions are those containing -mt in the distribution filename.

If you're building PHP for Apache 2, make sure you amend the instructions below that specify --with-apxs to --with-apxs2.

You don't need to use a threaded model in Apache 2, refer to httpd.conf.

Installing PHP 4 and Apache 2 on Red Hat 9

This section describes how to install Apache and PHP on Red Hat 9. Most of this will also be relevant to anyone installing from RPMs, but the details here are Red Hat 9 specifically.

Installing packages from a shell prompt

Read this section if you usually add Red Hat packages from a shell prompt.

Red Hat 9 contains its own distribution of the unixODBC Driver Manager, which this example uses. However, the details are equally applicable to any unixODBC installation, both from source and binary packages.

Install the unixODBC RPM with:

rpm -i unixODBC-2.2.3-6.i386.rpm

Once installed, you should have access to the odbcinst command, which can be used to verify the version installed:

odbcinst --version
unixODBC 2.2.3

You can also use odbcinst to find out where ODBC data sources and drivers should be defined:

odbcinst -j
unixODBC 2.2.3
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/person/.odbc.ini

Install the ODBC driver you want to use, create a data source for it and test it with unixODBC's isql (refer to Testing ODBC in PHP).

Now install the main PHP RPM:

rpm -i php-4.2.2-17.i386.rpm
rpm -i php-odbc-4.2.2-17.i386.rpm

and restart the web server:

/etc/rc.d/init.d/httpd restart

Now refer to Testing ODBC in PHP.

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Installing packages from the KDE desktop

To install the Apache, PHP, and unixODBC packages:

  1. Log into KDE as root.
  2. From the KDE desktop, choose the RedHat icon to display the Start Applications menu.
  3. Choose System Settings, and then choose Add/Remove Applications.
  4. In the Package Management window, scroll down to the Servers section.
  5. Select the Web Server check box.
  6. Choose Web Server Details.
  7. In the Web Server Package Details dialog box, in the Extra Packages section, select the php-odbc check box.
  8. Choose Close.
  9. Choose Update.
  10. Choose Continue when prompted.
  11. Choose OK.
  12. Choose Quit.

To display unixODBC configuration information:

  1. On the Start Applications menu, choose System Tools, and then choose Terminal.
  2. At the shell prompt enter: 
    /usr/bin/odbcinst -j

    The command's output shows you:

    The following lines show some sample output from the odbcinst command:

    unixODBC 2.2.3
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /username/.odbc.ini

To start the Apache web server:

  1. On the Start Applications menu, choose System Settings, and then choose Server Settings. Then choose Services.
  2. In the Service Configuration Tool, in the Services list, select the httpd check box.
  3. Choose the Start button.
  4. Choose OK when prompted.
  5. On the File menu, choose Save Changes.
  6. On the File menu, choose Quit.
Next steps

Install the ODBC driver you want to use, and then create and test an ODBC data source. For information on how to do this, refer to Testing ODBC in PHP.

Building and installing Apache 1.x and PHP 3.x or 4

There are four ways to build PHP 3.x or 4.x for use with Apache 1.x:

  1. Standalone PHP. You build PHP as a standalone executable containing the parser and engine and run it as CGI under Apache. We would not recommend this as it is a lot slower but it does allow you to play with PHP without making any Apache changes.

    In your configure line, you would use something like:

    ./configure --with-unixODBC  --without-mysql --enable-track-vars
--enable-force-cgi-redirect
  2. Static build of Apache and PHP. Here, you build PHP, insert the PHP archive into the Apache tree, and build Apache. The Apache code and PHP code both exist in Apache. This used to be the most common method of building Apache and PHP and is detailed below.

    The disadvantage is that if a new PHP is released, you need to rebuild Apache and PHP to start using the new PHP version and the Apache executable is larger.

  3. Dynamic build of Apache and PHP. Here, you build Apache with DSO support (mod_so) and name the Apache modules you want to dynamically load. PHP is built as a shared object using Apache's apxs command and then Apache is asked to load it.

    One obvious advantage of this method is that you can rebuild or change PHP without changing Apache. Many Linux distributions distribute Apache like this, so if you don't want to have to rebuild Apache but would like to use PHP with ODBC support, you can use this method.

  4. Dynamic build of Apache with PHP3 and PHP4 modules. This means you can run PHP3 and PHP4. It is basically the same as the previous method, but you need to read carefully the instructions at the end of PHP's INSTALL file and refer to Using PHP 3 and PHP 4 as concurrent Apache modules.
Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

PHP configure option

From PHP4b3, you can configure PHP for the ODBC-ODBC Bridge with the --with-esoob option instead of using the --with-custom-odbc option. However, if you have installed a standard ODBC-ODBC Bridge distribution (or unixODBC separately) we strongly recommend you build PHP with the unixODBC Driver Manager and then add ODBC drivers to unixODBC's odbcinst.ini file.

Building multiple ODBC drivers into PHP

Note that you cannot build PHP with multiple ODBC drivers or Driver Managers, that is, use any two from:

--with-custom-odbc
--with-iodbc
--with-esoob
--with-unixODBC
--with-openlink

If you want to access multiple ODBC drivers from PHP, the best method is to install the unixODBC Driver Manager and then tell the unixODBC Driver Manager about your ODBC drivers. The unixODBC Driver Manager is contained in all Easysoft product distributions, available as source from the unixODBC web site, and often available as a package for your distribution. You need to use the --with-unixODBC configure option to PHP to build with the unixODBC Driver Manager.

You can build PHP with an ODBC driver and non-ODBC database drivers such as Oracle or MySQL.

Building PHP (3.x or 4.x) with ODBC support for Apache (1.x) DSO

If you already have Apache built or it came with your UNIX distribution, you may be able to add PHP support without rebuilding Apache. To do this, Apache must be built with DSO support. To check if your Apache was built with DSO support, you run httpd -l to list the modules included statically. Check for mod_so.c. If you have mod_so then you can build PHP as a dynamic module.

To build Apache from scratch with support for DSO, you need to specify --enable-module=so to on the Apache configure line.

To build PHP dynamically for Apache, you use a PHP configure line similar to:

./configure --with-unixODBC --with-apxs

Note, we do not recommend using versions of unixODBC that come as RPMs in Red Hat 7.x, as these appear to be built with thread support and this currently will not work with Apache DSO. Instead, use the unixODBC distribution that comes with the Easysoft product.

Statically building post-PHP4b3 and Apache (1.x) with the ODBC-ODBC Bridge

Read the INSTALL files in the PHP and Apache distributions before following these instructions as they may provide more up to date information on Apache and PHP specifics.

The example below assumes you gave /usr/local as the installation path for the Easysoft ODBC-ODBC Bridge. If this is not the case then change /usr/local to the installation path specified when the ODBC-ODBC Bridge was installed.

Note, this is not the recommended way to build PHP as it restricts you to using only the Easysoft ODBC-ODBC Bridge as an ODBC driver whereas building PHP with a Driver Manager allows you access to all ODBC drivers you install under the Driver Manager.

To build PHP with Apache and the ODBC-ODBC Bridge:

  1. Download Apache.

  2. Download PHP.

  3. Refer to the instructions in the PHP and Apache INSTALL files. In particular, the PHP INSTALL file has step-by-step instructions for building with MySQL. (If you are not going to be using MySQL then you can omit --with-mysql). To build PHP with the Easysoft ODBC-ODBC Bridge you must add: 
    --with-esoob=/base_oob_install_dir/easysoft/oob/client

    where base_oob_install_dir is the base directory where the ODBC-ODBC Bridge was installed.

  4. When configuring Apache you need to define the environment variable LDFLAGS as:

    -L/base_oob_install_dir/oob/easysoft/client

    For UNIX platforms where the linker does not automatically work out the sub-dependencies (virtually all platforms but Linux) you also need to:

    • Add: 
      -L /base_oob_install_dir/lib -lesrpc -lessupp -lesextra

      to LDFLAGS. LDFLAGS tells the linker where to find the the ODBC-ODBC Bridge shared objects and what shared objects to use.

    • Set LD_LIBRARY_PATH, LD_RUN_PATH, SHLIB_PATH, or LIBPATH (depending on your platform) to: 
      /usr/local/easysoft/oob/client:/usr/local/easysoft/lib

      This setting is required because Apache now does a test link and run when configuring so if you don't tell the dynamic linker where to find all the shared objects, you'll probably get multiple "cannot find libesrpc.so messages" during configure.

      Export these environment variables before running the Apache configure.

Statically building pre-PHP4b3 and Apache (1.x) with the ODBC-ODBC Bridge

Read the INSTALL files in the PHP and Apache distributions before following these instructions as they may provide more up to date information on Apache and PHP specifics.

The example below assumes you gave /usr/local as the installation path. If this is not the case, change /usr/local to the installation path specified when the Easysoft ODBC-ODBC Bridge was installed.

Note, this is not a recommended way to build PHP as it restricts you to using only the Easysoft ODBC-ODBC Bridge as an ODBC driver, whereas building PHP with a Driver Manager allows you access to multiple ODBC drivers. If you want to build with the unixODBC Driver Manager, you need to replace the paths in the following examples and the library. However, you will find better ODBC support in later versions of PHP and we'd recommend upgrading.

To build PHP with Apache and the ODBC-ODBC Bridge:

  1. Download Apache.
  2. Download PHP.
  3. Refer to the instructions in the PHP and Apache INSTALL files. In particular, the PHP INSTALLfile has step-by-step instructions for building with MySQL. (If you are not going to be using MySQL then you can omit --with-mysql). To build PHP with the Easysoft ODBC-ODBC Bridge you must add: 
    --with-custom-odbc=base_oob_install_dir/easysoft/oob/client

    where base_oob_install_dir is the base directory where the ODBC-ODBC Bridge was installed.

    You must also define the environment variable CUSTOM_ODBC_LIBS as:

    CUSTOM_ODBC_LIBS="-lesoobclient"

    and export it or if using the bash shell, prefix the PHP configure line with CUSTOM_ODBC_LIBS="-lesoobclient".

  4. When configuring Apache, you need to define the environment variable LDFLAGS as: 
    -L/base_oob_install_dir/oob/easysoft/client

    For UNIX platforms where the linker does not automatically work out the subdependencies (virtually all platforms but Linux) you also need to:

    • Add: 
      -L /base_oob_install_dir/lib -lesrpc -lessupp -lesextra

      to LDFLAGS. LDFLAGS tells the linker where to find the ODBC-ODBC Bridge shared objects and what shared objects to use.

    • Set LD_LIBRARY_PATH, LD_RUN_PATH, SHLIB_PATH, LIBPATH (depending on your platform) to:

      /usr/local/easysoft/oob/client:/usr/local/easysoft/lib

      This setting is required because Apache does a test link and run when configuring, so if you do not tell the dynamic linker where to find all the shared objects, you'll probably get multiple "cannot find libesrpc.so messages" during configure.

      Export these environment variables before running the Apache configure.

Here's a log of a typical build process:

> cd apache_1.3.3
> ./configure --prefix=/httpx
Configuring for Apache, Version 1.3.3
Creating Makefile
Creating Configuration.apaci in src
Creating Makefile in src
 + configured for Linux platform
 + setting C compiler to gcc
 + setting C pre-processor to gcc -E
 + checking for system header files
 + adding selected modules
 + doing sanity check on compiler and options
Creating Makefile in src/support
Creating Makefile in src/main
Creating Makefile in src/ap
Creating Makefile in src/regex
Creating Makefile in src/os/unix
Creating Makefile in src/modules/standard
> cd ../php-3.0.6/
> CUSTOM_ODBC_LIBS="-lesoobclient"
> export CUSTOM_ODBC_LIBS
> ./configure --with-custom-odbc=/usr/local/easysoft/oob/client \
    --with-apache=../apache_1.3.3 --enable-track-vars
> make
 <output from make omitted for brevity>
> make install
mkdir -p ../apache_1.3.3/src/modules/php3; 
cp libmodphp3.a ./mod_php3.* ./php_version.h 
        ../apache_1.3.3/src/modules/php3;
cp ./apMakefile.tmpl ../apache_1.3.3/src/modules/php3/Makefile.tmpl; 
cp ./apMakefile.libdir ../apache_1.3.3/src/modules/php3/Makefile.libdir;
cp libphp3.module ../apache_1.3.3/src/modules/php3
> cd ../apache_1.3.3/
> LD_LIBRARY_PATH="/usr/local/easysoft/oob/client:/usr/local/easysoft/lib"
> export LD_LIBRARY_PATH
> LDFLAGS="-L/usr/local/easysoft/oob/client -L/usr/local/easysoft/lib"
> export LDFLAGS
> ./configure --prefix=/httpx \
    --enable-module=info --enable-module=log_referer \
    --enable-module=mime_magic --enable-module=status \
    --activate-module=src/modules/php3/libphp3.a
Configuring for Apache, Version 1.3.3
 + activated php3 module (modules/php3/libphp3.a)
Creating Makefile
Creating Configuration.apaci in src
Creating Makefile in src
 + configured for Linux platform
 + setting C compiler to gcc
 + setting C pre-processor to gcc -E
 + checking for system header files
 + adding selected modules
    o php3_module uses ConfigStart/End
 + doing sanity check on compiler and options
Creating Makefile in src/support
Creating Makefile in src/main
Creating Makefile in src/ap
Creating Makefile in src/regex
Creating Makefile in src/os/unix
Creating Makefile in src/modules/standard
Creating Makefile in src/modules/php3
> make
 <output from make omitted for brevity>
> make install

Using PHP 3 and PHP 4 as concurrent Apache modules

If you need to run PHP3 and PHP4 simultaneously, this can be done on a few platforms. Read the instructions at the end of PHP's INSTALL file first.

We recommend building PHP with the unixODBC Driver Manager and not directly with any Easysoft products.

We tried this successfully using the following configuration:

PHP3 was configured with:

LDFLAGS=-L/usr/local/easysoft/unixODBC/lib CUSTOM_ODBC_LIBS=-lodbc.so \
./configure --with-custom-odbc=/usr/local/easysoft/unixODBC
  --with-apxs=/http/bin/apxs --enable-versioning --enable-track-vars

PHP4 was configured with:

/configure --with-unixODBC=/usr/local/easysoft/unixODBC \
  --with-apxs=/http/bin/apxs --enable-versioning --enable-track-vars

Obviously, the argument to --with-apxs depends on where you installed Apache.

We also added the following entries to the httpd.conf file:

LoadModule php4_module libexec/libphp4.so
LoadModule php3_module libexec/libphp3.so
AddType application/x-httpd-php3 .php3
AddType application/x-httpd-php .php
Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Testing ODBC in PHP

This section shows you how to test ODBC in PHP under Apache. It assumes you have:

The first and most important step is to verify the ODBC driver is installed correctly and you have created and tested the ODBC data source (DSN).

  1. Locate the unixODBC Driver Manager:

    • If you installed unixODBC as part of your Easysoft installation, it will be in /usr/local/easysoft/unixODBC. unixODBC programs will be in /usr/local/easysoft/unixODBC/bin.
    • If you built unixODBC yourself, it will be in /usr/local (by default) or wherever you set --prefix to. unixODBC programs will be in /usr/local/bin or prefix/bin.
    • If you installed unixODBC as part of an RPM or package, it will be in various places, but the unixODBC programs should already be on your path (probably /usr/bin).

    You'll know if you have the correct path if you can find unixODBC's odbcinst program. In the rest of this section, the path to unixODBC programs is called UNIXODBCBIN.

  2. Locate where unixODBC is storing the installed ODBC drivers and system DSNs:

    UNIXODBCBIN/odbcinst -j

unixODBC 2.2.3
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/person/.odbc.ini

    In this case, /etc/odbcinst.ini is where unixODBC stores a list of the installed ODBC drivers and /etc/odbc.ini contains the system DSNs.

    The odbcinst.ini file should already contain an entry for the Easysoft ODBC driver you have installed. For example, for the ODBC-ODBC Bridge, it will look similar to this:

    [OOB]
Description = 
Driver = /usr/local/easysoft/oob/client/libesoobclient.so
Setup = /usr/local/easysoft/oob/client/libesoobsetup.so
FileUsage = 1

    If you haven't got an ODBC driver in odbcinst.ini, you need to check the Easysoft product installation found or installed unixODBC.

    The odbc.ini file is where you define your ODBC data sources. Unless you run Apache as a specific user with a home directory, you must put the DSNs in the system odbc.ini. (In the previous example, this is /etc/odbc.ini.)

  3. Create, check, and test your DSNs with isql.

    When you install an Easysoft ODBC driver, the installation will usually create a DSN in the system odbc.ini file. Exceptions to this are if unixODBC was not installed as part of the Easysoft installation or if unixODBC was not already found on your machine.

    Check your system odbc.ini file for the DSN to test. If your system odbc.ini file does not contain a DSN, you need to create one, refer to the instructions that come with the Easysoft ODBC driver, which are usually found in /usr/local/easysoft/DRIVER/docs.

    If the DSN you're using requires a user name and password to login to the database, you need these details.

    Run:

    UNIXODBCBIN/isql -v DSN; 
  database_user_name; database_password

    where DSN is the name of your DSN and database_user_name and database_password are the login information for your database.

    This should successfully connect to your database and provide a prompt at which you can enter some SQL. If it doesn't, there are a number of possibilities and you should:

    • Check that you've set any necessary environment variables like ORACLE_HOME, LD_LIBRARY_PATH, and so on (refer to the manual for the Easysoft ODBC driver you're using).
    • Consult the documentation that came with the ODBC driver.
    • Contact Easysoft support ()

    A few common problems are listed in Appendix B: Common problems accessing your ODBC data source.

    Until this works, there is no point in continuing with these instructions. PHP is just like isql, it is another application using ODBC, so if isql does not work, PHP won't either.

The second most important step is to make sure your PHP is working under Apache (irrespective of ODBC support in PHP):

  1. Make sure you built or installed Apache with PHP support.
  2. If Apache was built with DSO (it dynamically loads PHP) then make sure your Apache httpd.conf file contains a LoadModule line like: 
    LoadModule php5_module modules/libphp5.so

    –Or–

    LoadModule php4_module libexec/libphp4.so

    –Or–

    LoadModule php3_module libexec/libphp3.so

    Note If you installed PHP from a Red Hat package, check for a LoadModule entry in php.conf instead. By default, Red Hat installs php.conf in the /etc/httpd/conf.d directory.

  3. Make sure you that you edit the httpd.conf file to tell Apache what module will process .phtml and or .php files. This is usually a line like the following in the Apache httpd.conf file:

    AddType application/x-httpd-php .php .phtml

  4. Check you can run a simple PHP script. For example:

    <html>
<head>
<title>Test</title>
</head>
<body>
<p>
You should get PHP info below:
<?
        phpinfo();
?>
</body>
</html>

    Put the above in a .phtml file under your Apache and check it runs.

    If you have problems with this:

    • Check your Apache error log. This file is defined in your Apache configuration file(s) (for example, httpd.conf) with the ErrorLog setting.
    • Did you copy php.ini to the correct place?

      If you installed PHP from a Red Hat package, skip this step. By default, Red Hat installs php.ini in the /etc directory.

    • Turn on PHP logging (refer to the php.ini file).

      Some options in this file can prove useful for debugging:

      • track_errors = On Stores the last error in the php_errormsg variable.
      • error_reporting = 7 Outputs normal errors, normal warnings, and parser errors.
      • error_log = filename Defines the file errors and warnings are sent to.

Now try ODBC support under Apache and PHP:

  1. Locate a directory in Apache where you can store the .phtml files you want executing. Usually the directory defined in the Apache httpd.conf file by DocumentRoot is a good place. Quite often, this is apache_install_dir/htdocs or /var/www/html.

    Copy the file below into that directory, making sure the extension of the file is that defined in httpd.conf for PHP files (usually .phtml) then edit it as detailed below.

    <html>
<head>
<title>ODBC test script</title>
</head>
<p align="center"><b>ODBC Test Script</b></p>
<p>
<?
$con = odbc_connect("DSN", "DB_USERNAME", "DB_PASSWORD");
$rs2 = odbc_exec($con, "select * from MYTABLE");
odbc_result_all($rs2);
odbc_close($con);
?>
</body>
</html>
    • Replace MYTABLE with a table in your database you have at least select access to.
    • Replace DSN with the name of your DSN.
    • Replace DB_USERNAME and DB_PASSWORD with valid login information for your database.

    Browse to the URL on your Apache server for the file you've just placed there. You should get a table containing the results of the SQL.

Appendix A: Problems with PHP and ODBC

Potential leak of an ODBC connection in PHP

We posted the following to the ODBC-ODBC Bridge news group:

----------------------------------------
Subject: Notice: bug in PHP can cause Apache/PHP to leak 
  ODBC connections
  Organization: Easysoft Limited
CC: support@easysoft.com

We have discovered a bug in PHP 4.0.4pl1 and earlier
which can cause PHP to leak an ODBC connection. This
means a thread in the ODBC-ODBC Bridge server or a process created
by the ODBC-ODBC Bridge server is left connected to the Apache/PHP
httpd process until:

[1] that httpd is closed down
    (by Apache itself via MaxRequestsPerChild or by
    explicitly shutting down Apache).
[2] if not running PHP under Apache when whatever process
    running your PHP dies/exits.
[3] the ODBC-ODBC Bridge server times out the connection
    (the default timeout is 2 hours but can be altered).

The problem can arise in scripts which turn AutoCommit off
with odbc_autocommit($dbc, false) and then leave outstanding
transactions (this even includes open result-sets in some
DBMS).

The problem can be demonstrated best by switching the ODBC-ODBC Bridge
server to MultiProcess (i.e. create a new process for each
ODBC connection) and then (example for Microsoft SQLServer):

[1] create a table such as:
    create table stmtclose (a integer, b text)
    insert into the table one row containing
    1 and a text string of more than odbc.defaultlrl (PHP setting)
    bytes.
    e.g. insert into stmtclose values (1, 'a_very_very_long_string')

[2] Run a script such as the following:

$dbc = odbc_connect("test", "dbusername", "dbpassword");
if (!$dbc) {
echo $php_errormsg;
exit (1);
}
odbc_autocommit($dbc, false);
$stmt = odbc_prepare($dbc, "select * from stmtclose");
if (!$stmt) {
echo $php_errormsg;
exit(1);
}
odbc_execute($stmt);
$a = odbc_result($stmt, 1);
echo "column 1 is " . $a . "<br>";
    $b = odbc_result($stmt, 2);
    echo "column 2 is " . $b . "<br>";
        /* odbc_commit($dbc); here does fix the problem */
        /* odbc_free_result($stmt) here does not help */
        odbc_close($dbc);            

Each time you run the above a new ODBC-ODBC Bridge ODBC connection is made
and you will get a new esoobserver process on your server machine
(as the ODBC-ODBC Bridge server is running MultiProcess). When the script
terminates the esoobserver process will not go away and investigation
shows an open connection between the client and server in the process
that ran the script.

The problem is that the above script does not retrieve
all the result set as the b column is a longvarchar containing
much greater than PHPs odbc.defaultlrl bytes so PHP retrieves
the column with SQLGetData and the ODBC driver returns
odbc/defaultlrl bytes but says there is more to be retrieved.

When odbc_close() is called PHP calls:

        SQLDisconnect(conn->hdbc);
        SQLFreeConnect(conn->hdbc);
        SQLFreeEnv(conn->henv);

but the SQLDisconnect fails because there is a transaction still
in progress and hence the SQLFreeConnect and SQLFreeEnv also
fail since you cannot free a connected connection handle.

The above script can be made to stop leaking the ODBC connection if:

[1] the entire result-set is retrieved by looping on
    odbc_result($stmt,2) until it returns an empty string.
[2] odbc_commit() is called before disconnecting.

Also, if Apache's MaxRequestsPerChild is changed from the default
of infinite then the httpd process that ran the above script
will terminate after handling MaxRequestsPerChild requests which
will close the ODBC-ODBC Bridge client socket and disconnect the ODBC thread/process
at the server. The same happens when the ODBC-ODBC Bridge server times out the
connection but neither of these are really a solution.

A change to PHP has been submitted which would rollback the
transaction if SQLDisconnect failed but we cannot guarantee
it will still be there for the next release. In the mean time
you should be very careful when turning autocommit off to
finish any outstanding transactions before calling odbc_close.
----------------------------------------

Hanging code when using Multiple Active Statements to Microsoft SQL Server

Here's a PHP script explaining the problem and resolutions.

/*
*  This script can be used to demonstrate an apparent hang in the web server
*  when retrieving data.
*  The problem reported is the browser hanging whilst trying to receive
*  a request based on a PHP script retrieving data from a table in
*  Microsoft SQL Server. The setup is:
*
*  browser <=> Apache/PHP<=>unixODBC DM<=>OOBClient <network>
*  <=>OOB Server<=>Microsoft SQL Server ODBC driver<=>Microsoft SQL Server.
*
*  The actual hang occurs in the Microsoft Server Server ODBC driver where it appears
*  to be in a spinwait. It seems to happen when you start receiving a column's
*  data in one statement and then go off and do something with another
*  statement. This can easily be duplicated in Microsoft's odbctest utility.
*
*  For example:
*
*  create table hang1 (a int, b int)
*  insert into hang1 values (1,1)
*  insert into hang1 values (2,2)
*  create table hang3 (a int, b text)
*  insert into hang3 values (1, 'b' + space(18000) + 'e');
*  insert into hang3 values (2, 'bb' + space(18000) + 'ee');
*
*  Now in ODBC terms, do this:
*
*  connect
*  allocate stmt1
*  SQLSetStmtAttr(stmt1, SQL_ATTR_CURSOR_TYPE = SQL_CURSOR_DYNAMIC)
*  allocate stmt2
*  SQLSetStmtAttr(stmt2, SQL_ATTR_CURSOR_TYPE = SQL_CURSOR_DYNAMIC)
*    Now we have two statements and because the cursor is DYNAMIC, the
*    Microsoft SQL Server ODBC driver will allow us to have multiple active
*    statements.
*  SQLExecDirect(stmt1, "select * from hang"")
*  SQLFetch(stmt1)
*  SQLGetData(stmt1, column=2, BufferLength=500)
*    This will return SQL_SUCCESS_WITH_INFO as there is over 18K of data
*    and we have only retrieved 500 bytes so far.
*  SQLExecDirect(stmt2, "select * from hang1")
*    This hangs and if you examine the application you will discover that the code
*    is looping (spin waiting) inside the Microsoft SQL Server ODBC driver.
*
*  The above scenario is avoided if:
*  [a] stmt1 is closed before stmt2 enters the executed state -
*      SQLFreeStmt(SQL_CLOSE).
*  [b] SQLGetData(stmt1, column=2, BufferLength=n) is called
*      repeatedly until all the column data is retrieved.
*  [c] SQLFetch(stmt1) is called before stmt2 is used - presumably
*      this informs the ODBC driver it can throw away the partial row.
*
*  In PHP, the ODBC interface is written in C and as far as most
*  PHP developers are concerned - fixed. So, how do you avoid this scenario
*  in plain PHP scripts? Refer to the code below but stick to the following
*  rules and you should not go wrong.
*
*  [a] Always call odbc_free_result($rid) when you are finished with a
*      result-set.
*  [b] If you have tables with long columns (SQL_LONGVARCHAR, SQL_BINARY and so on)
*      (in Microsoft SQL Server, text, image, and so on) and you are selecting these
*      columns then examine PHP's odbc.defaultlrl configurable parameter
*      in your php.ini file or even safer, specifically call
*      odbc_longreadlen($rid, n).
*
*      What you need to ensure is that if a column can have more data in
*      it than odbc.defaultlrl or odbc_longreadlen is set to, call
*      odbc_result on that column repeatedly until odbc_result returns false
*      (concatenate the resulting strings if you like). If you are sure
*      columns will never have more than N bytes and N is not too big,
*      then set odbc.defaultlrl or odbc_longreadlen to N+1 and you
*      will always avoid the hang.
*
*      OR
*
*      Make sure odbc_fetch() is called to fetch the next row before
*      progressing in your script -- this appears to cancel the outstanding
*      column reads.
*
*  The code below demonstrates this problem and has the solutions above
*  commented out. You will need to create the hang1 and hang3 tables shown
*  above before running it. You will also need to change the odbc_connect
*  call below to a valid one for your set up. Be very careful. Make sure that
*  if you run this to an ODBC-ODBC Bridge server that the ODBC-ODBC Bridge server is running in its
*  default mode of multi-threaded or you will end up with a process on your
*  Windows server which is in a spin-wait, consuming a lot of CPU and you
*  are unable to kill it. When the ODBC-ODBC Bridge server is running multi-threaded
*  you can simply stop the ODBC-ODBC Bridge server service and restart it to get rid of
*  the thread that is looping in side the Microsoft SQL Server ODBC driver.
*
*  A small note on using Multiple Active Statements in PHP to Microsoft SQL Server
*  ========================================================================
*
*  PHP 4.2.0 now uses a forward-only cursor and not a dynamic one.
*
*  If you are connecting PHP to Microsoft SQL Server and you do not need
*  multiple active statements then it may be better to turn off the
*  setting of a dynamic cursor in the PHP module. The dynamic cursor is
*  slower than the default cursor and prone to the problem described above.
*  To turn off the setting of the dynamic cursor in PHP, you need to edit
*  the ext/odbc/php_odbc.c file in the PHP distribution and make a few
*  changes. It is not sufficient to simply set another cursor with
*  odbc_cursor() due to ODBC calls for which the return status is not
*  checked in the PHP module.
*
*  To make this change, unpack PHP, configure in the normal way and build
*  PHP. Before installing make the following changes to ext/odbc/php_odbc.c,
*  and run make again:
*
*  [1] search for the two occurrences of the code that sets the dynamic
*      cursor and ifdef them out.
*      The code you are looking for looks like this:
*
* rc = SQLGetInfo(conn->hdbc, SQL_FETCH_DIRECTION, (void *) &scrollopts,
* sizeof(scrollopts), NULL);
* if (rc == SQL_SUCCESS) {
*   if ((result->fetch_abs = (scrollopts & SQL_FD_FETCH_ABSOLUTE))) {
*     if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC)
*         == SQL_ERROR) {
*       odbc_sql_error(conn, result->stmt, " SQLSetStmtOption");
*       SQLFreeStmt(result->stmt, SQL_DROP);
*       efree(result);
*       RETURN_FALSE;
*     }
*   }
* } else {
*   result->fetch_abs = 0;
* }
*
* You need to change this to:
*
* rc = SQLGetInfo(conn->hdbc, SQL_FETCH_DIRECTION, (void *) &scrollopts,
* sizeof(scrollopts), NULL);
* if (rc == SQL_SUCCESS) {
*   if ((result->fetch_abs = (scrollopts & SQL_FD_FETCH_ABSOLUTE))) {
* #ifdef DO_NOT_USE_DYANMIC_CURSORS
*     if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC)
*         == SQL_ERROR) {
*       odbc_sql_error(conn, result->stmt, " SQLSetStmtOption");
*       SQLFreeStmt(result->stmt, SQL_DROP);
*       efree(result);
*       RETURN_FALSE;
*     }
* #endif
*   }
* } else {
*   result->fetch_abs = 0;
* }
*
* There are two occurrences of this code in odbc_prepare() and odbc_exec.
*
* [2] The problem with the above change is that the PHP module does not
*     check the return status from calls to SQLNumParams() and
*     SQLNumResultCols() but once dynamic cursors are turned off, a call to
*     SQLNumResultCols() will fail with "Connection is busy with results
*     for another hstmt" when a second concurrent statement is used. We
*     need to trap this error in case you accidentally use multiple active
*     statements.
*     Look for the functions odbc_exec() and odbc_prepare() in php_odbc.c.
*
*     In odbc_prepare there are two calls to SQLNumParams and
*     SQLNumResultCols neither of which are tested for successful operation.
*     You need to copy the failure code from call to SQLSetStmtOption above
*     it and add it to SQLNumResultCols and SQLNumParams. In PHP 4.1.0, this
*     would look like this:
*
*     Original code:
*
*      rc = SQLNumParams(result->stmt, &(result->numparams));
*      rc = SQLNumResultCols(result->stmt, &(result->numcols));
*
*     New code:
*
*      rc = SQLNumParams(result->stmt, &(result->numparams));
*      if (!SQL_SUCCEEDED(rc)) {
*        odbc_sql_error(conn, result->stmt, " SQLNumParams");
*        SQLFreeStmt(result->stmt, SQL_DROP);
*        efree(result);
*        RETURN_FALSE;
*      }
*
*      rc = SQLNumResultCols(result->stmt, &(result->numcols));
*      if (!SQL_SUCCEEDED(rc)) {
*        odbc_sql_error(conn, result->stmt, " SQLNumResultCols");
*        SQLFreeStmt(result->stmt, SQL_DROP);
*        efree(result);
*        RETURN_FALSE;
*      }
*
*  You now need to make a similar change in odbc_exec() except there is
*  only a single call to SQLNumResultCols() and no call to SQLNumParams().
*  Now re-run make and install PHP.
*/
$dbc = @odbc_connect("DSN=your_dsn;", "username", "password");

if (!$dbc) {
    echo $php_errormsg;
    die("Connection failed");
}
$rid = odbc_prepare($dbc, "select * from hang3");
if (!$rid) {
    echo $php_errormsg;
    die("Execute failed");
}
if (!odbc_execute($rid)) {
    echo $php_errormsg;
    die("Execute failed");
}
$num_fields = odbc_num_fields($rid);
if ( $num_fields == 0 )
{
    echo "Operation succeeded\n";
    echo "<p>".odbc_num_rows($rid)." rows altered\n";
}
else if ($num_fields > 0)
{
    /*
    *  We know the largest b column in the hang3 table is 18005 bytes in
    *  length so calling odbc_longreadlen($rid, N) where N > 18005 avoids
    *  a hang happening due to this query.
    */
    /*  odbc_longreadlen($rid, 32000); */

    odbc_fetch_row($rid);
    $c1 = odbc_result($rid, 1);
    /*
    *  The following call to odbc_result() will use SQLGetData supplying
    *  a buffer of whatever odbc.defaultlrl (or odbc_longreadlen) was set
    *  to. If the column contains more data than odbc.defaultlrl or
    *  odbc_longreadlen was set to then only part of the column data
    *  for the second column is retrieved in one call.
    */
    $c2 = odbc_result($rid, 2);
    /*
    *  Repeatedly calling odbc_result($rid, 2) will eventually retrieve
    *  all the column data in this row and that avoids a hang too.
    */
    //while(($d = odbc_result($rid, 2))) {$c = $c . $d;}
    /*
    *  Alternatively, calling odbc_fetch_row($rid) will avoid the hang.
    */
    //odbc_fetch_row($rid);
    /*
    *  If you are not going to get any more rows from this result-set then
    *  simply calling odbc_free_result($rid) will do.
    */
    // odbc_free_result($rid);
    echo "<p>" . $c1 . " " . $c2 ."\n";
}
else
{
    die( "odbc_num_fields() returns -1" );
}
$rid2 = odbc_prepare($dbc, "select * from hang1");
if (!$rid2) {
    echo $php_errormsg;
    die("Execute failed");
}
/*
*  If you don't do things right, the following call will hang.
*/
if (!odbc_execute($rid2)) {
echo $php_errormsg;
die("Execute failed");
}
$num_fields = odbc_num_fields($rid2);
if ( $num_fields == 0 )
{
    echo "Operation succeeded\n";
    echo "<p>".odbc_num_rows($rid2)." rows altered\n";
}
else if ($num_fields > 0)
{
    odbc_fetch_row($rid2);
    $c1 = odbc_result($rid2, 1);
    $c2 = odbc_result($rid2, 2);
    echo "<p>" . $c1 . " " . $c2 ."\n";

    $c1 = odbc_result($rid, 1);
    $c2 = odbc_result($rid, 2);
    echo "<p>" . $c1 . " " . $c2 ."\n";
}
else
{
    die( "odbc_num_fields() returns -1" );
}

odbc_free_result($rid);
odbc_free_result($rid2);
odbc_close($dbc);

Appendix B: Common problems accessing your ODBC data source

  1. isql fails to run because shared objects were not found.

    Make sure the dynamic linker can find the unixODBC Driver Manager shared object libodbc.xx and any shared objects required by the ODBC driver. This means adding their paths to LD_LIBRARY_PATH, LIBPATH, or SHLIBPATH environment variables or editing /etc/ld.so.conf, depending on your platform.

    This is described in the manuals for all Easysoft ODBC drivers.

  2. Apache and PHP fails because shared objects were not found.

    As above in point 1, but if you're running PHP as CGI then you may also have to add a SetEnv line to your Apache httpd.conf. For example:

    SetEnv LD_LIBRARY_PATH
/usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib
  3. To turn on PHP logging, refer to Testing ODBC in PHP.

  4. My column data is truncated at 4096 characters.

    Refer to odbc.defaultlrl in the PHP ini file.

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.