How do I return a value from a MySQL stored procedure in DBD::mysql?

MGDFresh used Ask the Experts™

I need to return a value from a stored procedure to use elsewhere in my script.  I have tried all sorts of things including binding, but I am not getting the results.  I have a feeling this is a simple thing, but I would really appreciate your help.

Below is my code.  Thank you.

use DBD::mysql;
use strict;

my $database = 'db'; 
my $dbUsername = 'root'; 
my $dbPassword = ''; 
my $dbHostname = 'hostname'; 
my $db = DBI->connect("DBI:mysql:$database:$dbHostname", $dbUsername, $dbPassword) or die "Could not connect to database: " . DBI->errstr;

						my $q1 = $db->prepare(qw{CALL stored_procedure(1,2,3,'cat','dog',112,'sum','avg',\@RETURNVALUE);	});			
						$q2->execute or $db->disconnect() . die "SQL Error: $DBI::errstr\n";
						my $SPvalue = $q2->fetchrow(); # need to return the @RETURNVALUE from SP!!!
						print "$SPvalue \n";

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nem SchlechtIT Supervisor
Top Expert 2009

This should work - there's nothing special about using stored procs & DBD::mysql *unless* your stored procedure is returning multiple result sets (in that case, you just need to call $sth->more_results.

In your code above, you're statement handle is assigned to $q1, but then you are calling execute() and fechrow() on $q2.

I'm assuming that is not actually the problem, though, correct?
IT Supervisor
Top Expert 2009
Ah, sorry - needed to scroll to the right a little more.  I'm assuming that this stored proc. doesn't actually return anything (by doing any open SELECT()s), but rather just assigns its output to @RETURNVALUE.  However, (again, guessing) you're never *asking* for @RETURNVALUE.

So, you could either modify your stored proc to do just a "SELECT @RETURNVALUE;" right before it exits (your fetrchow() will then actually get a result), or if that is unacceptable, you just need to create another statement handle to go and get it.

$q1->execute() or $db->disconnect() . die "SQL Error: $DBI::errstr\n";
$q2=$db->prepare("SELECT @RETURNVALUE");
$SPvalue = $q2->fetchrow();


Ahh yes, the $q1 / $q2 is a typo in this only.  That is not the issue.  I will edit it as soon as I figure out how.  thanks for point that out.  


Thank you for the quick response.  Your example was accurate and solved my problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial