How can I select multiple rows using parameters from an array in Perl's DBI module?

Question

I am required to pull out rows corresponding to column name. The rows being pulled out correspond to address in array @values. Following is my code:

use strict;
use DBI;

open (FH, "/user/address") or die $!;
my@values=<FH>;
close(FH);
my @names;

my $query = "Select name from table where address = ?";
my $sth = $dbh->prepare( $query ) or die "could not prepare statement\n", $dbh->errstr;
foreach my $value(@values){ #@values contain list of address
        $sth->execute($value) or die "could not execute statement $query\n", $sth->errstr;
        while ($result = $sth->fetchrow_hashref()){
               my $name_reqd = $result->{name};
               print "Name Req: $name_reqd\n"; #not printing anything 
               push (@names, $name_reqd);
        }
}
print "@names\n"; #not printing anything

But when I print @names, I don't get any output, I am unsure as to what is going wrong.

Answer 1

Is your table really called "table"? Maybe it should be $table, with $table set to the actual name of the table.

If you can, for example SQLite or MySQL have this, try running your query from the database command line.

Answer 2

untested, but shouldn't you access your fields like this.

my $name_reqd = $result[0];

This is because you are using fetchrow_array().

Answer 3

You should ensure that the database driver is returning column names in lower case. Also, use selectcol_arrayref for this type of query:

use strict;
use warnings;

use DBI;
use File::Slurp;

my @values = read_file '/user/address';
chomp @values;

my $dbh = DBI->connect(
    # appropriate parameters
);

my $sth = $dbh->prepare(
    'SELECT name FROM table WHERE address = ?'
) or die sprintf 'Cannot prepare: %s', $dbh->errstr;

my @names;

for my $value ( @values ) {
    my $names = $dbh->selectcol_arrayref(
        $sth,  {},  $value
    ) or die sprintf 'Cannot select col: %s',  $dbh->errstr;

    push @names,  @$names;
    print "'$_'\n" for @$names;
);

print "@names\n";

Answer 4

The problem with this code is file reading. Code my @values=<FH>; reads all lines with new-line (\n) symbol at the end. It should be manually removed in this case. You can do it using chomp function:

open (FH, "/user/address") or die $!;
my @values = <FH>;
chomp(@values);
close(FH);

Update: I think it's not searching anything because it just can't find. Addresses usually have spaces within. Query Select name from table where address = ? will find only exact equal addresses (letter case is the only thing ignored). For example " a" is not equal to "a" in sql.

Answer 5

DBI performs case conversion on column names, so it may be returing the result in the key "NAME" instead of "name".

What do you see if you print keys %$result after calling $sth->fetchrow_hashref ?

Answer 6

I see a few things:

• you aren't declaring @names before you use it, which means it is being automatically declared at the scope of its first use: inside the while loop. Moreover a new copy is destroyed and created on every iteration of the loop, and then a new (empty) one is created when you call print. Adding use warnings; would catch this.
• (as mobrule and others have said) you may not be accessing the data correctly out of $result. Try adding use Data::Dumper; print Dumper($result); in the top line of the while loop to see what data you have read in.
• I'm not sure what format you are using in /usr/address, but if it has more than one line, you're only reading the first line from that file. You can read in the entire file in one go by localizing $/ first (see perldoc perlvar). Moreover (as Ivan said), this string will still have a newline at the end: use chomp to strip it (perhaps after splitting into lines, if you slurped more than one). See perldoc -f chomp and perldoc -f split.

Between these points, you should have enough debugging data being printed that you should easily see where you went wrong.