Carping About DBI - Animal Antics
(Page 3 of 10 )
The best way to understand a new API (and that's all that the DBI is, a consistent database API) is by trying out some sample code.
Before I begin, though, you should make sure that you have the DBI installed on your system. A simple way to test for its presence is to use "perldoc" to look up its documentation.
$ perldoc DBI
If you can read the documentation, it's a good bet that the module is installed. If it isn't there, then you'll have to download it from
http://www.cpan.org/ and install it (note that you'll have to install both the base DBI module and the DBD of whichever database you're using). For the latter part of this article, you might also like to install the Carp module (although it's almost always present) and the CGI module (if you plan to use CGI::Carp).
With that out of the way, here's a simple example which demonstrates some of the functionality of the DBI. Consider the following database table,
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).
This article copyright Melonfire 2001. All rights reserved.Next: Do()ing More >>
More Perl Articles
More By Vikram Vaswani, (c) Melonfire
/ 1 
