本文將提供一些perl連接Microsoft SQL Server數據庫的實例。perl腳本運行在Windows和Linux平台。
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();