A long time ago I was tasked with creating a PHP application that would talk to our Sybase database that drives our entire enterprise at work. This is not normally a big thing to do as one can simply enable the Sybase extension for PHP and have a database connection generally within minutes. However there was one thing that was an absolute necessity in this application that made it significantly more difficult to get it working: some parts of the application would be reliant upon stored procedures that returned multiple result sets.
First off let me say that any time you have the option to build stored procedures for your database quieries, do it. They are faster, safer, compiled and centralized and they keep your code from being littered with query builders all over the place. That said, this is neither the time nor the place to talk about the merits of stored procedures in application development. Just know that where I work there are no direct queries ever allowed to touch our database servers.
Knowing that I had to work under the requirement of handling multiple Sybase result sets I set out to find out how to do it and what I found was alarming. Few database extensions in PHP handle multiple result sets. In fact, as I was looking around it seemed that only the MySQLi extension and the SQL Server extension handled multiple result sets. But after a little searching around I found something called the “PHP Sybase CT driver enhancements” project on Sourceforge that essentially provided prepared statement handling and multiple result set handling for the PHP Sybase Extension. However, after trying to install it it became evident that the extension, as it was written, was still not usable in the state it was offered up for download in.
Not to worry, I have modified the package for compiling within a PHP 5 environment and have made a few changes to it that should allow it to be used as is. The source code archive file can be downloaded (in tar.gz format) by clicking here.
Before we get started we need to cover a few assumptions:
- You have a licensed copy of Sybase Adaptive Server
I have not tried this against any Sybase database server other than Adaptive server.
- You have the Sybase client installed or at the very least have the installer available
For this exercise we are using a Red Hat RPM of the Sybase 12.5 client. Yes, we are installing on Ubuntu, which is essentially Debian and therefore does not use RPM files, but we will burn that bridge when we come to it.
- You are comfortable building PHP extensions from source
There is no way around this. In order to get this to work you will need to be able to build from source.
- You have the php-devel package installed on the machine you building this extension for
If you don’t have it, a quick
sudo apt-get install php5-devshould do the trick. This will be necessary to build the Sybase extension from source.
Ok, these are the steps I took in order to build the enhanced Sybase extension for PHP on my Ubuntu 8.10 desktop machine starting with Sybase ASE client and common packages in Red Hat RPM format.
- Convert the Sybase 12.5 OpenClient and Sybase 12.5 Common RPM archives to DEB packages and install them
To do this I used the alien package converter tool and, while inside the directory where the RPMs were living, issued the following command:
$ alien -k sybase-common-184.108.40.206de-1.i386.rpm
$ alien -k sybase-openclient-220.127.116.11esd-1.i386.rpm
This created two new archives, in the directory I was in, in debian format named
I then issued the following commands to install them:
$ sudo dpkg --install sybase-common-18.104.22.168DE-1_i386.deb
$ sudo dpkg --install sybase-openclient-22.214.171.124ESD-1_i386.deb
This made a directory named sybase-12.5 in the /opt directory.
- Prepare your environment to build the extension
To make things easier (and a little more compatible with the Red Hat way of doing things) I created a link inside of /top called sybase and pointed it to the sybase-12.5 directory.
$ cd /opt
$ ln -s -T /opt/sybase-12.5 sybase
I then had to export some environment vars that are needed by the extension and the web server in order to handle communication with the Sybase server:
$ export SYBASE=/opt/sybase
$ export SYBASE_OCS=OCS-12_5
$ export PATH=/opt/sybase/OCS-12_5/bin:$PATH
$ export LD_LIBRARY_PATH=/opt/sybase/OCS-12_5/lib:/opt/sybase/OCS-12_5/lib3p:$LD_LIBRARY_PATH
$ export INCLUDE=/opt/sybase/OCS-12_5/include:$INCLUDE
$ export LIB=/opt/sybase/OCS-12_5/lib:$LIB
Yes, I know that each one of these commands could have been placed in a file and sourced, but for some reason source is not available to my installation of Ubuntu so it was in fact easier and faster for me to do it this way. Do this how you will, but remember the values because you will need these later.
- Build and install the php-sybase-ct extension from source against the Sybase client you just installed
Remember that package I told you about earlier? The one that I said you could download? If not, get it now and unpack it to a directory somewhere where you have permission to unpack stuff on your system. For me, it was ~/Temp.
Change to the directory you unpacked the source code to and configure it for make and installation using the php-devel package:
$ cd ~/Temp
$ ./configure --with-sybase-ct=$SYBASE/$SYBASE_OCS
$ sudo make install
Find out where your extensions directory is on your machine and quickly check it to make sure there is a php_sybase_ct.so file living in it. On my machine the extension directory is /usr/lib/php5/20060613+lfs/. Yours may be different.
At this point you can safely do a
sudo make cleanbut you might want to hold off on that until it is all working in case you need to rebuild at all. It does happen from time to time. Just sayin’.
- Configure PHP to use the new extension
Now we need to tell PHP to use the new extension we just built. To do that we need to create an ini file for the extension and put it inside of the extensions directory where PHP can find it. On my machine PHP looks for ini files to parse in /etc/php5/conf.d/ so naturally that is where I am going to go to to tell PHP to use this extension.
$ cd /etc/php5/conf.d/
Now we need to create an ini file and put into a directive to load the extension. You can use whatever editor you like. I prefer to use vim:
$ sudo vim sybase_ct.ini
Inside this file place the following two lines:
; Enable the sybase extension
- Configure your environment to load the correct environment variables whenever the machine starts
This one caused me fits for a long time in Ubuntu. In order to ensure that you can use the Sybase extension from both the CLI and the web server you will need to take all of the environment variables that exported prior to building the extension and place them in both the /etc/profile startup script AND the web servers environment variable setting script. This caused countless hours of frustration and anger for me and I hope I can save you some angst with this little snippet.
Add the environment vars to /etc/profile. You can use any editor you like. I like vim:
$ sudo vim /etc/profile
At the end of the file add the environment vars:
Now add these same entries into your web server’s environment variables. I am using apache and assuming you are to. If not, consult your web server’s documentation for how to do this:
$ sudo vim /etc/apache2/envvars
Now add these entries to the end of the file:
- Restart your web server
Like everything that involves a change to the PHP environment or configuration on your machine, restart the web server. I am assuming this is being built upon an apache server. If not, you will need to know how to stop and start your web server or, at the very least, know how to reboot your machine:
$ sudo apache2ctl stop
$ sudo apache2ctl start
Run a PHP info page or CLI call to see if it is loaded:
$ php -m
You should see sybase_ct. If not, something went wrong. If so, you are now golden.
At this point you should be able to run queries against your Sybase server AND handle multiple result sets using the sybase_next_result() function. There is no documentation for this function, but a quick read through of the mssql_next_result() function will tell pretty well how to use it.
Enjoy! And if this was at all helpful please leave a comment to let me know.
Notes on the PHP Sybase CT Enhanced extension
Some things to keep in mind when using this extension:
- This extension is coded in PHP 4 source.
- When I got my hands on it the code was still much in development phase. Not much has changed about that other than I removed debug output from it.
- It does not handle connection failure well when the server does not respond. So unwell in fact that internally it causes segmentation faults and results in blank web pages upon failure. I do not know how to fix that.
- If you know how to program in c or want to make it better, please feel free to do so. I have contacted the original authors of this extension and of the three only one has done any real work on it and none have worked on it in the last five years and really do not intend to. I was given permission to distribute it and modify it. But I really do not know what I am doing in c. Yet. 😉