<p>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.</p>

[{'answer_id': 64283, 'author': 'aggergren', 'author_id': 7742, 'author_profile': 'https://Stackoverflow.com/users/7742', 'pm_score': 2, 'selected': False, 'text': '<p>First of all you should be probably connect through the DBI library and then you should use bind variables. E.g. something like:</p>\n\n<pre><code>#!/usr/bin/perl\n#\nuse strict;\nuse DBI qw(:sql_types);\n\nmy $dbh = DBI-&gt;connect(\n            $ConnStr,\n            $User,\n            $Password,\n            {RaiseError =&gt; 1, AutoCommit =&gt; 0}\n          ) || die "Database connection not made: $DBI::errstr";\nmy $sql = qq {CALL someProcedure(1);}    } \n\nmy $sth = $dbh-&gt;prepare($sql);\neval {\n  $sth-&gt;bind_param(1, $argument, SQL_VARCHAR);\n};\nif ($@) {\n warn "Database error: $DBI::errstr\\n";\n $dbh-&gt;rollback(); #just die if rollback is failing\n}\n\n$dbh-&gt;commit();\n</code></pre>\n\n<p>Mind you i haven\'t tested this, you\'ll have to lookup the exact syntax on CPAN.</p>\n'}, {'answer_id': 64424, 'author': 'Dave Cross', 'author_id': 7231, 'author_profile': 'https://Stackoverflow.com/users/7231', 'pm_score': 3, 'selected': False, 'text': '<p>There\'s an example in the <a href="http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm#MULTIPLE_RESULT_SETS" rel="nofollow noreferrer">section on Multiple result sets in the DBD::mysql docs</a>.</p>\n'}, {'answer_id': 64718, 'author': 'apenwarr', 'author_id': 42219, 'author_profile': 'https://Stackoverflow.com/users/42219', 'pm_score': 3, 'selected': False, 'text': '<p>MySQL stored procedures that produce datasets need you to use Perl DBD::mysql 4.001 or later. (<a href="http://www.perlmonks.org/?node_id=609098" rel="nofollow noreferrer">http://www.perlmonks.org/?node_id=609098</a>)</p>\n\n<p>Below is a test program that will work in the newer version:</p>\n\n<pre><code>mysql&gt; delimiter //\nmysql&gt; create procedure Foo(x int)\n  -&gt; begin\n  -&gt;   select x*2;\n  -&gt; end\n  -&gt; //\n\nperl -e \'use DBI; DBI-&gt;connect("dbi:mysql:database=bonk", "root", "")-&gt;prepare("call Foo(?)")-&gt;execute(21)\'\n</code></pre>\n\n<p>But if you have too old a version of DBD::mysql, you get results like this:</p>\n\n<pre><code>DBD::mysql::st execute failed: PROCEDURE bonk.Foo can\'t return a result set in the given context at -e line 1.\n</code></pre>\n\n<p>You can install the newest DBD using CPAN.</p>\n'}, {'answer_id': 10406264, 'author': 'Nexion', 'author_id': 1368915, 'author_profile': 'https://Stackoverflow.com/users/1368915', 'pm_score': 2, 'selected': False, 'text': '<pre><code>#!/usr/bin/perl\n# Stored Proc - Multiple Values In, Multiple Out\nuse strict;\nuse Data::Dumper;\nuse DBI;\nmy $dbh = DBI-&gt;connect(\'DBI:mysql:RTPC;host=db.server.com\',\n    \'user\',\'password\',{ RaiseError =&gt; 1 }) || die "$!\\n";\nmy $sth = $dbh-&gt;prepare(\'CALL storedProcedure(?,?,?,?,@a,@b);\');\n$sth-&gt;bind_param(1, 2);\n$sth-&gt;bind_param(2, 1003);\n$sth-&gt;bind_param(3, 5000);\n$sth-&gt;bind_param(4, 100);\n$sth-&gt;execute();\nmy $response = $sth-&gt;fetchrow_hashref();\nprint Dumper $response . "\\n";\n</code></pre>\n\n<p>It took me a while to figure it out, but I was able to get what I needed with the above. if you need to get multiple return "lines" I\'m guessing you just...</p>\n\n<pre><code>while(my $response = $sth-&gt;fetchrow_hashref()) {\n    print Dumper $response . "\\n";\n}\n</code></pre>\n\n<p>I hope it helps.</p>\n'}, {'answer_id': 29237364, 'author': 'ORS_IT', 'author_id': 4708291, 'author_profile': 'https://Stackoverflow.com/users/4708291', 'pm_score': 1, 'selected': False, 'text': '<p><em>Hi, similar to above but using SQL exec.  I could not get the CALL command to work.  You will need to fill in anything that is within square brackets and remove the square brackets.</em>  </p>\n\n<pre>\n   use DBI;\n   #START: SET UP DATABASE AND CONNECT\n   my $host     = \'*[server]*\\\\*[database]*\';\n   my $database = \'*[table]*\';\n   my $user     = \'*[user]*\';\n   my $auth     = \'*[password]*\';\n\n   my $dsn = "dbi:ODBC:Driver={SQL Server};Server=$host;Database=$database";\n   my $dbh = DBI->connect($dsn, $user, $auth, { RaiseError => 1 });\n\n   #END  : SET UP DATABASE AND CONNECT\n   $sql = "exec *[stored procedure name]* *[param1]*,*[param2]*,*[param3]*;";\n   $sth = $dbh->prepare($sql);\n   $sth->execute or die "SQL Error: $DBI::errstr\\n";\n</pre>\n'}]
['https://Stackoverflow.com/questions/64200', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']