📄 ch10_04.htm
字号:
<?label 10.4. DBI?><html><head><title>DBI (CGI Programming with Perl)</title><link href="../style/style1.css" type="text/css" rel="stylesheet" /><meta name="DC.Creator" content="Scott Guelich, Gunther Birznieks and Shishir Gundavaram" /><meta scheme="MIME" content="text/xml" name="DC.Format" /><meta content="en-US" name="DC.Language" /><meta content="O'Reilly & Associates, Inc." name="DC.Publisher" /><meta scheme="ISBN" name="DC.Source" content="1565924193L" /><meta name="DC.Subject.Keyword" content="stuff" /><meta name="DC.Title" content="CGI Programming with Perl" /><meta content="Text.Monograph" name="DC.Type" /></head><body bgcolor="#ffffff"><img src="gifs/smbanner.gif" alt="Book Home" usemap="#banner-map" border="0" /><map name="banner-map"><area alt="CGI Programming with Perl" href="index.htm" coords="0,0,466,65" shape="rect" /><area alt="Search this book" href="jobjects/fsearch.htm" coords="467,0,514,18" shape="rect" /></map><div class="navbar"><table border="0" width="515"><tr><td width="172" valign="top" align="left"><a href="ch10_03.htm"><img src="../gifs/txtpreva.gif" alt="Previous" border="0" /></a></td><td width="171" valign="top" align="center"><a href="index.htm">CGI Programming with Perl</a></td><td width="172" valign="top" align="right"><a href="ch11_01.htm"><img src="../gifs/txtnexta.gif" alt="Next" border="0" /></a></td></tr></table></div><hr align="left" width="515" /><h2 class="sect1">10.4. DBI</h2><p>The <a name="INDEX-2109" />DBI module is the most flexible wayto link <a name="INDEX-2110" /><a name="INDEX-2111" />Perl to databases. Applicationsthat use relatively standard SQL calls can merely drop in a new DBIdatabase driver whenever a programmer wishes to support a newdatabase. Nearly all the major relational database engines have<a name="INDEX-2112" />a DBIdriver on CPAN. Although database-specific modules such as Sybperland Oraperl still exist, they are being rapidly superseded by the useof DBI for most database tasks.</p><p>DBI supports a rich set of features. However, you need to use only asubset in order to accomplish most of what a simple databaseapplication requires. This section will cover how to create tables aswell as insert, update, delete, and select data in those tables.Finally, we will pull it all together with an example of an addressbook.</p><p>While DBI supports concepts such as bind parameters and storedprocedures, the behavior of these features is usually specific to thedatabase they are being used with. In addition, some drivers maysupport database-specific extensions which are not guaranteed toexist in each database driver implementation. In this section we willfocus on covering an overview of DBI features that are universallyimplemented across all DBI drivers.</p><a name="ch10-18-fm2xml" /><div class="sect2"><h3 class="sect2">10.4.1. Using DBI</h3><p>In the examples here, we will use the <a name="INDEX-2113" /><a name="INDEX-2114" /> <a name="INDEX-2,115" />DBD::CSV DBI driver. DBI drivers arepreceded with "DBD" (database driver) followed by theactual driver name. In this case, <a name="INDEX-2116" /><a name="INDEX-2117" /> <a name="INDEX-2,118" /><a name="INDEX-2119" />CSV is short for "Comma SeparatedValue," otherwise known as a comma-delimited flat text file.The reason the examples use DBD::CSV is that this driver is thesimplest in terms of feature availability, and also DBD::CSV does notrequire you to know how to set up a relational database engine suchas Sybase, Oracle, PostgreSQL, or MySQL.</p><p>If you are using Perl on Unix, the DBD::CSV driver may be found onCPAN and should be easily compiled for your platform by following theinstructions. If you are using Perl on <a name="INDEX-2120" /><a name="INDEX-2121" /><a name="INDEX-2122" /> <a name="INDEX-2,123" />Win32 from ActiveState, we recommendusing ActiveState's PPM (Perl Package Manager) to download theDBD::CSV binaries from the ActiveState package repository for Win32(refer to <a href="appb_01.htm">Appendix B, "Perl Modules"</a>).</p><a name="ch10-19-fm2xml" /><div class="sect3"><h3 class="sect3">10.4.1.1. Connecting to DBI</h3><p>To connect to a <a name="INDEX-2124" /><a name="INDEX-2125" /><a name="INDEX-2126" />DBI database, youneed to issue the <em class="emphasis">connect</em> method. A databasehandle that represents the connection is returned from the<em class="emphasis">connect</em> statement if successful:</p><blockquote><pre class="code">use DBI;my $dbh = DBI->connect("DBI:CSV:f_dir=/usr/local/apache/data/stats") or die "Cannot connect: " . $DBI::errstr;</pre></blockquote><p>The <em class="emphasis">use</em><a name="INDEX-2127" /> <a name="INDEX-2,128" /> statementtells Perl which library to load for accessing DBI. Finally, the<em class="emphasis">connect</em> statement takes the string that has beenpassed to it and determines the database <a name="INDEX-2129" />driver to load, which in this case isDBD::CSV. The rest of the string contains database driver specificinformation such as username and password. In the case of DBD::CSV,there is no username and password; we need to specify only adirectory where files representing database tables will be stored.</p><p>When you are finished with the database handle, remember todisconnect from the database:</p><blockquote><pre class="code">$dbh->disconnect;</pre></blockquote></div><a name="ch10-20-fm2xml" /><div class="sect3"><h3 class="sect3">10.4.1.2. Database manipulation</h3><p>Database manipulation in <a name="INDEX-2130" /><a name="INDEX-2131" />DBI is quite simple. All you need to do ispass the <em class="emphasis">create table, insert, update,</em> or<em class="emphasis">delete</em> statement to the <em class="emphasis">do</em>method on the database handle. Immediately, the command will beexecuted:</p><blockquote><pre class="code">$dbh->do( "insert into Player_Info values ('Hakeem Olajuwon', 10, 27, 11, 4, 2)") or die "Cannot do: " . $dbh->errstr( );</pre></blockquote></div><a name="ch10-21-fm2xml" /><div class="sect3"><h3 class="sect3">10.4.1.3. Database querying</h3><p>Querying a<a name="INDEX-2132" /><a name="INDEX-2133" /><a name="INDEX-2134" /> <a name="INDEX-2,135" />database with DBIinvolves a few more commands since there are many ways in which youmight want to retrieve data. The first step is to pass the SQL queryto a <tt class="command">prepare</tt> command. This will create a statementhandle that is used to fetch the results:</p><blockquote><pre class="code">my $sql = "select * from Player_Info";my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr( ); $sth->execute( ) or die "Cannot execute: " . $sth->errstr( );my @row;while (@row = $sth->fetchrow_array( )) { print join(",", @row) . "\n";}$sth->finish( );</pre></blockquote><p>Once the <em class="emphasis">prepare</em> command has been issued, the<tt class="command">execute</tt><a name="INDEX-2136" /> <a name="INDEX-2,137" /> command is used to startthe query. Since a query expects return results, we use a<a name="INDEX-2138" />while loop to get each databaserecord. The<tt class="command">fetchrow_array</tt><a name="INDEX-2139" /><a name="INDEX-2140" /> command is used to fetch each rowthat is returned as an array of fields.</p><p>Finally, we clean up the statement handle by issuing the<tt class="command">finish</tt> method. Note that in most cases we do nothave to explicitly call the <tt class="command">finish</tt> method. It isimplicitly called by virtue of the fact that we have retrieved allthe results. However, if the logic of your program decided to stopretrieving records before the entire statement had finished beingretrieved, then calling <tt class="command">finish</tt> is necessary inorder to flush out the statement handle.</p></div></div><a name="ch10-22-fm2xml" /><div class="sect2"><h3 class="sect2">10.4.2. DBI Address Book</h3><p>Most companies with an intranet have an online <a name="INDEX-2141" /> <a name="INDEX-2,142" />address book for looking up phonenumbers and other employee details. Here, we'll use DBI toimplement a full address book against any database that supports SQL.</p><a name="ch10-23-fm2xml" /><div class="sect3"><h3 class="sect3">10.4.2.1. Address book database creation script</h3><p>There are two scripts we need to take a look at. The first is not aweb script. It is a simple script that creates the<a name="INDEX-2143" /><a name="INDEX-2144" /> <a name="INDEX-2,145" />address table for the<a name="INDEX-2146" />address book CGI to access:</p><blockquote><pre class="code">#!/usr/bin/perl -wTuse strict;use DBI;my $dbh = DBI->connect("DBI:CSV:f_dir=/usr/local/apache/data/address_book") or die "Cannot connect: " . $DBI::errstr;my $sth = $dbh->prepare(qq` CREATE TABLE address (lname CHAR(15), fname CHAR(15), dept CHAR(35), phone CHAR(15), location CHAR(15))`) or die "Cannot prepare: " . $dbh->errstr( );$sth->execute( ) or die "Cannot execute: " . $sth->errstr( );$sth->finish( );$dbh->disconnect( );</pre></blockquote><p>As you can see, this script puts together the DBI concepts ofconnecting to a database and submitting a table creation command.There is one twist though. Although it was previously demonstratedthat the table creation could be accomplished through a simple<tt class="command">do</tt><a name="INDEX-2147" /> method on thedatabase handle, the DBI code we used is similar to the DBI commandsused to query a database.</p><p>In this case, we prepare the <em class="emphasis">createtable</em><a name="INDEX-2148" /> statement first, and then execute it aspart of a statement handle. Although it is quick and easy to use thesingle <tt class="command">do</tt> method, breaking up the code like thisallows us to<a name="INDEX-2149" /><a name="INDEX-2150" /> <a name="INDEX-2,151" />troubleshoot errors at different levelsof the SQL submission. Adding this extra troubleshooting code can bevery useful in a script that you need to support in production.</p><p>The final result is a table called <em class="emphasis">address</em> inthe <em class="filename">/usr/local/apache/data/address_book</em>directory. The address table consists of five fields:<tt class="literal">lname</tt> (last name), <tt class="literal">fname</tt> (firstname), <tt class="literal">dept</tt> (department),<tt class="literal">phone</tt>, and <tt class="literal">location</tt>.</p></div><a name="ch10-24-fm2xml" /><div class="sect3"><h3 class="sect3">10.4.2.2. Address book CGI script</h3><p>The <a name="INDEX-2152" />address book <a name="INDEX-2,153" /> <a name="INDEX-2,154" />CGI script is a self-contained programthat displays query screens as well as allows the users to modify thedata in the address book in any fashion they like. The default screenconsists of a list of form fields representing fields in the databaseyou might wish to query on (see <a href="ch10_04.htm#ch10-26687">Figure 10-1</a>). If theMaintain Database button is selected, a new workflow is presented tothe user for adding, modifying, or deleting address book records (see<a href="ch10_04.htm#ch10-34507">Figure 10-2</a>).</p><a name="ch10-26687" /><div class="figure"><img width="481" src="figs/cgi2.1001.gif" height="327" alt="Figure 10-1" /></div><h4 class="objtitle">Figure 10-1. Address book main page</h4><a name="ch10-34507" /><div class="figure"><img width="481" src="figs/cgi2.1002.gif" height="181" alt="Figure 10-2" /></div><h4 class="objtitle">Figure 10-2. Address book maintenance page</h4><p>Here's the beginning of the code for the address book CGIscript:</p><blockquote><pre class="code">#!/usr/bin/perl -wTuse strict;use DBI;use CGI;use CGI::Carp qw(fatalsToBrowser);use vars qw($DBH $CGI $TABLE @FIELD_NAMES @FIELD_DESCRIPTIONS);$DBH = DBI->connect("DBI:CSV:f_dir=/usr/local/apache/data/address_book") or die "Cannot connect: " . $DBI::errstr;@FIELD_NAMES = ("fname", "lname", "phone", "dept", "location");@FIELD_DESCRIPTIONS = ("First Name", "Last Name", "Phone", "Department", "Location");$TABLE = "address";$CGI = new CGI( );</pre></blockquote><p>The <tt class="literal">use</tt><a name="INDEX-2156" /><a name="INDEX-2157" /><a name="INDEX-2158" /><a name="INDEX-2159" /><tt class="command"></tt><tt class="literal">vars</tt> statement declares all the globalvariables we will use in the program. Then, we initialize the globalvariables for use. First,<tt class="literal">$DBH</tt><a name="INDEX-2160" /><a name="INDEX-2161" /><a name="INDEX-2162" /> contains the database handle to be usedthroughout the program. Then, <tt class="literal">@FIELD_NAMES</tt> and<tt class="literal">@FIELD_DESCRIPTIONS</tt> contains a list of the<a name="INDEX-2163" />field names in the database aswell as their descriptive names for display to a user.<tt class="literal">@FIELD_NAMES</tt> also doubles as a list of what theform variable names that correspond to database fields will becalled.<tt class="literal">$TABLE</tt><a name="INDEX-2164" /> simply contains thetable name.</p>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -