32 bit ODBC driver with 32 bit unixODBC on a 64 bit Ubuntu OS

In this article I’m going to build a 64 bit ubuntu 10 OS, and then install 32 bit unixodbc and get it to connect to a remote odbc data source using a third party 32 bit driver. No 64 bit driver available :-(


If I manage to pull it off, and then get ruby-odbc to work, I won’t have to build and manage a new server in order to get data from a remote data source into our current ruby on rails applications.

First up, lets install the OS

I’m building the OS with VMware Fusion on my mac using ubuntu-10.04.2-server-amd64.iso

Do not install any additional packages during the OS install

sudo locale-gen en_GB.UTF-8
sudo /usr/sbin/update-locale LANG=en_GB.UTF-8
sudo aptitude update
sudo aptitude safe-upgrade
sudo reboot
sudo aptitude -y install ssh
sudo aptitude install build-essential linux-headers-`uname -r`
sudo ln -s /usr/src/linux-headers-`uname -r` /usr/src/linux
sudo reboot
sudo aptitude install ia32-libs
sudo apt-get install g++-multilib
mkdir ~/src
cd ~/src
wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
tar -xzvf unixODBC-2.3.0.tar.gz
cd unixODBC-2.3.0/
CFLAGS=-m32 LDFLAGS=-m32 CXXFLAGS=-m32 ./configure
sudo make install

That has installed the 32 bit unixodbc libraries into /usr/local/lib, you can confirm that by running

file /usr/local/lib/libodbcinst.so.1.0.0

should say

/usr/local/lib/libodbcinst.so.1.0.0: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), dynamically linked, not stripped

Now upload the third party sassafras driver into that directory, and then test it

ldd /usr/local/lib/libksodbc.dbg.so
linux-gate.so.1 => (0xf770e000)
libgssapi_krb5.so.2 => /usr/lib32/libgssapi_krb5.so.2 (0xf7632000)
libm.so.6 => /lib32/libm.so.6 (0xf760c000)
libc.so.6 => /lib32/libc.so.6 (0xf74b1000)
libkrb5.so.3 => /usr/lib32/libkrb5.so.3 (0xf7400000)
libk5crypto.so.3 => /usr/lib32/libk5crypto.so.3 (0xf73dc000)
libcom_err.so.2 => /lib32/libcom_err.so.2 (0xf73d8000)
libkrb5support.so.0 => /usr/lib32/libkrb5support.so.0 (0xf73d0000)
libdl.so.2 => /lib32/libdl.so.2 (0xf73cb000)
libkeyutils.so.1 => /lib32/libkeyutils.so.1 (0xf73c7000)
libresolv.so.2 => /lib32/libresolv.so.2 (0xf73b3000)
/lib/ld-linux.so.2 (0xf770f000)
libpthread.so.0 => /lib32/libpthread.so.0 (0xf739a000)

Voila, it all looks rosey in the garden.

Now we need to define our odbc connections in /etc/odbc.ini and /etc/odbcinst.ini

These settings are specific to the driver you are using, check with the driver manufacturers for your recommended setup

sudo vi /etc/odbc.ini
Driver = ksodbc
Description = foobar
RemoteHost = [IP ADDRESS]

sudo vi /etc/odbcinst.ini
Description = Sassafras Keyserver Database
Driver = /usr/local/lib/libksodbc.dbg.so
UsageCount = 1

Let’s test it

isql -v ksdsn [username] [password]

| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |

If you get that back, you are at a joyous moment in time when you are connected.

Happy days.

Thanks to the good people on unixODBC mailing list for a lot of help with this process, the ubuntu forums of course, without whom I would be a lesser man, and last but not least, Julian Delvin at sassafras.

Part 2 will be configuring ruby and then rails to talk to the database, wish me luck!