Do()ing More - Perl

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.

This article copyright Melonfire 2001. All rights reserved.

>>> More Perl Programming Articles          >>> More By Vikram Vaswani, (c) Melonfire

blog comments powered by Disqus