Building the PHP MS SQL Server extension from source on Ubuntu 8.10

Yesterday I wrote about how I got an enhanced Sybase CT extension for PHP built on Ubuntu 8.10 from source files and from Red Hat RPM archives. Today I wanted to write about installing the SQL Server extension from source on Ubuntu 8.10.

I have written about this once before but it was for a Red Hat environment. Seeing as we are converting all of our system at work to Ubuntu I had to do the same thing for the Ubuntu platform. And wouldn’t you know it, Debian and Red Hat are vastly different from each other.

Aside from the stupidity that is the Ubuntu package manager’s rendition of the SQL Server extension for PHP the fact remained for me that I had to be able to integrate database communication from LAMP to a SQL Server on Windows as well to a Sybase server on Unix. The Sybase bit was taken care of in yesterday’s write up. But how do we get PHP to talk to SQL Server on Ubuntu 8.10?

Throw out the notion that you can use the php-sybase package from the package manager. It munges stuff up pretty badly and at the same time does things to the Sybase extension that make it unusable for my needs. Not to mention that if their is a Sybase extension already installed then it really pukes because the php-sybase extension that installs the SQL Server functions actually installs the mssql_* functions then maps the sybase_* functions to them, which makes PHP squawk since those functions are already defined.

So the only way to make this happen is to build the SQL Server extension from source.

Building and installing the mssql extension from source

The follow steps will guide you through how I got the mssql extension built and installed on Ubuntu 8.10:

  • Build the FreeTDS library
    FreeTDS is a free, open source library of clients that allow Linux machines to talk to SQL servers and Sybase servers. Download the latest stable version of the FreeTDS library (as of this writing it was 0.82) and unpack the source to a directory on your machine. Once the source is unpacked, change to the directory where the source is located and configure and make the library:
    $ ./configure --prefix=/usr/local/freetds --enable-msdblib
    $ make
    $ sudo make install

    DO NOT MAKE CLEAN AT THIS POINT.

    Before you do anything else you must make sure that you have copied over all the necessary files from FreeTDS. “Wait, didn’t the installation routine do that?” you might ask. No, it doesn’t, because of a change made on the part of the FreeTDS developers. There are two files that are needed in order to build the mssql extension. Those files are among the make files and must be copied over to the freetds install directory.

    From inside the source directory of freetds, where you built it from, enter:
    $ sudo cp include/tds.h /usr/local/freetds/include
    $ sudo cp src/tds/.libs/libtds.a /usr/local/freetds/lib

  • Get the PHP source code
    Change to a directory where you wouldn’t mind having the entirety of the PHP source stashed. From the command line type:
    $ apt-get source php5
  • Copy the mssql extension files to a directory you can build from
    My preference is to leave source alone and work from copies. So I always copy the files I need to a different location and build from the copies. To that end, I created a directory at ~/SourceCode/php-mssql and copied the following files from the original PHP source directory:
    $ cp ext/mssql/config.m4 ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.c ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.h ~/SourceCode/php-mssql
  • Make and install the extension
    Make sure you have the php5-dev package installed on your system so that you can build PHP extensions from source. From inside the directory where your mssql extension source code is, at the command line, enter:
    $ phpize
    $ ./configure --with-mssql=/usr/local/freetds
    $ make
    $ make install

    Find out where your extensions directory is on your machine and quickly check it to make sure there is a php_mssql.so file living in it. On my machine the extension directory is /usr/lib/php5/20060613+lfs/. Yours may be different.

  • Configure PHP to use the new extension we just made
    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 mssql.ini

    Inside this file place the following two lines:
    ; Enable the mssql extension
    extension=mssql.so

  • Configure your environment to load a much needed environment variable whenever the machine starts
    Much like the Sybase extension we did yesterday the mssql extension needs an environment variable in order to function properly. Again, this one caused me fits for a long time in Ubuntu. To be sure you can use the SQL Server extension from both the CLI and the web server you will need to add an environment variable to both the /etc/profile startup script AND the web servers environment variable setting script.

    $ sudo vim /etc/profile

    At the end of the file add:
    export FREETDSCONF=/etc/freetds/freetds.conf

    Now add this same entry 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:
    export FREETDSCONF=/etc/freetds/freetds.conf

  • Configure FreeTDS
    In order for FreeTDS to communicate properly with the SQL server a DSN of sorts needs to be created. To make a DSN you need to edit the freetds.conf file:
    $ sudo vim /etc/freetds/freetds.conf

    Go to the end of the file and add the following lines (MAKE SURE TO INDENT ALL OF THE CONFIG VALUES FOR THE SERVERNAME):
    ;--- 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

    Now we need to add the freetds library to the load library stack.
    $ sudo vi /etc/ld.so.conf

    Go to the end of the file and add the following line:
    /usr/local/freetds/lib

  • 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 now see mssql.

Again, I hope this was helpful to you. If it was, please leave a comment and let me know. It something went haywire for you, leave me a comment, too. Systems being what they are, it is never unheard of for two almost identical systems to have vastly different experiences with building software from source.

I hate open source

Actually, I should say “I hate open source as only someone that has used open source software could hate it.”

Today I spent the better part of the day trying to get my newest installation of Ubuntu 8.10 to talk to our Sybase and MS SQL Servers from PHP. Not only that, but I need to be able to handle multiple result sets from Sybase and I cannot use PDO since it has never, ever worked correctly when trying to connect to Sybase using the native drivers. And there is no way in hell I am using ODBC to connect to our Sybase servers (why should I have to when I can use native Sybase connectivity?).

So with this in mind I set out to tap into the ease of use of Ubuntu as an operating system and installed php-sybase using the Synaptic Package Manager. That was my first, and probably biggest mistake in the whole of this ordeal. Apparently the package maintainers for Ubuntu decided that when installing the php-sybase extension they would actually install the php-mssql extension and map all the sybase function names to the mssql function names. That is just stupid. Here is why:

The common functions between php-mssql and php-sybase in the Ubuntu package
Function purpose sybase_* mssql_*
Based upon the PHP manual as of February 11, 2009
connect sybase_connect mssql_connect
persistent connect sybase_pconnect mssql_pconnect
select database sybase_select_db mssql_select_db
query sybase_query mssql_query
result (cell) sybase_result mssql_result
result (row – numeric index) sybase_fetch_row mssql_fetch_row
result (row – associative index) sybase_fetch_assoc mssql_fetch_assoc
result (row – both index) sybase_fetch_array mssql_fetch_array
result (row – object) sybase_fetch_object mssql_fetch_object
result (row count – data sets) sybase_num_rows mssql_num_rows
result (row count – affected rows) sybase_affected_rows mssql_rows_affected
result (row pointer mover) sybase_data_seek mssql_data_seek
result (field count) sybase_num_fields mssql_num_fields
result (field info) sybase_fetch_field mssql_fetch_field
result (field pointer mover) sybase_field_seek mssql_field_seek
result (remove from memory) sybase_free_result mssql_free_result
errors sybase_get_last_message mssql_get_last_message
close sybase_close mssql_close
setting (message severity) sybase_min_message_severity mssql_min_message_severity
setting (error severity) sybase_min_error_severity mssql_min_error_severity

You can see in this common table that almost all of the mssql_* functions that share a common purpose with the sybase_* functions are the same, save for one glaring exception (rows_affected? Seriously?). The problem lies in the fact that there are a ton of mssql_* functions that do not have a matching function in the sybase_* family of functions according to the manual. Those functions are:

  • mssql_init
  • mssql_bind
  • mssql_execute
  • mssql_next_result
  • mssql_field_length
  • mssql_field_name
  • mssql_field_type
  • mssql_free_statement
  • mssql_fetch_batch
  • mssql_guid_string

Throw into this mix that there are a few sybase_* functions that do not have a corresponding mssql_* function, such as:

  • sybase_deadlock_retry_count
  • sybase_min_client_severity
  • sybase_min_server_severity
  • sybase_set_message_handler
  • sybase_unbuffered_query

And you can see that building the Sybase extension from the SQL Server extension will not work under normal circumstances if you have your code built around the functions in the manual. So… it was back to the drawing board. And this drawing board is way different than the last one I remember (think back to building the sybase and mssql extension for PHP in Fedora).

What this ended up boiling down to was that I had to compile a PHP 4 hacked version of the Sybase extension source code that offered handling of multiple result sets. Not that it was that entirely stupid to do, except for the fact that the client package I needed to compile against was in Red Hat RPM format. But thanks to the Alien package converter tool I was able to install the Sybase client on Ubuntu with little headache.

Building the Sybase extension was not that big of a deal either once the client was installed. But getting the SQL Server extension built has been a pain in the rump still yet.

The first thing I had to do was build the FreeTDS package from source since the Ubuntu pacakge installer installs the freetds libraries somewhere but they are really nowhere to be found. And since you need to build them in a way that allows for dblib use I had to build from source with build flags. So I got the freetds package installed, after a few tries, and then went to build the sql server extension from source.

And it looked like it worked. But when I restarted Apache I realized it hadn’t worked. You know why? Because the stupid source code for the mssql extension aliases all the sybase extensions WITHOUT checking to see if Sybase is already compiled. Whiskey Tango Foxtrot?

So I had to comment out the aliasing part of the extension source code that rewrites the sybase extension and compile again. And this time it worked. Except for some reason, even though I am editing the freetds.conf file the server I am trying to connect to is not resolving properly. And I have no idea why.

So here I am, pissed off at open source, Linux, Ubuntu, PHP, penguins and all things cute and cuddly. I know I will get it to work. And I know when I do I will be making sounds in my cubicle that will make my coworkers hand me tissues because they will think I need to clean up after myself. But for now, I hate open source. But only as once who spends a lot of time in it.

Sometimes good things just happen

As in the case of Ubuntu Linux. Man, is this operating system a blast or what?

I was fortunate enough to resurrect a fellow coworker’s old XP machine and reimage it so that it is a pure 100% Ubuntu OS. And I am so stoked I cannot put it into words. I have loved Linux for a while and, though I still use Winblows because I have to for various reasons, I am thinking that sooner or later I might just run a pure Linux environment and run Windows inside a VM or something.

I am just really pleased with Ubuntu. Yes, there are some things that the OS cannot do, and there are some things that happen better in Windows just because few people even know there are alternatives. But barring that Linux is a very viable alternative operating system for anyone that wants security and stability in their computer. No viruses, no malware, no stupidity. Only good performing, intuitive operation.

If you are ever feeling in the mood to try Ubuntu, you can, without even installing it, by loading it from the live CD and playing with it. If you are even more adventurous you can install Linux as a Windows application using Wubi, the Windows Ubuntu installer. This is how I got started playing with Ubuntu and I have not looked back since.

Seriously, if you ever want to just play with it, try it out. I think you will be glad with what you see.

Setting up Ubuntu 8.10 as a VM in Windows

We are in the process of transitioning all of our servers at work from Fedora to Ubuntu. I am also working on the development of an application that will be distributed as a VM running Ubuntu. For a geek, Ubuntu is like a little slice of heaven amid the hell that is the Windows operating system (with the one huge exception of the Mac OS – my goodness is that thing awesome).

When I last installed Linux on a machine at work I installed it as a dual boot between Windows XP and Fedora 7. On my laptop that I use at home I have Ubuntu 8.04 installed. I installed it using Wubi, the Windows Ubuntu Installer application that actual creates an instance of Ubuntu running as a Windows app but in a state identical to a dual boot setup. It runs clean, but is a bit buggy and cumbersome and requires a reboot to go from Windows to Linux.

So today a colleague of mine sent me a link on how to set up Ubuntu 8.10 as a Virtual PC. So I followed the instructions that started with …

Installing Ubuntu 8.10 under Virtual PC 2007 is the easiest version to install by far, if you have all your bits in the right place.

… and I gotta tell you it was just that easy. The only thing I did different was allocate 32G of hard disk instead of the 16G recommended in the article. But everything worked quite literally the way arcanecode’s article said it would.

I am jazzed. So jazzed that I may just do this all over again for Fedora 10. I have been meaning to play with that for a while now, too.

Using Picasa on Ubuntu

A few days ago I was working on some image stuff when I ran across a built-in Linux file system utility that allows you to sync images with Picasa. I used it to upload a few images then went about my other business.

A few days later I tried to find it again and was completely without luck. Not to be left unable to post pictures, I quickly began searching for a Linux Picasa app that would let me push my images to Picasa. At first all I found were pages that said “Google does not officially support Picasa on Linux”. Then I found something cool.

I am not sure where I got this link from but I found a Picasa for Linux app provided by Google. How cool is that? Now I can manage my Picasa photos right from my Linux desktop.

Now all I need is a way to sync my iPhone to iTunes on Linux without using Wine and I am golden. Anyone got any links?

Connecting to an HP Photosmart 2610 on Ubuntu 8.04

Click here to go to the instructions and bypass all of my wonderful writing.

Today I fell in love all over again. With Linux. Specifically Ubuntu 8.04.

I have been playing around with Ubuntu 8.04 for the last week and a half or so and I have totally fallen in love with software, operating systems and geekery again. Not only does Ubuntu just flat out work straight out of the box, it has made using my computer fun again. And productive.

Take installing my printer driver on my machine. A few years ago I bought a new HP Photosmart 2610 printer for an outrageously discounted price at a black Friday after Thanksgiving sale at Circuit City. I had gotten it home and installed the software for it on my computer (a Winblows 2000 computer at the time). It took about 30 minutes and two reboots (that’s right, you heard me) to get the software installed.

Once it was loaded it worked very well. It allowed me to print, fax, scan… the works. Right from my computer. Awesome.

When I got my new laptop about 2 years ago I installed the software for the printer on it. Once again, about 30 minutes and two reboots later I was able to use my printer. I followed this same process a few months later for my wife’s laptop.

Did I mention all of these computers I did this too were Winblows? The last two were XP machines, one XP Pro the other XP home. Thanks Microsoft.

Today I decided to “trudge” through getting my printer connected to my machine. I say “trudge” because I have always heard that installing printers on Linux is a lesson in futility and that, seeing as I am losing hair already, if I wanted to keep what little hair I had I would just not bother.

Was I ever stupid for listening to that mumbo jumbo.

Installing the HP Photosmart 2610 driver on Ubuntu 8.04

A quick Google search for HP Photosmart 2600 on Linux returned a link to the Linux Printing web site for my printer. After reading over the driver information I decided to visit the home page for HPIJS/HPLIP.

Once there I looked around and found that there is an unusual amount of support for many Linux distros form HP’s printer division. Since I quickly found my distro on the list I decided to use Add/Remove … from the Application menu to see if I could install my printer that way.

Did I mention I love Ubuntu? In 8.04 go to Applications -> Add/Remove … and in the search box enter HPLIP. You should get one result. Install the “HPLIP Toolbox” and once it is done, go to System -> Preferences -> HPLIP Toolbox.

If your system is like mine (vanilla to the core) you will have no printers installed to manage. I was asked by the Toolbox if I wanted to add a printer and I said I did by clicking the “Add device” button.

I was asked how my device was connected to my machine and I chose “Network” since that is how my printer is connected.

About 1 second later I was told my printer had been found and did I want to install that printer. I said yes and within about 1 second I was asked which driver I wanted to install. Since there was only one I chose it.

Within about 1 second my driver was installed.I was asked to set up some information for the printer (like the name, location and such as well as the fax number and fax name). I chose the defaults (because they were what I would have entered anyway) and let the setup work.

About five seconds later I was able to test print. No reboot. No 30 minute installs. Just printing. Fast.

Did I mention I love Ubuntu?

Anyhow, I hope this can help someone else who may have believed that setting up printers on Ubuntu was hard. This was painfully easy and now I am printing away like a printing machine.

Well, not really. My printer is doing that. But I am telling it to. And it is all because of Ubuntu.