PHP and SQL Server
1 sur 3
http://members.cox.net/midian/howto/phpMSSQL.htm
PHP and SQL Server Accessing Microsoft SQL Server with PHP Personal Links Articles Tutorials Editorials HOWTOs - Flex on Tomcat - Apache Tomcat Proxy - Apache Tomcat mod_jk - PHP and MS SQL Server - MySql Replication MySql Master-Master Replication Game Design Projects Resources Entertainment Links
Friday, November 1, 2007 PHP and SQL Server LAMP (Linux-Apache-MySQL-PHP) is a great combination for a dynamic web site. But sometimes, due to existing data structures, other data clients, or whatever reason, you don't want to use MySQL, but need Microsoft's SQL Server instead. For a Linux user, this may seem like an anethma, but real IT professionals will tell you, "You use the tool that fits the job." Similar to Windows, you can connect to a MS SQL Server through an open database connection data source name (ODBC DSN). FreeTDS provides a driver you can use to make this DSN. TDS (tabular datastream) is a protocol used by Sybase and Microsoft. This enables your Linux server to connect to a Microsoft SQL Server. Once the driver is installed, you can configure your ODBC connection to use the driver.
Install And Configure FreeTDS The first thing you need to do is to download and install the FreeTDS driver. You can get the source and compile it yourself from http://www.freetds.org/, but I prefer RPMs. Depending on your distribution of Linux, the version you want will vary. I'm running Red Hat Enterprise Linux 4 ES and CentOS 4, which are both almost identical. I installed freetds0.62.3-1 from http://rpmforge.net/user/packages/freetds/ After installing FreeTDS, you can check your driver by attempting to connect to the MSSQL Server. Of course, use the appropriate server name, username and password in your command line. # /usr/bin/tsql -S [mssql.servername.or.ip] -U [ValidUser] locale is "en-US.UTF-8" locale charset is "UTF-8" Password: [password] 1>
Enter "quit" to exit your successful connection. If the tsql command doesn't return the 1> prompt, verify that you can get to your MSSQL server with telnet [mssql.servername.or.ip] 1433 and that your username and password are valid. Next, edit your /etc/freetds.conf configuration file and add the following at the end of the file: [TDS] host = [mssql.servername.or.ip] port = 1433 tds version = 7.0
Setup ODBC Data Source Next, unixODBC needs to know about all ODBC drivers you intend to use. While you can use the GUI program that comes with unixODBC, you can also use the odbcinst command. First create a template file containing your FreeTDS setup information.
04/03/2009 16:02
PHP and SQL Server
2 sur 3
http://members.cox.net/midian/howto/phpMSSQL.htm
tdsdriver.template [FreeTDS] Description = v.062 with protocol v7.0 Driver = /usr/lib/libtdsodbc.so.0
Run odbcinst, telling it to install a driver entry using the template we just created. # odbcinst -i -d -f tdsdriver.template odbcinst: Driver installed. Usage count increased to 1. Target directory is /etc
This will add an entry to the end of the file /etc/odbcinst.ini [FreeTDS] Description Driver UsageCount
= v0.62 with protocol v7.0 = /usr/lib/libtdsodbc.so.0 = 1
Next you create a ODBC data source name, or DSN, so client applications can utilize the driver. Unlike the driver, which is used by all users on a server, the ODBC DSNs are specific to the user who installs them. If you want to install a DSN for a specific user, sign on as that user then follow these instructions: Once again, create a template then use odbcinst to create the DSN. tdsdatasource.template [MSSQLServer] Driver = FreeTDS Description = Sample Database Trace = No Server = [mssql.servername.or.ip] Port = 1433 Database = [database]
Run odbcinst, telling it to install the datasource entry using the template we just created. $ odbcinst -i -s -f tdsdatasource.template
Test your ODBC connection using isql. # isql -v MSSQLServer [username] [password] +----------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +----------------------------------+ SQL>
Use quit to exit the SQL command prompt.
Make it work with PHP To have a DSN for use with PHP, it has to be installed for the apache user, typically named "apache." Since you can't su to apache and install the DSN as above, you can add an entry to the /etc/odbc.ini file for all users and the apache user can make use of it. /etc/odbc.ini [MSSQLServer] Description = Sample Database Driver = FreeTDS Trace = No Server = [mssql.servername.or.ip] UID = [username] PWD = [password] Port = 1433
You must also have to have the php-odbc module installed. You can check using the following command:
04/03/2009 16:02
PHP and SQL Server
3 sur 3
http://members.cox.net/midian/howto/phpMSSQL.htm
# rpm -qa | grep php-odbc php-odbc-4.3.9-3.22.9
With the php-odbc module installed, in /etc/php.ini there should also be an entry under [ODBC]: extension = odbc.so
Connecting to your Database The last thing you need to do is to create a page that utilizes your ODBC DSN to connect to your database. Here is some sample code: index.php
W3C CSS2 W3C XHTML 1.0 Copyright © 2009 Charles Patterson
04/03/2009 16:02
