Using Oracle with Apache and PHP on Linux

William W. David

At a USENIX conference several years ago, Linus Torvalds was asked about the scarcity of database software for Linux. The problem, he answered, was that while operating systems are fun, databases are so ugly that you always have to pay someone to write them. MySQL and Postgres have proved that notion not entirely true, and until fairly recently, free database products were the only real option for database software on Linux. But now there are a number of commercial database products officially supported on Linux, including industry heavyweights Oracle and DB2. Those products, combined with the Apache Web server and the PHP programming language, make Linux a very attractive platform for developing and running Web-enabled database applications of all sizes. This article outlines the steps needed to access Oracle databases from PHP running under Apache.

Platform Notes

A development version of Oracle 9i is available at: http://www.oracle.com/ (The download requires registration.) PHP and Apache are available at: http://www.php.net/ and http://www.apache.org/.

Oracle 9i installs with only minor difficulties under the current versions of both SuSE and Red Hat Linux, and probably under other distributions as well. Oracle 8i and older distributions of Linux can also work, albeit with effort. An Oracle installation tutorial is beyond the scope of this article, but there are several helpful sites around the Web, including dbdomain: http://www.dbdomain.com/articles.htm/.

The examples in this article were written using Red Hat 7.3, Apache 1.3.23, PHP 4.1.2, and Oracle 9i (9.0.1.0.0.) They assume basic familiarity with the Apache and PHP build processes, Oracle in general, and a functioning Oracle database somewhere on your network. The examples use the sample database and users provided by the default Oracle installation.

Adding Oracle Support to Apache and PHP

There are several ways for PHP to talk to an Oracle database including using ODBC libraries, as well as two types of Oracle library support built into PHP. This article concentrates on using the Oracle native libraries -- specifically the Oracle 8 function calls. The older Oracle libraries still exist in PHP, but are largely deprecated by the OCI8 functions.

Before you can compile PHP with Oracle support, the computer doing the build must have Oracle network connectivity established. If the machine on which you're building Apache and PHP also hosts the Oracle database itself, you should already have everything you need. Otherwise, run the Oracle setup utility and install the Oracle client software. Test the installation by making sure you can connect to your database with sqlplus and log in with the one of the sample accounts Oracle provides (i.e., user name "scott"; password "tiger").

Once the Oracle client software is installed, it's time to add Oracle support to PHP. This is done using the --with-oci8= option to the PHP configuration script. The option should point to the location of the database client software (your $ORACLE_HOME directory, in Oracle terms).

The example PHP config.nice file used for this article looks like:

#! /bin/sh
#
# Created by configure

'./configure' \
'--with-apache=../apache_1.3.23' \
'--with-oci8=/db/oracle/product/901' \
'--enable-sigchild' \
"$@"

The --enable-sigchild line obviates a problem some users on the Internet have reported. The problem is characterized by a large number of defunct processes existing when using the Apache/PHP/Oracle combination under Linux.

After you've configured PHP and installed it, build Apache with PHP support enabled. Oracle support also requires that Apache be compiled with the pthread library included. The Apache configuration file with the minimal information for PHP and Oracle is very small:

#!/bin/sh
##
##  config.status -- APACI auto-generated configuration restore script
##
##  Use this shell script to re-run the APACI configure script for
##  restoring your configuration. Additional parameters can be supplied.
##

LIBS="-lpthread" \
./configure \
"--with-layout=Apache" \
"--prefix=/www/apache" \
"--activate-module=src/modules/php4/libphp4.a" \
"$@"

A real-world Apache configuration file will likely have many more options enabled in addition to those shown here.

For Oracle to work under Apache, the Web server needs to know a few things about the database environment. This is be done by adding several environment variables to the Apache startup script. These variables are the same ones that are typically set for normal Oracle users. You can add them to the configuration section of the apachectl script, which is located in the Apache installation's /bindirectory:

# ||||||||||||||||||| START CONFIGURATION SECTION  ||||||||||||||||||||
# --------------------                             --------------------
export ORACLE_SID=example
export ORACLE_BASE=/db/oracle
export ORACLE_HOME=/db/oracle/product/901
export ORA_NLS33=/db/oracle/product/901/ocommon/nls/admin/data
export PATH=$PATH:$ORACLE_HOME/bin:

An alternative, and probably more globally useful, solution is to keep them in an external place for all users and sourced where appropriate (from etc/profile, apachectl, other places as needed.) On the example system, they are in /db/dbparms, so the apachectl file would look like:

# ||||||||||||||||||| START CONFIGURATION SECTION  ||||||||||||||||||||
# --------------------                             --------------------
. /db/dbparms

After Apache is built and running, it's time to talk to the database.

Oracle Functions

There are several basic steps to running any Oracle SQL command under PHP, which include connecting to the database, parsing the SQL command, executing the command, and returning any results. Connecting to the database is accomplished using the OCILogon command. The command takes three arguments: database user name, password, and the name of the database itself:

$db_conn = ocilogon("scott", "tiger", "example.mydomain.net");

The database name parameter is optional, and if it isn't specified, the value of $ORACLE_SID is assumed.

Once you've connected to a database, the next thing is to run some SQL commands, which is accomplished with a combination of functions -- OCIParse and OCIExecute to generate results, and one of several statements to return the results in a usable form. For this example, I've used OCIFetchStatement, which puts the results of a query into an array, and OCIRowCount, which returns the number of affected rows from an update. The basic syntax of these commands is:

int OCIParse -- int conn, string query

int OCIExecute -- int stmt_id, int mode

int OCIFetchStatement -- int stmt_id, array variable

int OCIRowCount -- int stmt_idd

OCIParse takes as arguments the database connection generated in the previous example and an SQL string, and returns the statement ID of the parsed statement. OCIExecute takes the results of OCIParse and an optional mode, which tells the database whether to commit the results of the command (the default is to commit) and returns TRUE on success, FALSE on failure. OCIFetchStatement takes the statement ID of an executed statement and an array to populate with the results of the query, and returns the number of rows in the result set. OCIRowCount takes a statement ID and returns the number of rows altered by an update.

Here's an example showing all these commands in use. It updates an employee record in the sample database, and then queries for some information about employees named Smith and Jones:

<?
$db_conn = ocilogon("scott", "tiger", "example.mydomain.net");

$cmdstr = "update emp set job = 'techie' where ename = 'SMITH'";
$parsed = ociparse($db_conn, $cmdstr);
ociexecute($parsed);
$nrows = ocirowcount($parsed);
echo "$nrows row updated.<BR>\n";

$cmdstr = "select ename, job from emp where ename in ('SMITH', 'JONES')";
$parsed = ociparse($db_conn, $cmdstr);
ociexecute($parsed);
$nrows = ocifetchstatement($parsed, $results);
echo "Found: $nrows results<BR>\n";

echo "<TABLE BORDER=1>\n";
echo "<TR>\n";
print "<TH>Employee Name</TH>\n";
print "<TH>Job Title</TH>\n";
echo "</TR>\n";

for ($i = 0; $i < $nrows; $i++ ) {
  print "<TR>\n";
  echo "<TD>" . $results["ENAME"][$i] . "</TD>";
  echo "<TD>" . $results["JOB"][$i] . "</TD>";
  print "</TR>\n";
}

print "</TABLE>\n";
?>

The example should produce output similar to the following:

1 row updated.
Found: 2 results

Employee Name  | Job Title
--------------------------
SMITH          | techie
JONES          | MANAGER
Everyday Use

I've described how to get PHP and Apache to talk to a database, but the steps above are a little unwieldy for everyday use. Consider a typical Web site, with hundreds of pages (perhaps written by a team of programmers), with several queries each -- all writing the connect, parse, execute, and return sequences for each SQL statement. It's a good idea to abstract all the pieces that can be hidden into another layer to provide one well-known interface that all pages use, with consistent options and return values. This also makes it easy to take advantage of another feature -- persistent database connections. A persistent connection is set up once and reused for the entire PHP session, eliminating the overhead of repeatedly connecting to the database.

To that end, we put the following in an include file:

<?
function dbconnect()
{
   global $db_conn;

   $db_conn = ociplogon("scott", "tiger", "example.mydomain.net");
}


function perform_sql($cmdstr, &$results)
{

   global $db_conn;

   if (!$db_conn) {
      dbconnect();
   }

   $parsed = ociparse($db_conn, $cmdstr);
   ociexecute($parsed);

   if (func_num_args() == 2) {
      $nrows = ocifetchstatement($parsed, $results);
   }
   else {
      $nrows = ocirowcount($parsed);
   }

   return($nrows);
}
?>

and our example above becomes:

<?
include "dbutils.php";

$cmdstr = "update emp set job = 'techie' where ename = 'SMITH'";
$nrows = @perform_sql($cmdstr);
echo "$nrows row updated.<BR>\n";

$cmdstr = "select ename, job from emp where ename in ('SMITH', 'JONES')";
$nrows = perform_sql($cmdstr, $results);
echo "Found: $nrows results<BR>\n";


echo "<TABLE BORDER=1>\n";
echo "<TR>\n";
print "<TH>Employee Name</TH>\n";
print "<TH>Job Title</TH>\n";
echo "</TR>\n";

for ($i = 0; $i < $nrows; $i++ ) {
   print "<TR>\n";
   echo "<TD>" . $results["ENAME"][$i] . "</TD>";
   echo "<TD>" . $results["JOB"][$i] . "</TD>";
   print "</TR>\n";
}

print "</TABLE>\n";
?>

The sections of code that perform the database activity are more compact in the example, down to four lines from nine, but the HTML section is as ugly as ever. This savings becomes magnified on a large Web page containing many database calls. Notice the use of OCIPLogon in place of the original OCILogon, denoting a persistent connection. Also important is the consistency of the new interface. The same command, perform_sql(), is used for both update and select statements, rather than having two different ones (OCINumRows and OCIFetchInto), as in the original example.

Moving the OCI8-specific pieces out of the main page code also allows for some less obvious trickery. For example, to ensure that all string operations on the database have newlines removed, you might change the appropriate section of dbutils.php:

$cmdstr = ereg_replace("\n", "", $cmdstr);
$parsed = ociparse($db_conn, $cmdstr);
ociexecute($parsed);

Making the change here means the Web code is only changed in one place, and that the change applies to everybody. The same thing could be done for other characters (i.e., to eliminate dashes from social security numbers), to format date stamps, and so forth.

Summary

This article shows how to use Apache and PHP to access an Oracle database. All three are very flexible tools, and in practice the combination is quite pleasant to use. PHP can also be used as scripting language for accessing Oracle outside of a Web context. It has become my language of choice for database programs, whether Web-based, command line, or batch. With a little trial and error, it might be yours, too.

William W. David is a long-time UNIX and network administrator as well as sometime DBA and programmer. He's spent the past few years working with Web-enabled databases on the Internet, and is afraid they're here to stay. He can be reached at: wdavid@cowford.net.