Adventures in Oracle
Ok, so this is going to be a geek post of monumental proportions
So recently it has become necessary for me to get PHP working with an Oracle database. If you also see the need for this in the future you might want to listen up. There's also some interesting tricks in here for wrangling some Mac OS X stuff.
Important note: Currently the Oracle instant client libraries are only available for PPC, keep this in mind before continuing.
First things first I started with a Mac OS X 10.5 client system. Important to know that 10.5 comes with PHP 5.2.4 already installed, however it DOES NOT come with the necessary OCI8 (Oracle) extension built in. Nor does it come with some of the finerys like Lib GD (for images). So first we need to get a better version of PHP on the system.
The best place to go for PHP when you're a Mac user is here:
http://www.entropy.ch/software/macosx/php/
The entropy build of PHP includes pretty much everything you could want and is an easy package install. :D There is only one problem. As of this post there is no version with all the architectures built in for the newest processors. All you get is ppc and i386. There is a beta of 5.2.5 but it doesn't contain all the necessary extensions, so at the moment stick with 5.2.4. Now this is all well and good if you're using a G4 or one of the first Intel based Macs. But sadly if you have a newer machine you're out of luck (G5s = ppc64 & Mac Pros = x86_64). And since at the moment the binary will only load modules of its same architecture (if it has it that is) we are screwed.
So the first step is to thin out our httpd binary to a version that can be run on whatever machine we are going to use. I'm working on a G5 so here's what I did to get it to work.
cd /usr/sbin
lipo -info httpd
mv httpd httpd.ub
lipo -thin ppc7400 httpd.ub -output httpd.ppc
ln -s httpd.ppc httpd
Ok, now we have a version of httpd that will load our PHP library. Next step is to install it. I'm using Apache 2 so I used this link:
http://www2.entropy.ch/download/entropy-php-5.2.4-1-apache2.tar.gz
Sadly to say the package here is a little broken so it requires that we do a little massaging of our system before installing. If you use Apache2 then you should symlink your httpd directory to it:
cd /etc
ln -s apache2 httpd
Next go ahead and install the package. After that is done we need to install the oracle libraries. Get the instant client and the sqlplus stuff here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/macsoft.html
After downloading and unzipping the folder for both I would recommend organizing them on your system as follows:
/usr/local/oracle
/usr/local/oracle/bin
/usr/local/oracle/bin/sqlplus
/usr/local/oracle/lib
/usr/local/oracle/lib/classes12.jar
/usr/local/oracle/lib/libclntsh.dylib.10.1
/usr/local/oracle/lib/libnnz10.dylib
/usr/local/oracle/lib/libocci.dylib.10.1
/usr/local/oracle/lib/libociei.dylib
/usr/local/oracle/lib/libocijdbc10.dylib
/usr/local/oracle/lib/libsqlplus.dylib
/usr/local/oracle/lib/ojdbc14.jar
/usr/local/oracle/libclntsh.dylib.10.1
/usr/local/oracle/network
/usr/local/oracle/network/tnsnames.ora
Now if you know about Oracle you'll know that the tnsnames.ora file will contain information about your databases.
The next step is to configure httpd to run with the environment variables necessary to tell PHP where to find your Oracle stuff. Now I tried 4 different methods before finding the proper one for Mac OS X that would translate properly. Don't bother trying to set your environment variables in your PHP script or putting a SETENV in your httpd.conf file, for some reason this just doesn't work. Also putting attributes into /usr/sbin/envvars seems to be ignored as well. It's possible that when your first httpd server kicks off other children servers that some other method is employed, but so far I've found that this works....
You'll want to edit your LaunchDaemon file for httpd. Find that here:
/System/Library/LaunchDaemons/org.apache.httpd.plist
You'll want to add your environment variables into that file like so:
EnvironmentVariables
DYLD_LIBRARY_PATH
/usr/local/oracle/lib
LD_LIBRARY_PATH
/usr/local/oracle/lib
ORACLE_HOME
/usr/local/oracle
TNS_ADMIN
/usr/local/oracle/network
Label
org.apache.httpd
OnDemand
ProgramArguments
/usr/sbin/httpd
-D
FOREGROUND
SHAuthorizationRight
system.preferences
Once all that is done you should be about ready to go. Restart apache using apachectl and attempt some OCI magic. In the below example it is not necessarily needed to put all that TNS nonsense in there. Just put your database alias from your TNS file into that area and you'll be fine. Lookup the reference on the OCI_CONNECT function for more examples on that.
Ok I think I covered everything. If you have any questions just comment or email me. I'll try to help if I can. :)
Happy hacking!
So recently it has become necessary for me to get PHP working with an Oracle database. If you also see the need for this in the future you might want to listen up. There's also some interesting tricks in here for wrangling some Mac OS X stuff.
Important note: Currently the Oracle instant client libraries are only available for PPC, keep this in mind before continuing.
First things first I started with a Mac OS X 10.5 client system. Important to know that 10.5 comes with PHP 5.2.4 already installed, however it DOES NOT come with the necessary OCI8 (Oracle) extension built in. Nor does it come with some of the finerys like Lib GD (for images). So first we need to get a better version of PHP on the system.
The best place to go for PHP when you're a Mac user is here:
http://www.entropy.ch/software/macosx/php/
The entropy build of PHP includes pretty much everything you could want and is an easy package install. :D There is only one problem. As of this post there is no version with all the architectures built in for the newest processors. All you get is ppc and i386. There is a beta of 5.2.5 but it doesn't contain all the necessary extensions, so at the moment stick with 5.2.4. Now this is all well and good if you're using a G4 or one of the first Intel based Macs. But sadly if you have a newer machine you're out of luck (G5s = ppc64 & Mac Pros = x86_64). And since at the moment the binary will only load modules of its same architecture (if it has it that is) we are screwed.
So the first step is to thin out our httpd binary to a version that can be run on whatever machine we are going to use. I'm working on a G5 so here's what I did to get it to work.
cd /usr/sbin
lipo -info httpd
mv httpd httpd.ub
lipo -thin ppc7400 httpd.ub -output httpd.ppc
ln -s httpd.ppc httpd
Ok, now we have a version of httpd that will load our PHP library. Next step is to install it. I'm using Apache 2 so I used this link:
http://www2.entropy.ch/download/entropy-php-5.2.4-1-apache2.tar.gz
Sadly to say the package here is a little broken so it requires that we do a little massaging of our system before installing. If you use Apache2 then you should symlink your httpd directory to it:
cd /etc
ln -s apache2 httpd
Next go ahead and install the package. After that is done we need to install the oracle libraries. Get the instant client and the sqlplus stuff here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/macsoft.html
After downloading and unzipping the folder for both I would recommend organizing them on your system as follows:
/usr/local/oracle
/usr/local/oracle/bin
/usr/local/oracle/bin/sqlplus
/usr/local/oracle/lib
/usr/local/oracle/lib/classes12.jar
/usr/local/oracle/lib/libclntsh.dylib.10.1
/usr/local/oracle/lib/libnnz10.dylib
/usr/local/oracle/lib/libocci.dylib.10.1
/usr/local/oracle/lib/libociei.dylib
/usr/local/oracle/lib/libocijdbc10.dylib
/usr/local/oracle/lib/libsqlplus.dylib
/usr/local/oracle/lib/ojdbc14.jar
/usr/local/oracle/libclntsh.dylib.10.1
/usr/local/oracle/network
/usr/local/oracle/network/tnsnames.ora
Now if you know about Oracle you'll know that the tnsnames.ora file will contain information about your databases.
The next step is to configure httpd to run with the environment variables necessary to tell PHP where to find your Oracle stuff. Now I tried 4 different methods before finding the proper one for Mac OS X that would translate properly. Don't bother trying to set your environment variables in your PHP script or putting a SETENV in your httpd.conf file, for some reason this just doesn't work. Also putting attributes into /usr/sbin/envvars seems to be ignored as well. It's possible that when your first httpd server kicks off other children servers that some other method is employed, but so far I've found that this works....
You'll want to edit your LaunchDaemon file for httpd. Find that here:
/System/Library/LaunchDaemons/org.apache.httpd.plist
You'll want to add your environment variables into that file like so:
EnvironmentVariables
DYLD_LIBRARY_PATH
/usr/local/oracle/lib
LD_LIBRARY_PATH
/usr/local/oracle/lib
ORACLE_HOME
/usr/local/oracle
TNS_ADMIN
/usr/local/oracle/network
Label
org.apache.httpd
OnDemand
ProgramArguments
/usr/sbin/httpd
-D
FOREGROUND
SHAuthorizationRight
system.preferences
Once all that is done you should be about ready to go. Restart apache using apachectl and attempt some OCI magic. In the below example it is not necessarily needed to put all that TNS nonsense in there. Just put your database alias from your TNS file into that area and you'll be fine. Lookup the reference on the OCI_CONNECT function for more examples on that.
Ok I think I covered everything. If you have any questions just comment or email me. I'll try to help if I can. :)
Happy hacking!