PHP Access To An MSSQL Database From Debian Etch With ODBC And FreeTDS

This assumes you already have Apache2 and PHP5 set up properly on your system. My efforts to get this connection working were compiled from information found at www.unixodbc.org and www.freetds.org. These steps worked for me with an Apache2 web server with php5 running on Debian Etch stable in October of 2007. The SQL server is running Microsoft SQL 2005 on a Windows 2003 Server OS.

In these instructions I assume you've su'd to a root account. I also use joe as my editor so replace that with your editor of choice.

First install the necessary packages:

#apt-get install tdsodbc libct3 unixodbc php5-odbc

This should download and install unixODBC, FreeTDS, and the odbc extentions for php5.

Next we create templates to register the ODBC Driver for FreeTDS and for creating the ODBC DSN for FreeTDS. You can put these files wherever you like as you'll be using "odbcinst" to actually configure the system. I chose to house them in /etc/freetds where the tds configuration files live.

ODBC Driver registration:

#joe /etc/freetds/tds.driver.template

Then paste this into the editor:

[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so

Now the DSN:

#joe /etc/freetds/tds.dsn.template

Then paste this into the editor:

[DSN_NAME]
Description     = Test to freeTDS
Driver          = TDS
Trace           = No
Database        = DefaultDatabase [replace with your database name]
Server          = mysqlserver.inter.net [replace with your SQL server's host]
Port            = 5050 [replace with the port that SQL is listening on]

Ok, now you need to get these templates inserted into the unixODBC system:

#odbcinst -i -d -f /etc/freetds/tds.driver.template
#odbcinst -i -s -f /etc/freetds/tds.dsn.template

A problem I had at this point is that the DSN got installed to the root account's personal odbc settings. Since I can't log in as www-data to run the dsn installation command I simply copied the root's .odbc file to /etc/odbc.ini which is the system wide DSN file that all users can reference.

#mv /etc/odbc.ini /etc/odbc.ini.bak  [in case you had others]
#cp /root/.odbc.ini /etc/odbc.ini

Now you need to enable the odbc extension in php by editing your php.ini file. Unless you made some sort of custom build or install the php ini file should be in the /etc/php5/apache2/ directory, so:

#joe /etc/php5/apache2/php.ini

Best practice is to add this to the extensions section of the php.ini file:

extension = odbc.so

Now you need to restart Apache as to get the php changes into the system.

#/etc/init.d/apache2 restart

That should be it. Assuming you have connectivity to the MSSQL machine you should be able to test this setup using isql. Using the above settings and a username of Bob with a password of Marley here's the test:

#isql -v DSN_NAME Bob Marley

You should get the SQL> prompt and you can run any select or other commands you like. Now onto the PHP portion. All you need to do (again assuming the above exmamples) is add the conenction info to your php file and get to coding. The following is a basic example that you will of course need to bend to your will.

<?php

# connect to a DSN "DSN_NAME" with a user "Bob" and password "Marley"
$connect = odbc_connect("DSN_NAME", "Bob", "Marley");

# query the users table for all fields
$query = "SELECT * FROM users";

# perform the query
$result = odbc_exec($connect, $query);

# fetch the data from the database
while(odbc_fetch_row($result)) {
$field1 = odbc_result($result, 1);
$field2 = odbc_result($result, 2);
print("$field1 $field2\n");
}

# close the connection
odbc_close($connect);

?>

If you have problems start with basic network troubleshooting. Make sure you can ping the server from your debian box. Try telneting to the server and port that MSSQL is listening on. If it's working you should get a blank screen that you need to break out of. After that if the isql example works but your php connection doesn't, I'm not sure where to point you.

Good luck!

Share this page:

8 Comment(s)