Making a PHP Site on Linux Work with a Microsoft SQL Server Database | Linux Journal
Skip to main content
Toggle navigation
Topics+ Cloud Containers Desktop Kernel Mobile Networking Privacy Programming Security Servers SysAdmin News eBooks Search
Enter the terms you wish to search for.
Popular
Recent
Making a PHP Site on Linux Work with a Microsoft SQL Server Database
Webmaster
by David Perrin
on February 14, 2003
A recent project at my current employer
called for creating a new web frontend to an existing MSSQL
database. My boss, having created a sophisticated MySQL and
PHP-driven black diamond web site in PHP was enthused about the
prospects of further web development with PHP. He suggested trying
to get PHP on a Linux box to connect to a MSSQL database
server.
An attempt at this task made months before ended in
frustration. This time, after nibbling away at the task for a
couple of days on a standard Red Hat system, success was had.
Here's how.
Similar to Windows, one method of connecting to a MSSQL
database is through an ODBC DSN (open database connection, data
source name). The ODBC DSN specifies a MSSQL driver to make the
connection to the database. Recent versions of Red Hat include the
utility for creating the ODBC DSN, but not the driver.
The driver chosen and discussed in this article is a
FreeTDS
driver. TDS
(tabular datastream) is a protocol used by Sybase and MSSQL. This
driver enables the Linux machine to connect to the MSSQL
Once the driver is installed, you can configure an ODBC
connection on your Linux machine to use the driver, which then
allows a connection to MSSQL. Start by downloading and saving the
FreeTDS driver.
[root@localhost]# lynx
http://ibiblio.org/pub/Linux/ALPHA/freetds/freetds-0.60.tgz
Next, uncompress, configure and make the FreeTDS
driver.
[root@localhost]# tar xvfz freetds-0.60.tgz
[root@localhost]# cd freetds-0.60
[root@localhost]# ./configure --with-tdsver=7.0 --with-unixodbc
su
to root if you are not
already root.
[root@localhost]# make
[root@localhost]# make install
[root@localhost]# make clean
Now test the ability of FreeTDS to connect to your MSSQL
server:
[root@localhost]# /usr/local/bin/tsql -S <mssql.servername.com> -U
<ValidUser>
Password: <password>
With luck, you'll see the following prompt
1>
Then, use Ctrl-C to exit.
If the tsql command doesn't return the 1> prompt, verify
that you can get to your MSSQL server with
[root@localhost]# telnet <mssql.servername.com> 1433
If you're able to telnet to port 1433, try opening the
Microsoft Query Analyzer. Use the login combination you tried above
to verify that a user name and password combination exists for your
SQL server.
Once you can get the 1> prompt from tsql, we can configure
the TDS driver and make the ODBC connection.
[root@localhost]# cd /usr/local/etc
From /usr/local/etc/, edit freetds.conf. At the end of this
file, add an entry something like this:
[TDS]
host = mssql.serverhost.com
port = 1433
tds version = 7.0
Set Up the ODBC Data Source
Red Hat comes with a graphic interface tool called
ODBCConfig. We'll use it to set up our DSN.
From KDE, select K -> System -> ODBCConfig
From GNOME, select G -> Programs -> System ->
ODBCConfig
Click on the Drivers Tab and click Add. The window should
contain the following data:
Name: TDS
Description: v0.60 with Protocol v7.0
Driver: /usr/local/lib/libtdsodbc.so
Setup: /usr/lib/libtdsS.so
FileUsage: 1
The rest can be blank. Click the check-mark in the upper
left-hand corner. Select the TDS driver and click OK.
Name: MSSQLServer
Description: TDS MSSQL (description isn't important)
Servername: mssql.serverhost.com
UID: sa
PWD:
Port: 1433
Then click the System DSN tab and select Add. Test out the
unixODBC connection with:
[root@localhost]# isql -v MSSQLServer username password
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
SQL> use Northwind
0 rows affected
SQL> SELECT TOP 1 CategoryName from Categories
+-------------------------------+
| CategoryName
| Beverages
1 rows affected
SQL> quit
Now to test it using PHP, put this page in a web-viewable
directory and try to view it from the browser.
--- begin odbctest.php---
<?
// connect to DSN MSSQL with a user and password
$connect = odbc_connect("MSSQLServer", "username", "password") or die
("couldn't connect");
odbc_exec($connect, "use Northwind");
$result = odbc_exec($connect, "SELECT CompanyName, ContactName " .
"FROM Suppliers");
while(odbc_fetch_row($result)){
print(odbc_result($result, "CompanyName") .
' ' . odbc_result($result, "ContactName") . "<br>\n");
odbc_free_result($result);
odbc_close($connect);
?>
--- end odbctest.php --
Should a SQL statement contain an error, PHP will return a
cryptic, incomprehensible warning message via the driver. In
debugging, it has been helpful to echo out the offending
queries.
The web application that instigated this setup is now in its
third month of production and is performing quite well.
Resources
www.freetds.org/userguide/
www.freetds.org/faq.html
www.unixodbc.org/doc/FreeTDS.html
www.php.net
email:
bumproad@earthlink.net
Load Disqus comments
Our discussions are
powered by Disqus
, which require JavaScript.
 
Linux Journal Week in Review
Sign up to get all the good stuff delivered to your inbox every week.
-->
Email
Sign Up
I give my consent to be emailed
The Value of Open Source Journalism
Subscribe and support our coverage for technology's biggest thinkers – with up to 52% savings.
Subscribe
Connect With Us
Linux Journal, representing 25+ years of publication, is the original magazine of the global Open Source community.
© 2024 Slashdot Media, LLC. All rights reserved.
PRIVACY POLICY
TERMS OF SERVICE
ADVERTISE
Footer Menu Column 2
Masthead
Authors
Contact Us
Footer Menu Column 3
RSS Feeds
About Us
×