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

Dear Monks,

How can I get the following bit of code to work? I have pointed to where I think the problem is. This bit of code is within a while loop and this may alter how we can use the $_. 

my $Select_areas = "SELECT DISTINCT Instance_name FROM Geo_definitions
+_normalised where Geo_category_class LIKE ".$Special_data_retrieval."
+ ORDER BY Instance_name \;";
      
my $sth_Geo = $dbh->prepare($Select_areas) or die "Couldn't prepare qu
+ery: ".$dbh->errstr;
      
$sth_Geo->execute() or die "Couldn't execute  query: ".$sth_Geo->errst
+r;
      
my @Geo_areas = $sth_Geo->fetchrow_array;   ## I think that the proble
+m is here.

[download]

Replies are listed 'Best First'.
Re: my @Array = $sth->fetchrow_array;
by jbrugger (Parson) on Jan 10, 2006 at 12:14 UTC
    What is the error message you get? what does the while loop do? Why do you think there is the problem????

    "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.
[reply]
      And in addition to this: why did he post while (my @row = $sth->fetchrow_array), in which he asks about "a way of improving the efficiency of this bit of code" where the code is basically the same as here, if it "doesn't work" anyway? Mistery!!
[reply]
      I don't get any error messages. It just does not print the @Geo_areas after it should be full with the content of the select statement. But I know that this bit of code is passed in the process because I have got it to print something else before the code that is shown here is executed (or not).
[reply]
        It just does not print the @Geo_areas after it should be full with the content of the select statement.

        <not_entirely_tongue_in_cheek> Of course not, as there is no print statement in the code you showed us :p </not_entirely_tongue_in_cheek>

        My guess is that your $Special_data_retrieval variable isn't correctly formed, and so your query is returning zero rows. Try adding a print "$Select_areas\n"; before the query is executed, and then test it manually.

        Also, be aware that selectrow_array fetchrow_array will only give you a single row of data. So if you are expecting multiple rows then it would generally be contained within a while loop. Yes, I know you mentioned a while loop in your original post, but you indicated that the whole code snippet is within a while loop. And that isn't what I mean. What you probably need is something like this: 

        while (my @row = $sth_Geo->fetchrow_array) {
    print "@row\n"; # Or whatever
}

        [download]

        If you want to grab the whole lot in one go, then you probably want to use selectall_arrayref fetchall_arrayref

        Cheers,
        Darren :)

[reply]
[d/l]
[select]
        To add to McDarren's excellent reply, in case of doubt why don't you just 
        print 0+@Geo_areas;

        [download]
        to verify it's not empty?
[reply]
[d/l]

Back to Seekers of Perl Wisdom