Linux, PHP (and Zend Framework) and MS SQL Server

As mentioned in my previous post Mac OS X, PHP, Zend Server CE, FreeTDS and MS SQL setting up a database connection to Microsoft SQL Server is quite easy.

In the case you are not using Zend Server or you are on Linux (Debian, Ubuntu, SuSE, RedHat, Fedora, Gentoo or one of the other distributions) i am going to show an easy way to connect to a Microsoft SQL Server (SQL Server 2005 Standard Edition x64 in my case, but this also works for all other actual supported SQL Server Versions from Microsoft). In my examples I use Ubuntu 10.10.

First step is installing php via the distributions package and repository system, aptitude or apt-get in my case with Ubuntu or any other Debian based distribution.

sudo aptitude install libapache2-mod-php5 php5

After the installation process is done, i have to restart the apache to getting the php5 module to work. Its easily done by

sudo /etc/init.d/apache restart

Testing the successful installation is easy by creating a index.php in the Apache document root.

sudo vi /var/www/index.php

index.php:

phpinfo();

You should see your php informations when browsing to http:///

Next step is the database connectivity:

On Linux, the used PDO Module for MS SQL Connections is dblib.
Sometimes the package is called dblib, sometimes sybase, and on ubuntu there is a virtual package php5-mssql

sudo aptitude install php5-mssql

Reload the Webserver configuration:

sudo /etc/init.d/apache2 reload

And check if the module is correctly loaded:

php -i | grep MSSQL  # should print MSSQL Support => enabled
php -i | grep PDO # should print PDO Driver for FreeTDS/Sybase DB-lib => enabled

At this point, you are able to connect to a MS SQL Server. But there are several issues with this type of connection:

• Date Format (depends on user settings of connected database user)
• UTF-8 Support (there is an option for the connection string to solve it)
• binary and (long) text data

So the next step is configuring freeTDS (details of configuration can be found on the freeTDS User Guide)

the date time formatting:
/etc/freetds/locales.conf:

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

the database connection settings
/etc/freetds/freetds.conf:

[mydbhost]
        host = your database hostname or ip
        tds version = 8.0
        # text size don't need to be such a high value, its just an example
        text size = 4294967295
        client charset = UTF-8

example script
/var/www/index.php:

try
 {
  $dbh = new PDO('dblib:host=yourdbhost;dbname=databasename;', '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);
 }

At least, we are going to connect to the database with zend frameworks db adapter:

Note: Example here is done by ubuntu repository package for zend framework, but this version may be outdated (surely it is ;-)). grab the latest version from http://framework.zend.com
install zend framework:

sudo aptitude install zend-framework zend-framework-bin

be sure to have the zend framework library path in your include dirs.

sudo vi /etc/php5/conf.d/zend-framework.ini

[Zend]
include_path=${include_path} ":/usr/share/php/libzend-framework-php"

cd /var/www
sudo zf.sh create project /var/www/ zftest
sudo zf.sh configure db-adapter "adapter=Pdo_Mssql&host=mydbhost&dbname=mydbname&username=myusername&password=mypassword&pdoType=dblib"
sudo vi application/controllers/IndexController.php

Be sure you include pdoType=dblib in your connection string, otherwise you got an error similar to "The mssql driver is not currently installed".

Edit the indexAction() method content to test connection:

$db = $this->getFrontController()
            ->getParam('bootstrap')
            ->getResource('db');

        $result = $db->fetchAll("SELECT * FROM myexampletable");
        var_dump($result);

And move your browser of choice to http://<your_ip_here>/public/.

You are done! Congratulations...