PHP and Accessing MS SQL Server in Unix/Linux

Introduction

Microsoft provides a very good driver and documentation on how to connect and work with their MS SQL Server. Unfortunately, it only works on windows operating system. So for Linux/Unix, you need to find different approach to connect with. FreeTDS is there for you to rescue. It is free and works with both Linux and Unix. 

Background

For this post, I am using Ubuntu 12. The first step is of course having proper working FreeTDS. So, for this you need to install several packages. In your terminal just execute the command:

apt-get install freetds-bin freetds-common tdsodbc odbcinst php5-odbc unixodbc

This command will install all necessary components to add ability to connect to remote MSSQL server.

Second step is to copy the odbcinst.ini file from /usr/share/tdsodbc (in some case /usr/share/doc/freetds-common/examples/odbcinst.ini) to /etc. Yes, you can use GUI to copy the file but just make sure you backup the existing odbcinst.ini file in /etc. If you want to do this by command line here

mv /etc/odbcinst.ini /etc/odbcinst.ini.bak
cp /usr/share/tdsodbc/odbcinst.ini /etc/

Using the code

Yes!!! You are done with the configuration step. Now it is the time of coding, real challenge.

As you have your TDS driver and ODBC in your Unix, you can create PDO connection object and execute query based on that connection. Here you go:

try {
  //$con = new PDO('odbc:Driver=FreeTDS; Server=full_machinename_or_ip_address\
  //   (servername_if_any); Database=db_to_connect; UID=username;PWD=password;);
  $con = new PDO('odbc:Driver=FreeTDS; Server=remote_server\mssqlserver; 
              Database=db_students; UID=db_user; PWD=password123;');
  // Now you have connection object. You can query on your db.
  // Example to execute ful SQL query
  $result = $con->query('SELECT Name FROM dbo.students');
  foreach ($result as $row) {
   print $row['Name'] . '<br />';
  }

  // Example to execute stored procedure
  $result = $con->query('EXEC dbo.GetAllStudents');
  foreach ($result as $row) {
   print 'Name: ' . $row['Name'] . ' Grade: ' + $row['Grade'] . '<br />';
  }
  $con = null;
 } catch (PDOException $e) {
  echo $e->getMessage();
}

For Remote local table above code will work fine but the above code will fail when there is Linked Server. This means MSSQL Server has linked database which is somewhere else and can be other database server like Oracle. In this case your $result will be false. To ensure that your code doesn't fail on such type of scenarios, you need to execute two commands before actual query. Here is the full code which will work for Linked Server.

try {
  $con = new PDO('odbc:Driver=FreeTDS; Server=remote_server\mssqlserver; 
     Database=db_students; UID=db_user; PWD=password123;');

 // Make sure it won't fail for Linked Server
 $command = $con->prepare('SET ANSI_WARNINGS ON');
 $command->execute();
 $command = $con->prepare('SET ANSI_NULLS ON');
 $command->execute();

  // Now you have connection object. You can query on your db.
  // Example to execute ful SQL query
  $result = $con->query('SELECT Name FROM dbo.students');
  foreach ($result as $row) {
   print $row['Name'] . '<br />';
  }

  // Example to execute stored procedure
  $result = $con->query('EXEC dbo.GetAllStudents');
  foreach ($result as $row) {
   print 'Name: ' . $row['Name'] . ' Grade: ' + $row['Grade'] . '<br />';
  }
  $con = null;
 } catch (PDOException $e) {
  echo $e->getMessage();
}

You are done now. You can connect to MS SQL Sever from non windows machine and from PHP. If you have any questions just let me know via comments.