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();

你還可以使用預(yù)先設(shè)置的一個系統(tǒng)DSN來連接。要建立一個系統(tǒng)DSN，可以這樣訪問控制面板->管理工具->數(shù)據(jù)源。

使用系統(tǒng)DSN連接，需要更改連接字符串。如下所示：

復(fù)制代碼代碼如下:

# 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數(shù)據(jù)庫。

復(fù)制代碼代碼如下:

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數(shù)據(jù)庫需要使用到DBD::Sybase包。

安裝SQL Server支持庫

Sybase DBD包依賴FreeTDS驅(qū)動程序。

FreeTDS下載地址：www.freetds.org

安裝FreeTDS驅(qū)動的說明文檔參見：http://www.freetds.org/userguide/config.htm

該驅(qū)動沒有使用到ODBC.

配置數(shù)據(jù)源

修改freetds.conf文件包括SQL Server數(shù)據(jù)庫信息，如下所示：

復(fù)制代碼代碼如下:

[SS_MY_DB]

host = 10.0.0.1 # or host name port = 1433

tds version = 7.0

安裝Sybase DBD模塊

該模塊文檔參見：http://search.cpan.org/~mewp/DBD-Sybase/Sybase.pm

此外，需要將sybase環(huán)境變量應(yīng)設(shè)置為FreeTDS安裝路徑，export SYBASE=/usr/local/freetds

使用Sybase DBI和SQL Server DSN實例

復(fù)制代碼代碼如下:

# 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();