📄 ch14_11.htm
字号:
<HTML><HEAD><TITLE>Recipe 14.10. Executing an SQL Command Using DBI and DBD (Perl Cookbook)</TITLE><METANAME="DC.title"CONTENT="Perl Cookbook"><METANAME="DC.creator"CONTENT="Tom Christiansen & Nathan Torkington"><METANAME="DC.publisher"CONTENT="O'Reilly & Associates, Inc."><METANAME="DC.date"CONTENT="1999-07-02T01:43:00Z"><METANAME="DC.type"CONTENT="Text.Monograph"><METANAME="DC.format"CONTENT="text/html"SCHEME="MIME"><METANAME="DC.source"CONTENT="1-56592-243-3"SCHEME="ISBN"><METANAME="DC.language"CONTENT="en-US"><METANAME="generator"CONTENT="Jade 1.1/O'Reilly DocBook 3.0 to HTML 4.0"><LINKREV="made"HREF="mailto:online-books@oreilly.com"TITLE="Online Books Comments"><LINKREL="up"HREF="ch14_01.htm"TITLE="14. Database Access"><LINKREL="prev"HREF="ch14_10.htm"TITLE="14.9. Persistent Data"><LINKREL="next"HREF="ch14_12.htm"TITLE="14.11. Program: ggh - Grep Netscape Global History"></HEAD><BODYBGCOLOR="#FFFFFF"><img alt="Book Home" border="0" src="gifs/smbanner.gif" usemap="#banner-map" /><map name="banner-map"><area shape="rect" coords="1,-2,616,66" href="index.htm" alt="Perl Cookbook"><area shape="rect" coords="629,-11,726,25" href="jobjects/fsearch.htm" alt="Search this book" /></map><div class="navbar"><p><TABLEWIDTH="684"BORDER="0"CELLSPACING="0"CELLPADDING="0"><TR><TDALIGN="LEFT"VALIGN="TOP"WIDTH="228"><ACLASS="sect1"HREF="ch14_10.htm"TITLE="14.9. Persistent Data"><IMGSRC="../gifs/txtpreva.gif"ALT="Previous: 14.9. Persistent Data"BORDER="0"></A></TD><TDALIGN="CENTER"VALIGN="TOP"WIDTH="228"><B><FONTFACE="ARIEL,HELVETICA,HELV,SANSERIF"SIZE="-1"><ACLASS="chapter"REL="up"HREF="ch14_01.htm"TITLE="14. Database Access"></A></FONT></B></TD><TDALIGN="RIGHT"VALIGN="TOP"WIDTH="228"><ACLASS="sect1"HREF="ch14_12.htm"TITLE="14.11. Program: ggh - Grep Netscape Global History"><IMGSRC="../gifs/txtnexta.gif"ALT="Next: 14.11. Program: ggh - Grep Netscape Global History"BORDER="0"></A></TD></TR></TABLE></DIV><DIVCLASS="sect1"><H2CLASS="sect1"><ACLASS="title"NAME="ch14-77835">14.10. Executing an SQL Command Using DBI and DBD</A></H2><DIVCLASS="sect2"><H3CLASS="sect2"><ACLASS="title"NAME="ch14-pgfId-1262">Problem<ACLASS="indexterm"NAME="ch14-idx-1000005007-0"></A><ACLASS="indexterm"NAME="ch14-idx-1000005007-1"></A><ACLASS="indexterm"NAME="ch14-idx-1000005007-2"></A><ACLASS="indexterm"NAME="ch14-idx-1000005007-3"></A></A></H3><PCLASS="para">You want to send SQL queries to a database system such as Oracle, Sybase, mSQL, or MySQL, and process their results.</P></DIV><DIVCLASS="sect2"><H3CLASS="sect2"><ACLASS="title"NAME="ch14-pgfId-1268">Solution</A></H3><PCLASS="para">Use the DBI (DataBase Interface) and DBD (DataBase Driver) modules available from CPAN:</P><PRECLASS="programlisting"><CODECLASS="literal">use DBI;</CODE><CODECLASS="literal">$dbh = DBI->connect('DBI:driver:database', 'username', 'auth',</CODE><CODECLASS="literal"> { RaiseError => 1, AutoCommit => 1});</CODE><CODECLASS="literal">$dbh->do($SQL);</CODE><CODECLASS="literal">$sth = $dbh->prepare($SQL);</CODE><CODECLASS="literal">$sth->execute();</CODE><CODECLASS="literal">while (@row = $sth->fetchrow_array) {</CODE><CODECLASS="literal"> # ...</CODE><CODECLASS="literal">}</CODE><CODECLASS="literal">$sth->finish();</CODE><CODECLASS="literal">$dbh->disconnect();</CODE></PRE></DIV><DIVCLASS="sect2"><H3CLASS="sect2"><ACLASS="title"NAME="ch14-pgfId-1306">Discussion</A></H3><PCLASS="para">DBI acts as an intermediary between your program and any number of DBMS-specific drivers. For most actions you need a database handle (<CODECLASS="literal">$dbh</CODE> in the example). This is attached to a specific database and driver using the <CODECLASS="literal">DBI->connect</CODE> call.</P><PCLASS="para">The first argument to <CODECLASS="literal">DBI->connect</CODE> is a single string with three colon-separated fields. It represents the <EMCLASS="emphasis">data source </EM> - the DBMS you're connecting to. The first field is always <CODECLASS="literal">DBI</CODE>, and the second is the name of the driver you're going to use (<CODECLASS="literal">Oracle</CODE>, <CODECLASS="literal">mysql</CODE>, etc.). The rest of the string is passed by the DBI module to the requested driver module (DBD::mysql, for example) where it identifies the database.</P><PCLASS="para">The second and third arguments authenticate the user.</P><PCLASS="para">The fourth argument is an optional hash reference defining attributes of the connection. Setting PrintError to true makes DBI warn whenever a DBI method fails. Setting RaiseError is like PrintError except that <CODECLASS="literal">die</CODE> is used instead of <CODECLASS="literal">warn</CODE>. AutoCommit says that you don't want to deal with transactions (smaller DBMSs don't support them, and if you're using a larger DBMS then you can read about transactions in the DBMS documentation).</P><PCLASS="para">You can execute simple SQL statements (those that don't return rows of data) with a database handle's <CODECLASS="literal">do</CODE> method. This returns Boolean true or false. SQL statements that return rows of data (like <CODECLASS="literal">SELECT</CODE>) require that you first use the database handle's <CODECLASS="literal">prepare</CODE> method to create a statement handle. Then call the <CODECLASS="literal">execute</CODE> method on the statement handle to perform the query, and retrieve rows with a fetch method like <CODECLASS="literal">fetchrow_array</CODE> or <CODECLASS="literal">fetchrow_hashref</CODE> (which returns a reference to a hash mapping column name to value).</P><PCLASS="para">Statement handles and database handles often correspond to underlying connections to the database, so some care must be taken with them. A connection is automatically cleaned up when its handle goes out of scope. If a database handle goes out of scope while there are active statement handles for that database, though, you will get a warning like this:</P><PRECLASS="programlisting">disconnect(DBI::db=HASH(0x9df84)) invalidates 1 active cursor(s) at -e line 1.</PRE><PCLASS="para">The <CODECLASS="literal">finish</CODE> method ensures the statement handle is inactive (some old drivers need this). The <CODECLASS="literal">disconnect</CODE> method, er, disconnects from the database.</P><PCLASS="para">The DBI module comes with a FAQ (<CODECLASS="literal">perldoc</CODE> <CODECLASS="literal">DBI::FAQ</CODE>) and regular documentation (<CODECLASS="literal">perldoc</CODE> <CODECLASS="literal">DBI</CODE>). The driver for your DBMS also has documentation (<CODECLASS="literal">perldoc</CODE> <CODECLASS="literal">DBD::mysql</CODE>, for instance). The DBI API is larger than the simple subset we've shown here; it provides diverse ways of fetching results, and it hooks into DBMS-specific features like stored procedures. Consult the driver module's documentation to learn about these.</P><PCLASS="para">The program in <ACLASS="xref"HREF="ch14_11.htm#ch14-35497"TITLE="dbusers">Example 14.7</A> creates, populates, and searches a MySQL table of users. It uses the RaiseError attribute so it doesn't have to check the return status of every method call.</P><DIVCLASS="example"><H4CLASS="example"><ACLASS="title"NAME="ch14-35497">Example 14.7: dbusers</A></H4><PRECLASS="programlisting">#!/usr/bin/perl -w# <ACLASS="indexterm"NAME="ch14-idx-1000005013-0"></A>dbusers - manage MySQL user tableuse DBI;use User::pwent;$dbh = DBI->connect('DBI:mysql:dbname:mysqlserver.domain.com:3306', 'user', 'password', { RaiseError => 1, AutoCommit => 1 })$dbh->do("CREATE TABLE users (uid INT, login CHAR(8))");$sql_fmt = "INSERT INTO users VALUES( %d, %s )";while ($user = getpwent) { $sql = sprintf($sql_fmt, $user->uid, $dbh->quote($user->name)); $dbh->do($sql);}$sth = $dbh->prepare("SELECT * FROM users WHERE uid < 50");$sth->execute;while ((@row) = $sth->fetchrow_array) { print join(", ", map {defined $_ ? $_ : "(null)"} @row), "\n";}$sth->finish; $dbh->do("DROP TABLE users");$dbh->disconnect;<ACLASS="indexterm"NAME="ch14-idx-1000005009-0"></A><ACLASS="indexterm"NAME="ch14-idx-1000005009-1"></A><ACLASS="indexterm"NAME="ch14-idx-1000005009-2"></A><ACLASS="indexterm"NAME="ch14-idx-1000005009-3"></A></PRE></DIV></DIV><DIVCLASS="sect2"><H3CLASS="sect2"><ACLASS="title"NAME="ch14-pgfId-1386">See Also</A></H3><PCLASS="para">The documentation for the DBI and relevant DBD modules from CPAN; <ACLASS="systemitem.url"HREF="http://www.hermetica.com/technologia/perl/DBI/">http://www.hermetica.com/technologia/perl/DBI/</A> and <ACLASS="systemitem.url"HREF="http://www.perl.com/CPAN/modules/ by-category/07_Database_Interfaces/">http://www.perl.com/CPAN/modules/ by-category/07_Database_Interfaces/</A></P></DIV></DIV><DIVCLASS="htmlnav"><P></P><HRALIGN="LEFT"WIDTH="684"TITLE="footer"><TABLEWIDTH="684"BORDER="0"CELLSPACING="0"CELLPADDING="0"><TR><TDALIGN="LEFT"VALIGN="TOP"WIDTH="228"><ACLASS="sect1"HREF="ch14_10.htm"TITLE="14.9. Persistent Data"><IMGSRC="../gifs/txtpreva.gif"ALT="Previous: 14.9. Persistent Data"BORDER="0"></A></TD><TDALIGN="CENTER"VALIGN="TOP"WIDTH="228"><ACLASS="book"HREF="index.htm"TITLE="Perl Cookbook"><IMGSRC="../gifs/txthome.gif"ALT="Perl Cookbook"BORDER="0"></A></TD><TDALIGN="RIGHT"VALIGN="TOP"WIDTH="228"><ACLASS="sect1"HREF="ch14_12.htm"TITLE="14.11. Program: ggh - Grep Netscape Global History"><IMGSRC="../gifs/txtnexta.gif"ALT="Next: 14.11. Program: ggh - Grep Netscape Global History"BORDER="0"></A></TD></TR><TR><TDALIGN="LEFT"VALIGN="TOP"WIDTH="228">14.9. Persistent Data</TD><TDALIGN="CENTER"VALIGN="TOP"WIDTH="228"><ACLASS="index"HREF="index/index.htm"TITLE="Book Index"><IMGSRC="../gifs/index.gif"ALT="Book Index"BORDER="0"></A></TD><TDALIGN="RIGHT"VALIGN="TOP"WIDTH="228">14.11. Program: ggh - Grep Netscape Global History</TD></TR></TABLE><HRALIGN="LEFT"WIDTH="684"TITLE="footer"><FONTSIZE="-1"></DIV<!-- LIBRARY NAV BAR --> <img src="../gifs/smnavbar.gif" usemap="#library-map" border="0" alt="Library Navigation Links"><p> <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 + -