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.

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.

Besides, you can always catch the die in an eval; just make sure that you handle the errors rather than ignoring them.

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";
}

Re: Re (tilly) 2: Tricks with DBI
by htoug on Aug 15, 2001 at 10:44

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.

my $dbh = DBI->connect("dbi:mysql:foo", "bar", "baz",
      { AutoCommit => 1, RaiseError => 1})
        or die die "Can't connect: ", $DBI::errstr;

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.

my($id, $name, $phone);
$sth->bind_columns(undef, \$id, \$name, \$phone);

$sth->bind_columns(\my ($id, $name, $phone));

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 (?,?,?,?,?)

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.

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

package Database;

use strict;
use warnings;
# with or without use Apache::DBI; the result is the same
# i also load Apache::DBI in the startup.pl file...
use DBI;
use DBD::mysql;

our (@EXPORT, $dbh);

use base qw/Exporter/;
@EXPORT = qw($dbh);

#BEGIN {
print "Content-Type: text/html\n\n";
print "\nopening db connection!\n";
$dbh = DBI->connect('DBI:mysql:dbname', 'user', 'pwd');
#}

END {
   $dbh->disconnect;
   print "\nshutting down db-conn!\n"; #should not happen...
}

1;

#!/usr/bin/perl -w

use strict;
use warnings;
use CGI;

use Database;

print "Content-type: text/html\n\n";

my $q = new CGI;
my %params = $q->Vars;

my $userid = delete $params{USER};

my $user = $dbh->selectrow_array("select NAME from USER where ID=?", u
+ndef, $userid);

print "name of the user: $user!";