In a previous post, I had spoken of a true solution. Now I will break down the lessons learned because I have never seen a contrast of the draw backs of the various past mssql solutions.

PDO tdslib driver:

This is the installation I recommended last. It was my most recent discovery and fixed all issues I had previously had except for one. Output variables are not to be binded to, and there is no support for multiple result sets, so it can be easy to get the error of all results were not exhausted.

In comparison with the previous driver, these are small fry.

PDO mssql through freetds as odbc driver:

This driver had many problems. Nulls would crash processing, strings above 255 characters would try to allocate 4gb ram, exceptions were not reported. This was the first segment we tackled and we worked around all these failings, but a permanent solution, never.

Solution 3:

A newcomer solution with much experience is now on the scene. Stay tuned for my analysis of its readyness

One day, I found myself needing to use MSSQL Server Procs, using PHP 5.3 in a Linux environment. I soon found out that this was easier said than done as PHP 5.3 had removed built-in MSSQL support from the linux distribution and a proper install guide was far more common for Windows PHP users and PHP 5.2.

My first solution was to use FreeTDS, Linux ODBC and PDO ODBC. Out of a myriad voodoo style solutions, this one seemed the most straight forward. Unfortunately, there were issues. Exceptions never really worked right (caused the PHP module to die), NULL values were essentially unsupported, and if any string took more than 255 characters, PHP would demand 4GB of RAM.

Due to the short deadline of the project, crazed hacks were implemented, but I vowed to solve the issue properly at a later date.

So, after having long since conquered the beast, I present the ultimate guide, cradle to grave.

First step is a proper driver for MSSQL that works in Linux. The best and officially PHP supported is FreeTDS at http://www.freetds.org/

This guide assumes freetds-0.91.

Once you have it:

tar xzf freetds.tar.gz
cd freetds-0.91
./configure --with-tdsver=7.1 --enable-msdblib --with-gnu-ld && make
sudo make install
sudo /sbin/ldconfig

Once installed, you can do a basic connectivity test.

/usr/local/bin/tsql -S host -U username

You will be prompted for password. If you get a prompt, all is well.

Now for PHP, assuming PHP 5.3.8.

./configure line should have whatever your environment needs, but the key addition is the following:

--with-pdo-dblib=/usr/local

The libraries from FreeTDS by default go there in Centos, so that is where PHP needs to look.

Once PHP is all installed, we can do a basic test that things are working with a quick script.

<?php
$dbh= new PDO('dblib:host=hostserverhere;dbname=databasename;appname=MySweetApp', 'username', 'password');
$stmt = $dbh->prepare("SELECT SYSDATETIME()");
$stmt->execute();
while ($row = $stmt->fetch()) {
print_r($row);
}
unset($dbh); unset($stmt);
?>

Run that and if you get output, congrats, you have succeeded.

That should be enough to get you started. Next edition, I share some PDO recipes to help the non-MSSQL familiar.

I found an issue that many have not been able to resolve from my googles so I thought I’d share my solution.

To fix, I found a new version of VimBall.tar.gz (think it was V28). I then extracted it to the vim main directory rather than my personal plugin directory. This seemed to fix it and all was well.

I think I’m finally coming over the hill of learning VI. A key collection of plugins I found via http://spf13.com/post/the-15-best-vim-plugins

I used most but the SQL plugin. One lacking from the list is the command-t plugin. It requires you to install ruby and its dev headers, but in return you get a file finder almost better than TextMates. Folding I must learn and windows, but my coding speed with VI nav short cuts and block editting have been tremendous. Taking 3 lines and moving them down while indenting them without moving from the home row keys is pretty cool. I also think Its more comfortable since I was getting some RSD from moving to the mouse but without that motion, my hands feel much better.

I highly advise learning vi, even a little at a time. I thought I couldn’t live without TextMate. But now I know I can have the same level of editting (minus cool plugins) on any nix box by default. Much cooler than being stuck on an IDE Island.