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');
