DBI
DBI - перловый интерфейс,позволяющий разработчикам стандартным образом иметь дело с различными
типами баз данных.
DBI сам не отвечает за взаимодействие между базой и перловым скриптом,
для этого существует еще один абстрактный слой - DBD.
DBI управляет и автоматически загружает DBDs,
проверяет ошибки и т.д.
Существуют варианты DBD для различных баз данных -
MySQL,PostgreSQL, Oracle и т.д.
Есть даже ODBC DBD, позволяющий коннектиться к Microsoft Access.
Для начала давайте проверим,стоит ли у вас DBI :
$ perldoc DBI
Если модуль у вас не установлен,его можно загрузить с http://www.cpan.org/
и инсталлировать его (нужно установить как DBI,так и DBD).
Также понадобится модуль Carp , ну и CGI это уже само собой.
В качестве примера рассмотрим следующую таблицу из базы данных:
mysql> SELECT * FROM pets;
+--------+------------+-----+
| name | species | age |
+--------+------------+-----+
| Dawg | dog | 5 |
| Rollo | rhinoceros | 7 |
| Polly | parrot | 1 |
| Chucky | chicken | 2 |
+--------+------------+-----+
4 rows in set (0.00 sec)
and then consider this short Perl script, which connects to the database and prints out the data within the table.
#!/usr/bin/perl
# load module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
my $sth = $dbh->prepare("SELECT * FROM pets");
$sth->execute();
# iterate through resultset
while(my $ref = $sth->fetchrow_hashref())
{
print "Name: $ref->{'name'}\nSpecies: $ref->{'species'}\nAge: $ref->{'age'}\n\n";
}
# clean up
$dbh->disconnect();
Here's the output.
Name: Dawg
Species: dog
Age: 5
Name: Rollo
Species: rhinoceros
Age: 7
Name: Polly
Species: parrot
Age: 1
Name: Chucky
Species: chicken
Age: 2
The script starts off simply enough; as you probably already know, the
first line calls the Perl interpreter and tells it to parse and run the
statements that follow.
use DBI()
is the first of those statements. It loads and activates the interface,
making it possible to now use the DBI from within the script.
The next line calls the function connect() from within the DBI, and
passes it a large number of parameters, including the name of the DBD
to use (mysql), the name of the database (somedb), the address of the
database server (localhost), and the database username and password.
Opening a connection to the database is generally an expensive
operation, requiring a certain amount of time and consuming a certain
amount of system resources. It's best to connect just once at the
beginning, and then disconnect at the end.
Next, a call to RaiseError ensures that if the DBI encounters an error,
it will die() rather than return an error value. For a simple script
like the one above, this works out pretty well; however, in more
complicated scripts, you might prefer to turn this off and handle
errors in a more intelligent manner.
Note that there is no standard for the string that follows a DBD name;
it differs in format from DBD to DBD. You'll need to consult the
documentation that came with your DBD to obtain the format specific to
your database.
As you can see, connect() returns a handle to the database, which is
used for all subsequent database operations. This also means that you
can open up connections to several databases simultaneously, using
different connect() statements, and store the returned handles in
different variables. This is useful if you need to access several
databases at the same time; it's also useful if you just want to be a
smart-ass and irritate the database administrators. Watch them run as
the databases over heat! Watch them scurry as their disks begin to
thrash! Watch them gibber and scream as they melt down!
The prepare() function, which takes an SQL query as parameter, readies
a query for execution, but does not execute it (kinda like the priest
that walks down the last mile with you to the electric chair). Instead,
prepare() returns a handle to the prepared query, which is stored and
then passed to the execute() method, which actually executes the query
(bzzzt!).
Although overkill for our simple needs, you should be aware that
prepare() can provide a substantial performance boost in certain
situations. Many database scripts involve preparing a single query (an
INSERT, for example) and then executing it again and again with
different values, and using a prepare() statement in such a situation
can help reduce overhead.
Once the query has been executed, the next order of business is to do
something with the returned data. A number of methods are available to
iterate through the resultset and parse it into different fields - I've
used the fetchrow_hashref() method to pull in the data as hash
references and format it for display. I could also have printed out the
entire table line by line using fetchrow_array() - this will be
demonstrated in the next example.
Once all the data has been retrieved, the disconnect() function takes
care of disengaging from the database (freeing up memory and generally
cleaning things up).
The DBI also comes with a couple of other methods to iterate through a
resultset. The first of these is the fetchrow_array() method, which
returns the various record fields as array elements. The following
example demonstrates how this can be used, by rewriting the example
above in terms of fetchrow_array().
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
my $sth = $dbh->prepare("SELECT * FROM pets");
$sth->execute();
# iterate through resultset
while(my @data= $sth->fetchrow_array())
{
print "Name: $data[0]\nSpecies: $data[1]\nAge: $data[2]\n\n";
}
# clean up
$dbh->disconnect();
If you prefer references, you can use the fetchrow_arrayref() method, which works in a similar manner.
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
my $sth = $dbh->prepare("SELECT * FROM pets");
$sth->execute();
# iterate through resultset
while(my $data = $sth->fetchrow_arrayref())
{
print "Name: $data->[0]\nSpecies: $data->[1]\nAge: $data->[2]\n\n";
}
# clean up
$dbh->disconnect();
And you can even get all the returned data in one fell swoop with the
fetchall_arrayref() method, which returns a reference to an array whose
every element is a reference to a row in the resultset (try saying that
backwards!). The following example should make this clearer.
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
my $sth = $dbh->prepare("SELECT * FROM pets");
$sth->execute();
# get everything as an array of arrays
my $everything = $sth->fetchall_arrayref();
# iterate
my($i);
for $i ( 0 .. $#{$everything} ) {
print "Name: $everything->[$i][0]\n";
print "Species: $everything->[$i][1]\n";
print "Age: $everything->[$i][2]\n";
print "\n";
}
# clean up
$dbh->disconnect();
Of course, there's a lot more to the DBI than what you've just seen.
For example, if you don't like the thought of prepare()-ing and
execute()-ing a query, you can take a shortcut with the do() method,
used to execute a query directly.
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658");
# execute query
my $rows = $dbh->do("UPDATE pets SET age = age+1 WHERE name = 'polly'");
print "$rows row(s) affected\n";
# clean up
$dbh->disconnect();
It makes more sense to use do() for SQL statements like CREATE and DROP
than to laboriously prepare() and then execute() them. However, if
you're going to be running a similar query over and over again,
prepare() and execute() will be more efficient. Additionally, do()
can't be used for SELECT queries because a statement handle is needed
for further data extraction, and do() only returns the number of rows
affected by the query (or an undef on error), making it impossible to
use for these kinds of operations.
The rows() method can be used to discover the number of rows affected
by the last SQL query. However, don't try to use this after an SQL
SELECT query (it's better for INSERTs and DELETEs), because the only
way most drivers can figure out the number of rows affected is to
actually count them as they are returned. Here's an example:
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
my $sth = $dbh->prepare("UPDATE pets SET age=age+1 WHERE name='polly'");
$sth->execute();
# print affected rows
print $sth->rows() . " row(s) affected\n";
# clean up
$dbh->disconnect();
If you need special characters quoted correctly, quote() will leap to your rescue.
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
$name = $dbh->quote("Godzilla's");
$dbh->do("INSERT INTO pets VALUES ($name, 'Iguana', 4)");
# clean up
$dbh->disconnect();
Finally, the finish() method is used to indicate that no more rows will
be processed from the resultset. This allows the database to clean up
some of the buffers at its end.
#!/usr/bin/perl
# activate module
use DBI();
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658", {'RaiseError' => 1});
# execute query
my $sth = $dbh->prepare("SELECT * FROM pets");
// snip
# clean up
$sth->finish();
$dbh->disconnect();
This function is not really required, but it's a good idea to use it if
you can. It keeps your code clean, and makes other programmers think
you know more than you do, which is always useful.
No matter how good you are, you're bound to screw up sometime. And when
it happens, you'll be glad that I told you about $DBI::errstr, used to
display error messages when things go wrong.
# connect
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "me", "me545658") || die "Can't connect: $DBI::errstr";
Additionally, you can also check the handle for errors during a query - as the following example demonstrates.
while(my $ref = $sth->fetchrow_hashref())
{
print "Name: $ref->{'Name'} Species: $ref->{'Species'} Age: $ref->{'Age'} \n";
if($sth->err)
{
die "There was an error: $sth->errstr";
}
The single greatest performance boost you can realize with DBI, is
through the intelligent use of prepare() and execute() functions.
Whenever you find yourself using a particular SQL statement over and
over again, prepare() it and call it using execute().
However, it rarely happens that the very same query, with no difference
at all, is executed over and over again. For example, suppose you're
trying to INSERT some data into the database. The INSERT statement may
remain similar in form, but the data values will keep changing every
time. In this case, prepare() and execute() must be used in combination
with DBI "placeholders", as demonstrated below.
$sth = $dbh->prepare("INSERT INTO pets VALUES (?,?,?)");
$sth->execute($name, $species, $age);
The questions marks you see in the prepare() statement are
placeholders, and they function just like variables; they indicate
where the data values for the current query will be placed later. The
data values themselves come from the subsequent execute() statement.
Typically, the execute() statement is placed in a loop and the
variables $name, $species and $age change every time. The data could be
entered interactively at the command prompt, read in from a file, or
injected intravenously.
It's also important to be careful about the number of open database
connections while using DBI. All databases have a finite limit on the
number of concurrent client connections they can handle. If the maximum
number of concurrent clients is unspecified, it may make more sense to
open up a new connection only when required and to shut it down as soon
as possible. This is necessary to ensure that there is never a time
when the database server is so heavily loaded that it cannot accept any
new connections and is forced to turn away client programs.
Unfortunately, opening and closing connections is computationally
expensive and time-consuming. You'll have to find a balance between
keeping connections open, and loading the server. This is something
that you can't apply in a cookbook fashion; it depends on many
variables and the final compromise will differ from implementation to
implementation.
Those of you who've been programming for some time know the value of
error messages - they're a great way to scare the pants off clueless
users. They also have other, more mundane uses, like actually helping
developers track down bugs in their scripts (yeah, right!) and
gracefully handling errors as and when they occur.
Perl comes with two very important and useful error-handling functions,
die() and warn(). You're probably already familiar with both of these;
die() is used to kill a rogue script, printing out an optional status
message, while warn() is used to warn the user about possible error
conditions by printing out either a user-supplied string or a default
message.
Unfortunately, sometimes more descriptive errors are needed. Although
die() and warn() work great for printing out errors and warnings, they
don't offer this information from the perspective of the "caller" in
the program. For example, when you call die() from within a function,
die() will report the error from the location where it is called and
not from the perspective of the called function. An example should make
this clearer.
#!/usr/bin/perl
sub readFile
{
my $filename = shift(@_);
open(FILE, $filename) or die("Cannot locate file!");
print <FILE>;
close FILE;
}
readFile("dummy.txt");
If the file "dummy.txt" cannot be found, Perl will exit the script with the following error message:
Cannot locate file! at ./carpdemo.pl line 8.
What is required, therefore, is something that not only prints out the
error when it happens, but also provides information on the sequence of
function calls leading up to the error. Something, in short, like the
Carp module.
The Carp module comes with four functions which are quite symmetrical
in form and function. Two functions, carp() and cluck(), only print out
warnings, while two other functions, croak() and confess(), terminate
the script and also print out a sequence of error messages. Both
cluck() and confess() can also print out the stack backtrace, making it
easier to pinpoint the problem.
The Carp module can be used wherever descriptive error messages warning
you about potential problems are required. They're not going to make
your program run any better, but they will simplify and streamline the
process of error detection and recovery.
It should be noted that although, they provide much-needed information,
all the Carp functions internally call warn() or die(). However, since
they're very lightweight and offer numerous advantages, I'd still
recommend that you use them in preference to both die() and warn().
I've rewritten the example above to demonstrate the utility of Carp.
#!/usr/bin/perl
use Carp;
sub readFile
{
my $filename = shift(@_);
open(FILE, $filename) or croak("Cannot locate file!");
print <FILE>;
close FILE;
}
readFile("dummy.txt");
And here's the output.
Cannot locate file! at ./carpdemo.pl line 8
main::readFile('dummy.txt') called at ./carpdemo.pl line 13
The Carp module also comes with a bunch of additional features designed
specifically for use in Perl CGI scripts. These features make it easier
to track errors thrown up by your CGI scripts, and to log these errors
appropriately.
When running as a CGI script, your error output is automatically
redirected to the HTTP server error log. Unfortunately, the output is
not time-stamped, making it difficult to figure out when the error
occurred. To get time-stamped error and warning messages, simply
replace the line
use Carp;
with
use CGI::Carp;
The rest of your code remains the same.
Another nifty little feature here is the ability to echo error messages
(though not warnings) to the client browser. Simply import the
fatalsToBrowser() subroutine and this will be done for you
automagically. Change the line
use CGI::Carp;
to
use CGI::Carp qw(fatalsToBrowser);
Now, not only will errors be recorded in the log, but they will also be
displayed to the user. This may or may not be something you want (do
you really want the world to see the error messages your shoddy little
script vomits out?), which is why it has to be turned on manually by
importing fatalsToBrowser().
Finally, the carpout() function allows you to redirect error messages
to a file of your choosing, rather than sending them to the standard
error log. This can be a great help while debugging, since you won't
have to wade through miles of log file messages in order to find the
hairballs you script coughed up.
Here's an example:
BEGIN {
use CGI::Carp qw(carpout);
open (CARPLOG, ">>/usr/home/luser/my-script.log") or die ("Can't open log file!");
carpout(CARPLOG);
}
As you can see, carpout() has to be specially imported in before it can
be used. carpout() needs just one parameter, the file handle of the
user-defined error log.
Keep in mind that carpout() degrades performance and should not be used
in an actual production script. Use it only as a tool for debugging
your script, or as an excuse to explain why it's so slow.
Both DBI and Carp are extremely useful modules. DBI is being worked on
extensively and is very popular amongst Perl coders. Its popularity
ensures that database drivers are written for even the most obscure
platforms and then kept up to date. It's not too difficult to write
your own database drivers, so if you have some really strange database
you have to interface with, you can put in a little more work and still
use DBI.
DBI also makes the process of porting code between databases trivial.
If your code is general enough and doesn't use any database specific
tricks, then all you may have to do is change the DBD string in the
connect() function. Light, fast, well-designed and very well-supported,
this is a gem of a module.
Carp is also pretty popular. Many developers still prefer to use die()
and warn() directly, but once you're hooked onto Carp, it's difficult
to let go. It's a very small module, and using it entails a negligible
performance and size hit. It's also small enough to read and
understand...which means that it can be easily modified or customized.
Both these modules come with good documentation, although the
documentation which ships with DBI is far more complete. Be sure to not
only read the DBI documentation, but also the documentation for the
relevant DBD, since it will usually contain sample code and detailed
information about each and every available function.
You can obtain more information on both these modules at any of the links below:
The DBI FAQs at http://theoryx5.uwinnipeg.ca/cgi-bin/doc-filter?page=data/DBI/DBI/FAQ.html;query=dbi;match=and;where=all;stem=;type=data and http://theoryx5.uwinnipeg.ca/cgi-bin/doc-filter?page=data/DBI/DBI.html;query=dbi;match=and;where=all;stem=;type=data
Perldoc documentation for DBI at http://www.perldoc.com/cpan/DBI.html
Perldoc documentation for Carp at http://www.perldoc.com/cpan/CGI/Carp.html
"A Short Guide To DBI" by Mark-Jason Dominus at http://www.perl.com/pub/1999/10/DBI.html
|
|
LINUX