-1

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.

Improve this question
9
  • The code says "use strict" at the top. Commented Nov 20, 2009 at 17:49
  • 3
    Please use warnings is probably what @Ivan Nevostruev meant. Commented Nov 20, 2009 at 17:51
  • 1
    If you have not already done so, it is worthwhile to learn how to use the Perl debugger. I'll bet you could step through this code, see what the return value of $sth->fetchrow_hashref() is, and debug this program in two minutes. Commented Nov 20, 2009 at 18:01
  • 1
    Well, it took me a while to figure out how to put the URL in my answer, but try specifying lower case column names or just use selectcol_arrayref: search.cpan.org/perldoc/… Commented Nov 20, 2009 at 18:03
  • 2
    @tinker: In your comment right below the accepted answer you say @Ivan: nope, adding chomp does not help. – tinker 41 mins ago Do you think this is funny? Commented Nov 20, 2009 at 18:43

6 Answers 6

Reset to default
4

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.

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

3 Comments

printing $result before while loop, print : $VAR1=undef;
@Ether +1 for summarizing useful debugging ideas. @tinker I am giving up on you: I do not think the code you are showing us is actually the code that exhibits the problem. Or, your input does not match what's in the database. Either way, we do not have real information that can help us give real answers. Voting to close your question as a not a real question.
@tinker: you're dumping $result before it has either been declared or assigned? No wonder it's empty. Try dumping it just after you assign it in the while loop, perhaps?
3

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.

Improve this answer

6 Comments

It isn't 100% but that looks likely.
@Ivan: nope, adding chomp does not help.
Do you mean after the while loop? $result has no value/defination before while loop.
@tinker: yes after loop... it will show you values of variables, which can be usefull to undestand what's happening. But the code looks good so far. 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.
"I think it's not searching anything because it just can't find. Addresses usually have spaces within. " of Ivan answer helped.
|
2

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

my $name_reqd = $result[0];

This is because you are using fetchrow_array().

Improve this answer

1 Comment

+1 The OP first had fetchrow_array() in his post before he edited it. So the downvote on this post was not deserved.
2

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";
Improve this answer

7 Comments

Considering you told me not to post non-answers, what on earth do you mean by this? The syntax of tinker's question is correct, you are simply giving a different way to achieve the same result, and not even answering the question.
Going by the documentation, selectcol_arrayref combines prepare and execute. I dont think that is the problem I am facing. My prepare and execute statements work. the issue is either with the '$result' part or the while loop.
@Kinopiko: A question such as Is the name of the table really table? is a non-answer.
I think adding a print Dumper $result; in the body of the while loop will reveal the answer.
@Kinopiko Because you keep posting those non-answer answers which should rightly be comments, you keep taking away the opportunity to earn additional badges for others and creating pressure to post quickly. The point is selectcol_arrayref does not depend the database driver returning keys in lower case, but I did not get a chance to write out my post because your non-answer answer popped up as I was typing my answer. So, post-edit-post-edit was how I responded to it.
|
1

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 ?

Improve this answer

1 Comment

does not print any thing (just a new line).
0

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.

Improve this answer

2 Comments

Don't post non-answer answers.
This seems to me to be a valid answer. It's not the correct answer but I don't see how it is invalid or not an answer. It's perfectly possible that he'd forgotten the dollar sign.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.