Windows和Linux系統下perl連接SQL Server數據庫的方法

2020-02-23 19:45:29

字體：大中小

來源：轉載

供稿：網友

perl腳本運行在windows/" target="_blank" title="windows">Windows和 Linux平臺上，如果在Windows平臺下運行perl腳本，建議使用依賴DBI的兩個模塊包，本篇文章是Windows和Linux系統下perl連接SQL Server 數據庫的方法，一起來看看吧！

Windows平臺

如果在Windows平臺下運行perl腳本，建議使用依賴DBI的兩個模塊包，提供標準的數據庫接口模塊。

DBD::ODBC
DBD::ADO

使用DBD::ODBC

如果選用DBD::ODBC，下面的實例代碼將展示如何連接到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，可以這樣訪問控制面板->管理工具->數據源。

使用系統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數據庫。

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包。

安裝SQL Server支持庫

Sybase DBD包依賴FreeTDS驅動程序。

FreeTDS下載地址：www.freetds.org

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

該驅動沒有使用到ODBC.

配置數據源

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

[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環境變量應設置為FreeTDS安裝路徑，export SYBASE=/usr/local/freetds

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