Mac OS X, PHP, Zend Server CE, FreeTDS and MS SQL

This post handles the configuration of a database connection from Mac OS X (Snow Leopard) and Microsoft SQL Server 2005/2008.

By default, you can connect to a SQL Server from Mac OS X PHP with pdo_dblib.
But for my needs, i had trouble with the following points:

• troubles in correct displaying or saving of special characters like äöü in german or else in cause of UTF-8 charset and database collation
• differences in returning datetime values, also depending on settings for the connecting user of the database
• returning text and binary fields, espacially with large content

A solution to the first point is to add the client charset encoding to the DSN connection string:

header('Content-Type: text/html; charset=UTF-8');
try
 {
  $dbh = new PDO('dblib:host=192.168.1.1;dbname=databasename;charset=utf8', 'username', 'password');
  echo "Yeaha!";
  $stmt = $dbh->prepare("SELECT column_xyz FROM table_xyz");
    $stmt->execute();
    while ($row = $stmt->fetch()) {
          // the returned values are UTF-8 encoded
          var_dump($row);
    }
    unset($stmt);
    unset($dbh); 
 }
 catch (PDOException $e)
 {
  die($e);
 }

To solve all points, the easiest way is to use FreeTDS via unixODBC. The following steps describe the installation and configuration.

Requirements:

• installed PHP Stack (e.g. Zend Server or Mac OS X own...) in latest version (5.3.3 at the moment of writing)
• all links and descriptions are made for Mac OS X 10.6 (Snow Leopard)

1. Installing XCode

For the MacPorts installation we need XCode be installed. You need a registered AppleID for Apple's developer site. Grab it from the Apple developer website here Xcode 3.2.5 and iOS SDK 4.2. Take a coffee, its about 3.52 GB in size...
After downloading, just follow the instructions from the installer.

2. Installing MacPorts

Get the MacPorts installation package from the website Macports for Snow Leopard and follow the install instructions. After this, open up a Terminal session and run the selfupdate command to update the package repository.

sudo port -v selfupdate

If you get an error like "-sh: port: command not found" add the path /opt/local/bin to your PATH environment variable. edit your ~/.bash_profile file and add the line

export PATH=$PATH:/opt/local/bin

After you started a new shell, you are able to run the port command without giving the full path. Re-run the selfupdate command above.

3. Installing unixODBC

Now we install the unixodbc package (with all dependencies) with

sudo port install unixodbc

There's nothing special here.

4. Installing FreeTDS

ATTENTION: If you are using Zend Server (or Zend Server CE) you do not need to install FreeTDS, as Zend Server comes with it's own version. You just have to pay attention on the note in the place of your configuration files for FreeTDS.

The next step is the installation of FreeTDS. As some MacPorts packages came along with several so called variants, we have to give the port install command the variant as parameter which we want to install

sudo port install freetds +odbc +mssql

This installs FreeTDS with unixODBC and dblib/MS SQL Server support. We can check the installation with tsql -C. This should return somwthing similiar to this:

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.82
             freetds.conf directory: /opt/local/etc/freetds
     MS db-lib source compatibility: yes
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 8.0
                              iODBC: no
                           unixodbc: yes

5. Configure unixODBC and FreeTDS

The configuration needs four files. Samples of these files are installed in /opt/local/etc/.

/opt/local/etc/odbc.ini

[MyMSSQLServer]
Driver          =       FreeTDS
Description     =       My Desription
Servername      =       MyMSSQLServer

/opt/local/etc/odbcinst.ini

[FreeTDS]
Driver  = /opt/local/lib/libtdsodbc.so

/opt/local/etc/freetds/freetds.conf

[MyMSSQLServer]
        host = DB_SERVER_ADDRESS_OR_NAME
        port = PORT
        tds version = 8.0
        encryption = request
        client charset = UTF-8
        text size = 2147483647

/opt/local/etc/freetds/locales.conf

[default]
    date format = %Y-%m-%d %H:%M:%S.%z

[de_DE]
    date format = %Y-%m-%d %H:%M:%S
    language = Deutsch

ATTENTION: For Zend Server (and Zend Server CE) you have to save or better link your freetds.conf and locales.conf configuration files to the zend etc directory in /usr/local/zend/etc/.

6. Testing connection

We testing the connection to a sample database as configured above with the installed unixODBC tools isql and with PHP after this.

Testing with tsql:
On Mac OS X, you sometimes need to set your LC_ALL environment variable. On a new, fresh installed system on another installation i do not need to set this. Attention, FreeTDS needs UTF-8 in uppercase letters. A sign to this is when getting 20017 and 20002 errors in your tsql command.

export LC_ALL="de_DE.UTF-8"

Testing the connection:

tsql -S MyMSSQLServer -U username -P password

Testing with PHP:
Change line no 4 from the first code snippet on this site with the following

$dbh = new PDO('dblib:host=MyMSSQLServer', 'username', 'password');