Running MySQL script in Perl

[smithji]

Hi All,
I'm having a problem executing a MySQL query witihin Perl and was hoping that someone will be able to help me in my moment of stress.

When I run the below script I get this error:

'Can't call method "prepare" on an undefined value at C:\Perl\Jimmy\Testing2.pl line 73'

Line 73 refers to 'e.badge as Badge, \'. The MySQL query runs fine in MySQL, but not sure where I'm going wrong in getting the Perl script to work. Any help would be greatly appreciated.

Jimmy

use DBI ;
use MIME::Lite ;
use Net::SMTP;
 
my @my_data;
my ($msg_body, $my_table, $result) ;
 
sub OpenMySQL {
 
$dbh = DBI->connect('dbi:mysql:host=167.67.61.69;sid=esd','root','root',{RaiseError => 1, AutoCommit => 0});
if ($dbh) {
        # print "\tConnected!\n";    
 
    } else {
         die "Cannot connect to MySQL" ;
        }
}
 
 
sub CloseMySQL {
 
    $dbh->disconnect;
    if ($dbh) {
            # print "\tDisconnected\n";
 
        } else {
            die "Could not disconnect";
            }
}
 
 
sub RunQueries {
 
 $sth = $dbh->prepare("select distinct concat(e.LastName, ' ', e.Name) as Name, \
             e.badge as Badge, \
             curdate() as 'Date' \
            FROM esduser e \
            LEFT JOIN checkesd c on e.badge = c.badge\
            and c.Date > curdate() \
            where c.badge is null \
            order by e.LastName");
 
  $sth->execute() || die "\tCould not execute query\n";
  $result = $dbh->selectall_arrayref($sth);
 
  $sth->finish; 
 
 
 
}
 
 

[KevinADC]

Look in the DBI documentation to properly add error checking to your script. The problem appears to be that $sth is not getting defined. My SQL is too rusty to try and debug your query but it looks odd. What are all the '\' for in the query?

[Icecrack]

as Kevin said:

Why are you putting \ (Backslashes) in

 $sth = $dbh->prepare("select distinct concat(e.LastNa
me, ' ', e.Name) as Name, \
 
             e.badge as Badge, \
             curdate() as 'Date' \
            FROM esduser e \
            LEFT JOIN checkesd c on e.badge = c.badge\
            and c.Date > curdate() \
            where c.badge is null \
            order by e.LastName");
 

Perl does not need this it will read the whole (" ..... ") with out the \ like linux.