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

Want to support HowtoForge? Become a subscriber!
 
Submitted by kkimmell (Contact Author) (Forums) on Wed, 2007-10-17 12:56. :: Debian | Apache | PHP

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!


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Thu, 2010-10-21 19:32.
does not work: odbc.so does not exist... -> Fatal error: Call to undefined function mssql_connect() in /var/www/test.php on line 4
Submitted by Pudrik (not registered) on Tue, 2011-02-22 16:12.

You also need to edit /etc/freetds/freetds.conf

add following lines at the end

[Server80]
        host = serverip
        port = 1433
        tds version = 8.0
 

 

 Run

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

" isql -v DSN_NAME username password  " should now work for mssql 8.0 server.

Submitted by Anonymous (not registered) on Tue, 2010-06-08 00:14.

worked to perfection
thanks!

 funciono a la perfeccion
gracias!

Submitted by tr3027 (registered user) on Sun, 2007-11-11 10:31.

odbcinst creates User DSN's by default. You can instruct it to create a System DSN directly by defining the option "-l" in the command line:

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

Tom

Submitted by Marcel (not registered) on Thu, 2010-03-25 18:11.

Hi.

Nice tutorial though.

I have one question:
I have set up everything very straight away and via
isql and tsql I get instantly access to the remote database without any problem.

But when I try it via PHP I get:
odbc_connect(): SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect

I have investigated this topic and there are a lot of people having the same problem, but there are no clear solutions out there.

The funny thing is that I have everything configured.

I do not have any clue what's going wrong. Does anybody have a clue?
THX a lot in advance...

Cheers.
M.

Submitted by Anonymous (not registered) on Tue, 2011-01-11 18:22.
you could probably try to replace "DSN_NAME" in the above example with the name of the odbc Datasource you're trying to connect to. In my case the DSN has to be named "sugarcrm" because sugarcrm is configured in a way it expects a odbc datasource named sugarcrm.
Submitted by Anonymous (not registered) on Wed, 2011-04-20 14:29.
One thing I found:  odbc.ini is picky about spaces.  Don't indent.