How do I call MySQL stored procedures from Perl?

Question

How do I call MySQL stored procedures from Perl? Stored procedure functionality is fairly new to MySQL and the MySQL modules for Perl don't seem to have caught up yet.

Answer 1

First of all you should be probably connect through the DBI library and then you should use bind variables. E.g. something like:

#!/usr/bin/perl
#
use strict;
use DBI qw(:sql_types);

my $dbh = DBI->connect(
            $ConnStr,
            $User,
            $Password,
            {RaiseError => 1, AutoCommit => 0}
          ) || die "Database connection not made: $DBI::errstr";
my $sql = qq {CALL someProcedure(1);}    } 

my $sth = $dbh->prepare($sql);
eval {
  $sth->bind_param(1, $argument, SQL_VARCHAR);
};
if ($@) {
 warn "Database error: $DBI::errstr\n";
 $dbh->rollback(); #just die if rollback is failing
}

$dbh->commit();

Mind you i haven't tested this, you'll have to lookup the exact syntax on CPAN.

Answer 2

There's an example in the section on Multiple result sets in the DBD::mysql docs.

Answer 3

MySQL stored procedures that produce datasets need you to use Perl DBD::mysql 4.001 or later. (http://www.perlmonks.org/?node_id=609098)

Below is a test program that will work in the newer version:

mysql> delimiter //
mysql> create procedure Foo(x int)
  -> begin
  ->   select x*2;
  -> end
  -> //

perl -e 'use DBI; DBI->connect("dbi:mysql:database=bonk", "root", "")->prepare("call Foo(?)")->execute(21)'

But if you have too old a version of DBD::mysql, you get results like this:

DBD::mysql::st execute failed: PROCEDURE bonk.Foo can't return a result set in the given context at -e line 1.

You can install the newest DBD using CPAN.