Perl连接SqlServer

#!/usr/bin/perl
package db::conn::sqlserver;
use Data::Dumper;
use DBI;
use Encode;
use vars qw($VERSION @ISA @EXPORT);
$VERSION = 1.00;
@ISA     = qw();
@EXPORT  = ();
# ----------------------------------------------------
# 
构造函数
# 数据库参数集合(host,port,dbname,username,pwd,tablename)
# 
# create
# ----------------------------------------------------
sub new {
	my $class = shift;
	my $paramHash = shift;
	my $host = $paramHash->{'host'};
	my $port = $paramHash->{'port'};
	my $dbName = $paramHash->{'dbname'};
	my $username = $paramHash->{'username'};
	my $password = $paramHash->{'password'};
	my $tableName = $paramHash->{'tablename'};
	my $this = {};
    bless $this,$class;
    $this->{'tableName'} = $tableName;
    $this->{dbh} = $this->getConnect($host,$port,$dbName,$username,$password);
    return $this;
}
# ----------------------------------------------------
# 
连接数据库，并获取数据源
# 数据库服务器ip地址
# 数据库服务器端口号
# collection名称
# 
# create
# ----------------------------------------------------
sub getConnect {
	my $this = shift;
	my $host = shift;
	my $port = shift;
	my $dbName = shift;
	my $username = shift;
	my $password = shift;
	my $DSN = "driver={SQL Server};Server=$host;Database=$dbName;UID=$username;PWD=$password";
	my $dbh=DBI->connect("DBI:ODBC:$DSN") or die "couldn't open database: DBI->errstr";
	if ($dbh->err()) { die "$DBI::errstr\n"; } #连接错误提示
	return $dbh;
}
# ----------------------------------------------------
# 
获取给定条件下的数据条数
# 过滤条件
# 符合条件的记录数量
# create
# ----------------------------------------------------
sub count(){
	my $this = shift;
	my $whereString = shift;
	my $sql = "SELECT COUNT(*) FROM ".$this->{'tableName'};
	if ($whereString) {
		$sql .= " WHERE ".$whereString;
	}
	my $result = undef;
	eval{
		my $sth = $this->{dbh}->prepare($sql);
		$sth->execute();
		$result = @{$sth->fetchall_arrayref()}[0];
		$sth->finish ();
	};
	if($@){
#		need error log here
		return 0;
	}
    return @{$result}[0];
}

# ----------------------------------------------------
# 
插入操纵
# 插入的数据集合
# 
# create
# ----------------------------------------------------
sub set {
	my $this = shift;
	my $dataset = shift;
	eval{
		for my $oneData (@{$dataset}) {
			my @columnArray;
			my @insertValueArray;
			for my $key (keys(%{$oneData})) {
				push @columnArray, $key;
				push @insertValueArray, $this->{dbh}->quote($oneData->{$key});
			}
			my $sql = "INSERT INTO ".$this->{'tableName'}." (".join(',', @columnArray).") VALUES (".join(',',@insertValueArray).")";
			print $sql;
			my $sth = $this->{dbh}->prepare ($sql);
	    	$sth->execute();
	    	$sth->finish();
		}
	};
	if($@){
#		need error log here
		print $@; 
		return 0;
	}
	
}
# ----------------------------------------------------
# 
更新操作
# 更新条件
# 更新内容的键值对
# 0:更新失败 1:更新成功
# create
# ----------------------------------------------------
sub update {
	my $this = shift;
	my $whereString = shift;
	my $updateValue = shift;
	my @setValueArray;
	for my $key (keys(%{$updateValue})) {
		push @setValueArray, $key."=".$this->{dbh}->quote($updateValue->{$key});
	}
	my $sql = "UPDATE ".$this->{'tableName'}." SET ".join(',',@setValueArray);
	if ($whereString) {
		$sql .= " WHERE ".$whereString;
	}
	
	eval{
		my $sth = $this->{dbh}->prepare($sql);
		$sth->execute();
		$sth->finish ();
#		$this->{dbh}->commit();
	};
	if($@){
#		need error log here
		return 0;
	}
	return 1;
}

# ----------------------------------------------------
# 
获取给定条件下的数据
# 过滤条件
# 【可选参数】分段获取的启示记录
# 【可选参数】分段获取记录数量
# 符合条件的记录
# create
# ----------------------------------------------------
sub get {
	my $this = shift;
	my $whereString = shift;
	my $skip = shift;
	my $num = shift;
	my @results = {};
	my $sql;
	if ($whereString) {
		$sql .= " WHERE ".$whereString;
	}
	if ($num) {
		if (!$skip) {
			$skip = 0;
		}
		$num =int($num+$skip+1);
		my $temp_table_n = "temp_".int(rand(1000) + 1);
		my $temp_sql = "SELECT identity(int,1,1) as temp_id, * into #$temp_table_n FROM ".$this->{'tableName'}.$sql;
		$sql ="$temp_sql;SELECT * FROM #$temp_table_n WHERE temp_id > $skip AND temp_id<".$num;
	}else{
		$sql ="SELECT 1 as temp_id, * FROM ".$this->{'tableName'}.$sql;
	}
	eval {
		my $sth = $this->{dbh}->prepare ($sql);
		$sth->execute();
    	while (my $row = $sth->fetchrow_hashref) {
    		my $item = $main::core->box->newItem();
    		foreach my $key (keys(%{$row})) {
    			if($key ne 'temp_id'){
#    				print "key--->".$key."=====val--->".$row->{$key}."\n";
    				$item->setContent($key, $row->{$key});
    			}
    			
    		}
			push @results, $item;
	    }
	    $sth->finish ();
	};
	
	return \@results;
	
}
# ----------------------------------------------------
# 
删除操作
# 要删除数据条件
# 0:更新失败 1:更新成功
# create
# ----------------------------------------------------
sub delete {
	my $this = shift;
	my $whereString = shift;
	my $sql = "DELETE FROM ".$this->{'tableName'};
	if ($whereString) {
		$sql .= " WHERE ".$whereString;
	}
#	print "$sql\n";
	eval{
		my $sth = $this->{dbh}->prepare($sql);
		$sth->execute();
		$sth->finish ();
#		$this->{dbh}->commit();
	};
	if($@){
#		need error log here
		return 0;
	}
	return 1;
}

1;