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-194.0.0.0.3.el5xen #1 SMP Mon Mar 29 18:27:00 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
  1. 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
  2. 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:
     wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz

    . Make sure you are in an desired location like your home folder.

  3. Unpack the tar-Gzip ball by running the following command
    tar -xf  ./freetds-stable.tgz
  4. Browse into the unpacked folder and run the configure command
    ./configure --with-tdsver=7.2 --enable-msdblib
  5. If the configure ran without any issues you can link/compile the driver by running the command:
    make

    and then

    make install

    and then

    make clean

    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.

  1. unixODBC is configured properly by using the
    odbcinst

    command.

  2. unixODBC will write into:
    odbc.ini

    files and uses input files to do so;

  3. 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)
    screenshot
  4. Documentation on how to use FreeTDS in conjunction with unixODBC can be found here.
    http://www.freetds.org/userguide/odbcconnattr.htm
  5. Documentation on how to use ODBC can be found here
    http://www.unixodbc.org/odbcinst.html
    (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

  1. Create the file /etc/odbcDriver.ini
  2. Insert the following in the file (check the paths)
    [FreeTDS]
    Description     = FreeTDS Driver with protocol v5.0
    Driver          = /usr/local/freetds/lib/libtdsodbc.so
    
  3. Create the file /etc/odbc.ini
  4. 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.
    
  5. Register the ODBC driver
     odbcinst -i -d -f /etc/odbcDriver.ini 
  6. Register the data source
     odbcinst -i -s -f /etc/odbc.ini

Finally test your config by using the php odbc functions.

<?php
$sql = "select 1 + 5 as outcome";

$conn = odbc_connect("ExampleSource" , "Username", "Password");
$result = odbc_exec($conn, $sql);
$row = odbc_fetch_array($result);
echo $row['outcome'];

Good luck queryingūüôā

About Chris Gralike

Momenteel ben ik manager van de afdeling business continuity bij de zakelijke IT dienstverlener AMIS Services BV. Sinds 2003 ben ik actief in de ICT branche. Tussen 2003 en nu heb ik verschillende rollen vervuld. In de rollen: systeem- en netwerkbeheer, system engineer, servicemanager en nu practice manager ben ik in contact gekomen met uiteenlopende technologie√ęn, methodologie√ęn, idee√ęn, oplossingen en innovaties. Een rijke ervaring waarmee ik de klanten van Conclusion en AMIS elke dag probeer te ondersteunen. Mijn credo: 'Altijd opzoek een win-win tussen business en technologie.'

Posted on January 29, 2015, in Linux, Linux Commands, PHP and tagged , , , , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: