I have a need to be able to issue "create trigger" over DBI. I can't seem to get the delimiter command working. Can anyone find a way to make this work?
Code:
use strict;
use DBI;
my $dbargs = {mysql_auto_reconnect => 1,
AutoCommit => 0,
RaiseError => 1,
ShowErrorStatement => 1};
my $dsn = "DBI:mysql:database=xdisp;host=cycldev06";
my $dbh = DBI->connect( $dsn, 'sqluser', '', $dbargs);
my $sql = qq{
DELIMITER //
CREATE TRIGGER `hardware_last_status` BEFORE UPDATE
ON `hardware` FOR EACH ROW BEGIN
IF NEW.status != OLD.status AND NEW.last_status = OLD.last_status THEN
SET NEW.last_status = OLD.status;
END IF;
END
//
};
$dbh->do($sql);
Results:
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //
CREATE TRIGGER `hardware_last_status` BEFORE UPDATE
' at line 1 at test.pl line 24.
and that SQL works fine at the MySQL command line.
DBI is a database-independent interface for the Perl programming language. DBD::mysql is the driver for connecting to MySQL database servers with DBI. DBI is the basic abstraction layer for working with databases in Perl.
The DBI is the standard database interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used.
use DBI; my $create_procedure = qq{ CREATE PROCEDURE simpleproc () BEGIN SELECT 'helloworld' As Messgae; END }; $dbh->do($create_procedure); $sql = "CALL simpleproc()"; my $sth = $dbh->prepare($sql); $sth->execute();
The delimiter
command is used by the client program to determine the limits of the SQL statement. It is (almost certainly) not seen by the server itself. Therefore, in Perl + DBI, you should simply omit the delimiters. Thus, the command you should execute is:
my $sql = qq{
CREATE TRIGGER `hardware_last_status` BEFORE UPDATE
ON `hardware` FOR EACH ROW BEGIN
IF NEW.status != OLD.status AND NEW.last_status = OLD.last_status THEN
SET NEW.last_status = OLD.status;
END IF;
END
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With