
		Wednesday February 22, 2012 19:50:37 UTC

Perl access to Microsoft SQL + MySQL

At work, I was looking for a simple way to access a Microsoft SQL 2008 database via Perl on a Linux host so I could make some tools to access the DB. I needed this functionality for accessing the database of a legacy application that I did not want to change around. I also need the script to talk to MySQL as any future databases for related applications I plan to store in MySQL instead of Microsoft SQL. This is a quick write-up on how I got this working without having to use unixODBC.

Install FreeTDS

FreeTDS Mirror

tar xf freetds-current.tgz
cd freetds-0.92.dev.20110819  (replace with approriate version)
./configure --prefix=/usr/local/freetds --with-tdsver=7.1 --with-gnutls --with-openssl  (tls and ssl are optional)
make -j8
make install

Now edit the /usr/local/freetds/etc/freetds.conf and add this to the end of the file:

[sqlhostname]
        host = sqlhostname.domain.com
        port = 1433
        tds version = 7.1

Replace the hostname and domain name to a valid DNS name for your Microsoft SQL server. Make sure that your SQL server is accessible via TCP/1433 to the Linux host. You can then test connectivity to the DB.

$ /usr/local/freetdb/bin/tsql -S sqlhostname -U sa
$ Password:
$ locale is "C"
$ locale charset is "ANSI_X3.4-1968"
$ using default charset "ISO-8859-1"
$ 1> quit

Install DBD::Sybase and DBD::mysql

Download Sybase Module

tar xf DBD-Sybase-1.13.tar.gz  (replace with appropriate version)
cd DBD-Sybase-1.13
export SYBASE=/usr/local/freetds
perl Makefile.PL
make -j8
make install

cpan
cpan shell -- CPAN exploration and modules installation (v1.9456)
Enter 'h' for help.

cpan[1]> install DBD::mysql

Sample Perl script to access both databases

Here is a simple Perl script for accessing both databases. This example assumes the databases, tables, and permissions were already configured.

#!/usr/bin/perl

use DBI ;
use Data::Dumper ;

my $dbh1 = DBI->connect("dbi:Sybase:server=sqlhostname", "sa", 'password') ;
my $dbh2 = DBI->connect("dbi:mysql:;host=localhost", "user", 'password') ;
die "unable to connect to DB servers" unless $dbh1 && $dbh2 ;

my $ref1_= $dbh1->selectall_arrayref("SELECT * FROM testdb.dbo.testtable", { Slice => {} }) ;
my $ref2_= $dbh2->selectall_arrayref("SELECT * FROM testdb.testtable", { Slice => {} }) ;

print Dumper($ref1_) ;
print Dumper($ref2_) ;