Home arrow Perl Programming arrow Page 4 - Carping About DBI

Do()ing More - Perl

One of the nice things about Perl is the huge amount of free codeout there. Available in the form of modules, this code can simplify manycommon tasks while simultaneously offering a powerful toolkit for theexperienced developer. In this article, learn about two of the most popularPerl modules: DBI, used for database connectivity, and Carp, used tosimplify error handling.

TABLE OF CONTENTS:
  1. Carping About DBI
  2. Dissecting The DBI
  3. Animal Antics
  4. Do()ing More
  5. When Things Go Wrong
  6. Speed Demon
  7. Dummy Data
  8. Croak!
  9. Whining Some More
  10. Final Thoughts
By: Vikram Vaswani, (c) Melonfire
Rating: starstarstarstarstar / 1
May 02, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
escort Bursa Bursa escort Antalya eskort
   

PERL PROGRAMMING ARTICLES

- Perl Turns 25
- Lists and Arguments in Perl
- Variables and Arguments in Perl
- Understanding Scope and Packages in Perl
- Arguments and Return Values in Perl
- Invoking Perl Subroutines and Functions
- Subroutines and Functions in Perl
- Perl Basics: Writing and Debugging Programs
- Structure and Statements in Perl
- First Steps in Perl
- Completing Regular Expression Basics
- Modifiers, Boundaries, and Regular Expressio...
- Quantifiers and Other Regular Expression Bas...
- Parsing and Regular Expression Basics
- Hash Functions

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: