Connecting to SQL Server from Perl on Linux

One of the tasks I recently had to do at the office was to get a program written in Perl and running on Linux to connect to and work with Microsoft's SQL Server. I thought that I would document the process here on my blog, in the off chance that it might help someone, and also so that I have a reference I can go back to in the future.

So, I went through the process of installing the latest stable FreeTDS driver. If you get the tarball and install using the 'configure, make, make install' method the files are installed under the '/usr/local' directory.

To create a connection to your SQL Server you have to modify the '/usr/local/etc/freetds.conf' file and add the details of your server. For example here is what the details of a SQL Server 2008:

# SQL Server 2008
[sql2008]
host = 231.321.452.871
port = 1433
tds version = 8.0

In order to test the connection you can use the 'tsql' command found under '/usr/local/bin' by passing it the Server, Username and Password variables:

tsql -S sql2008 -U [username] -P [password]

Note that if the connection is successful, you should get a very simple prompt (something like '1>') from which you can exit using the 'quit' or 'exit' commands.

So, now that we know that we have the FreeTDS driver working, we can set about getting access to this interface programatically, i.e. from our program.

For the next step we're going to install the DBD::Sybase module from CPAN and configure it to work with FreeTDS. When I first tried this I attempted to run the following command:

sudo cpan DBD::Sybase

This however, resulted in the following error:

Please set SYBASE in CONFIG, or set the $SYBASE environment variable at Makefile.PL line 103, <in> line 45.

This was a relatively simple fix which involved going into the '/root/.cpan/build/DBD-Sybase-1.09/CONFIG' file and changing the line:

SYBASE=$ENV{SYBASE}||'/opt/sybase'

To:

SYBASE=/usr/local

After making the change, run the command 'perl Makefile.PL' and just leave all of the values set to the default and when it comes to the part where it asks you for the server, database, username and password in order to test the install, enter the credentials for your server.

The next part is to simply run the 'make' command, which will go and compile the module. Trying this gives the following errors:

dbdimp.c:786: error: ‘BLK_VERSION_150’ undeclared (first use in this function)
dbdimp.c:786: error: (Each undeclared identifier is reported only once
dbdimp.c:786: error: for each function it appears in.)
dbdimp.c:790: error: ‘BLK_VERSION_125’ undeclared (first use in this function)
dbdimp.c:794: error: ‘BLK_VERSION_120’ undeclared (first use in this function)


In order to fix these errors, simply add the following lines after any #include statements in the 'dbdimp.c' file:

#define BLK_VERSION_150 BLK_VERSION_100
#define BLK_VERSION_125 BLK_VERSION_100
#define BLK_VERSION_120 BLK_VERSION_100

Run 'make' again, and this time it should build without any errors (although not without warnings). The next step is to run the 'make test' command to test the compiled code. Again, you should note that I've never gotten the code to pass all of the tests, my theory is that some of the tests are specific to a Sybase database and fail with SQL Server.

Once 'make test' finishes, run 'make install', which simply copies the compiled files accross to the correct place in the filesystem. Now, to test that what we've just done works, take the following code, paste it into a file, update it for you database and see if you get the correct return values:

#!/usr/bin/perl

use DBI;

$dsn = 'DBI:Sybase:server=sql2008';

my $dbh = DBI->connect($dsn, '[username]', '[password]');
die "Unable to connect to server $DBI::errstr" unless $dbh;

$dbh->do('USE [database]');

$query = 'SELECT * FROM [table]';
$sth = $dbh->prepare ($query) or die 'prepare failed\n';
$sth->execute() or die "unable to execute query $query error $DBI::errstr";

$rows = $sth->rows ;
print "$rows rows returned by query\n";

while ( @first = $sth->fetchrow_array ) {
foreach $field (@first) {
print "$field, ";
}
print "\n";
}

That's it! Hopefully you're able to access SQL Server through Perl running on Linux now. For more information please have a look at the links below:
http://www.perlmonks.org/?node_id=392385
http://www.n3ncy.net/UNIX/FreeBSD/FreeTDS.htm
http://lists.ibiblio.org/pipermail/freetds/2006q3/020583.html