Tag Archives: odbc

Installing unixODBC 2.3.2 and higher on Ubuntu 12.04 LTS

Before we start – this tutorial assumes you’re using an Ubuntu Server, and you’re OK with removing your existing unixODBC driver manager and any problems that come with that.

OK – on to the goods.

  1. Remove any previous unixODBC packages – take note of any additional packages APT wants to remove so that you can reconfigure/reinstall/fix them later:
    $ sudo apt-get remove libodbc1 unixodbc unixodbc-dev
  2. (Optional – only necessary if you don’t use my .deb package) Get your system ready to compile software if you don’t already have make and gcc installed:
    $ sudo apt-get install build-essential

Now you have three choices – download, configure, and compile yourself, use my modified version of Microsoft’s “build_dm” script they offer with the SQL Server ODBC Driver for Linux, or use the unixodbc_2.3.2-1_amd64 Ubuntu 12.04 LTS package I built.

Personally – I’d choose the package as any other packages that depend on unixodbc or libodbc should easily install and be able to use our custom unixODBC to fulfill any package requirements.

Ubuntu deb package method:

  1. Get the package:
    $ wget http://onefinepub.com/wp-content/uploads/2014/03/unixodbc_2.3.2-1_amd64.deb
  2. Install the package:
    $ sudo dpkg -i unixodbc_2.3.2-1_amd64.deb

Automated script method:

  1. Get the automated build_dm.sh script here or use this command:
    $ wget https://raw.github.com/Andrewpk/Microsoft--SQL-Server--ODBC-Driver-1.0-for-Linux-Fixed-Install-Scripts/master/build_dm.sh
  2. Make sure it’s executable and then run it:
    $ chmod u+x build_dm.sh; sudo ./build_dm.sh --libdir=/usr/lib/x86_64-linux-gnu
  3. After it’s finished, the script will give you a /tmp/unixODBC.RANDOMNUMBERS directory which it tells you to change to, and then ‘make install’. An example of the command I ran is below – replace the XXXX’s with the exact path the script gave you upon it finishing:
    $ sudo su -c 'cd /tmp/unixODBC.XXXX.XXXX.XXXX/unixODBC-2.3.2; make install'

That’s it – unixODBC was automatically configured with some options the Microsoft ODBC driver recommends and the make target “install” was executed.

Do it yourself method:

  1. Download unixODBC

    $ wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gz
  2. Ungzip and untar the gzipped tarball – this example uses a modern gnu tar:
    $ tar -zxvf unixODBC-2.3.2.tar.gz
  3. Change to the new directory that has been created:
    $ cd unixODBC-2.3.2
  4. Configure with any custom options you want – this is an example for Ubuntu 64-bit using the recommendations provided by the Microsoft ODBC driver for server installations (note: if you’re installing on a headless server, you may want to add “–enable-stats=no” to increase performance):
    $ ./configure --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --libdir=/usr/lib/x86_64-linux-gnu --prefix=/usr --sysconfdir=/etc
  5. Make the install target with root privileges:
    $ sudo make install

PHP segfaults with pdo_odbc and bound parameters on 64-bit platforms

The title says it all with this post: PHP segfaults with pdo_odbc on 64-bit platforms when using a query that has bound parameters (named, indexed/placeholder, bindParam(), and bindValue() in any combination).

I’ve submitted a pull request (which fails its Travis build due to 5.5.9 failing its Travis build) to keep this in the minds of the php maintainers as it’s a pretty severe problem for people using php in a more “corporate” environment (where postgres and mariadb/mysql aren’t as pervasive).

With our millions of records stored in MS SQL and iSeries DB2 UDB databases at my current employer – this is a huge problem. We’re basically confined to 32-bit environments unless we want to pay for an additional method to connect to the iSeries (IBM DB2 Connect) and even then we’d be reliant on the MS SQL ‘sqlsrv’ php driver which I’ve found to be incredibly slow with medium-sized or larger data sets.

This hasn’t been a huge problem yet for most people using Windows since IIS’ fastcgi support seems to be only 32-bit currently, but with the way Azure has been getting pushed and adopted I would assume that a demand for 64-bit fastcgi apps on Azure will be approaching soon.

php 5.5 32-bit on Azure x64

PHP 5.5 is pre-installed as 32-bit on a Microsoft Azure 64-bit “Standard” scaled “Website”.

While bugs for this issue have been outstanding for quite some time, I’ve compiled a version of pdo_odbc as a shared extension with the patches people have agreed upon. After taking a look at the history of pdo_odbc – my shared extension may work with php versions as far back as the last stable release of the 5.3 branch and has been compiled on Ubuntu 13.10 x64 (so it should work on most 64-bit Ubuntu/Debian derivatives that have glibc 2.14+) against the php 5.5.9 stable source.

The extension is relatively simple to toss in to your php installation – but use it at your own risk. I’ll try to remember to keep it updated – but hopefully this will just get fixed upstream.

Here’s a link to the php 5.5 (5.5.9 to be precise) 64-bit patched pdo_odbc shared extension compiled on Ubuntu (Ubuntu 13.10 – but should work on most modern Ubuntu/Debian variants without any problems).

On non-Ubuntu/Debian platforms, you may get an error like the following:

"PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/pdo_odbc.so' - libodbc.so.1: cannot open shared object file: No such file or directory"

You’ll probably need to create some symlinks.

If you get an error about glibc – it’s because I’ve compiled this against glibc 2.14 initially. This is a problem for both Ubuntu 12.04 LTS and CentOS, so I’ll likely be recompiling this against glibc 2.12 in the very near future.

I have been somewhat vague with this post on purpose compared to my normal “tutorial” type posts I do, due the technical nature of the problems described here. You should not be following a tutorial or step-by-step procedure without fully understanding the steps you’re executing when it comes to hacking extensions/patches into your programming language runtime – unexpected results may occur, which is why I’ve posted my compiled extension AS-IS with a “Use at your own risk” disclaimer.

Microsoft-SQL-Server

How To Install Microsoft SQL Server ODBC Driver for Linux on Ubuntu Server

UPDATE: I’ve included a list of items to consider when connecting to a Microsoft SQL Server from Linux here. Please review this if you’re starting out and don’t fully understand your possibly choices (using your programming language of choice’s driver, using the Microsoft ODBC driver for linux, or possibly using an open-source driver such as freetds).

UPDATE 2: I’ve included a new link to a tutorial I wrote on how to install an updated unixODBC on Ubuntu Server.

Need to connect to a Microsoft SQL Server on Linux? Your best bet is to use their ODBC drivers (available here) – but OH NO – they’re only supported via Red Hat Enterprise Linux.

No fear – of course they’ll work on most 64bit distributions.

Install Instructions for Ubuntu Server 12.10 – this should actually work for most Debian/Ubuntu distributions that have packages available for openssl-1.0.0 and unixODBC 2.3.0 (though you may want to download and install 2.3.2 for better performance):

The following numbered steps have become mostly unnecessary. I’ve fixed the Microsoft scripts so you no longer have to use “–force” to install the driver on Ubuntu and create all the symlinks yourself.
Those fixed scripts are available here: Microsoft SQL Server ODBC Driver 1.0 for Linux Fixed Install Scripts.
To use the modified scripts you can:

To install the driver manually:

  1. Visit http://www.microsoft.com/en-us/download/details.aspx?id=36437 and download the file for “RedHat6\msodbcsql-11.0.2270.0.tar.gz”. Currently you can use the following command until the link changes:
    $ wget http://download.microsoft.com/download/B/C/D/BCDD264C-7517-4B7D-8159-C99FC5535680/RedHat6/msodbcsql-11.0.2270.0.tar.gz
  2. Extract the tarball:  tar -zxvf msodbcsql-11.0.2270.0.tar.gz
  3. Download and Install unixODBC 2.3.0+ if you haven’t already
  4. change to the new directory ( cd sqlncli-11.0.1790.0 ) and run the install script:
    sudo bash install.sh install --accept-license --force
  5. make sure the SQL Server dependencies are installed:
    sudo apt-get install openssl libkrb5-3 libc6 e2fsprogs
  6. Create some symlinks so everything works with the paths these binaries are expecting to find libraries:
    • sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/x86_64-linux-gnu/libcrypto.so.10;
    • sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/x86_64-linux-gnu/libssl.so.10;
    • sudo ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.2.0.0 /usr/lib/x86_64-linux-gnu/libodbcinst.so.1;
    • sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0 /usr/lib/x86_64-linux-gnu/libodbc.so.1

And that should be it. The last two steps are dependent on unixODBC-2.3.1 or higher. If you’re using 2.3.0 (please upgrade) you’ll need to link against the “1.0.0” libraries.

Test your install by connecting to your server using sqlcmd (sqlcmd -S my.sql.server.com -U username) to make sure everything is ok. You should now be able to configure ODBC to use the MS SQL ODBC Driver for Linux on Ubuntu.

Yes – in that last steps we’re creating  symlinks from “2.0.0” with a link names “.so.1” – masquerading as the older version 1 doesn’t seem to hurt anything in any of my installations. According to the unixODBC changelog: “Major change is to change the library version number from 1 to 2 to signal the SQLLEN change for 64 land. Should have been done for 2.3.0, but better late than never. So if after installing you have apps that can’t find libodbc.so, its likely they are linked to libodbc.so.1, so just create a symlink from libodbc.so.2”

So I think we’re OK.