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

I am trying to get a table's name from the information_schema database to use in the main database (called probedb) however the code when run returns the error 'fetchrow_array failed: fetch() without execute()' I have tried to rewrite the code a couple of times with no success, I have included the relevent parts below:

#!c:\perl\bin\perl.exe

use lib "C:\\Perl";
use DBI;
use DBD::mysql;

# # # # # # # # # # # #
#Variable declarations#
# # # # # # # # # # # #

my @tblname;

###########
#DB1 START#
###########
my ($user, $pass) = ("root", "pass"); #credentials for SQL database
my $table_data = q/DBI:mysql:database=information_schema;host=localhos
+t;port=13308/;
#Connect to the data source and get a handle for that connection
my $dbtbl = DBI->connect($table_data, $user, $pass) || die "Can't conn
+ect to $data_source: $DBI::errstr";

my $tblname = "SELECT table_name FROM `TABLES` where table_name like '
+event\_%'";
my $sthtbl=$dbtbl->prepare($tblname);
$sthtbl->execute();
while ( @row = $sthtbl->fetchrow_array ) {
    my $table_name = $row[0];
    push(@tblname, $table_name); 
}
$sthtbl->finish;
$dbtbl->disconnect;

###########
#DB2 START#
###########
my $data_source = q/DBI:mysql:database=probedb;host=localhost;port=133
+08/;
#Connect to the data source and get a handle for that connection
my $dbh = DBI->connect($data_source, $user, $pass) || die "Can't conne
+ct to $data_source: $DBI::errstr";
#open handle for error/log file

foreach (@tblname){
    $table_name = $_;
    my $NCquery= "SELECT id, text FROM ‘$table_name’ WHERE alarmcode L
+IKE '%Trap%Calltouch%'";
    my $sth=$dbh->prepare($NCquery);
    $sth->execute();
    while ( @row = $sth->fetchrow_array ) { #error here#
        $NCid = $row[0];
        $NCsevno = $row[1];
        if  ($NCsevno =~ /alarmState=(\d)/i){
            $newstat = 1 if $1 == 2;
            $newstat = 5 if $1 == 1;
            $newstat = 3 if $1 == 3; #ack?#
        }
        my $updateNC="UPDATE '$table_name' SET severity = '$newstat' W
+HERE id = '$NCid' AND modtime > '$ntime'";
        my $sth=$dbh->prepare($updateNC);
        $sth->execute();
    }
    print "$table_name\n";
}

my $query="SELECT name FROM managedobject WHERE displayname like '%TSE
+PAL%-CT%' AND type LIKE '%Windows%'";
my $sth=$dbh->prepare($query);
$sth->execute();
while ( @row = $sth->fetchrow_array ) {
    $id = $row[0];
    #update all matching alarms to newest severity
    &update();
}
$sth->finish;
$dbh->disconnect;

sub update {
    my $alquery="SELECT source, severity, ttime, text FROM '$table_nam
+e' WHERE source = '$id' AND alarmcode LIKE '%Trap%Calltouch%'";
    my $sth=$dbh->prepare($alquery);
    $sth->execute();
    while ( @row = $sth->fetchrow_array ) { #error here#
        $NCsource = $row[0];
        $NCsev = $row[1];
        $NCmodtime = $row[2];
        $NCmmessage = $row[3];
        if  ($NCmsg =~ /alarmDescription=([^\s+])/i){
            $NCmmessage = $1;
        }
        my $updateNC="UPDATE '$table_name' SET severity = '$NCsev' WHE
+RE source = '$NCsource' AND ttime < '$NCmodtime' and text like '%$NCm
+message%' and alarmcode LIKE '%Trap%Calltouch%'";
        my $sth=$dbh->prepare($updateNC);
        $sth->execute();
    }
    my $query="SELECT severity FROM alert WHERE source = '$id' ORDER B
+Y severity ASC LIMIT 1";
    my $sth=$dbh->prepare($query);
    $sth->execute();
    while ( @row = $sth->fetchrow_array ) {
        $sev = $row[0];
    }
    my $updatemo="UPDATE managedobject SET status = '$sev' WHERE name 
+= '$id'";
    my $sth=$dbh->prepare($updatemo);
    $sth->execute();
}

[download]

Sorry the code is a bit of a mess, however I just can't see how it's not seeing the execute before the fetch! Any help would be much appreciated.

Thanks, Ben.

Replies are listed 'Best First'.
Re: Using DBI to extract from 2 databases
by marto (Cardinal) on Apr 24, 2013 at 12:15 UTC

    Your code is a little messy. Firstly add:

    use strict;
use warnings;

    [download]

    And work through those problems. You have variables declared out of scope then try to use them later, for example $table_name.This isn't going to work. Also, please consider the advice I gave last time you asked a databae question.

    Update: On second look this is a little more messy that I thought. The advice about strict and warnings stands, see line 55, the sql statement uses a variable $ntime, which is never declared. Check for errors when doing anything with DBI, either with the RaiseError constructor, or manually each time.

[reply]
[d/l]
[select]

      Your code is a little messy
      is an understatement similar in magnitude to
      the Titanic has hit a little iceberg and has a tiny hole in it
      :-)

      If any of my proposed solutions have minor errors, it's because I don't waste my genius on trivial matters. :-P
[reply]

        It's hardly this disaster you liken it to. With the advice given and a few minutes the existing code could be made less messy. Most DB issues would be caught by enabling RaiseError, advice has been given (in a previous thread also) on place holders, redundant variables would identified and easily resolved.

        With this out of the way OP can concentrate on the bigger issues which have been highlighted by resolving these trivial problems. Hopefully the exercise of cleaning this up would help with learning why such things are important, and how to think about approaching problems like this in future.

[reply]
[d/l]
Re: Using DBI to extract from 2 databases
by space_monk (Chaplain) on Apr 24, 2013 at 13:35 UTC

    As others have suggested, look at binding your parameters - whilst you cannot bind table names, some of your code would look better if you bound the other parameters - see below.

    What happens to $newstat if the $NCsevno value is not 1,2,3? Your code does not protect itself against errors sufficiently

    You have funny left/right single quotes around $tableName in your original example, check you're using the proper boring single quote marks (I don't think you need quotes anyway - remove them)

    You only appear to use the last $table_name to call the update method with- why?

    A partially improved version of your code is below:

    Read more... (5 kB)
    If any of my proposed solutions have minor errors, it's because I don't waste my genius on trivial matters. :-P
[reply]
[d/l]

      Firstly thank you for all the detailed responses. I have bitten the bullet and rewritten the script in a much neater, strict complying form. I was still getting the initial error message of fetch without an execute, however it looks as if the problem was down to the single quotes wrapping the database name in the query so thank you space_monk for suggesting I remove them - the script is now running flawlessly!

[reply]
Re: Using DBI to extract from 2 databases
by scorpio17 (Canon) on Apr 24, 2013 at 13:18 UTC

    You don't need to explicitly "use DBD::mysql" - DBI will handle that for you.

    To help with debugging, wrap all your "prepares" and "executes" with eval, like this: 

      eval {
    $sth = $self->dbh->prepare( $sql );
  };
  if ($@) {
    die "Database error (prepare): ", $self->dbh->errstr;
  }

  eval {
    $sth->execute();
  };
  if ($@) {
    die "Database error (execute): ", $self->dbh->errstr;
  }

    [download]
[reply]
[d/l]
      What's wrong with DBI's RaiseError?
[reply]

      I'm assuming that RaiseError is on. That causes DBI to "die" if any method results in an error. You use the eval block to trap the error and handle it however you wish. For example, I've found it useful to print out the sql statement if a prepare fails, or the argument list if an execute fails, etc.

      The connect statement should look something like this: 

      $dbh = DBI->connect($dsn, $user, $password, {RaiseError => 1, });

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom