Perl, DBI and MySQL delimiter

Question

I need to be able to issue a "create trigger" over the DBI. I can't seem to get the splitter command to work. Can anyone find a way to make this work?

the 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 on the MySQL command line.

Answer 1

The delimiter command is used by the client program to determine the limits of an SQL statement. This (almost certainly) is not visible by the server itself. Therefore, in Perl + DBI, you just have to omit the delimiters. So the command you have to execute is as follows:

 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 };