How to implement sql LIKE qualifier with placeholdes for an array?

Question

I am trying to implement sql LIKE qualifier with placeholders for a set of values. I know how to do it for one particular value as follows:

use strict;
use DBI;
my $dbh = DBI->connect("dbi:Sybase:server=$Srv;database=$Db", "$user", "$passwd") or die "could not connect to database";
my $query = "Select key, first_name from names where last_name like ? ";
my $sth = $dbh->prepare( $query ) or die "could not prepare statement\n", $dbh->errstr;
$sth->bind_param(1, "Mar%") or die "Could not bind_param", $sth->errstr ;
$sth->execute or die "Could not execute $query", $sth->errstr;
while (my @result = $sth->fetchrow_array){
print "@result\n";
}

When I try to make changes so as to implement the above code for a set of values present in array @l_names, I don't get any result. I am doing it as follows:

use strict;
use DBI;
open (FH, "/home/usr/file") or die $!;
my @l_names= <FH>; 
close(FH);

my $dbh = DBI->connect("dbi:Sybase:server=$Srv;database=$Db", "$user", "$passwd") or die "could not connect to database";
my $query = "Select key, first_name from names where last_name like ? ";
my $sth = $dbh->prepare( $query ) or die "could not prepare statement\n", $dbh->errstr;
foreach my $l_name (@l_names){
    $sth->bind_param(1, "$l_name%") or die "Could not bind_param", $sth->errstr ;
    $sth->execute or die "Could not execute $query", $sth->errstr;
}
while (my @result = $sth->fetchrow_array){
    print "@result\n";
}

Any suggestions on what is going wrong and how to correct it? Thanks!

Community · Accepted Answer · 2017-05-23 12:19:06Z

4

You didn't remove EOL marker from lines you'lve read from file. Here is fix for it:

my @l_names = <FH>; 
chomp(@l_names);

Update:

You can also look at "How can I select multiple rows using parameters from an array in Perl’s DBI module?" question

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Dec 7, 2009 at 17:20

Ivan Nevostruev

28.9k8 gold badges69 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

runrig Over a year ago

or in one line: chomp(my @l_names = <FH>);

Collectives™ on Stack Overflow

How to implement sql LIKE qualifier with placeholdes for an array?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related