Perl使用DBI模块访问数据库

上海isp

需要按照DBI模块，如果访问特定的数据库如MySQL还需要安装特定的模块如DBI::MySQL……

Using Database in Perl with DBI：
<!--[if !supportLists]-->1. <!--[endif]-->参考文档：
Installing DBI and Using MySQL with Perl in Linux： http://home.ubalt.edu/abento/752/dbi/
A Short Guide to DBI：http://www.perl.com/pub/a/1999/10/DBI.html
Perl DBI Examples： http://www.saturn5.com/%7Ejwb/dbi-examples.html
Advanced DBI：http://search.cpan.org/~timb/DBI/DBI.pm
DBI sites: http://dbi.perl.org/
Books：《Programming the Perl DBI》。。。

2. You can see below the basic DBI model. The scripts are written in Perl using standard Perl variables, commands and syntax. The DBI has methods and handles which are database software independent. You program to access, change or query a database using the standard SQL language, combined with the DBI methods and handles. You install as many DBD::type modules as you need to support the different database software you may have, but you Perl script will be the same.
<!--[if gte vml 1]><v:shapetype id="_x0000_t75"coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe"filled="f" stroked="f"><v:stroke joinstyle="miter" /><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0" /><v:f eqn="sum @0 1 0" /><v:f eqn="sum 0 0 @1" /><v:f eqn="prod @2 1 2" /><v:f eqn="prod @3 21600 pixelWidth" /><v:f eqn="prod @3 21600 pixelHeight" /><v:f eqn="sum @0 0 1" /><v:f eqn="prod @6 1 2" /><v:f eqn="prod @7 21600 pixelWidth" /><v:f eqn="sum @8 21600 0" /><v:f eqn="prod @7 21600 pixelHeight" /><v:f eqn="sum @10 21600 0" /></v:formulas><v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" /><o:lock v:ext="edit" aspectratio="t" /></v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" alt="" style='width:306pt;height:169.5pt'><v:imagedata src="file:///C:\DOCUME~1\Hemin\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"o:href="http://home.ubalt.edu/abento/752/dbi/DBImodel.gif" /></v:shape><![endif]--><!--[if !vml]--><!--[endif]-->


3. Using MySQL with the Perl DBI Interface：
1) Basic concepts:
Perl uses three levels of handlers to deal with databases:
$drh -- the driver handle = DBD::mysql, which is transparent for you once is installed. You will not need regularly to include $drh in your script.

$dbh -- the database handle = what you will need to create a connection to the database, it is different for each type of database software. It has the following format in MySQL:

my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host",$user,$password);

$sth -- the statement handle = children of the $dbh, and will let the various statements be declared. The types of statements will depend on the SQL commands you are using. SELECT uses three statements in order to perform the query and display the results: prepare, execute and fetch (see examples below). INSERT,UPDATE and DELETE do not use fetch, only prepare and execute. See the above on-line references and the book on reserve for more details.
2) SELECT:
3）INSERT，UPDATE，DELETE ……
4. 使用示例：

1) DBI的MySQL中使用SELECT：
#!/usr/bin/perl

usestrict;
useDBI();

#connecttothedatabase
my$dbh=DBI->connect("DBI:mysql:database=broker;host=localhost","broker","broker",{'RaiseError'=>1});

#selectdatafromthetable
my$sth=$dbh->prepare("select*frombike");
$sth->execute();

#showtheselectedresults
while(my$ref=$sth->fetchrow_hashref()){
print"Foundarow:id=$ref->{'id'},university=$ref->{'university'} ";
}
$sth->finish();

#disconnectfromthedatabase.
$dbh->disconnect();


2) 在cgi中，同时使用MySQL，将结果显示到网页中：
#!C:perlinperl-w
usewarnings;
useCGIqw(:standard);
useDBI();


printheader;

#!mustuse'my'todefineavariable
my$now_string=localtime();
print"<b>Hello,CGIusingPerl!</b><br/>It's$now_stringNOW!<br/>";


#connecttothedatabase
my$dbh=DBI->connect("DBI:mysql:database=broker;host=localhost","broker","broker",{'RaiseError'=>1});

#selectdatafromthetable
my$sth=$dbh->prepare("select*frombike");
$sth->execute();

print"<p>ContentfromtheDatabase<br/>";
#showtheselectedresults
while(my$ref=$sth->fetchrow_hashref()){
print"Foundarow:id=$ref->{'id'},university=$ref->{'university'} ";
}
$sth->finish();

#disconnectfromthedatabase.
$dbh->disconnect();


3) DBI的插入INSERT:
#connecttothedatabase
my$dbh=DBI->connect("DBI:mysql:database=broker;host=localhost","brokeruser","brokerpassword",{'RaiseError'=>1});

$insert_statement="insertintopostvalues('','$c','$type','$title','$content','$u','$pubtime')";
my$sth=$dbh->prepare($insert_statement);
if($sth->execute()){
$last_insert_id=$dbh->last_insert_id(undef,undef,undef,undef);
printqq[恭喜您，您的信息发布成功了！ID号为$last_insert_id<br/><ahref="p.html">继续发帖</a>|<ahref="s.pl">查看</a>];

}else{
printqq[抱歉，信息发布失败，请稍后重试。<br/><ahref="p.html">返回</a>];
}

#disconnectfromthedatabase.
$dbh->disconnect();



关于使用DBI访问数据库更多的用法可以参考：

DBI官方网站： http://dbi.perl.org/
<!--[endif]-->