Click here to Skip to main content
Click here to Skip to main content

PHP and Accessing MS SQL Server in Unix/Linux

, 17 Jun 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

C Is Sharp
Software Developer
United States United States
No Biography provided
Follow on   Twitter   Google+

Comments and Discussions

 
Questionphp5-sybase missing? Pinmemberdasimak27-Jun-13 6:20 
AnswerRe: php5-sybase missing? PinmemberC Is Sharp1-Jul-13 6:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141022.1 | Last Updated 17 Jun 2013
Article Copyright 2013 by C Is Sharp
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid