Getting PHP in Homebrew to access MSSQL via FreeTDS and PDO dblib

This quick little guide is aimed primary at me in the future, but I hope it could be of some use to others as well. This process can be used to install any PHP extension into any version of PHP that comes pre-packed but doesn’t include it.

Some examples are:

  • Packages missing from homebrew-php
  • MAMP PHP missing extensions

Getting PHP (and Ruby) talking to MSSQL is actually quite easy on a Mac. Perhaps even as easy as on Ubuntu.

So, let’s get started.

Installing PHP

I’m using PHP 5.3 here, for no good reason:

$ brew tap homebrew/dupes
$ brew tap josegonzalez/homebrew-php
$ brew update
$ brew install php53

If you run into trouble, run brew doctor!

Link PHP 5.3 into your console by editing ~/.bash_profile or ~/.zshrc or whatever:

echo 'export PATH="$(brew --prefix josegonzalez/php/php53)/bin:$PATH"' >> ~/.zshrc

Reload your shell.

Installation of Required Brew

Install freetds and unixodbc:

$ brew install freetds unixodbc

Check your Homebrew php version:

$ php -v
PHP 5.3.27 (cli) (built: Nov 11 2013 00:32:09)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2013 Zend Technologies

This next part is taken shamelessly from here (thank you Ben Walters!).

Obtain a copy of PHP source code that matches:

$ cd /tmp/
$ wget -O php-5.3.27.tar.gz http://us3.php.net/get/php-5.3.27.tar.gz/from/ca1.php.net/mirror
$ tar xzvf php-5.3.27.tar.gz
$ cd php-5.3.27

Configure and compile:

$ ./configure
$ make

Go into the MSSQL module folder:

$ cd ext/mssql

PHPize, configure, and make:

$ phpize
$ ./configure --with-freetds=/usr/local/opt/freetds
$ make

Copy into your PHP extensions folder:

$ php -i | grep extension_dir
extension_dir => /usr/local/Cellar/php53/5.3.27/lib/php/extensions/no-debug-non-zts-20090626

$ mkdir -p /usr/local/Cellar/php53/5.3.27/lib/php/extensions/no-debug-non-zts-20090626
$ cp modules/mssql.so /usr/local/Cellar/php53/5.3.27/lib/php/extensions/no-debug-non-zts-20090626/

Repeat for pdo_dblib.so (mind the configure arguments!)

$ cd ../pdo_dblib
$ phpize
$ ./configure --with-pdo-odbc=unixODBC
$ make
$ cp modules/pdo_dblib.so /usr/local/Cellar/php53/5.3.27/lib/php/extensions/no-debug-non-zts-20090626/

Edit /usr/local/etc/php/5.3/php.ini and add (where it fits):

extension=mssql.so
extension=pdo_dblib.so

You might need to repeat this step if you’re going to use the non CLI PHP, and edit the appropriate ini file for that context.

Et voila. You will have to use a dblib style dsn to make your PDO connections. Something like so — but this makes reference to our own multi-connection PDO wrapper:

FluxIncPDO::setDsn( 'dblib:host=our-host.local:1433;dbname=Xcelera', 'xcelera' );
FluxIncPDO::setUser( 'testuser', 'xcelera' );
FluxIncPDO::setPassword( 'testpassword', 'xcelera' );

You might also need to edit your freetds.conf file; However, it should already be present and you can substitute all the parameters within the dblib connection string. This also holds true for the TDS version, which I’ve defaulted on my system to 7.1.

/usr/local/etc/freetds.conf

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
    tds version = 7.1

    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
    dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    # If you get out-of-memory errors, it may mean that your client
    # is trying to allocate a huge buffer for a TEXT field.
    # Try setting 'text size' to a more reasonable limit
    text size = 64512


# A typical Microsoft server
[XCELERA]
  host = 192.168.11.11
  port = 1433
  tds version = 7.1

Note:

Very shortly afterward discovered that you can do this using a single command:

brew install php54 --with-mssql

Go figure.

Leave a Reply

Your email address will not be published. Required fields are marked *