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-18.104.22.168.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 🙂
Recently I was creating a simple PHP webpage that required an MSSQL connection for a query. I installed the freetds-0.91-15 driver on my Oracle Linux and configured unixODBC-2.2.11-10.el5 appropriately. All went well and I soon had the first results on my page. My next task was to create a simple enough query that would ‘count’ some database rows. Simple enough, right? Well, at least up to the part where I ran into this error:
No tuples available at this result index in […]
All the posts I found referred to multiple result sets in which ‘odbc_next_result($result);’ should be the resolution. The weird part was, pasting of the echoed $sql into the MSSQL query box would give me the desired effect. One result with an count of the rows. The same SQL in the PHP script would give me the ‘no tuples’ error. I nearly fixed it the ugly way (counting the rows in an php while that would work) when I had an insight.
In the mssql output i noticed NULL fields in the table column that I was counting. So i figured, might counting NULL values somehow trigger the ‘No Tuples’ error im getting?
So this is what I ended up testing:
SELECT COUNT([sdk].[Events].[FirstName]) as Counted FROM [sdk].[Events] WHERE [sdk].[Events].[PeripheralName] like '%Search%' AND [sdk].[Events].[EventTime] BETWEEN '1' AND '31'
Which resulted in : Warning: odbc_fetch_array(): No tuples available at this result index in /var/www/db.class.php on line 52
SELECT COUNT([sdk].[Events].[FirstName]) as Counted FROM [sdk].[Events] WHERE [sdk].[Events].[PeripheralName] like '%Search%' AND [sdk].[Events].[EventTime] BETWEEN '1' AND '31' AND [sdk].[Events].[FirstName] is not null
Which resulted in an working count query.