linux下perl操作mysql数据库(须要安装DBI)

tar xzf DBI-1.620.tar.gz,下面的实例代码将展示如何连接到SQL Server数据库,你还可以使用预先设置的一个系统DSN来连接,可移植的DBI方法,perl DBI 学习总结

图片 1

1.下载DBI包: wget
2 解压
tar xzf DBI-1.620.tar.gz 3…

[SS_MY_DB]
host = 10.0.0.1 # or host name port = 1433
tds version = 7.0

sub trim
{
 my ($line)=@_;
 $line=~s/^[ /t]+//;  #轮换掉开始空格
 $line=~s/[ /t/r/n]+$//;#轮换掉结尾空格
 return $line;
}

#! /usr/bin/perl
use DBI;
my $driver=”DBI:mysql”;
my $database=”perl_test”;
my $user=”root”;
my $host=”localhost”;
my $passwd=”root”;
my $rules=”alert_rules”;
my $dbh =
DBI->connect(“$driver:database=$database;host=$host;user=$user;password=$passwd”)
or die “Can’t connect: ” . DBI->errstr;
my $sth=$dbh->prepare(“select app_name,receivers from $rules “);
$sth->execute() or die “Can’t prepare sql statement”.
$sth->errstr;
my $sth=$dbh->prepare(“select app_name,receivers from $rules “);
$sth->execute() or die “Can’t prepare sql statement”.
$sth->errstr;
# 打字与印刷获取的数量
while(@recs=$sth->fetchrow_array){
print $recs[0].”:”.$recs[1].”\n”;
}
$sth->finish();
$dbh->disconnect();

要是选拔DBD::ADO模块,下边包车型客车实例展现怎样连接到SQL Server数据库。

#接连数据库
print “开首接连$db_type数据库[DataBaseServer:$db_server]…/n”;
my $dbh =
DBI->connect(“dbi:$db_type:$db_server”,”$user_name”,”$password”,{PrintError
=> 0});
if($dbh){
 print “连接$db_type数据库成功[DataBaseServer:$db_server]…/n”;
}else{
 print “连接$db_type数据库战败[DataBaseServer:$db_server]…/n”;
 exit(1);
}

复制代码 代码如下:

别的,须要将sybase情况变量应安装为FreeTDS安装路线,export
SYBASE=/usr/local/freetds

 

DBI安装:DBI详细音信仿效:
1.下载DBI包: wget

2 解压
tar xzf DBI-1.620.tar.gz
3 步向解压包内,
perl Makefile.PL
4 make test
5 make
6 make install (要是还是不是root用户,要sudo一下)
DBD安装:
1.下载DBD
wget

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

 

    1. 同上
      在进行第三步是唯恐会产出mysql_config找不到的图景,应该是不曾安装开垦工具包
      sudo apt-get install libmysqld-dev
      sudo apt-get install libmysqlclient-dev
      来安装这七个包。
      另外同上。
      姣好安装后就可以应用perl脚本进行mysql数据的操作了。
      编写制定perl脚本操作数据库

复制代码 代码如下:

my $vendor=shift;
my $db_type=shift;
my $db_server=shift;
my $user_name=shift;
my $password=shift;
my $omc_name=shift;
my $src_dir=shift;
my $desc_dir=shift;
my $cmd_list=shift;
my $sql_list=shift;

接纳系统DSN连接,要求转移连接字符串。如下所示:

 

使用Sybase DBI和SQL Server DSN实例

##########################################################################

该驱动未有利用到ODBC.

use DBI;
use DBD::Informix;

你恐怕感兴趣的篇章:

  • 让apache2以cgi形式运行perl
    cgi程序的落真实情形势
  • windows下Apache+MySql+PHP3+PHP4+PE瑞虎L安装配备
  • Win两千+Apache+MySql+PHP4+PEEscortL安装使用小结
  • Windows Server 二零一五 上配置
    APACHE+SSL+PHP+perl的教程详解

 

DBD::ODBC
DBD::ADO

#获得当前时刻并展开写文件
my ($sec,$min,$hour,$mday,$mon,$year)=(localtime)[0,1,2,3,4,5];
$year=$year + 1900;
$mon=$mon+1;
$mon=sprintf(“%02d”,$mon);
$mday=sprintf(“%02d”,$mday);
$hour=sprintf(“%02d”,$hour);
$min=sprintf(“%02d”,$min);
$sec=sprintf(“%02d”,$sec);
$time_stamp=$year.$mon.$mday.$hour.$min.$sec;
$time_stamp2=$year.”-“.$mon.”-“.$mday.” “.$hour.”:”.$min.”:”.$sec;

配置数据源

#!/usr/bin/perl

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

图片 1
##########################################################################
可移植的DBI方法:
connect    创建到一个数据库服务器的一连
disconnect   断开数据库服务器的连日
prepare    打算实践二个SQL语句
execute    试行盘算好的说话
do    盘算并实践三个SQL语句
quote    加引号于要插入的字符串或BLOB值
fetchrow_array   作为多个字段数组收取下一行
fetchrow_arrayref  作为两个字段的引用数组抽取下一行
fetchrow_hashref  作为三个哈希表的援引收取下一行
fetchall_arrayref  作为一个字段数组抽取全体数据
finish    完结一条语句并且让系统释放能源
rows    重回受影响的行数
data_sources   重返可在localhost上获得的数据库的数组
ChopBlanks   控制fetchrow_*措施是不是剥去空格
NUM_OF_PARAMS   在希图的说话中的占位(placeholder-参数)的数额
NULLABLE   其列能够是NULL
trace    试行调节和测验追踪
##########################################################################
$dbh 数据库句柄
$sth 语句句柄
$rc 重临代码(平时是二个情景)
$rv 再次回到值(平日是三个行数)
##########################################################################
①connect($data_source, $username, $password) 
应用connect方法使得八个数据库连接到数据源。$data_source值应该以DBI:driver_name:开首。以DBD::mysql驱动程序使用connect的事例:
  $dbh = DBI->connect(“DBI:mysql:$database”, $user, $password);
  $dbh = DBI->connect(“DBI:mysql:$database:$hostname”,
                      $user, $password);
  $dbh = DBI->connect(“DBI:mysql:$database:$hostname:$port”,
                      $user, $password);