Configure unixODBC for use with PHP and MSSQL on Oracle Linux
This short article describes how to configure unixODBC 2.2.11-10.el5 in conjunction with PHP 5.3.3-26. Many forums out there contain articles that describe the absence of php_mssql drivers in the oracle yum repo. There various reason for that; non in which Oracle has a part. No matter what reason you like best, there is a decent alternative by using unixODBC.
To help all the people out that are just looking for a solution I wrote this article. I wont go into depths, ill just describe the major steps with some hints and tips. Happy reading 🙂
The OS version of my virtual box image:
Linux sandboxpinguin 2.6.18-126.96.36.199.3.el5xen #1 SMP Mon Mar 29 18:27:00 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
- Make sure you have the latest version of unixODBC installed. If Yum is configured correctly the following command should do the trick.
yum update unixODBC
- Download the freeDTS driver, this is the driver unixODBC will use to connect to mssql. If wget is available on your production environment (remove it) after running the command:
. Make sure you are in an desired location like your home folder.
- Unpack the tar-Gzip ball by running the following command
tar -xf ./freetds-stable.tgz
- Browse into the unpacked folder and run the configure command
./configure --with-tdsver=7.2 --enable-msdblib
- If the configure ran without any issues you can link/compile the driver by running the command:
Congratulations, you are now the pride owner of the freeDTS driver 🙂
The next part tend to get a bit fuzzy, feel free to ask questions in the comment and ill try to answer them to the best of my ability.
There are allot of articles available on how to configure the unixODBC DSN correctly. Be adviced: the config is specific for your setup and usually needs to be tweaked. In order to enable you to ill explain the concepts of unixODBC. Ill point out some documentation, commands and stuff. Afterward ill have a short tutorial of the steps i used to configure the odbc connection.
- unixODBC is configured properly by using the
- unixODBC will write into:
files and uses input files to do so;
- The connection can be tested with osql commands from bash, but! it will use the hidden .odbc.ini file in your profile instead of the /etc/odbc.ini. PHP and odbcinst use the one in /etc/odbc.ini. If the one in your profile works than make sure it is identical to the one located in /etc/ directory. Below how the osql output will look if configured correctly. (charset isnt relevant in this stage)
- Documentation on how to use FreeTDS in conjunction with unixODBC can be found here.
- Documentation on how to use ODBC can be found here
(ignore the freetds configuration here and use ad4 to figure the settings out for your setup)
Next ill describe my steps in order to make it work.
Configuring the FreeTDS driver
- Create the file /etc/odbcDriver.ini
- Insert the following in the file (check the paths)
[FreeTDS] Description = FreeTDS Driver with protocol v5.0 Driver = /usr/local/freetds/lib/libtdsodbc.so
- Create the file /etc/odbc.ini
- Insert the following and tweak this to match your environment
[ExampleSource] Description = FreeTDS Driver with protocol v5.0 Driver = /usr/local/freetds/lib/libtdsodbc.so Server = [SERVERIP] Port = [REMOTE_TSQL_PORT] ClientCharset = UTF-8 TDS_Version = 7.1 Database = [DATABASENAME] Trusted_Connection = Yes # Required with most MSSQL environments.
- Register the ODBC driver
odbcinst -i -d -f /etc/odbcDriver.ini
- Register the data source
odbcinst -i -s -f /etc/odbc.ini
Finally test your config by using the php odbc functions.
<?php $sql = &amp;amp;quot;select 1 + 5 as outcome&amp;amp;quot;; $conn = odbc_connect(&amp;amp;quot;ExampleSource&amp;amp;quot; , &amp;amp;quot;Username&amp;amp;quot;, &amp;amp;quot;Password&amp;amp;quot;); $result = odbc_exec($conn, $sql); $row = odbc_fetch_array($result); echo $row['outcome'];
Good luck querying 🙂
Posted on January 29, 2015, in Linux, Linux Commands, PHP and tagged configuration, Data, for, FreeTDS, Linux, Make, mssql, ODBC, Oracle, PHP, Source, unixODBC, Work, You. Bookmark the permalink. Leave a comment.