More DBI Examples

1. Other Fetchrow() forms

DBI has a whole family of fetchrow() functions:

fetchrow_arrayref() (aka fetch())

$sth = $dbh->prepare('SELECT catalog,name FROM ProductList') 
       || die $dbh->errstr;
$sth->execute() || die $sth->errstr;
while (my $r = $sth->fetchrow_arrayref) {
  my ($catalog,$name) = @$r;
  print "catno => $catalog, name => $name\n";
}
$sth->finish;

fetchrow_hashref()

$sth = $dbh->prepare('SELECT catalog,name FROM ProductList') 
       || die $dbh->errstr;
$sth->execute() || die $sth->errstr;
while (my $r = $sth->fetchrow_hashref) {
   print "catno => $r->{catalog}, name => $r->{name}\n";
}
$sth->finish;

2. Input Placeholders

Use "?" as a variable placeholder in SELECT or UPDATE statements. Avoids having to escape quotes, etc.

$sth = $dbh->prepare('SELECT name,description
                      FROM ProductList
                      WHERE catalog=?') || die $dbh->errstr;
$sth->execute('64-119293') || die $sth->errstr;
while (my $r = $sth->fetchrow_hashref) {
   print "name => $r->{name}, description => $r->{description}\n";
}
$sth->finish;

3. Binding Output Variables

$sth = $dbh->prepare('SELECT name,description
                      FROM ProductList
                      WHERE catalog=?') || die $dbh->errstr;
my ($name,$description);
$sth->bind_columns(undef,\($name,$description));
$sth->execute('64-119293') || die $sth->errstr;
while ($sth->fetch) {
   print "name => $name, description => $description\n";
}
$sth->finish;

Contents

Lincoln D. Stein, lstein@cshl.org
Cold Spring Harbor Laboratory

Last modified: Sun Apr 25 14:33:04 EDT 1999