Connecting MSSQL via Linux

reference: http://www.linuxjournal.com/article/5732

Ever wondered how you can connect to a Remote MSSQL server via a simple perl script?  I’ve got here a host running on a 64bit CentOs5 and have managed to get the connection up and running.  I hope my guide below is of help to you!

cd /usr/src
wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
wget http://search.cpan.org/CPAN/authors/id/M/ME/MEWP/DBD-Sybase-1.08.tar.gz
tar -zxvf freetds-stable.tgz
tar -zxvf DBD-Sybase-1.08.tar.gz
cd freetds-0.82
./configure –with-tdsver=7.0 –prefix=/usr/local/freetds
make
make install
export SYBASE=/usr/local/freetds
cd ..
cd DBD-Sybase-1.08
vi CONFIG    (under enable the 64)
perl Makefile.PL (when asked for threaded, select YES)

#Next configure your SQL connection.  You’ll need to edit freetds.conf under /usr/local/freetds

You’ll see something like this:

[MSSQL]
             host = PutYourServerNameHere
             port = 1433
             tds version = 7.0

Replace the hostname to the one you’ll be connecting to and save it.

Yup if there are no complications, you are done.  Perl is ready to rock and roll with your SQL!  You can test it with the script below which connects to the Default NorthWind database; thanks to Trevor Price.

Copy and paste the following to a file and name it test.pl
Type perl test.pl in your favourite shell to run.

Listing 1. Trevor Price’s Perl Script that Queries the Sample Database Called Northwind #!/usr/bin/perl # # test the db2 dbi driver # use DBI ; $user = 'sa' ; $passwd = 'password' ; $dbh = DBI->connect('DBI:Sybase:server=file1', $user, $passwd); $dbh->do("use Northwind"); $action = $dbh->prepare("sp_help") ; $action->execute ; $rows = $action->rows ; print "rows is $rows\n"; while ( @first = $action->fetchrow_array ) { foreach $field ( @first ) { print "$field\t"; } print "\n"; } exit(0);

Issues & Solutions

1 Issue	During compilation of DBD-Sybase dbdimp.c:5317: warning: format â%ldâ expects type âlong intâ, but argum86ent 5 has type âCS_INTâ make: *** [dbdimp.o] Error 1
solution	Open dbdimp.c and add the following line somewhere near the top; I added it just below #include "Sybase.h" #define BLK_VERSION_150 BLK_VERSION_100 #define BLK_VERSION_125 BLK_VERSION_100 #define BLK_VERSION_120 BLK_VERSION_100
2 Issue	During execution of perl test.pl install_driver(Sybase) failed: Can’t load ‘/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Sybase/Sybase.so’ for module DBD::Sybase: libct.so.4: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230. at (eval 3) line 3 Compilation failed in require at (eval 3) line 3. Perhaps a required shared library or dll isn’t installed where expected at testsql.pl line 12
solution	This is due to library path issues.  ln -s for all files in /usr/local/freetds/lib to /lib64 *note: ln -s to lib instead of lib64 directory if you’re using 32bit version of linux