Linux + PHP + PDO -> SQL Server

From the title of this post you might think, why are you doing this? What self-respecting software engineer wants to connect from a Linux distro to a Microsoft SQL Server instance? Well, never you mind!! Quit being nosey!! I thought I’d share my experience of trying to do this, partly to share the knowledge and partly to remind myself in a few years when I need to upgrade or revisit my code. It was an “interesting” and not at all frustrating experience…

Word of warning. You’ll never have as good an experience connecting from Linux to SQL Server as you would with PHP running on windows. This is the best solution that I could find without resorting to using Windows ew!

FreeTDS

FreeTDS is a library and a few binaries that allow you to connect to SQL Server and Sybase instances. It is available in most package manager repos. For instance I tried both Ubuntu (apt) and CentOS (yum). All you need to do is install the devel package as we’ll be building PHP from source.

Ubuntu

sudo apt-get install freetds-dev libxml2-dev

CentOS

sudo yum install freetds-devel libxml2-devel

PHP

At the time of writing this I am migrating from php 5.3 to php 5.5. So I’ll give you instructions for both version. The first thing to note is that you need to build PHP yourself are there are several configure flags that need to be set. So lets get on and install it.

Get the source

wget http://uk3.php.net/distributions/php-5.3.28.tar.bz2
tar -vxf php-5.3.28.tar.bz2
cd php-5.3.28

Configure

Now that we have the source and we’re in the correct directory we need to configure our PHP install. We need to enable a both PDO support and the “dblib” PDO driver, which will be using our FreeTDS library under-the-hood. Please not this will not enable the mssql_* functions, it will just enable us to connect to a SQL Server instance using PDO classes/methods.

./configure --with-pdo-dblib

If you build PHP yourself normally you’ll probably have other configure flags to set. This shouldn’t conflict with any of them and can safely be added to your existing list of arguments.

Make

We’ll this step isn’t too difficult

make
sudo make install

Done!

Now we can use PHP to connect to a SQL Server instance:

<?php
try {
    $pdo = new PDO("dblib:my.database.com:1449;dbname=mydb", "myusername", "mypassword");
    $result = $pdo->query("select * from mytable");
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

I hope this is useful to you. Please let me know if it was, or if you had any issues.

Advertisements

2 thoughts on “Linux + PHP + PDO -> SQL Server

  1. On RHEL5 it is much easier and more maintinance-friendly to just add 3rd-party repositories remi+epel, which have fresh versions on PHP and mssql extension.

  2. Thank you A LOT man!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s