Saturday, September 25, 2010

Setup Perl in Solaris 10 to talk to Microsoft SQL Server 2000.

I tested following solution in Solaris 10 to connect to Microsoft SQL Server 2000, But it should work with MS SQL 2005. (Maybe with MS SQL 2008 as well)
In Unix/Linux, there are two typical ways to have Perl to talk to Microsoft SQL Server.
1)  Perl DBI + Perl DBD::ODBC + ODBC Driver
2)  Perl DBI + Perl DBD::Sybase + Freetds driver
I choose Freetds solution  because Unix ODBC driver is not free (
http://www.unixodbc.org/).
Install complier and build tools
Compiler choices for Solaris: 1) GCC For Sun Systems: Compatible with gcc and optimised for Sun hardware
free download from Oracle.
2) Solaris Studio: Solaris Native complier. free download from Oracle 

3) gcc GNU compiler
Free download from  http://www.sunfreeware.com/  or retrieve from Solaris 10 CD (SUNWgcc)
My Personal opinion: “GCC For Sun Systems” is the best choice for compatibility  and performance,GNU gcc is the last choice.
Build tools choices for Solaris: 1) GNU build tools
NAME: SUNWgmake  SUNWbinutils SUNWgm4 
DOWNLOAD: Free download from http://www.sunfreeware.com/ or retrieve from Solaris 10 CD
PATH: /usr/sfw/bin/{gmake,gld,gnm,gar,gas,ggprof,gm4,granlib,gsize,gstrip}
2) SUN build tools
NAME:SUNWbtool
DOWNLOAD: Retrieve from Solaris 10 CD
PATH:/usr/ccs/bin/{make,ld,nm,ar,as,gprof,m4,ranlib,size,strip}
I recommend to start with SUN build tools, if it doesn’t work try GNU build tools.
If you choose GNU build tools, you may need to create links to remove prefix “g” and rename dir /usr/ccs/bin (in case SUN build tools are picked up first)
 for i in   {gmake,gld,gnm,gar,gas,ggprof,gm4,granlib,gsize,gstrip}; do echo ln -s $i ${i#g}; done

Setup Freetds, Perl DBI and Perl DBD::sybase
Install Freetds
##Download
  Download source code from:
http://www.freetds.org/
##Install
./configure --with-tdsver=8.0; make ; make install
tdsver=8.0 is compatible with MS SQL 2000, but it has been reported that SQL 2005 works with it as well.
By default, freetds binaries are installed in /usr/local/bin and libraries are installed in /usr/local/lib. you can override this with --prefix.
##add /usr/local/lib to search path.
If your crle output doesn't include /usr/local/lib, you can append it current lib dirs e.g 
crle -u -l /lib:/usr/lib:/usr/local/lib
(the equivalent Linux command is ldconfig)

##Test Login
   /usr/local/bin/tsql -H 172.1.1.1 -p 1433 -U 'username' -P password -D DB-NAME
(tsql is just for testing connection, The interactive shell working with freetds is http://shellsql.sourceforge.net/, so you can run sql command in shell script with shellsql!)
make sure above command works before continue to next step
Install Perl DBI
download from
http://search.cpan.org/
perl Makefile.PL; make ; make install
Install Perl DBD::Sybase
download from
http://search.cpan.org/
export SYBASE=/usr/local;
perl Makefile.PL; make ; make install

Sample Perl Script
#!/usr/local/bin/perl -w
use DBI;
my $dbh = DBI->connect('DBI:Sybase:server=172.1.1.1;port=1433','username,'password') or die $DBI::errstr;
$dbh->do("use DB-NAME");
$SQL= "SELECT uid,name from employee;";
my $ids = $dbh->selectall_arrayref( "$SQL", { Slice => {} } );
foreach my $id ( @$ids ) {
print "$id->{uid} | $id->{name} \n";
}

No comments:

Post a Comment