http://qs321.pair.com?node_id=201039

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

Fellow monks, i am in dire need of your assistance. I have a mysql database which has names and a default value for ip addresses. I have other code , a cgi app that uses a form to add/search the database and also view all current items in the database which still seems to be working. i am trying to write a script that will take the names, look them up and populate the database with the correct ip address. When i run my script i get a really wierd error.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at lookup.pl line 34.
If anyone can offer some advice as too why my code is failing, i would greatly appreciate it. Thank you
Ray
#!/usr/bin/perl -w # lookup.pl - This will take the name from the database and lookup the + ip address and populate it in the database. # Ray Espinoza # version 1.0 9/26/02 ########################################################## use strict; use DBI; # Create database handle ######################## my $dbh = &connect; get_name_and_lookup ($dbh); # Close connection to the database and end html ############################################### $dbh->disconnect(); # Subs ############################################################### +############ sub get_name_and_lookup { my $dbh = shift; my ($sth, $stmt, $hostname, $id, $addr, $ipaddr); $stmt = qq {SELECT * from nt_machines ORDER BY name}; $sth = $dbh->prepare ($stmt); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { $row->{name} = $hostname; $row->{id} = $id; $addr = (gethostbyname($hostname))[4]; if ($addr) { $ipaddr = join(".", unpack("C4", $addr)); update_item($ipaddr,$id) } $sth->finish(); } } ###################################################################### +##### sub update_item { my ($dbh, $ipaddr, $id) = @_; $ipaddr =~ s/^\s+//; $ipaddr =~ s/\s+$//; $dbh->do (qq{ UPDATE nt_machines SET ipaddr = ? WHERE id = ? } +, undef, $ipaddr, $id) or warn "Can't update database: $!\n"; } ###################################################################### +##### sub connect { use DBI; my ($dbh, $sth, $count); $dbh= DBI->connect("DBI:mysql:host=localhost;database=testsites","qaus +er","blah",{PrintError => 0, RaiseError => 1}); return $dbh; } ###################### # Ray Espinoza 9.26.02# ######################

Replies are listed 'Best First'.
Re: perl DBI help
by swiftone (Curate) on Sep 26, 2002 at 20:50 UTC
    I believe your problem may be here:
    sub get_name_and_lookup { my $dbh = shift; my ($sth, $stmt, $hostname, $id, $addr, $ipaddr); $stmt = qq {SELECT * from nt_machines ORDER BY name}; $sth = $dbh->prepare ($stmt); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { $row->{name} = $hostname; $row->{id} = $id; $addr = (gethostbyname($hostname))[4]; if ($addr) { $ipaddr = join(".", unpack("C4", $addr)); update_item($ipaddr,$id) } $sth->finish(); } }
    Note the $sth->finish() call inside the while loop.. That kills (so to speak) the $sth object. Thus, you execute(), call fetchrow_() once, then kill the object, then call fetchrow_ again, and it complains.

    Only call finish() if you want to discard the remaining results of an execute(). Since you while() through all the results, you have no need of the call at all in this code.

    Hope that helps

Re: perl DBI help
by George_Sherston (Vicar) on Sep 26, 2002 at 20:49 UTC
    That error message is telling you that you can't fetch something from $sth because it didn't execute properly. What that means is that the trouble actually started earlier, and probably surfaced somewhere in either of these lines:
    $sth = $dbh->prepare ($stmt); $sth->execute();
    On the face of it I can't see anything wrong with your code (though other monks may be able to). My guess is something like a typo in $stmt. The easy way to find out is to edit those lines to
    $sth = $dbh->prepare ($stmt) or die $dbh->errstr; $sth->execute() or die $dbh->errstr;
    That may well throw some light on exactly where it's breaking down, by giving you some informative error messages. (NB if it dies on the first of these and doesn't give you an informative error message, that probably means your DB handle didn't get properly initialised in the first place - password prob?)

    § George Sherston
      Actually, the RaiseError => 1 in his connect routine has this exact effect. (You may want to then wrap your calls in eval, but the die-ing has been accomplished.)
Re: perl DBI help
by thunders (Priest) on Sep 26, 2002 at 20:50 UTC
    one thing i saw immediately is that you have an unecessary use DBI; in the connect sub, that may cause your $dbh to go out of scope, i'm not sure. Problem with the script as it is now, it that you are not error checking So you are not raising and error until you actully try to fetch data.

    Try something like this on each DB call to pinpoint the problem

    $dbh= DBI->connect("DBI:mysql:host=localhost;database=testsites","qaus er","blah",{PrintError => 0, RaiseError => 1}) or die $DBI::errstr; -and- $sth = $dbh->prepare ($stmt) or die $dbh->errstr;
    Update: swiftone has a good eye. I believe the finish call is your real problem. but ALWAYS check for failure on db calls.
      A hundred use DBI; shouldn't affect anything -- that's the beauty of use.
        I trust that you are correct on that. I still recommend deleting the redundant one.
Re: perl DBI help
by Daruma (Curate) on Sep 27, 2002 at 17:12 UTC
    Greetings!!

    I have to agree with swiftone on this... The call to $sth->finish is troublesome. It basically will stop your fetch immediately. According to the perldoc's, finish is rarely used, and then only when you expect the statement to be destroyed or re-executed. By including it in your loop, you are trying to fetch using a destroyed (finished) statement.

    I tested your code (against an Oracle DB...) and it works when I remove the $sth->finish call entirely.

    -Daruma