Connecting to an Oracle 8 database using PHP4 is no simple task. Before calling me a moron for not using the most recent versions of these applications, you should know that I was attempting to create a mock production environment so I could deploy this solution on an existing server. Using a virtual machine to test stuff like this would be a wise thing to do since you can roll the entire system back if you mess up, just a tip.
What you need:
- A crusty, old Oracle 8 database server (might also work with 8.x)
- An equally crusty, old version of ApacheFriends XAMPP 1.4.4
- A slightly less crusty, but almost as old Oracle 9i Database Release 2 Client
- Strongly Recommended - A fresh Windows XP virtual machine (VM)
Honestly, it isn’t really that hard once you know which version of the Oracle client to use. Here is a rundown of each Oracle Client:
- Oracle 8 - PHP complains
- Oracle 9 - The only one that none of the components bitch about
- Oracle 10 - Oracle complains
- Oracle 11 - Oracle complains
Now for the steps:
- Fire up your Windows XP VM (Be sure to use “Bridged”, not “NAT” if you are using VMWare)
- Install XAMPP
- Install Oracle 9i R2 Client using the default install location settings (Be sure to select the “Runtime” installation type). I canceled the “Oracle Net Configuration Assistant” because I am not set up for that.
- Reboot the computer to make sure the PHP _SERVER["Path"] and _ENV["Path"] variables get updated.
- Create a tnsnames.ora file in the folder “C:\oracle\ora92\network\ADMIN” and include an entry for the server you want to access. Obviously, modify the information below to match your connection settings, here is an example: db=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=PROTOCOL=TCP)(Host=hostname)(Port=1521)))(CONNECT_DATA=(SID=db)))
- Open the Command Prompt and type: “tnsping db” If you cannot use the tnsping command, make sure your Oracle 9 installation is listed at the beginning of the “Path” variable for Windows (Contol Panel > System > Environment Variables). Ensure you have something like “C:\oracle\ora92\bin”. Don’t proceed until tnsping returns something to the effect of “Blah Blah…OK (10 msec)”. If you get “TNS-03505: Failed to resolve name”, check your tnsnames.ora file for errors.
- Now you need to edit the php.ini file. Make sure the line starting with extension_dir has the correct path to your extension directory. For example:
extension_dir = C:\xampp\php\extensions\
- Also in the php.ini file,uncomment the line “extension=php_oci8.dll” (delete the semi-colon) to activate the Oracle extension in PHP. You might have multiple php.ini files on your system, just make sure you update all of them.
- Start the Apache web server. If you get errors on start up, you might have missed a step.
- Use the phpinfo link on your local ApacheFriends start page or create a phpinfo() script of your own to display the current php configuration. If Oracle is enabled you should see a section like this:
oci8
OCI8 Support enabled
Revision $Revision: 1.183.2.12 $
- The very last trick is that you have to use the deprecated php4 oci commands rather than the shiny new php5 ones. From what I have seen so far oci* commands work, oci_* commands aren’t compatible with php4.
Try running this first to make sure everything is working:
<?php
$conn = OCILogon("username", "password", "db");
if ($conn){
print "Connection Established. ";
}
OCILogoff($conn);
print "Connection Closed.";
?>
Here is a fancier example which outputs the results of a select statement into an html table:
<?php
$conn = OCILogon("username", "password", "db");
$sql = "<PUT A SELECT STATEMENT IN HERE>";
$stmt = OCIParse($conn, $sql);
OCIExecute($stmt);
$rows = OCIFetchstatement($stmt,$results);
$keys = array_keys($results);
$table = "<table>\n <TR>\n";
foreach($keys as $key)
{
$table .= " <TH>$key</TH>\n";
}
$table .= " </TR>\n";
for($i=0;$i<$rows;$i++)
{
$table .= " <TR>";
foreach($results as $spalte)
{
$data = $spalte[$i];
$table .= ” <TD>$data</TD>”;
}
$table .=” </TR>”;
}
echo $table;
OCILogoff($conn);
?>