Perl failed to call a stored procedure which has been tested on MySQL Query Browser
Posted by:
Leah Chou
Date: April 07, 2006 02:52PM
Hi:
I am using MySQl 5.0 on windows.
I wrote a stored procedure and run it successfully on MySQL Query Browser. However if I called it from Perl, then it will return error message:
DBD:mysql::st execute failed: You have an error in you SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getClientsStatusByDate' at line 1 .....
The following is the stored proceduer:
##################################################################
ELIMITER $$
DROP PROCEDURE IF EXISTS `tmpdb`.`getClientsStatusByDate` $$
CREATE PROCEDURE getClientsStatusByDate(status INT, starttime DATETIME, endtime DATETIME)
BEGIN
select time as "DATE", TID AS "TASK ID", STR1 as "NODE"
from eventInfo
where
time >= starttime and
time <= endtime and
eventId = status
order by tid;
END $$
DELIMITER ;
########################################################################
my called from Perl is like
my $dsn = "dbi:mysql:tmpdb:ocalhost:3307";
my $username = "root";
my $password = "xxxx
my $dbh = DBI->connect($dsn, $username, $password,
{RaiseError => 1, PrintError => 0, AutoCommit => 1});
$call_function = 'getClientsStatusByTaskID(?,?)';
$sth = $dbh->prepare($call_function);
$sth->execute($event, $tid);
########################################################################
Where do I do it wrong? How can a stored procedure which has been tested on MySQL Query Brown, and failed in Perl script?
Please help !!!!!!!!!!!!!