One man’s voice

Connecting PHP on Linux to MSSQL on Windows

Posted on March 31st, 2008 in Geek Stuff, PHP, Web Technologies

So last week I completed a task that involved setting up our PHP installation (which happens to be on a Red Hat Application Stack) to be able to talk to a MS SQL Server living on a Windows Server 2008 box while keeping my ability for PHP to talk to a Sybase Server living on a Solaris machine. Oh yeah, the Sybase extensions also needs to handle multiple result sets.

About 18 months ago I was in need of being able to talk to Sybase from PHP in a way that allowed Sybase to return multiple result sets and have PHP be able to use them.

This was not a huge problem, I thought, since just about all the beefy database servers can return multiple results and PHP can talk to just about any database server.

Boy, was I wrong.

After developing several high level headaches over the course of a few weeks trying to get multiple result sets to work, we found a solution that worked. Mostly.

It is a PHP4 hack to a PHP Sybase CT extensions that someone put together that allows the multiple result returns from Sybase to be captured. Unfortunately it also totally redefines the mssql_* family of functions at the same time. And I didn’t find out about that until after we had built the MSSQL extension and tried to run Sybase on the same PHP installation.

Anyway, if there is anyone else in the same boat as me, here is what I did to, first, get PHP to talk to MSSQL then to get PHP to talk to Sybase using multiple result sets. These instructions are for a Linux environment (RHAS, Fedora Core 6 and Fedora 7) using both PHP 5.1.6 AND 5.2.4.

Instructions:

• Step 1: FreeTDS
  1. Download FreeTDS
  2. Unpack the tarball
  3. $]cd freetds-VERSION (where version is the FreeTDS version number)
  4. $]./configure --prefix=/usr/local/freetds --enable-msdblib
  5. $]make
  6. $]su root
  7. $]make install
  8. $]make clean
• Step 2: SQL Server PHP extension
  1. Download the source for the right version of PHP (http://php.net/downloads)
  2. Unpack the tarball
  3. $]cd php-VERSION/ext (where VERSION is the PHP version number)
  4. Copy the mssql/ directory to someplace where you can compile it from
  5. CD to that directory
  6. $]phpize (this requires the php-devel package)
  7. $]./configure --with-mssql=/usr/local/freetds
  8. $]make
  9. $]su root
  10. $]make install
  11. $]make clean
• Step 3: FreeTDS configuration
  1. $]vi /etc/freetds.conf (any editor available can used for this)
  2. Go to the end of the file
  3. Add the following lines
     ;--- Custom MSSQL server name ---
; THIS CAN BE ANY NAMED SERVER REFERENCE YOU WANT TO CREATE
[SERVERNAME]
; This is the host name of the MSSQL server
host=HOSTNAME
; This is the port number to use
port=PORTNUMBER
; This is the TDS version to use for anything over Server 2000
tds version=8.0
  4. Save the file and exit
  5. $]vi /etc/ld.so.conf (Again, any editor should be able to do this)
  6. Go to the end of the file
  7. Add the following line:
     /usr/local/freetds/lib
  8. Save the file and exit
• Step 4: Enabling MSSQL PHP extension
  1. $]cd /etc/php.d
  2. If there is no file named mssql.ini create one
  3. Edit the file and enter the following lines if not already there
     ; Enable mssql extension module
extension=mssql.so
  4. Save the file and exit
• Step 5: Apache (This should be done any time PHP changes)
  1. $]/etc/rc.d/init.d/httpd stop
  2. $]/etc/rc.d/init.d/httpd start
  3. NOTE: $]apachectl -k graceful might work as well, though httpd start offers a status flag
• Step 6: Test a file at the CLI and at the Web Server

If anyone wants the steps to setup the hacked version of the Sybase CT extension to handle multiple result sets, comment. I can post the files and instructions if anyone wants them.