Enabling ODBC support in PHP under Apache

Contents:

1.0 Introduction

This document contains instructions on making ODBC support in PHP work under Apache and specifically with Easysoft ODBC drivers.

Easysoft have tested our ODBC drivers with Apache 1.3.n - 2.0.50 and PHP 3.0.0 - 5.0.0 and we expect them to be compatible past these releases.

Due to the long history of Apache and PHP releases, there are many different ways to build/install them with ODBC support. We have attempted to document these methods with the most usual/popular first. If you are using an older copy of Apache or PHP please see "Build/Install Methods (Apache 1.n and PHP (3.n|4.n)".

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

2.0 PHP 5.n and Apache 2.0.n

For Apache 2.0.x and PHP 5.x the recommended method is detailed here. If you are building Apache 1.n and PHP 4.n see sections below.

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

2.1 Building PHP 5.n and Apache 2.0.n yourself

This section describes how to build PHP 5.n and Apache 2.0.n 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.

Get Apache and PHP source distributions from the sites listed in the Appendix A, Useful Links.

Untar Apache, cd into the apache directory.

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

Build Apache with --enable--so. This enables the DSO module which 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 see 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 unixODBC that comes with the Easysoft products or whether you have built/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 RPM etc 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 dir may differ 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. e.g.
        extension_dir = 
            "/usr/local/lib/php/extensions/no-debug-non-zts-20040412"
    
  2. extension is set to "extension=odbc.so" where the .so depends on the platform but will be a file in extension_dir. This causes odbc.XX to be loaded automatically. The .xx is platform dependent but in SYSV systems is always .so (typically .sl on HP-UX and .a on AIX).

Now restart Apache. e.g.

/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 which can run. See 5.0 Testing ODBC in PHP

2.1.1 NOTE: Apache 2

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 OOB distribution and you are advised to build PHP with the unixODBC driver manager included with OOB. Thread-safe OOB distributions are those containing "-mt" in the distribution filename.

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

You do not need to use a threaded model in Apache 2 - see httpd.conf.

2.2 Installing PHP 4 and Apache 2 on RedHat 9 or from RPMs

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

Installing Packages from a Shell Prompt

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

RedHat 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.

You can obtain the unixODBC RPMs from RedHat (see Appendix A. Useful Links).

Install 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 - see 5.0 Testing ODBC in PHP.

You can obtain the PHP RPMs from RedHat (see Appendix A. Useful Links).

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 see 5.0 Testing ODBC in PHP.

Installing Packages from the KDE Desktop

Read this section if you usually add RedHat packages from the K Desktop Environment.

To install the Apache, PHP, and unixODBC packages

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

To display unixODBC configuration information

  1. On the Start Applications menu, click System Tools, and then click Terminal.
  2. At the shell prompt type:
    /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, click System Settings, and then click Server Settings. Then click Services.
  2. In the Service Configuration Tool, in the Services list, click the httpd check box.
  3. Click the Start button.
  4. Click OK when prompted.
  5. On the File menu, click Save Changes.
  6. On the File menu, click 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, see 5.0 Testing ODBC in PHP.

3.0 Build/Install Methods (Apache 1.n and PHP (3.n|4.n)

There are four ways to build PHP 3|4 for use with Apache 1:

  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 all exists 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/change PHP without changing Apache. Many Linux distributions (RedHat etc) distribute Apache like this so if you do not 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 [3] above but you need to read carefully the instructions at the end of PHP's INSTALL file and see "Using PHP 3 and PHP 4 as concurrent apache modules" later in this document.

3.1 NOTE: specific configure option in PHP

From PHP4b3 you can configure PHP for OOB with the --with-esoob option instead of using the --with-custom-odbc option. However, if you have installed a standard OOB 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).

3.2 NOTE: Building multiple drivers into PHP

Please note that you cannot build PHP with multiple ODBC drivers or driver managers e.g. 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 RPM etc (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.

3.3 Building PHP (3.n|4.n) with ODBC support for Apache (1.n) DSO

If you already have Apache built or it came with your UNIX distribution then 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 - you need to look 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 configure for Apache.

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 RedHat 7.x etc 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.

3.4 Statically Building post-PHP4b3 and Apache (1.n) with OOB

Please 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 please change /usr/local to the installation path specified when the 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 OOB you should follow this recipe:

  1. Download Apache. (see Apache.org).

  2. Download PHP. (see PHP)

  3. Refer to the instructions in php-4.y.z/INSTALL and apache_1.3.x/INSTALL. In particular, php-4.y.z/INSTALL shows step by step instructions for building with MySql (if you are not going to be using MySQL then you can omit the --with-mysql). To build PHP with the Easysoft ODBC-ODBC Bridge must add

    --with-esoob=/base_oob_install_dir/easysoft/oob/client
    

    where /base_oob_install_dir is the base directory where OOB was installed. The install directory defaults to /usr/local/easysoft/oob/client and as the install symbolically links /usr/local/easysoft to you_install_dir/easysoft you should not need to specify the directory.

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

    -L/base_oob_install_dir/oob/easysoft/client
    

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

    • add

      -L /base_oob_install_dir/lib -lesrpc -lessupp -lesextra
      

      to LDFLAGS. LDFLAGS tells the linker where to find the OOB 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 now 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 will probably see multiple "cannot find libesrpc.so messages" during configure.

      Export these environment variables before running the Apache configure.

3.5 Statically Building pre-PHP4b3 and Apache (1.n) with OOB

Please read the INSTALL files in the PHP and Apache distributions before following these instructions as they may provide more uptodate 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 please 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 all ODBC drivers you install under the driver manager. 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 OOB you should follow this recipe:

  1. Download Apache. (Apache.org).

  2. Download PHP. (see PHP)

  3. Refer to the instructions in php-x.y.z/INSTALL and apache_1.3.x/INSTALL. In particular, php-x.y.z/INSTALL shows step by step instructions for building with MySql (if you are not going to be using MySQL then you can omit the --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 OOB 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
    

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

    • add

      -L /base_oob_install_dir/lib -lesrpc -lessupp -lesextra
      

      to LDFLAGS. LDFLAGS tells the linker where to find the OOB 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 now 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 will probably see multiple "cannot find libesrpc.so messages" during configure.

      Export these environment variables before running the Apache configure.

Here is 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

4.0 Using PHP 3 and PHP 4 as concurrent Apache modules

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

Easysoft recommend building the PHPs with the unixODBC driver manager and not directly with any Easysoft products when you want PHP3 and PHP4.

We tried this successfully on 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 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

5.0 Testing ODBC in PHP

This section will show 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/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 a RPM or package it will be in various places but the unixODBC programs should already be on your path (probably /usr/bin).

    You will 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. e.g. for OOB 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 account you must put the DSNs you want to access with PHP in the SYSTEM odbc.ini (in the above 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 will need to create one - see instructions that come with the Easysoft ODBC driver you are using usually found in /usr/local/easysoft/DRIVER/docs.

    If the DSN you are using requires a username and password to login to the database you need to have these to hand.

    Run:

    UNIXODBCBIN/isql -v DSN; 
      database_username; database_password
    

    where DSN is the name of your DSN and database_username 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 type in SQL. If it doesn't then there are a number of possibilities and you should:

    • Check you've set any necessary environment variables like ORACLE_HOME, LD_LIBRARY_PATH etc (see the manual for the Easysoft ODBC driver you are using).
    • consult the documentation that came with the ODBC driver.
    • contact Easysoft support if you have a support contract or are trialling.

    A few common problems are listed in Appendix C: Common problems accessing your DSN.

    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 so it dynamically loads PHP then make sure your Apache httpd.conf file contains a LoadModule line like the following.

        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 RedHat 9 package, check for a LoadModule entry in php.conf instead. By default, RedHat installs php.conf in the /etc/httpd/conf.d directory.

  3. Make sure you have amended your Apache httpd.conf file to tell it 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. e.g.

    <html>
    <head>
    <title>Test</title>
    </head>
    <body>
    <p>
    You should see 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 named in your Apache configuration file(s) (e.g. httpd.conf) with the ErrorLog setting.
    • Did you copy php.ini to the correct place?

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

    • Turn on PHP logging - see 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, Parser errors.
      • "error_log = filename" defines the file errors/warnings will be sent to.

Now try ODBC support under Apache/PHP:

  1. Locate a directory in Apache where you can store .phtml files you want executing by PHP. 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 with RedHat.

    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.

    For example, OOB installs a SYSTEM DSN called "demo" with database login of username "demo" and password "easysoft" and contains a table called "oobdist_contact".

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

Appendix A. Useful Links

Appendix B

B.1 Potential leak of an ODBC connection in PHP

On 3rd April 2001 we posted the following to the OOB news group:

----------------------------------------
Subject: Notice: bug in PHP can cause Apache/PHP to leak 
  ODBC connections
Date: Tue, 03 Apr 2001 09:28:38 +0100
From: "Martin J. Evans" <martin@easysoft.com>
Organization: Easysoft Limited
CC: support@easysoft.com
Newsgroups: easysoft.public.da2k.odbc-odbc-bridge


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 OOB Server or a process created
by the OOB 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 OOB 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 OOB
Server to MultiProcess (i.e. create a new process for each
ODBC connection) and then (example for MS 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 OOB ODBC connection is made
and you will see a new esoobserver process on your server machine
(as the OOB 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 OOB client socket and disconnect the ODBC thread/process
at the server. The same happens when the OOB 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.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
----------------------------------------

B.2 hanging code when using multiple active statements to MS SQL Server

What follows is 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
*  MS SQL Server. The setup is:
*
*  browser <=> Apache/PHP<=>unixODBC DM<=>OOBClient <network>
*  <=>OOB Server<=>MS SQL Server ODBC driver<=>MS SQL Server.
*
*  The actual hang occurs in the MS 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 MS's odbctest utility.
*
*  e.g.
*
*  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
*    MS SQL Server ODBC driver will allow us to have multiple active
*    statements.
*  SQLExecDirect(stmt1, "select * from hang3")
*  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 see the code
*    is looping (spin waiting) inside the MS 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 of course 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? See the code below but stick to these 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 etc)
*      (in MS SQL Server, text, image etc) 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 that odbc.defaultlrl or odbc_longreadlen is set to that you 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/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 OOB Server that the OOB 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 alot of CPU and you
*  are unable to kill it. When the OOB Server is running Multi-threaded
*  you can simply stop the OOB Server Service and restart it to get rid of
*  the thread that is looping in side the MS SQL Server ODBC driver.
*
*  A small note on using Multiple Active Statements in PHP to MS SQL Server
*  ========================================================================
*
*  PHP 4.2.0 now uses a forward-only cursor and not a dynamic one.
*
*  If you are connecting PHP to MS SQL Server and you do not need
*  multiple active statements then it may be better to disable 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 disable 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 disabled, 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 C. Common problems accessing your DSN

The Easysoft FAQ at the Easysoft web site is a very useful resource for finding the answers to common problems.

  1. If 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/PHP fails because shared objects were not found.

    As above in 1 but if you are running PHP as CGI then you may also have to add a SetEnv line to your Apache httpd.conf e.g.

        SetEnv LD_LIBRARY_PATH
            /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib
    
  3. To turn on PHP logging see 5.0 Testing ODBC in PHP above.
  4. My column data is truncated at 4096 characters.

    See odbc.defaultlrl in the PHP ini file.


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.