扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
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 server.
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
suto 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
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> "); } 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.
如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。
现场直击|2021世界人工智能大会
直击5G创新地带,就在2021MWC上海
5G已至 转型当时——服务提供商如何把握转型的绝佳时机
寻找自己的Flag
华为开发者大会2020(Cloud)- 科技行者