本文將提供一些perl連接Microsoft SQL Server數(shù)據(jù)庫的實例。perl腳本運行在Windows和Linux平臺。

Windows平臺

如果在Windows平臺下運行perl腳本，建議使用依賴DBI的兩個模塊包，提供標(biāo)準(zhǔn)的數(shù)據(jù)庫接口模塊。

DBD::ODBC
DBD::ADO

使用DBD::ODBC

如果選用DBD::ODBC，下面的實例代碼將展示如何連接到SQL Server數(shù)據(jù)庫：

代碼如下:
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連接，需要更改連接字符串。如下所示：

代碼如下:
# 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ù)庫。

代碼如下:
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();