ЭЛЕКТРОННАЯ БИБЛИОТЕКА КОАПП |
Сборники Художественной, Технической, Справочной, Английской, Нормативной, Исторической, и др. литературы. |
14.10. Executing an SQL Command Using DBI and DBDProblemYou want to send SQL queries to a database system such as Oracle, Sybase, mSQL, or MySQL, and process their results. SolutionUse the DBI (DataBase Interface) and DBD (DataBase Driver) modules available from CPAN:
DiscussionDBI acts as an intermediary between your program and any number of DBMS-specific drivers. For most actions you need a database handle ( The first argument to The second and third arguments authenticate the user. 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 You can execute simple SQL statements (those that don't return rows of data) with a database handle's 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: disconnect(DBI::db=HASH(0x9df84)) invalidates 1 active cursor(s) at -e line 1. The The DBI module comes with a FAQ ( The program in Example 14.7 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. Example 14.7: dbusers#!/usr/bin/perl -w # dbusers - manage MySQL user table use 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; See AlsoThe documentation for the DBI and relevant DBD modules from CPAN; http://www.hermetica.com/technologia/perl/DBI/ and http://www.perl.com/CPAN/modules/ by-category/07_Database_Interfaces/ |