📄 ch12_02.htm
字号:
<html><head><title>Design of DBI (Perl in a Nutshell, 2nd Edition)</title><link rel="stylesheet" type="text/css" href="../style/style1.css" /><meta name="DC.Creator" content="Stephen Spainhour" /><meta name="DC.Format" content="text/xml" scheme="MIME" /><meta name="DC.Language" content="en-US" /><meta name="DC.Publisher" content="O'Reilly & Associates, Inc." /><meta name="DC.Source" scheme="ISBN" content="0596002416L" /><meta name="DC.Subject.Keyword" content="stuff" /><meta name="DC.Title" content="Perl in a Nutshell, 2nd Edition" /><meta name="DC.Type" content="Text.Monograph" /></head><body bgcolor="#ffffff"><img src="gifs/smbanner.gif" usemap="#banner-map" border="0" alt="Book Home" /><map name="banner-map"><area shape="rect" coords="1,-2,616,66" href="index.htm" alt="Java and XSLT" /><area shape="rect" coords="629,-11,726,25" href="jobjects/fsearch.htm" alt="Search this book" /></map><div class="navbar"><table width="684" border="0"><tr><td align="left" valign="top" width="228"><a href="ch12_01.htm"><img src="../gifs/txtpreva.gif" alt="Previous" border="0" /></a></td><td align="center" valign="top" width="228" /><td align="right" valign="top" width="228"><a href="ch12_03.htm"><img src="../gifs/txtnexta.gif" alt="Next" border="0" /></a></td></tr></table></div><h2 class="sect1">12.2. Design of DBI</h2><p><a name="INDEX-1751" /><a name="INDEX-1752" />If DBM is too primitive for yourdatabase requirements, you'll have to use a moresophisticated database package. Options include the commercialproducts Oracle, Sybase, and Informix, and the freely available<em class="emphasis">MySQL</em> and <em class="emphasis">Postgres</em>.</p><p>Prior to Perl Version 5 and DBI, the problem was that with all thedatabase packages to choose from, there was no way to universalizedatabase support for Perl. You'd have to rebuild thePerl executable itself against libraries that included subroutinesfor direct access to the <a name="INDEX-1753" /><a name="INDEX-1754" />database package. For example,<em class="emphasis">sybperl</em> and <em class="emphasis">oraperl</em> areboth packages for building Perl Version 4 with Sybase and Oraclecalls embedded, respectively. An application written for<em class="emphasis">sybperl</em> would not be portable to Oracle, or viceversa. However, since current versions of Perl support binaryextension loading at runtime, database support can now be added atruntime, which simplifies adding database interfaces to Perl programswhile keeping the size of the Perl binary to a minimum.</p><p>Support for binary extensions doesn't mean thatdatabase access has been standardized. There are still many databaseextensions to Perl, each with a different API. However, theirunderlying support for SQL (or ability to be manipulated into a formthat mimics the behavior of SQL through a DBD) has made it possibleto develop a standard set of methods to work with any database. DBIdefines a set of functions, variables, and conventions that provide aconsistent database programming interface for Perl.</p><a name="perlnut2-CHP-12-SECT-2.1" /><div class="sect2"><h3 class="sect2">12.2.1. Database Drivers (DBDs)</h3><p><a name="INDEX-1755" /><a name="INDEX-1756" />The success of DBI is thatit is only half of the story. The other half is a DBD, or a databasedriver. DBI provides the interface and framework for the drivers, butit's the database drivers that do the real work.Drivers implement the DBI methods for the private interface functionsof the corresponding database engine.</p><p>Unless you're developing a sophisticated databaseapplication, you probably don't care about thedrivers except that you want to install the correct one. <a href="ch12_02.htm#perlnut2-CHP-12-TABLE-1">Table 12-1</a> lists database servers, where you can findthem, and the DBD driver designed for it. (The freeware or sharewaredatabase servers are available for download, and some of thecommercial servers offer evaluation copies for download.)</p><a name="perlnut2-CHP-12-TABLE-1" /><h4 class="objtitle">Table 12-1. Database servers </h4><table border="1" cellpadding="3"><tr><th><p>Server</p></th><th><p>URL</p></th><th><p>DBD</p></th></tr><tr><td><p>DB2</p></td><td><p><a href="http://www.software.ibm.com/data/db2/">http://www.software.ibm.com/data/db2/</a></p></td><td><p>DBD::DB2</p></td></tr><tr><td><p>Empress</p></td><td><p><a href="http://www.empress.com/">http://www.empress.com/</a></p></td><td><p>DBD::Empress</p></td></tr><tr><td><p>Fulcrum</p></td><td><p><a href="http://www.fulcrum.com">http://www.fulcrum.com</a></p></td><td><p>DBD::Fulcrum</p></td></tr><tr><td><p>Informix</p></td><td><p><a href="http://www.informix.com/">http://www.informix.com/</a></p></td><td><p>DBD::Informix</p></td></tr><tr><td><p>Ingres</p></td><td><p><a href="http://www.cai.com/products/ingres.htm">http://www.cai.com/products/ingres.htm</a></p> <p><a href="http://epoch.cs.berkeley.edu:8000/postgres/index.html">http://epoch.cs.berkeley.edu:8000/postgres/index.html</a></p></td><td><p>DBD::Ingres</p></td></tr><tr><td><p>miniSQL</p></td><td><p><a href="http://www.hughes.com.au/">http://www.hughes.com.au/</a></p></td><td><p>DBD::mSQL</p></td></tr><tr><td><p>MySQL</p></td><td><p><a href="http://www.tcx.se/">http://www.tcx.se/</a></p></td><td><p>DBD::mysql</p></td></tr><tr><td><p>Oracle</p></td><td><p><a href="http://www.oracle.com/">http://www.oracle.com/</a></p></td><td><p>DBD::Oracle</p></td></tr><tr><td><p>PostgreSQL</p></td><td><p><a href="http://www.postgresql.org/">http://www.postgresql.org/</a></p></td><td><p>DBD::Pg</p></td></tr><tr><td><p>QuickBase</p></td><td><p><a href="http://www.openbase.com/">http://www.openbase.com/</a></p></td><td><p>DBD::QBase</p></td></tr><tr><td><p>Solid</p></td><td><p><a href="http://www.solidtech.com/">http://www.solidtech.com/</a></p></td><td><p>DBD::Solid</p></td></tr><tr><td><p>Sybase</p></td><td><p><a href="http://www.sybase.com/">http://www.sybase.com/</a></p></td><td><p>DBD::Sybase</p></td></tr></table><p></div><a name="perlnut2-CHP-12-SECT-2.2" /><div class="sect2"><h3 class="sect2">12.2.2. Creating a Database</h3><p>Before you can open a connection to a database with DBI, you mustcreate the database. DBI can't do this step for you,although your DBD might allow you to. For example, DBD::MySQLimplements a <tt class="literal">_CreateDB</tt> function. Your DBD mightalso support the <tt class="literal">func</tt> method, which is used tocall private (and often nonportable) methods in the driver. You coulduse a one-liner like this to create the database from the commandline:</p><blockquote><pre class="code">perl -MDBI -e '$db_name = q[<em class="replaceable"><tt>database_name_here</tt></em>]; \ $result = DBD::mysql::dr->func($db_name, '_CreateDB');'</pre></blockquote><p>If your DBD allows to you to create databases via the API,it's likely that it will allow you to drop them,too:</p><blockquote><pre class="code">perl -MDBI -e '$db_name = q[<em class="replaceable"><tt>database_name_here</tt></em>]; \ $result = DBD::mysql::dr->func($db_name, '_DropDB');'</pre></blockquote></div><a name="perlnut2-CHP-12-SECT-2.3" /><div class="sect2"><h3 class="sect2">12.2.3. Database Handles and Statement Handles</h3><p><a name="INDEX-1757" /><a name="INDEX-1758" /><a name="INDEX-1759" />DBI methods work on two differenttypes of handles: database handles and statement handles. A databasehandle is like a filehandle; <tt class="literal">connect</tt> is a DBIclass method that opens a connection to a database and returns adatabase handle object:</p><blockquote><pre class="code">$db_handle = DBI->connect(dbi:mSQL:bookdb, undef, undef) || die("Connect error: $DBI::errstr");</pre></blockquote><p>Statement handles are another thing entirely. DBI makes a distinctionbetween the preparation of SQL statements and their execution byallowing you to preformat a statement into a statement handle.<a name="INDEX-1760" />You can prepare a statement with the<tt class="literal">prepare</tt> method, which returns a statement handle.You can then assign a SQL statement to the statement handle viavarious statement handle methods and execute it with the<tt class="literal">execute</tt> method when you're done.(You can also prepare and execute in the same command with the<tt class="literal">do</tt> method.)</p><p>Changes to the database are written to the database<a name="INDEX-1761" />automatically if the AutoCommit attributeis turned on. If AutoCommit is off, use the <tt class="literal">commit</tt>method when you're ready to write the changes to thedatabase.</p><p>AutoCommit is only one of many attributes that can be set for bothdatabase and statement handles. For example, if<tt class="literal">$st_handle</tt> is a statement handle, you can set<tt class="literal">$st_handle->{NULLABLE}</tt> to determine if thefields can contain null characters. <a href="ch12_02.htm#perlnut2-CHP-12-TABLE-2">Table 12-2</a> is alisting of all the attributes supported by database handles,statement handles, or both.</p><a name="perlnut2-CHP-12-TABLE-2" /><h4 class="objtitle">Table 12-2. Attributes for database and statement handles </h4><table border="1" cellpadding="3"><tr><th><p>Attribute</p></th><th><p>Description</p></th></tr><tr><td><p><b class="emphasis-bold">Attribute for database handles</b></p></td></tr><tr><td><p>AutoCommit</p></td><td><p>Commits any changes to the database immediately, instead of waitingfor an explicit call to <tt class="literal">commit</tt>. Default is true.</p></td></tr><tr><td><p><b class="emphasis-bold">Attributes for statement handles</b></p></td></tr><tr><td><p>CursorName</p></td><td><p>The name of the cursor associated with the statement handle.</p></td></tr><tr><td><p>NAME</p></td><td><p>A reference to an array of field names.</p></td></tr><tr><td><p>NULLABLE</p></td><td><p>A reference to an array describing whether each field can contain anull character.</p></td></tr><tr><td><p>NUM_OF_FIELDS</p></td><td><p>Number of fields the prepared statement will return.</p></td></tr><tr><td><p>NUM_OF_PARAMS</p></td><td><p>Number of placeholders in the prepared statement.</p></td></tr><tr><td><p><b class="emphasis-bold">Attributes common to all handles</b></p></td></tr><tr><td><p>Warn</p></td><td><p>Enables warnings.</p></td></tr><tr><td><p>CompatMode</p></td><td><p>Enables compatible behavior for a specific driver.</p></td></tr><tr><td><p>InactiveDestroy</p></td><td><p>Destroying a handle does not close prepared statements or disconnectfrom the database.</p></td></tr><tr><td><p>PrintError</p></td><td><p>Errors generate warnings.</p></td></tr><tr><td><p>RaiseError</p></td><td><p>Errors raise exceptions.</p></td></tr><tr><td><p>ChopBlanks</p></td><td><p>Truncates trailing space characters in fixed-width character fields.</p></td></tr><tr><td><p>LongReadLen</p></td><td><p>Controls the maximum length of long data.</p></td></tr><tr><td><p>LongTruncOk</p></td><td><p>Controls whether fetching long data that has been truncated shouldfail.</p></td></tr></table><p></div><a name="perlnut2-CHP-12-SECT-2.4" /><div class="sect2"><h3 class="sect2">12.2.4. Placeholders and Statement Handles</h3><p><a name="INDEX-1762" /><a name="INDEX-1763" />Many database drivers allow you touse question marks as placeholders in SQL statements and then bindvalues to the placeholders before executing them. This enables you toprepare a single statement with placeholders and reuse it for eachrow of the database. For example, the <tt class="literal">prepare</tt>statement might read:</p><blockquote><pre class="code">$st_handle = $db_handle->prepare(q{ insert into books (isbn, title) values (?, ?)}) || die db_handle->errstr;</pre></blockquote><p>And a subsequent <tt class="literal">execute</tt> statement might read: </p><blockquote><pre class="code">$st_handle->execute("1-56592-286-7", "Perl in a Nutshell") || die $db_handle->errstr;</pre></blockquote></div><hr width="684" align="left" /><div class="navbar"><table width="684" border="0"><tr><td align="left" valign="top" width="228"><a href="ch12_01.htm"><img src="../gifs/txtpreva.gif" alt="Previous" border="0" /></a></td><td align="center" valign="top" width="228"><a href="index.htm"><img src="../gifs/txthome.gif" alt="Home" border="0" /></a></td><td align="right" valign="top" width="228"><a href="ch12_03.htm"><img src="../gifs/txtnexta.gif" alt="Next" border="0" /></a></td></tr><tr><td align="left" valign="top" width="228">12. Databases and Perl</td><td align="center" valign="top" width="228"><a href="index/index.htm"><img src="../gifs/index.gif" alt="Book Index" border="0" /></a></td><td align="right" valign="top" width="228">12.3. DBI Methods</td></tr></table></div><hr width="684" align="left" /><img src="../gifs/navbar.gif" usemap="#library-map" border="0" alt="Library Navigation Links" /><p><p><font size="-1"><a href="copyrght.htm">Copyright © 2002</a> O'Reilly & Associates. All rights reserved.</font></p><map name="library-map"><area shape="rect" coords="1,0,85,94" href="../index.htm"><area shape="rect" coords="86,1,178,103" href="../lwp/index.htm"><area shape="rect" coords="180,0,265,103" href="../lperl/index.htm"><area shape="rect" coords="267,0,353,105" href="../perlnut/index.htm"><area shape="rect" coords="354,1,446,115" href="../prog/index.htm"><area shape="rect" coords="448,0,526,132" href="../tk/index.htm"><area shape="rect" coords="528,1,615,119" href="../cookbook/index.htm"><area shape="rect" coords="617,0,690,135" href="../pxml/index.htm"></map></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -