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.

In reply to Using DBI to extract from 2 databases by whittick

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.