Perl: Connecting to Sybase and MSSQL using DBD::ODBC with unixODBC and FreeTDS

Someone asked me if I could figure out how to connect to a Sybase instance using Perl DBD, and my first thought was "Sure, should be easy". I know there was a DBD::Sybase module out there and I could just hop onto CPAN and have it running in no time. A day or so later, and a lot of web crawling and I finally had a solution that worked for our situation. 

In this particular instance we have a Perl web application running that monitores and controls our internal systems. The Syabase instance was a remote one, so I needed to setup the Perl modules without having a local Sybase install, this turned out to be the first hurdle to overcome. After looking around I found the freeTDS project, http://www.freetds.org/. For anyone who has worked in Java and connecting to Sybase or MSSQL, you might know jTDS, which is a similar Java implementation that I have used in the past.

I decided to download and compile the latest stable release as opposed to installing with yum which would allow me to package it with my application install if needed.The latest distibution can be obtained from  http://www.freetds.org/ using the "stable" link on the right side of the page. After downloading, I extraced to a work directory and did the standard configure-make-make install, using a custom install directory to put it in my project space.

tar -vxf freetds-patched.tar.gz
cd freetds-1.00.23/
./configure --prefix=/dashboard/odbc/freetds
make
make install

After installing, I began trying to install the Perl Sybase modules, but quickly ran into several problems. The first thing was to set the SYBASE envinroment variable to the freeTDS home and try to install DBD::Sybase.

export SYBASE=/dashboard/odbc/freetds
cpanm --verbose DBD::Sybase

Notice that I included a –verbose switch in the cpanm call. The Sybase modules, which all seem to wrap sybperl, need to run several tests against a running Sybase instance. I was never able to get a clean install and the tests kept failing. Even after forcing the install and skipping the tests, when trying to connect I was geting buffer overload dumps and many, many other errors. I was able to connect manually using freeTDS from a command line test using tsql in /dashboard/odbc/freetds/bin, so I knew the library wasn't the issue, it was something in the Perl module. At this point I decided to go another route.

After a little research I found the DBD::ODBC module which would facilitate connections to both Sybase and MSSQL, so I decided to go down that path. This required another linux package to be installed and an ODBC include in the freeTDS insallation. I downloaded the lates stable unixODBC package from http://www.unixodbc.org/ and got to work. Once again I added a custom install path to allow inclusion in my project space and did the configure-make-make install. I also found a lot of valuable information at the unixODBC site for linking these two libraries, How to use unixODBC with FreeTDS.

tar -vxf unixODBC-2.3.4.tar.gz
cd unixODBC-2.3.4
./configure --prefix=/dashboard/odbc
make
make install

Now that I had the unixODBC library installed, I just need to add the freeTDS odbc driver and create a reference in the unixODBC installation. FreeTDS needs to be configured with a switch to tell it to install the ODBC library. The switch points freeTDS to the unixODBC home:  –with-unixodbc=/dashboard/odbc

cd freetds-1.00.23/
./configure --prefix=/dashboard/odbc/freetds --with-unixodbc=/dashboard/odbc
make
make install

Now the freeTDS driver needs to be register with unixODBC. This can be done using the bin/odbcinst in the unixODBC bin directory. (see How to use unixODBC with FreeTDS for additional details). I tried to make this as easy as possible so I could replicate it on mutiple servers.

(
echo [FreeTDS]
echo Description     = v1.00
echo Driver          = /dashboard/odbc/freetds/lib/libtdsodbc.so
)>"/dashboard/odbc/bin/tds.driver.setup"

/dashboard/odbc/bin/odbcinst -i -d -f /dashboard/odbc/bin/tds.driver.setup

This will create a setup template and then install the driver refence in /dashboard/odbc/etc/odbcinst.ini. Basically just tells unixODBC where to look for the freeTDS driver when you setup the DSN later.

Now that the two required libraries were install, I could install DBD::ODBC.After the first try and fail, I saw in the install log that DBD::ODBC needs an environment variable for ODBCHOME, which is just the location of the unixODBC installation: export ODBCHOME=/dashboard/odbc, after that it's pretty painless.

export ODBCHOME=/dashboard/odbc
cpanm DBD::ODBC

Now that everything is ready to go, the most difficult part is determining how to setup the DBD connecter in a Perl module. It turned out that setting up the DSN was the trickiest part. There is plenty of information on how to setup freeTDS.conf and the odbc.ini files in order to run command line connections, but suprisingly little on setting up a regular DBD connector. After searching around Google I was able to find some posts at perlmonks.org (http://www.perlmonks.org/?node_id=1008467) and various other places to complete a valid DSN. One thing to note is that with freeTDS, you need to specify a TDS version to use. The latest version is 8.0, which will work with the latest releases of MSSQL. If you are using Sybase, you need to specify version 5.0. The complete dsn (for Sybase) should be something along the lines of:

my $dsn = "dbi:ODBC:DRIVER={FreeTDS};Server=$server;Port=$port;TDS_Version=5.0;database=$database;";

I wrote a quick test scipt to validate my solution, and away I went.

#!/usr/bin/env perl
use Data::Dumper;
use DBI;

my $database = 'sbtest';
my $server = 'sybasetestserver.com';
my $port = 5000;
my $user = 'sb_user';
my $passwd = 'sb_password';
my $dsn = "dbi:ODBC:DRIVER={FreeTDS};Server=$server;Port=$port;TDS_Version=5.0;database=$database;";
my $dbh = DBI->connect("$dsn", "$user", "$passwd");
die "Unable for connect to server $DBI::errstr" unless $dbh;

# Get the table names from the database and dump the hash rows to stdout
my $sql = "select name from sysobjects where type = 'U'";
my $sth = $dbh->prepare($sql);

$sth->execute();
while (my $row = $sth->fetchrow_hashref) {
    print Dumper($row);
}

1;

 

Hopefully this will be helpful for others. I had to really look around to find a simple way to connect to Sybase in Perl. The existing sybperl implementation seems to be broken, or at least it did not work at all for me using freeTDS.