Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Tricks with DBI

by btrott (Parson)
on Apr 14, 2000 at 03:36 UTC ( [id://7568]=perltutorial: print w/replies, xml ) Need Help??

Tricks with DBI

1. Check for database errors.

You're going to run into errors with databases for similar reasons that you do when using system calls and the like. And just as you should always check the return code of your system calls, so should you always check the return status of your database calls.

The easiest way to do this is by setting DBI's RaiseError attribute to 1; first connect to the database (and check the return), then set the RaiseError attribute:

my $dbh = DBI->connect('foo', 'bar', 'baz', 'mysql') or die "Can't connect: ", $DBI::errstr; $dbh->{RaiseError} = 1;
By doing this, you ensure that any database error will cause a die. Why is this good? Because generally, if you're writing a database application and you have a database error, you don't want to continue as if nothing happened. :) (Besides, you can always catch the die in an eval; just make sure that you handle the errors rather than ignoring them.)

The other way to check for errors, of course, is to check the return of each method call, eg.:

my $sth = $dbh->prepare("select id from foo") or die "Can't prepare: ", $dbh->errstr; $sth->execute or die "Can't execute: ", $dbh->errstr;
So as not to make your code one big C-like mess of error checking, though, you might as well just use RaiseError.

2. Use placeholders instead of literal values.

This is covered pretty thoroughly in What are placeholders in DBI. Suffice it to say here, then, that you should use placeholders instead of literal values. Always (or pretty much, at least).

And, for the same reasons, you should use prepare_cached instead of prepare.

3. The fastest way to fetch.

When you execute a SELECT statement, you want to get the data back as quickly as possible. The fastest way to do this is to use the bind_columns and fetch methods, because they don't copy a bunch of memory around.

bind_columns binds Perl variables to columns returned from your SELECT statement. For example, if you had the following SQL statement:

select id, name, phone from people
You'd want to bind 3 variables to the associated columns. So you set up the variables, then use bind_columns to bind them:
my($id, $name, $phone); $sth->bind_columns(undef, \$id, \$name, \$phone);
(The first argument to bind_columns is actually a hash reference specifying DBI attributes to associate with this particular method; we don't want to associate any attributes with this particular method, so we'll just pass it undef.)

After you've called execute on your statement handle, you'll want to fetch the data. To do this, use the fetch method, which fetches the next row and returns an array reference holding the field values. But you don't need to use that array reference, because you've got the column values bound to Perl variables, and you can just use those directly:

while ($sth->fetch) { print join("\t", $id, $name, $phone), "\n"; }
Each time you call fetch on your statement handle, the values of the bound variables get updated.

A Brief Interlude

Putting Tricks 1-3 together, we get something like the following:
# Connect to the database and set the RaiseError # attribute so that any database error will # cause a die my $dbh = DBI->connect('foo', 'bar', 'baz', 'mysql') or die "Can't connect: ", $DBI::errstr; $dbh->{RaiseError} = 1; # Setup our statement handle using a placeholder my $sth = $dbh->prepare_cached(<<SQL); select id, name, phone from people where birth_month = ? SQL my @months = qw/January February March April May June July August September October November December/; # Bind Perl variables to columns returned # from SELECT my($id, $name, $phone); $sth->bind_columns(undef, \$id, \$name, \$phone); for my $month (@months) { print "People born in $month:\n"; # Execute the statement for this $month $sth->execute($month); # Fetch each row and print out the values while ($sth->fetch) { print "\t", join("\t", $id, $name, $phone), "\n"; } } $sth->finish; $dbh->disconnect;

4. Using DBI with mod_perl.

Part of the purpose of mod_perl is to make Perl programs run much faster on the Apache web server. mod_perl accomplishes this by building a Perl interpreter into the web server and compiling your programs in memory; thus, when it gets a new request, it maps that request onto a program compiled into memory--this is very fast.

If you're going to use DBI with mod_perl, you'll want to make sure that your database transactions are fast, as well (because you don't want the database being the bottleneck). The easiest way to accomplish this is to use the Apache::DBI module, which gives your programs persistent database connections. It does so by overriding the DBI connect method and keeping a cache of open database handles. One of the nicest things about Apache::DBI is that you won't have to modify your existing DBI code to use it. Just add

use Apache::DBI;
BEFORE the
use DBI;
in your code, and you're set. You don't even have to take out the calls to disconnect, because Apache::DBI overloads those calls and makes sure that the handle doesn't actually get disconnected.

See Also

The new Programming the Perl DBI; Mark-Jason Dominus's A Short Guide to DBI; the DBI manpage (also (probably) available as perldoc DBI).

Replies are listed 'Best First'.
RE: Tricks with DBI
by Michalis (Pilgrim) on May 31, 2000 at 10:40 UTC
    Well done. Some corrections if I may.
    (a) bind_columns SHOULD be after execute has been called. As DBI's manual says, that's for maximum compatibility.
    (b) You call bind_columns with four parameters (the first one been undef) while your execute only returns three (id, name and phone). While I have never used bind_columns before execute, I beleive that should fail.

    My way is similar but not the same (I include just the different part):

    my $sth = $dbh->prepare_cached(<<SQL); select id, name, phone from people where birth_month = ? SQL for my $month (@months) { print "People born in $month:\n"; my($id, $name, $phone); # Execute the statement for this $month $sth->execute($month); $sth->bind_columns(\$id, \$name, \$phone); # Also valid: $sth->bind_columns(\($id, $name, $phone)); # Fetch each row and print out the values while ($sth->fetch) { print "\t", join("\t", $id, $name, $phone), "\n"; } }
      Thanks for the suggestions. You're quite right about bind_columns being called after execute; I've been bitten by that in the past.

      As for the 4 arguments to bind_columns, and the first argument being undef... I believe this is an anachronism of DBI. I just looked at the manpage, and you're right that it seems to only expect 3 arguments, now.

      In the past, though, I *think* the first argument to bind_columns was supposed to be a hash ref. So I got into the habit of using undef as the first arg, because I never cared about passing anything in the hash ref. I can't find that in the DBI manpage anymore, but I did find this:

      bind_columns .... For compatibility with old scripts, if the first parameter is undef or a hash reference it will be ignored.
      I don't remember what the hash ref was supposed to hold--nor would I, really, since I never actually used it. :) I always just used undef. Thanks for pointing this out.
      Thanks for sharing the code and helping me to solve my problem.
Re: Tricks with DBI
by Arguile (Hermit) on Sep 18, 2001 at 01:34 UTC
    "One of the nicest things about Apache::DBI is that you won't have to modify your existing DBI code to use it. Just add use Apache::DBI; BEFORE the use DBI; in your code, and you're set."

    A great article, but this line could potentially be misleading.

    The Apache::DBI module is completely transparent; so you don't need to touch your scripts for any reason. Where Apache::DBI actually is defined, is in Apache's configuration file (httpd.conf by default).

    Sample mod_perl section of httpd.conf

    ... # preload these modules PerlModule Apache::Registry; PerlModule CGI; PerlModule Apache::DBI; PerlModule DBI; # startup script PerlRequire /path/to/startup.pl ...

    If you have a lot of modules to preload, or to use Perl syntax as illustrated above by bttrot, you can define them in the startup script instead. The rule about Apache::DBI going before any other DBI modules still applies.

    Just thought I'd clear that up for anyone who read it to mean, "Add use Apache::DBI; to your CGI scripts."

    Apache::DBI Potential Traps (UPDATE)

    Before everyone runs and starts using Apache::DBI, I'd like to add a cautionary warning.

    Don't create a database connection in startup.pl. When Apache forks the connection will be clobbered. Instead Apache::DBI has the startup.pl safe Apache::DBI->connect_on_init() method (that runs in the PerChildInit phase), though there are caveats to it as well.

    Don't use this module if you're logging users into the database individually. For each apache child (pre-forking model) a database connection may be established for each connection string used. This means if you have 10 apache children connecting as 'wwwuser', you'll possibly have 10 connections being kept open. If you log each of say qw(Bill Joe Sally Evan Sue) in individually, a connection for each user in each child might be established. That's 50 open database connections in our tiny example.

    More detailed info on these and other issues have been gathered in the new mod_perl and Relational Databases article, be sure to check it out.

    See Also

Re: Tricks with DBI (esp. Connect)
by htoug (Deacon) on Aug 15, 2001 at 12:33 UTC
    In newer versions of DBI the connect call has changed (the old one still works, but is depreceated). The call should be:
    my $dbh = DBI->connect("dbi:mysql:foo", "bar", "baz", { AutoCommit => 1, RaiseError => 1}) or die die "Can't connect: ", $DBI::errstr;
    Where the "dbi:mysql:foo" bit is "dbi:'DBD-driver-name':'database-name-and-options'".
    You will have to see in the documentation for your chosen DBD to see what is possible and legal for the 'database-name-and-options' part.

    RaiseError is set in an anonymous hash - along with other database attributes.

    Note that I set AutoCommit state in the connect call. This is highly recommended, and will become mandatory in a coming DBI release.
    The reason is that the ODBC spec (which DBI follows as closely as possible) has AutoCommit=>1 as default, while many (most?) major database systems have AutoCommit=>0 as default. This has lead to much confusion and tearing of hairs in the past, as things were commited unintentionally.

Re: Tricks with DBI
by runrig (Abbot) on Oct 19, 2001 at 02:10 UTC
    my($id, $name, $phone); $sth->bind_columns(undef, \$id, \$name, \$phone);
    A shorter way is:
    $sth->bind_columns(\my ($id, $name, $phone));
    And regarding this statement:

    And, for the same reasons, you should use prepare_cached instead of prepare.

    Choosing prepare or prepare_cached really depends. The caching is a slight overhead, so if I can reasonably arrange my SQL so that something is only prepared once (or if its used just once anyway), then I'll just use 'prepare'. Or if you're dynamically creating a SQL statement (lets say a million times) and the number of possible unique combinations is large, then you don't want to use prepare_cached, e.g. you have a couple of 'IN' clauses, and each one might have 1-100 elements, so you end up with something like:

    select stuff from table where field1 in (?,?,?,?) and field2 in (?,?,?,?,?)
    (Note: placeholders are still a good idea in this case)
      And, for the same reasons, you should use prepare_cached instead of prepare.
      Never prepare_cached() ping calls, because you will get true each time instead of actual ping results.
Re: Tricks with DBI
by mpeppler (Vicar) on Nov 13, 2001 at 21:34 UTC
    Great article - however: And, for the same reasons, you should use prepare_cached instead of prepare.

    Don't use prepare_cached() with DBD::Sybase - this would open multiple connections to your server (one for each cached statement handle). If you have a need to have a few requests that you are going to call a *lot* you may want to create stored procedures for them instead.

    Michael

      I was just curious: is this still true? Here we are four years later. Just wondering if any progress has been made on this front...

      thor

      Feel the white light, the light within
      Be your own disciple, fan the sparks of will
      For all of us waiting, your kingdom will come

        I have to admit that I haven't really tested prepare_cached with DBD::Sybase. I suspect that in certain situations this might still open multiple connections, primarily if DBD::Sybase doesn't realize that a particular query is "finished" before running another one.

        If I have the time I'll run a few tests to see what the deal is - and maybe add an override for prepare_cached in DBD::Sybase to avoid any bad surprises.

        Update: After thinking about this a little more, I want to add that with Sybase prepare_cached is really only useful for statements that include placeholders. Any other statement won't really get cached anyway, and doesn't actually get parsed/compiled/optimized until you call DBI's execute().

        Michael

Another Good Reason to use RaiseError
by grantm (Parson) on Jun 19, 2002 at 12:08 UTC

    btrott mentioned that RaiseError is the recommended way to check for errors but that you could alternatively check the error return value for each DBI method call. Here's subtle case where not using RaiseError can bite you:

    while ($sth->fetch) { print join("\t", $id, $name, $phone), "\n"; }

    On the face of it, this will repeatedly call fetch until there is nothing more to fetch. In fact, fetch will return undef either when all rows have been fetched or when there was an error. Here's a couple of cases where fetch might successfully return a number of rows then return undef for an error:

    • you selected a calculated value (eg: total/count) which caused a divide by zero error
    • one of your columns is a long text field that exceeds the default buffer size (LongReadLen) and because you left the LongTruncOK option at default, it throws an error

    In each case, you might see some output and mistakenly believe you'd seen all matching rows. The moral - always set RaiseError and wrap DBI calls using eval

RE: Tricks with DBI
by nutate (Novice) on May 30, 2000 at 22:53 UTC

    Very tasty intro. I have been doing some ugly PostgreSQL/ Perl CGI stuff at work, but I am working on prettying it up and modularizing it. Hopefully, I will be putting some of it up on perlmonks.

Re: Tricks with DBI
by coolmichael (Deacon) on Mar 11, 2001 at 03:58 UTC
    Besides, you can always catch the die in an eval; just make sure that you handle the errors rather than ignoring them.
    I haven't seen anything like this before. Could you post an example? Thanks.

    Michael
    who just became a monk

      This is documented in eval. Here is how it works:
      eval {call_function_that_may_die()}; if ($@) { # This is your error case print "Caught a die saying '$@'\n"; }
        Instead of using die, you can unset $dbh->{RaiseError} locally and use the other C-like error check.

        Ths is usefull when you have a SQL-statement that can fail, but where you don't want to die because of the failure. (I have used it when dropping temporary tables, that perhaps aren't there and other suchlike tings).

        The code looks like this:

        my $dbh=DBI->connect(....{RaiseError=>1}) or die... my $sth=$dbh->prepare(...); { local $dbh->{RaiseError} = 0; $sth->execute; if ($sth->Errstr) { # handle the error } } # $dbh->{RaiseError} is back to normal here
        The neat thing about setting $dbh->{RaiseError} with local is that it is automagically set back to whatever it was when you leave the block, however that is done - even if it is by way of a die, that is caught in an eval somewhere else.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perltutorial [id://7568]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2024-03-19 05:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found