ODBC-PHP HOWTO                                

  1. Disclaimer
  2. Overview
  3. iODBC
  4. Openlink ODBC
  5. PHP / Apache Quick Install
  6. Sample connection
  7. Additional Sources of Information

Last updated May 15, 2000

  1. Disclaimer

The following is a HOWTO document for installing PHP libraries with Openlink ODBC or iODBC as an Apache module on Linux/Unix systems. Feel free to criticize, suggest modifications, or ask further questions. It is currently maintained by Andrew Hill of Openlink Software. ahill@openlinksw.com.

Prerequisites include basic Unix familiarity, such as creating directories and users, using an editor, etc.

This HOWTO is intended to assist in connecting php/apache to back end databases via ODBC in a development environment and should not take the place of thorough testing before deployment on a production system.

  1. Overview

 

ODBC (Open Database Connectivity) is an operating system and database independent communication API that allows a client application (productivity tool, other database, web page, custom application, etc.) to communicate via standard-based function calls to a back end database without relying on that vendor’s proprietary communication protocols. 

 

ODBC connections involve an application, driver manager, driver, and database.  The driver manager under Microsoft Windows platforms is the ODBC Control Panel.  The driver manager registers a set of ODBC driver connection parameters called a Data Source Name (DSN).  An application looks to the driver manager for a DSN, and then passes the connection parameters specified in the DSN to the appropriate driver, which makes the connection.

 

A driver manager also makes ODBC API call translations between different versions of the ODBC specification where necessary.  Thus, it’s possible to use a driver that is ODBC 2.5 compatible and make ODBC 3 calls against it, as long as the driver manager is ODBC 3 compliant, and can make the necessary translations.

 

Under non-Windows platforms, this driver manager will be supplied by third parties or sometimes bypassed altogether.  Openlink Software maintains an open-source driver manager and SDK called iODBC, which is available at www.iodbc.org.  As you will note above, the driver manager is not a driver, and does not enable connectivity by itself.  Compiling PHP with iODBC will make your php scripts ‘ODBC-aware’ and will allow you to use any ODBC driver with them, but does not provide you with a driver directly.  The SDK is also useful when building ODBC-compliant applications. 

 

Openlink uses a similar iODBC library in its commercial drivers, but one that is optimized for our drivers and may be out-of-synch with the iODBC distribution version.   This means that you should choose to compile PHP with either Openlink, or iODBC, but you do not need to do both. 

 

Openlink has a 2-tier driver architecture under linux/unix platforms.  This includes a driver and driver manager on the client side, and a request broker and database agent on the server side.  The request broker listens for connection calls and initiates a connection between the database agent and the database.  Openlink’s database agent speaks directly to the CLI (Call Level Interface) of the database, bypassing the vendor specific communication layer, avoiding the addition of  an additional communication layer.  This combined with a thorough and efficient implementation of the ODBC specification, produces very fast drivers with robust ODBC support.  Often SQL syntax that is not explicitly supported by database vendors is enforced onto the database by Openlink’s drivers.  Full ANSI SQL92 support is enabled, allowing such things as natural joins, is bi-directional scrollable cursor support, etc.

 

 

N.B.  Under linux/unix platforms, the driver manager and associated library files are located by setting environment variables.  These must be set before compiling apache and php with ODBC support. 

 

 

  1. iODBC

Download the Openlink-maintained iODBC source code or RPM package from http://www.iodbc.org. Compile source or install via RPM in your system:

To compile: (also refer to the README and INSTALL file to review changes)

    1. download latest source code. At the time of this writing, it is version 2.50.3 with a beta of 3.0.2 (ODBC 3.0) available.
    2. gunzip libiodbc-2.50.3
    3. tar –xvf libiodbc-2.50.3
    4. cd libiodbc-2.50.3
    5. make
    6. make check
    7. make install

To install via RPM

    1. determine your libc version. The command: ls -l /lib/libc* should give you libc.so.5 (which is libc5) or libc.so.6 (which is glibc2). RPM can also be queried for this information, via: rpm -qa | grep libc
    2. download the correct libiodbc RPM for your libc version.
    3. rpm –ivh libiodbc-2.50.3-i386-glibc2.rpm (replace with the version you downloaded) You may also use the –prefix=/path/to/directory to relocate the components as you wish.

Repeat the above instructions for the SDK and install script available at the same location. The odbcsdk will contain an /odbcsdk/doc/odbc.doc file that includes instructions for setting up odbc data sources. After setting up the SDK, you can test ODBC using the odbctest binary in the /odbcsdk/examples/ directory (see odbctest comments in the next section).

 

  1. Openlink ODBC

Openlink Multi-tier drivers involve installation on both the client and server sides of your architecture. Proceed to http://www.openlinksw.com and follow the "Product Availability and Download" link. Select Multi-Tier drivers, and answer the questions about server/client/database versions. For testing purposes this HOWTO will assume the unix/linux machine is both your client and server.

The Openlink odbcsdk contains many tools and utilities, as well as source code, and is available at the same location.

    1. mkdir /usr/local/openlink.
    2. create an openlink user on your system, with home directory /usr/local/openlink.
    3. download the drivers, the request broker, database agent, and installation script to the directory.
    4. repeat the product selection process for the odbcsdk, downloading the .taz file into the same directory.
    5. run the install script: ./install.sh or sh install.sh, and follow the prompts. It is safe to take the defaults, but be aware of what they are (e.g. admin/admin is default for login/pass in the web-based Admin Assistant).
    6. at this point, you should have a fully installed, if not configured version of Openlink.
    7. Examine your /usr/local/openlink directory for a .profile or .bash-profile. Also examine the contents of openlink.sh, and verify that the paths listed match your installation. Cat the contents of the edited openlink.sh into your .profile or .bash-profile. (create one if one doesn’t exist). The openlink.sh file contains environment variable settings. The ..profile will be run automatically when you login as openlink, and set the appropriate variables. For now, run it manually:# . ./.profile. If you have a distribution that has another default profile, such as Red Hat’s .bash_profile, edit this file and add the line: . ./.profile to ensure it’s picked up when you login.
    8. Bring up the Openlink request broker oplrqb +loglevel 7
    9. Follow the Openlink documentation for creating Data Source Names (DSN’s) in the odbc.ini file, and for configuring the database agent against your installed database.
    10. Run the odbctest application in the /openlink/samples/ODBC/ directory:# ../odbctest
    11. Type a "?" to make sure your DSN appears, and enter DSN=dsnname at the prompt.  Note that just putting in the DSN at the prompt without a DSN= string will not work, as this prompt takes many other parameters and needs a qualifying string before each one.
    12. Congratulations, you have a working Multi-Tier ODBC connection.

 

5.      PHP/Apache Quick Install Instructions (copied from the PHP installation section of the manual) 

Please choose to use either –with-openlink or –with-iodbc.

Download most recent source of apache from http://www.apache.org/dist/

Download the most recent version of PHP from www.php.net.

Check an output of your env command for the values of LD_LIBRARY_PATH.  If not applied, do so, either by running your profile (section 4g above) or by manually setting it:  export LD_LIBRARY_PATH=/path/to/iodbc/libraries/directory

    1. gunzip apache_1.3.x.tar.gz
    2. tar xvf apache_1.3.x.tar
    3. gunzip php-3.0.x.tar.gz
    4. tar xvf php-3.0.x.tar
    5. cd apache_1.3.x
    6. ./configure --prefix=/www
    7. cd ../php-3.0.x
    8. ./configure --with-openlink=/path/to/openlink/ --with-iodbc=/path/to/iodbc/ --with-apache=../apache_1.3.x --enable-track-vars
    9. make
    10. make install
    11. cd ../apache_1.3.x
    12. ./configure --prefix=/www --activate-module=src/modules/php3/libphp3.a
    13. make
    14. make install
    15. cd ../php-3.0.x
    16. cp php3.ini-dist /usr/local/lib/php3.ini. You can edit /usr/local/lib/php3.ini file to set PHP options. If you prefer this file in another location, use --with-config-file-path=/path in step h.
    17. Edit your httpd.conf or srm.conf file and add (or uncomment): AddType application/x-httpd-php3 .php3 (You can choose any extension you wish here, .php3 is simply the one we suggest.)
    18. Use your normal procedure for starting the Apache server. (You must stop and restart the server, not just cause the server to reload by using a HUP or USR1 signal.) 
 

6.     Sample Connection

This code can be saved at test.php3 in your /www/htdocs/ and you can examine it via a browser pointing to http://domain_name/test.php3.

Notice the putenv’s.  These variables include ones necessary for an Openlink driver connection.  Third-party drivers with iODBC may require different environment variables.  I usually put all the necessary variables in an putenv.inc file, and ‘require’ it at the beginning of each script that wants ODBC connectivity.  Not a huge deal for a small site with 3 putenv’s on each page, but in practice you could have many pages with many environment variables on each one.  It’s cleaner to only be able to make mistakes in one place.  This also allows you to  apply different sets of env’s to different scripts, interchanging values with great flexibility.

<?
putenv("LD_LIBRARY_PATH=/usr/local/openlink/odbcsdk/lib");
putenv("ODBCINSTINI=/usr/local/openlink/odbcinst.ini");
putenv("ODBCINI=/usr/local/openlink/odbc.ini");
$dsn="DSN=OracleLocal"; // this is a valid DSN set up in the above odbc.ini file, tested in odbctest
$user="scott"; //default user for the demo Oracle database
$password="tiger"; //default password for demo Oracle database
 
$sql="SELECT * FROM EMP";  
// directly execute mode 
if ($conn_id=odbc_connect("$dsn","","")){
    echo "connected to DSN: $dsn";
    if($result=odbc_do($conn_id, $sql)) {
        echo "executing '$sql'";
         echo "Results: ";
        odbc_result_all($result);
        echo "freeing result";
        odbc_free_result($result);
    }else{
        echo "can not execute '$sql' ";
    }
    echo "closing connection $conn_id";
    odbc_close($conn_id);
}else{
    echo "can not connect to DSN: $dsn ";
}
?>
 
  1. Additional Sources of Info