CODEWITHSUNDEEP
mysqlperl
Bram Vanroy
Bram Vanroy

Reputation: 28475

Multi-line MySQL in Perl

I am trying to pass a command to my database (MariaDB/MySQLi) but Perl's DBI is giving me an error, whatever I try. The thing is that I can execute the MySQL fine in phpMyAdmin without fault. The error that I receive is:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near SELECT DISTINCT t.bf FROM wrpec t JOIN wrpec r ON t.path LIKE r.path || '%' at line 2 at retrieve-includes.pl line 20.

DBD::mysql::st fetchrow_array failed: fetch() without execute() at retrieve-includes.pl line 22.

The full test file (fake database obviously) is as below.

#!/usr/bin/perl
use strict;
use warnings;

use DBI;
use Data::Dumper;

my $dsn = "DBI:mysql:database=mydatabase;host=machine";
my $dbh = DBI->connect($dsn, 'root', '') or die "Couldn't connect to database: " . DBI->errstr;;

my $request_includes = "
SET sql_mode = PIPES_AS_CONCAT;
SELECT DISTINCT t.bf
FROM `wrpec` t
JOIN `wrpec` r
ON t.path LIKE r.path || '%'
WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree;";

my $sth = $dbh->prepare($request_includes) or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute();

while (my @row = $sth->fetchrow_array()) {
   print Dumper(\@row);
}

$sth->finish();

I have also tried some ideas I found online: using a clean-up sub that replaces all white-space characters by hard spaces to make sure you're passing a string, and I also tried this notation (I forgot the name) but it doesn't work either (same error).

my $request_includes = <<"REQUEST_INCLUDES";

SET sql_mode = PIPES_AS_CONCAT;
SELECT DISTINCT t.bf
FROM `wrpec` t
JOIN `wrpec` r
ON t.path LIKE r.path || '%'
WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree;
REQUEST_INCLUDES

Upvotes: 0

Views: 1443

Answers (1)

ThisSuitIsBlackNot
ThisSuitIsBlackNot

Reputation: 24063

You're getting a SQL syntax error because by default, DBD::mysql doesn't allow you to execute multiple statements at once. You should run two separate DBI commands instead:

$dbh->do( q{SET sql_mode = 'PIPES_AS_CONCAT'} );

my $sth = $dbh->prepare(q{
    SELECT DISTINCT t.bf
    FROM `wrpec` t
    JOIN `wrpec` r
    ON t.path LIKE r.path || '%'
    WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree
});

I was trying to create a script that works on MySQL as wel as PostgreSQL without changes.

Enable MySQL's ANSI mode and double quote your identifiers:

$dbh->do( q{SET sql_mode = 'ANSI'} ) if $mysql;

my $sth = $dbh->prepare(q{
    SELECT DISTINCT t.bf
    FROM "wrpec" t
    JOIN "wrpec" r
    ON t.path LIKE r.path || '%'
    WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree
});

There are a couple other issues, though:

  • unescaped _ and % in r.path will be interpreted as wildcards
  • 'foo' LIKE 'foo%' is true

To only fetch rows where the t.path is a prefix of r.path, but they're not equal, do:

SELECT DISTINCT t.bf
FROM "wrpec" t
JOIN "wrpec" r
  ON POSITION(t.path IN r.path) = 1
  AND t.path != r.path
WHERE r.bf = 'WRPECsmain\%hd'
  AND t.has_tree

Upvotes: 1

Related Questions