Setting up FreeTDS Against Sybase with Ruby

Connecting to Sybase from within Linux is not as easy as, say, connecting to PostgreSQL or MySQL, but if you know the steps involved it can be rather painless. To connect we are going to use the FreeTDS project and ODBC.
Configuring the FreeTDS Client

First step is to install the freetds package.
Arch Linux: pacman -Sy freetds
Debian Linux: apt-get install freetds-common

Next, setup your /etc/freetds/freetds.conf. In this example, our alias is “PROD”
1 2 3 4

[PROD]
host = sybase_hostname
port = sybase_port
tds version = 5.0

view raw
freetds.conf hosted with ❤ by GitHub

We can now test freetds with the included sqsh program:
sqsh -S PROD -U -P
Configuring UNIX ODBC Connections

Once successful with FreeTDS, we now will configure unix ODBC to work with it. This will let us use a plethora of tools and languages to interact with our database, including ruby.

Arch Linux: pacman -Sy unixodbc
Debian Linux: apt-get install unixodbc

/etc/odbcinst.ini
1 2 3 4 5 6 7

[TDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
FileUsage = 1
CPTimeout =
CPReuse =

view raw
odbcinst.ini hosted with ❤ by GitHub

/etc/odbc.ini
1 2 3 4 5 6

[prod]
Driver = /usr/lib/libtdsodbc.so
Description = ODBC
Trace = No
Servername = PROD
Database =

view raw
odbc.ini hosted with ❤ by GitHub

We can now test with isql. This program can also be used to run sql scripts, for example via cron. This is a great way to run miscellaneous reports and queries.
isql -v qual1
Configuring Ruby

gem install dbi
gem install dbd-odbc
gem install ruby-odbc

Simple test script
1 2 3 4 5 6 7 8 9 10 11 12

!/usr/bin/ruby

require ‘dbi’
require ‘odbc’

dbh = DBI.connect(‘DBI:ODBC:prod’, ‘username’, ‘password’)
query = “select getdate()”
row = dbh.select_one(query)

puts “got a value of: “ + row[0].to_s

dbh.disconnect if dbh

view raw
odbc_ruby_example.rb hosted with ❤ by GitHub