perl如何连接SQL Server数据库

本文将提供一些perl连接Microsoft SQL Server数据库的实例。perl脚本运行在Windows和Linux平台。

Windows平台

如果在Windows平台下运行perl脚本，建议使用依赖DBI的两个模块包，提供标准的数据库接口模块。文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

DBD::ODBC文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

DBD::ADO文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

使用DBD::ODBC

如果选用DBD::ODBC，下面的实例代码将展示如何连接到SQL Server数据库：文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

use DBI; 
 
# DBD::ODBC 
 
my $dsn = 'DBI:ODBC:Driver={SQL Server}'; 
my $host = '10.0.0.1,1433'; 
my $database = 'my_database'; 
my $user = 'sa'; 
my $auth = ‘s3cr3t'; 
 
# Connect via DBD::ODBC by specifying the DSN dynamically. 
my $dbh = DBI->connect("$dsn;Server=$host;Database=$database", 
 $user, 
 $auth, 
 { RaiseError => 1, AutoCommit => 1} 
 ) || die "Database connection not made: $DBI::errstr"; 
 
#Prepare a SQL statement my $sql = "SELECT id, name, phone_number FROM employees "; 
my $sth = $dbh->prepare( $sql ); 
 
#Execute the statement 
$sth->execute(); 
 
my( $id, $name, $phone_number ); 
 
# Bind the results to the local variables 
$sth->bind_columns( undef, \$id, \$name, \$phone_number ); 
 
#Retrieve values from the result set 
while( $sth->fetch() ) { 
 print "$id, $name, $phone_number\n"; 
} 
 
#Close the connection 
$sth->finish(); 
$dbh->disconnect();

你还可以使用预先设置的一个系统DSN来连接。要建立一个系统DSN，可以这样访问控制面板->管理工具->数据源。文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

使用系统DSN连接，需要更改连接字符串。如下所示：文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

# Connect via DBD::ODBC using a System DSN 
my $dbh = DBI->connect("dbi:ODBC:my_system_dsn", 
 $user, 
 $auth, 
 { 
 RaiseError => 1, 
 AutoCommit => 1 
 } 
 ) || die "Database connection not made: $DBI::errstr";

使用DBD::ADO

如果选择DBD::ADO模块，下面的实例展示如何连接到SQL Server数据库。文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

use DBI; 
 
my $host = '10.0.0.1,1433'; 
my $database = 'my_database'; 
my $user = 'sa'; 
my $auth = ‘s3cr3t'; 
 
# DBD::ADO 
$dsn = "Provider=sqloledb;Trusted Connection=yes;"; 
$dsn .= "Server=$host;Database=$database"; 
my $dbh = DBI->connect("dbi:ADO:$dsn", 
 $user, 
 $auth, 
 { RaiseError => 1, AutoCommit => 1} 
 ) || die "Database connection not made: $DBI::errstr"; 
 
#Prepare a SQL statement 
my $sql = "SELECT id, name, phone_number FROM employees "; my $sth = $dbh->prepare( $sql ); 
 
#Execute the statement 
$sth->execute(); 
 
my( $id, $name, $phone_number ); 
 
# Bind the results to the local variables 
$sth->bind_columns( undef, \$id, \$name, \$phone_number ); 
 
#Retrieve values from the result set 
while( $sth->fetch() ) { 
 print "$id, $name, $phone_number\n"; 
} 
 
#Close the connection 
$sth->finish(); 
$dbh->disconnect();

Linux平台

如果是在Linux平台下运行perl脚本，连接SQL Server数据库需要使用到DBD::Sybase包。文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

安装SQL Server支持库

Sybase DBD包依赖FreeTDS驱动程序。文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

FreeTDS下载地址：www.freetds.org文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

安装FreeTDS驱动的说明文档参见：http://www.freetds.org/userguide/config.htm文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

该驱动没有使用到ODBC.文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

配置数据源

修改freetds.conf文件包括SQL Server数据库信息，如下所示：文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

[SS_MY_DB]
host = 10.0.0.1 # or host name port = 1433
tds version = 7.0文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

安装Sybase DBD模块

该模块文档参见：http://search.cpan.org/~mewp/DBD-Sybase/Sybase.pm文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

此外，需要将sybase环境变量应设置为FreeTDS安装路径，export SYBASE=/usr/local/freetds文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

使用Sybase DBI和SQL Server DSN实例

# load the DBI module 
use DBI; 
use DBD::Sybase; 
 
my $database="my_database"; 
my $user="sa"; 
my $auth="s3cr3t"; 
 
BEGIN 
{ 
 $ENV{SYBASE} = "/usr/local"; 
} 
 
# Connect to the SQL Server Database 
my $dbh = DBI->connect("dbi:Sybase:server=ss_my_db;database=$database", 
 $user, 
 $auth 
 {RaiseError => 1, AutoCommit => 1} 
 ) || die "Database connection not made: $DBI::errstr"; 
 
#Prepare a SQL statement 
my $sql = "SELECT id, name, phone_number FROM employees"; 
my $sth = $dbh->prepare( $sql ); 
 
#Execute the statement 
$sth->execute(); 
 
my( $id, $name, $phone_number ); 
 
# Bind the results to the local variables 
$sth->bind_columns( undef, \$id, \$name, \$phone_number ); 
 
#Retrieve values from the result set 
while( $sth->fetch() ) {  print "$name, $title, $phone\n"; 
} 
 
#Close the connection 
$sth->finish(); 
undef $sth; # This fixes a segfault bug with certain versions of DBD::Sybase 
$dbh->disconnect();

参考资料

http://technet.microsoft.com/library/bb497071文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/

http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/ 文章源自运维生存时间-https://www.ttlsa.com/database/howto-connecting-perl-to-sql-server/