How can I call MySQL stored procedures from Perl?

Question

How do I call MySQL stored procedures from Perl? The functionality of stored procedures is fairly new for MySQL, and the MySQL modules for Perl do not seem to have caught up yet.

Answer 1

MySQL stored procedures that create datasets, you need 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 new 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 version of DBD :: mysql, you get the following results:

 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 latest DBD using CPAN.

Answer 2

Here is an example in a section in several result sets in DBD :: mysql docs .

Answer 3

First of all, you should probably connect through the DBI library and then use the bind variables. For example. 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(); 

Remember that I have not tested this, you will have to look for the exact CPAN syntax.

Answer 4

 #!/usr/bin/perl # Stored Proc - Multiple Values In, Multiple Out use strict; use Data::Dumper; use DBI; my $dbh = DBI->connect('DBI:mysql:RTPC;host=db.server.com', 'user','password',{ RaiseError => 1 }) || die "$!\n"; my $sth = $dbh->prepare('CALL storedProcedure(?,?,?,?,@a,@b);'); $sth->bind_param(1, 2); $sth->bind_param(2, 1003); $sth->bind_param(3, 5000); $sth->bind_param(4, 100); $sth->execute(); my $response = $sth->fetchrow_hashref(); print Dumper $response . "\n"; 

It took me a while to figure this out, but I was able to get what I needed with the above. if you need to get some return "strings", I assume that you just ...

 while(my $response = $sth->fetchrow_hashref()) { print Dumper $response . "\n"; } 

Hope this helps.

Answer 5

Hi, similarly above, but using SQL exec. I could not get the CALL command to work. You will need to fill in everything that is in square brackets and remove the square brackets.

  use DBI;
    #START: SET UP DATABASE AND CONNECT
    my $ host = '* [server] * \\ * [database] *';
    my $ database = '* [table] *';
    my $ user = '* [user] *';
    my $ auth = '* [password] *';

    my $ dsn = "dbi: ODBC: Driver = {SQL Server}; Server = $ host; Database = $ database";
    my $ dbh = DBI-> connect ($ dsn, $ user, $ auth, {RaiseError => 1});

    #END: SET UP DATABASE AND CONNECT
    $ sql = "exec * [stored procedure name] * * [param1] *, * [param2] *, * [param3] *;"
    $ sth = $ dbh-> prepare ($ sql);
    $ sth-> execute or die "SQL Error: $ DBI :: errstr \ n";