Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Perl, DBI and the MySQL delimiter

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.

like image 234
SecondGear Avatar asked Sep 14 '12 01:09

SecondGear


People also ask

What is Perl DBI in MySQL?

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.

What is DBI 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.

How do I run a stored procedure in Perl?

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();


1 Answers

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
};
like image 68
Jonathan Leffler Avatar answered Oct 05 '22 14:10

Jonathan Leffler