Linux上使用Perl查询 Microsoft SQL Server

前面说到了《perl如何连接SQL Server数据库》，下面来实际应用下。

安装FreeTDS 

# yum install freetds.x86_64
# yum install freetds-devel.x86_64
# vim /etc/freetds.conf
[SQLSERVER] 
     host = 10.0.1.1  #SQL server服务器地址
     port = 1433
     tds version = 7.0

需要先安装EPEL源，EPEL源如何安装可以参见本博客以前内容。文章源自运维生存时间-https://www.ttlsa.com/perl/linux-using-perl-query-microsoft-sql-server/

测试

# tsql -S sqlserver.hostname.com -U sqlusername -D your_db_name
select * from sys.Tables;
GO
quit

安装DBD::Sybase

# export SYBASE=/usr 
# cpanm DBD::Sybase

cpanm安装可以参见本博客之前内容。文章源自运维生存时间-https://www.ttlsa.com/perl/linux-using-perl-query-microsoft-sql-server/

perl实例

#!/usr/bin/perl

#use lib ('./modules');
use DBI;
use DBD::Sybase;

my $database = "your_db_name";
my $username = 'sqlusername';
my $password = 'sqlpassword';

#SQLSERVER 字串对应于 /etc/freetds.conf 里面的 [SQLSERVER] 
$dsn = "DBI:Sybase:server=SQLSERVER;database=$database";

my $dbh = DBI->connect($dsn, $username, $password) or die "Database connection not made : $DBI::errstr";

my $sql = "SELECT TOP 5 * FROM httpstatus order by testtime desc";
my $sth = $dbh->prepare( $sql );
$sth->execute() or die "unable to execute query $sql, error $DBI::errstr\n";

$rows = $sth->rows;

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

$sth->finish();
undef $sth;
$dbh->disconnect();

print "Query successfully\n";