Connecting to SQL Server from Linux using Perl DBI
Download Software Components
Perl DBI Module :DBI-1.633.tar
http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.633.tar.gz
Download FreeTDS :freetds-stable.tar
http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
Perl DBD::Sybase Module :DBD-Sybase-1.15.tar
http://search.cpan.org/CPAN/authors/id/M/ME/MEWP/DBD-Sybase-1.15.tar.gz
Install DBI
1 - DBI-1.633.tar
perl Makefile.PL
make
make install
Install FreeTDS
2 - freetds-stable.tar
./configure --with-tdsver=7.0 --prefix=/usr/local/freetds
perl Makefile.PL
make
make install
Install DBD-Sybase
3 - DBD-Sybase-1.15.tar
# set||export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:/usr/local/freetds/lib
# set||export SYBASE=/usr/local/freetds
perl Makefile.PL
Use 'CHAINED' mode by default (Y/N) [Y]: Y
Use the threaded (lib..._r) libraries [N]: N
Sybase server to use (default: SYBASE): 192.168.0.98
User ID to log in to Sybase (default: sa): sa
Password (default: undef): 12345678
Sybase database to use on 192.168.0.98 (default: undef): TESTDB
make
make install
Configure FreeTDS
# vim /usr/local/freetds/etc/freetds.conf
[TESTDB]
host = 192.168.0.98
port = 1433
tds version = 8.0
dump file = /tmp/freetds.log
text size = 64512
SCRIPT TO TEST
#!/usr/bin/perl
use DBI;
my $user = "userdb";
my $passwd = "12345678";
my $server = "TESTDB";
my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0});
my $sth;
unless ($dbh) {
die "ERROR: Failed to connect to server ($server).\nERROR MESSAGE: $DBI::errstr";
} else {
print "\n";
print "Successful Connection.";
print "\n\n";
}
print "Current Connection Properties\n";
print "---------------------------------------------------------------------------------------\n";
my $sqlStatement = "select \@\@servername, \@\@version, db_name(), system_user, host_name()";
unless ($sth = $dbh->prepare($sqlStatement)) {
$dbh->disconnect;
die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
$dbh->disconnect;
die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while (( $serverName
, $sqlServerVersion
, $currentDatabase
, $currentUser
, $clientMachine) = $sth->fetchrow) {
($sqlServerVersion, @dummy) = split(/\n/, $sqlServerVersion);
print " SQL Server Instance : $serverName\n";
print " SQL Server Version : $sqlServerVersion\n";
print " Current Database : $currentDatabase\n";
print " Current User : $currentUser\n";
print " Client Machine : $clientMachine\n";
}
print "\n";
$sth->finish;
print "Available Databases\n";
print "-----------------------------------------------------------------\n";
my $sqlStatement = "select
isnull(db_name(dbid), 'MSSQL SYSTEM RESOURCE') as Name
, str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size
from sys.sysaltfiles
group by dbid
order by 2 desc";
unless ($sth = $dbh->prepare($sqlStatement)) {
$dbh->disconnect;
die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
unless ($sth->execute) {
$dbh->disconnect;
die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr";
}
while (($databaseName, $databaseSize) = $sth->fetchrow) {
printf(" %-40s %20s\n", $databaseName, $databaseSize);
}
print "\n";
$sth->finish;
print "Disconnecting from SQL Server.\n\n";
$dbh->disconnect;
exit(0);
