Using FreeTDS to connect to SQL Server from Debian Apache/PHP

This post describes how to set up a web site which uses PHP to connect to a backend SQL Server database using FreeTDS. I wrote it because I found the documentation currently available a bit confusing until I had completed the task, when it turned out to be really simple.

I am setting up a development environment for a project I will be undertaking which ports a Microsoft Access application connected to a SQL Server database to become a web application. To avoid a complete tie in with Microsoft technologies I decided to use an Apache Web server running PHP as the main platform for this application. Although the final system will probably run on a Windows server, it is much more convenient for me to develop in a Linux environment where I have all my tools setup. I needed a way to connect to the database from PHP. The obvious choice was FreeTDS.

I started reading the documentation to figure how to do it but I found it quite confusing. Eventually, however, after a bit of trial and error I got it working, and when I did it turned out to be remarkably simple.  However, importantly, I felt that there was much confusion in the documentation with references to some libraries such as DB-Library and CT-Library and to ODBC that ended up as irrelevant to the final result, that I would write this simple guide of how to make it work.

The first puzzle is which Debian packages do you actually need to install.  The truth is only one package is needed and that is php5-sybase.  It is this package which contains two shared libraries to access the database.  One to access via PHP PDO, one to access directly.  This, in this particular configuration, is freetds.

Since I will be using PDO, I didn’t have to do anything more since installing it actually added this library to a directory of PDO drivers and enabled it.

There are two other packages that might be useful (freetds-bin and freetds-common), but they are not necessary.  Freetds-bin contains command line utilities to connect and then perform queries on a database, freetds-common contains an example version of a file you need to install as /etc/freetds/freetds.conf

In freetds.conf you configure the connection to the databases via sections delimited by names in square brackets.  Here is mine to give you a simple example to follow

[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 = /var/log/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

# My SqlServer Instance
[roodb]
host = roo.home
instance = SQLEXPRESS

The host line “roo.home” is the fqdn of the host where the server sits. In my case I have a private local domain in my house called .home and roo is one of the machines in this domain.

Notice my use of roodb as the name of my particular instance of the database.  This is important in the final section of the puzzle.  How to you connect to this instance of the server and a particular database within it.  This little test program shows how to set up the connection string using the $dsn variable. The ‘host=’ section of that string points to ‘roodb’

<?php
$dsn = 'dblib:host=roodb;dbname=PAS_Live'; //dbname and host is on different server, not localhost
$user = 'yourdatabaseuser';
$password = 'yourdatabaseuserpassword';
try {
    $db = new PDO($dsn, $user, $password);
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );  
}
catch(PDOException $e) {  
    die("Failed with message: " . $e->getMessage());  
} 
echo "It worked"
?>

And that is all there is to it.

I trust that this simple guide was useful.

Author: Alan

I am Alan Chandler.

Leave a Reply

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