windows上Perl连接SQL Server Linux上Perl查询SQL Server

本文将提供一些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_numbern";
}
 
#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_numbern";
}
 
#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, $phonen";
}
 
#Close the connection
$sth->finish();
undef $sth; # This fixes a segfault bug with certain versions of DBD::Sybase
$dbh->disconnect();


Linux上使用Perl查询 Microsoft SQL Server

安装FreeTDS

# yum install freetds.x86_64
# yum install freetds-devel.x86_64
# vim /etc/freetds.conf
[SQLSERVER]
     host = 10.0.1.1  #SQL server服务器地址
     port = 1433
     tds version = 7.0

需要先安装EPEL源，EPEL源如何安装可以参见本博客以前内容。

测试

# tsql -S sqlserver.hostname.com -U sqlusername -D your_db_name
select * from sys.Tables;
GO
quit

安装DBD::Sybase

# export SYBASE=/usr
# cpanm DBD::Sybase

cpanm安装可以参见本博客之前内容。

perl实例

view source
print?
#!/usr/bin/perl
 
#use lib ('./modules');
use DBI;
use DBD::Sybase;
 
my $database = "your_db_name";
my $username = 'sqlusername';
my $password = 'sqlpassword';
 
#SQLSERVER 字串对应于 /etc/freetds.conf 里面的 [SQLSERVER]
$dsn = "DBI:Sybase:server=SQLSERVER;database=$database";
 
my $dbh = DBI->connect($dsn, $username, $password) or die "Database connection not made : $DBI::errstr";
 
my $sql = "SELECT TOP 5 * FROM httpstatus order by testtime desc";
my $sth = $dbh->prepare( $sql );
$sth->execute() or die "unable to execute query $sql, error $DBI::errstrn";
 
$rows = $sth->rows;
 
while ( @first = $sth->fetchrow_array ) {
        foreach $field (@first) {
                print "$field ";
        }
        print "n";
}
 
$sth->finish();
undef $sth;
$dbh->disconnect();
 
print "Query successfullyn";