Use DBI to select and return multiple database entries

jayto has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, I am having a database problem. I am trying to make a standard select statement call using DBI but I am having trouble understanding the DBI module and getting my script to work. I tested my select statement in the mysql database and it worked fine so I know the problem must lie in the perl. Here is my code:

use warnings;
use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:database=bing_music_scrape;host=loca
+lhost",
                         "root", "",
                         {'RaiseError' => 1});

my $sth = $dbh->prepare("SELECT domain,path FROM Directory WHERE expir
+es <= CURDATE());
");
  if (!$sth) {
      die "Error:" . $dbh->errstr . "\n";
  }
my $results = $sth->fetchall_hashref([ qw(domain path) ]);
print "done!\n";
[download]

The error occurs while executing this:

my $results = $sth->fetchall_hashref([ qw(domain path) ]);
[download]

And this is the error:

DBD::mysql::st fetchall_hashref failed: statement contains no result [
+err was 4 now 2000000000]
Field 'domain' does not exist (not one of ) at /home/shrikis/workspace
+/load/janitor.pl line 14.
 at /home/shrikis/workspace/load/janitor.pl line 14.
[download]

What am I doing wrong?

Comment on Use DBI to select and return multiple database entries Select or Download Code

Replies are listed 'Best First'.
Re: Use DBI to select and return multiple database entries by runrig (Abbot) on Jul 16, 2012 at 19:05 UTC
What does this print: `my $sql = "select * from Directory"; my $sth = $dbh->prepare($sql); $dbh->execute(); print "$_\n" for @{$sth->{NAME}};` [download] Is 'domain', and 'path' in the list? Is it properly upper/lower cased? Do you need to set FetchHashKeyName on the handle? BTW, with RaiseError set, the whole "if (!$sth) {...}" block you have is useless.	[reply] [d/l]
Re^2: Use DBI to select and return multiple database entries by jayto (Acolyte) on Jul 16, 2012 at 19:44 UTC
If you meant $sth->exceute, it returns: dir_id path domain expires domain and path were properly cased and im not sure what FetchHashKeyName is, but you made it return something from the table, so progress is being made	[reply]
Re: Use DBI to select and return multiple database entries by Neighbour (Friar) on Jul 17, 2012 at 06:40 UTC
You have forgotten to execute your statement. After you `prepare`, you must `$sth->execute` your statement before `$sth->fetch`ing results. As you haven't executed your statement, it contains no results. Alternatively, try fiddling with `$dbh->selectall_arrayref` or `$dbh->selectall_hashref`, which does the preparing, executing and fetching for you in one go.	[reply] [d/l] [select]
Re^2: Use DBI to select and return multiple database entries by jayto (Acolyte) on Jul 17, 2012 at 18:36 UTC
Thanks that's what I ended up doing and it worked.	[reply]
Re: Use DBI to select and return multiple database entries by NetWallah (Canon) on Jul 16, 2012 at 21:25 UTC
You have an extra Close-paren ")" after "CURDATE()" . I hope life isn't a big joke, because I don't get it. -SNL	[reply]